Всем привет! Решил написать эту статью по одной простой причине — не нашел подробной инструкции по настройке удаленного связанного сервера между серверами teradata и ms sql server.
В Сбербанке данные хранятся на разных серверах: oracle, teradata, mssql. Часто (бывает, по несколько раз день) мне и моим коллегам приходится выполнять действия, которые назвать «эффективными» очень трудно: пишешь запрос, экспортируешь данные в файл, импортируешь его на сервер mssql, работаешь. Временные затраты на один запрос — более 3 часов (около 90 млн строк).
«Эффективная» работа
Причина таких сложных действий — отсутствие прав по созданию таблиц на серверах teradata. Именно поэтому мы «заливаем» данные на ms sql server (тут с правами все хорошо). Это было вступление, а теперь перехожу к описанию последовательности действий.
Примечание: инструкция подготовлена с использованием SQL Server Management Studio 12 и Teradata Client ver 15 (если версия Teradata будет ниже, например 14, то необходимо скачать с официального сайта дополнительное программное обеспечение).
Действие 1 — Настройка подключения ODBC
Заходим:Панель управления\Система и безопасность\Администрирование\Источники данных ODBC
В разделе «Пользовательский DSN» нажимаем «Добавить». Далее выбираем драйвер «Teradata» и заполняем поля.
Важно: запомнить имя нового источника. Оно еще нам понадобится.
Действие 2 — Связанный сервер
Открываем Management Studio — Объекты сервера — связанные сервера — создать связанный сервер.
Поля раздела «General/Общие»: Linked Server — произвольное имя, Provider — Microsoft OLE DB Provider for ODBC Drivers, Product Name — ServiceNow, Data Source — имя источника данных, который создан в «Действии 1».
Связанный сервер раздел «General/Общие»
Поля раздела «Security/Безопасность»: Remote login — имя УЗ на сервере Teradata, With password — пароль УЗ на сервере Teradata.
Связанный сервер раздел «Security/Безопасность»
Чтобы никто не мог войти через связанный сервер (teradata) под моей УЗ, добавлю сопоставление имен входа на локальным и удаленном серверах. Сохраняемся.
Сопоставление имен входа на локальным и удаленном серверах
Открываем новый запрос. Пишем select * from openquery(SERVICENOW, 'select * from dual') и получаем ошибку. Доступ закрыт. Идем дальше.
Пример ошибки
Действие 3 — SQLEXPRESS и встроенная УЗ
Заходим в SQL Configuration Manager — свойства SQL Server (MSSQLSERVER). Меняем галочку на «Использовать для входа встроенную УЗ (Local System)».
SQLEXPRESS и встроенная УЗ
Если запускать Management Studio 12 от имени администратора, то работать будет. Нам нужно не под администратором. Продолжаем настройку.
Действие 4 — SQLEXPRESS и сетевая служба
Заходим в SQL Configuration Manager — свойства SQL Server (MSSQLSERVER). Меняем галочку на «Использовать для входа встроенную УЗ (Network Service)», но под УЗ NT Service\MSSQLSERVER.
SQLEXPRESS и сетевая служба
Должно получиться так.
Диспетчер конфигурации SQL Server
Действие 5 — Служба компонентов
win + R — dcomcnfg — компьютеры — свойства мой компьютер — вкладка свойства по умолчанию.
Заполни по примеру ниже:
Служба компонентов
Действие 6 — Настройка DCOM
win + R — dcomcnfg — компьютеры — мой компьютер — настройка DCOM — свойства msdainitialize. Копируем Application ID/Код приложения. Необходимо для поиска объекта в реестре windows.
Свойства объекта msdainitialize
Дальше win + R — regedit- поиск. Нужно найти в реестре, по коду приложения, объект и войти в его permission/разрешение.
Реестр объекта msdainitialize
Full control для локального администратора ПК.
Свойства объекта msdainitialize в реестре WINDOWS
Подтверди и сохрани. Сделали это для того, чтобы у объекта (код приложения) стал активен раздел Security.
Раздел безопасность объекта msdainitialize
Customize/Настройка — Edit ( кнопка стала активна) — добавляем службе NT Service\MSSQLSERVER полные права.
Полные права для службы MSSQLSERVER
Предоставить права службе нужно в разделах: Launch and Activation Permission, Access Permission, Configuration Permission. Далее необходимо перезапустить службу MSQSQLSERVER — Restart/Перезапустить NT Service\MSSQLSERVER.
Действие 7 COM Security/Безопасность COM
win + R — dcomcnfg — компьютеры — мой компьютер — настройка DCOM
Изменить значение по умолчанию, добавив службу NT Service\MSSQLSERVER. Присвоить ей максимальные права для разделов: Launch and Activation Permission, Access Permission.
Access Permission
Далее необходимо перезапустить службу MSQSQLSERVER — Restart/Перезапустить NT Service\MSSQLSERVER. Запускаем select * from openquery(SERVICENOW, 'select * from dual'). Работает.
Выполнение запроса
Я надеюсь, что данная инструкция поможет с настройками технологии удаленного доступа. Теперь данные импортируем сразу на mssql server (экономя не минуты, а часы). Запрос, который раньше выполнялся около 3 часов (экспорт данных в файл, импорт на mssql), после настройки технологии отрабатывает за 47 секунд (импорт результата запроса сразу на mssql).
В Сбербанке данные хранятся на разных серверах: oracle, teradata, mssql. Часто (бывает, по несколько раз день) мне и моим коллегам приходится выполнять действия, которые назвать «эффективными» очень трудно: пишешь запрос, экспортируешь данные в файл, импортируешь его на сервер mssql, работаешь. Временные затраты на один запрос — более 3 часов (около 90 млн строк).
«Эффективная» работа
Причина таких сложных действий — отсутствие прав по созданию таблиц на серверах teradata. Именно поэтому мы «заливаем» данные на ms sql server (тут с правами все хорошо). Это было вступление, а теперь перехожу к описанию последовательности действий.
Примечание: инструкция подготовлена с использованием SQL Server Management Studio 12 и Teradata Client ver 15 (если версия Teradata будет ниже, например 14, то необходимо скачать с официального сайта дополнительное программное обеспечение).
Действие 1 — Настройка подключения ODBC
Заходим:Панель управления\Система и безопасность\Администрирование\Источники данных ODBC
В разделе «Пользовательский DSN» нажимаем «Добавить». Далее выбираем драйвер «Teradata» и заполняем поля.
Важно: запомнить имя нового источника. Оно еще нам понадобится.
Действие 2 — Связанный сервер
Открываем Management Studio — Объекты сервера — связанные сервера — создать связанный сервер.
Поля раздела «General/Общие»: Linked Server — произвольное имя, Provider — Microsoft OLE DB Provider for ODBC Drivers, Product Name — ServiceNow, Data Source — имя источника данных, который создан в «Действии 1».
Связанный сервер раздел «General/Общие»
Поля раздела «Security/Безопасность»: Remote login — имя УЗ на сервере Teradata, With password — пароль УЗ на сервере Teradata.
Связанный сервер раздел «Security/Безопасность»
Чтобы никто не мог войти через связанный сервер (teradata) под моей УЗ, добавлю сопоставление имен входа на локальным и удаленном серверах. Сохраняемся.
Сопоставление имен входа на локальным и удаленном серверах
Открываем новый запрос. Пишем select * from openquery(SERVICENOW, 'select * from dual') и получаем ошибку. Доступ закрыт. Идем дальше.
Пример ошибки
Действие 3 — SQLEXPRESS и встроенная УЗ
Заходим в SQL Configuration Manager — свойства SQL Server (MSSQLSERVER). Меняем галочку на «Использовать для входа встроенную УЗ (Local System)».
SQLEXPRESS и встроенная УЗ
Если запускать Management Studio 12 от имени администратора, то работать будет. Нам нужно не под администратором. Продолжаем настройку.
Действие 4 — SQLEXPRESS и сетевая служба
Заходим в SQL Configuration Manager — свойства SQL Server (MSSQLSERVER). Меняем галочку на «Использовать для входа встроенную УЗ (Network Service)», но под УЗ NT Service\MSSQLSERVER.
SQLEXPRESS и сетевая служба
Должно получиться так.
Диспетчер конфигурации SQL Server
Действие 5 — Служба компонентов
win + R — dcomcnfg — компьютеры — свойства мой компьютер — вкладка свойства по умолчанию.
Заполни по примеру ниже:
Служба компонентов
Действие 6 — Настройка DCOM
win + R — dcomcnfg — компьютеры — мой компьютер — настройка DCOM — свойства msdainitialize. Копируем Application ID/Код приложения. Необходимо для поиска объекта в реестре windows.
Свойства объекта msdainitialize
Дальше win + R — regedit- поиск. Нужно найти в реестре, по коду приложения, объект и войти в его permission/разрешение.
Реестр объекта msdainitialize
Full control для локального администратора ПК.
Свойства объекта msdainitialize в реестре WINDOWS
Подтверди и сохрани. Сделали это для того, чтобы у объекта (код приложения) стал активен раздел Security.
Раздел безопасность объекта msdainitialize
Customize/Настройка — Edit ( кнопка стала активна) — добавляем службе NT Service\MSSQLSERVER полные права.
Полные права для службы MSSQLSERVER
Предоставить права службе нужно в разделах: Launch and Activation Permission, Access Permission, Configuration Permission. Далее необходимо перезапустить службу MSQSQLSERVER — Restart/Перезапустить NT Service\MSSQLSERVER.
Действие 7 COM Security/Безопасность COM
win + R — dcomcnfg — компьютеры — мой компьютер — настройка DCOM
Изменить значение по умолчанию, добавив службу NT Service\MSSQLSERVER. Присвоить ей максимальные права для разделов: Launch and Activation Permission, Access Permission.
Access Permission
Далее необходимо перезапустить службу MSQSQLSERVER — Restart/Перезапустить NT Service\MSSQLSERVER. Запускаем select * from openquery(SERVICENOW, 'select * from dual'). Работает.
Выполнение запроса
Я надеюсь, что данная инструкция поможет с настройками технологии удаленного доступа. Теперь данные импортируем сразу на mssql server (экономя не минуты, а часы). Запрос, который раньше выполнялся около 3 часов (экспорт данных в файл, импорт на mssql), после настройки технологии отрабатывает за 47 секунд (импорт результата запроса сразу на mssql).