Как настроить linked server

Как создать связанный сервер (Linked Server) в Microsoft SQL Server

Приветствую Вас на сайте Info-Comp.ru! Сегодня я расскажу Вам о том, как можно создать связанный сервер в Microsoft SQL Server, а также каким образом мы можем обращаться к этому серверу.

Итак, в прошлом материале мы выяснили, что связанный сервер (Linked Server) – это объект на SQL Server, который хранит подключение к внешнему источнику данных. И к данному объекту мы можем обращаться и выполнять распределённые запросы к разнородным источникам данных, которые расположены за пределами SQL Server.

Иными словами, связанный сервер в Microsoft SQL Server обладает примерно такой же функциональностью, что и конструкции OPENDATASOURCE и OPENROWSET, но в случае с Linked Server нам не нужно непосредственно в запросе указывать строку подключения к источнику данных.

Создание связанного сервера в Microsoft SQL Server

Создать связанный сервер в Microsoft SQL Server можно следующим образом:

• Используя инструкции T-SQL;
• Используя графический интерфейс среды SQL Server Management Studio.

Сегодня мы рассмотрим оба способа.

Исходные данные для примеров

Давайте представим, что у нас есть файл Excel, к которому нам нужно периодически обращаться в своих запросах, поэтому мы решили создать связанный сервер для удобства.

Файл Excel мы сохранили на диск D и он содержит следующие данные.

Подготовка к созданию связанного сервера

Чтобы подключаться к внешним источникам данных и выполнять распределенные запросы, на сервере должен быть установлен соответствующий провайдер.

В нашем случае мы будем использовать провайдер Microsoft.ACE.OLEDB.12.0 x64.

Поэтому перед тем как переходить к созданию связанного сервера, нам необходимо убедиться в том, что данный провайдер у нас установлен, так как в противном случае примеры, которые мы рассмотрим ниже, работать не будут.

Чтобы проверить, есть ли у нас этот провайдер, мы можем запустить следующую процедуру или в обозревателе объектов открыть контейнер «Объекты сервера -> Связанные серверы -> Поставщики» и посмотреть там.

В случае если у Вас в списке нет нужного провайдера, Вам необходимо скачать его с официального сайта Microsoft и установить.

Создание связанного сервера на T-SQL

Для создания и управления связанными серверами в Microsoft SQL Server существуют специальные хранимые процедуры:

• sp_addlinkedserver – процедура создания связанного сервера;
• sp_addlinkedsrvlogin – процедура настройки безопасности связанного сервера.

Таким образом, чтобы создать связанный сервер, нам необходимо выполнить следующие инструкции.

В результате выполнения данных инструкций будет создан связанный сервер, источником которого будет выступать файл Excel, а также будут выполнены определённые настройки для авторизации на данном сервере.

В обозревателе объектов отобразится данный сервер.

Описание параметров процедуры sp_addlinkedserver

• @server – название связанного сервера;
• @srvproduct – название продукта;
• @provider – провайдер (поставщик);
• @datasrc – источник данных;
• @provstr – строка поставщика для подключения.

Описание параметров процедуры sp_addlinkedsrvlogin

• @rmtsrvname – название связанного сервера;
• @useself – указывает, как будет происходить авторизация, с указанием логина и пароля, либо с использованием сопоставления с контекстом безопасности;
• @locallogin – имя входа на локальный сервер;
• @rmtuser – удаленное имя входа, используемое для подключения к связанному серверу;
• @rmtpassword – пароль для удаленного имени входа.

Создание связанного сервера с помощью SQL Server Management Studio

Все то же самое, что мы сделали чуть выше с помощью инструкций T-SQL, мы можем выполнить и в графическом интерфейсе среды SQL Server Management Studio.

Для этого нажмите на контейнер «Связанные серверы» правой кнопкой мыши и выберите «Создать связанный сервер».

Затем в открывшемся окне внесите соответствующие данные для подключения (данные соответствуют параметрам процедуры sp_addlinkedserver).

Чтобы сделать точно такую же авторизацию, которую мы сделали в примере с использованием процедуры sp_addlinkedsrvlogin, необходимо перейти на вкладку «Безопасность» и выбрать пункт «Устанавливать с использованием текущего контекста безопасности имени для входа».

После этого нажать «ОК», и точно такой же связанный сервер с файлом Excel будет создан.

Использование связанного сервера в Microsoft SQL Server

Теперь, когда связанный сервер создан, мы можем к нему обращаться и получать данные как из обычных таблиц или представлений, не указывая никаких данных для подключения к источнику.

При этом обратиться к связанному серверу мы можем с помощью двух способов:

• Используя функцию OPENQUERY (рекомендованный способ);
• Используя полное имя объекта.

Обращение к связанному серверу с помощью OPENQUERY

OPENQUERY – эта функция, с помощью которой можно обратиться к связанному серверу и выполнить указанный SQL запрос.

На эту функцию можно даже ссылаться в инструкциях по модификации данных, т.е. мы можем изменять данные на связанном сервере.

В случаях, когда необходимо получить данные из связанного сервера, функцию OPENQUERY нужно указывать в секции FROM и в качестве первого параметра указывать название связанного сервера, а в качестве второго — SQL запрос, который необходимо выполнить на связанном сервере.

Для примера давайте обратимся к связанному серверу и получим данные, которые хранятся в нашем тестовом файле Excel.

Обращение к связанному серверу с помощью указания полного имени объекта

Как было отмечено, обратиться к связанному серверу мы можем не только с помощью функции OPENQUERY, но и путем простого обращения к нему как к объекту, указав полное имя источника данных, в нашем случае это таблица Excel. Ведь связанный сервер – это объект на сервере, поэтому, соответственно, мы можем к нему обращаться. Это делается следующим образом.

Как видим, результат у нас точно такой же.

Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.

В следующих материалах я подробно расскажу, как удалить связанный сервер, а на сегодня это все, надеюсь, статья была Вам интересна и полезна, удачи!

Источник

MSSQL Server. Пример применения связанного сервера

Сегодня решил поделиться статьей как однажды мне пришел на выручку связанный сервер при работе с MSSQL. Сначала опишу ситуацию, в которой мне пришлось с ним познакомиться.

Я работал web программистом в информационном центре одного из министерств с около сотней подведомственных учреждений. В каждом подведомственном учреждении на сервере была установлена десктоп программа, написанная на delphi, в которую ежедневно вносились данные. Раз в квартал каждому такому учреждению нужно было выгрузить dbf файл, приехать к нам в центр, по данным этой выгрузки получить отчеты и сдать их в министерство. Так было еще в досовской программе, а потом этот алгоритм просто ничего не меняя, перенесли в delphi. Выгрузка осуществлялась средствами Transact-SQL, и логика в ней была не простая.

Параллельно с этим в оффлайн режиме работала шина, которая скапливала данные со всех учреждений на единый центральный сервер. В шине были багги: она создавала дубли по первичному ключу и не все данные доходили. Конкретного алгоритма по исправлению этих ошибок не было, этим занимались разные сотрудники в разные периоды времени, не ставя друг друга в известность. Разработчик шины уволился. Через три года такой работы данные на центральном сервере значительно отличались от данных на серверах учреждений, однако все официально придерживались версии, что с шиной проблем нет.

В один момент схему с выгрузкой файла посчитали устаревшей, и были выделены деньги на доработку. Было решено сдавать отчеты на сайте в личном кабинете по нажатию кнопки. Теперь сотрудники учреждений не должны были ездить для сдачи отчета сначала к нам и потом в министерство, все общение должно было осуществляться через сайт. Данные нужно было брать с центрального сервера. Начальник отдела, не смотря на то, что знал про проблемы с шиной, велел взять процедуру, что работала на серверах учреждений, в выборке добавить условие учитывающее сегмент учреждений, и реализовать выгрузку отчетов на сайте с центрального сервера. После того как это было сделано, он назначил меня ответственным за данный процесс, а руководство официально объявило, что отчеты в министерство сдаем по новой схеме.

Все рухнуло. Из-за расхождения данных между серверами отчеты были с неверными цифрами. Так же все легло в плане производительности, мы не были готовы к такой нагрузке. От меня требовалось быстрое решение проблемы. Вариант просто прописать у себя на сайте для каждого учреждения параметры подключения к их БД и запускать процедуру у них на сервере (средствами языка программирования) не подходил, так как помимо получения данных нужно было каждый раз запускать обработку для конвертации этих данных в отчеты. Процедура обработки уже была реализована и отлажена в mssql на центральном сервере, а перенос ее в язык программирования занял бы много ресурсов и времени. Нужно было справляться средствами БД.

Погуглив я нашел информацию, что в MSSQL существуют связанные серверы. С помощью них для своего сервера я мог настроить связь с любым удаленным сервером, который в одной сети с моим и от которого у меня есть авторизационные данные. После настройки я мог на своем сервере написать запрос, указать на каком связанном сервере его нужно выполнить, и запрос выполнялся на удаленном сервере, с использованием его баз данных и его ресурсов.

Для создания связанного сервера нужно выполнить скрипт:

server – имя сервера, по которому мы будем к нему обращаться
@datasrc – ip адрес удаленного сервера

Параметры авторизации сервера

@rmtsrvname – имя, которое мы назначили серверу
@locallogin – имя учетной записи
@rmtpassword – пароль учетной записи
@rmtuser – пользователь БД

При создании связанного сервера часть параметров по доступу к данным проставляется в значение ‘false’ (список параметров вы можете посмотреть тут ). Если вам нужно, какие-то параметры установить в значение ‘true’, например ‘rpc’и ‘rpc out’, то к скрипту создания нужно добавить следующие команды:

Обратите внимание, что в параметре server мы указали то имя, которое мы дали связанному серверу.

В итоге скрипт создания связанного сервера целиком выглядел бы так

Запрос к созданному серверу выполняется, так же как и к своему, но в начале указывается префикс с именем связанного сервера. Так же при обращении нужно указывать имя схемы (в примере ниже схема называется ‘DBO’):

В общем, техническая поддержка в течение пары дней для всех учреждений прописала связанные сервера. Я дописал код, чтобы данные получались с серверов учреждений. Цифры в отчетах стали вновь верными и вопрос производительности решился. Вот так я быстро и легко вышел из сложной ситуации.

Конечно, изначально при разработке системы не стоит закладываться на связанные сервера для реализации описанной функциональности. Лучше изначально грамотно подойти к проектирование системы, например сделав ее на web, где будет один центральный сервер а чтобы все не тормозило держать в штате специалистов разбирающихся в оптимизации баз данных. Данный пример для случаев, когда система уже спроектирована, и переделать ее вряд ли получится. Так же связанный сервер будет полезен при выверке отчетов, в случае если данные есть только на продуктивном сервере, а менять хранимую процедуру можно только на сервере разработки.

Источник

Создание связанных серверов (компонент SQL Server Database Engine)

Применимо к: SQL Server (все поддерживаемые версии) Управляемый экземпляр SQL Azure

В этом разделе описано, как создать связанный сервер и производить доступ к данным из другого экземпляра SQL Server с помощью среды SQL Server Management Studio или Transact-SQL. Путем создания связанного сервера вы можете работать с данными из нескольких источников. Связанный сервер не обязательно должен быть другим экземпляром SQL Server, хотя такой вариант часто встречается.

Историческая справка

Связанные серверы позволяют выполнять распределенные разнородные запросы к источникам данных OLE DB. После создания связанного сервера можно выполнять распределенные запросы к этому серверу, причем в запросах могут соединять таблицы из нескольких источников данных. Если связанный сервер определен в качестве экземпляра SQL Server, на нем могут выполняться удаленные хранимые процедуры.

Возможности связанного сервера и необходимые аргументы могут сильно различаться. В примерах из этого раздела представлены типичные ситуации, но описаны не все параметры. Дополнительные сведения см. в статье sp_addlinkedserver (Transact-SQL).

безопасность

Разрешения

При использовании инструкций Transact-SQL требуется разрешение ALTER ANY LINKED SERVER на сервер или членство в предопределенной роли сервера setupadmin . Для работы с Среда Management Studio требуется разрешение CONTROL SERVER или членство в предопределенной роли сервера sysadmin .

Создание связанного сервера

Можно использовать следующие параметры.

Использование среды SQL Server Management Studio

Создание связанного сервера для другого экземпляра SQL Server в среде SQL Server Management Studio

В среде SQL Server Management Studioоткройте обозреватель объектов, разверните узел Объекты сервера, щелкните правой кнопкой мыши узел Связанные серверы и выберите команду Создать связанный сервер.

На странице Общие в поле Связанный сервер введите имя экземпляра SQL Server , с которым связывается область.

SQL Server
Идентификация связанного сервера как экземпляра MicrosoftSQL Server. При использовании этого метода определения связанного сервера SQL Server имя, указанное в поле Связанный сервер , должно быть сетевым именем этого сервера. Кроме того, все таблицы, полученные от сервера, будут получены из базы данных, по умолчанию определенной для имени входа на связанный сервер.

Другой источник данных
Укажите тип сервера OLE DB, отличный от SQL Server. Включение этой функции активирует дополнительные параметры, расположенные под ней.

Поставщик
Выберите источник данных OLE DB в окне списка. Поставщик OLE DB зарегистрирован в реестре с данным идентификатором PROGID.

Название продукта
Введите название продукта для источника данных OLE DB, который добавляется в качестве связанного сервера.

Источник данных
Введите имя источника данных согласно интерпретации поставщика OLE DB. При соединении с экземпляром служб SQL Serverуказывается имя экземпляра.

Строка поставщика
Введите уникальный программный идентификатор (PROGID) поставщика OLE DB, соответствующий источнику данных. Примеры допустимых строк поставщиков см. в статье sp_addlinkedserver (Transact-SQL).

Местоположение
Введите местонахождение базы данных, понятное поставщику OLE DB.

Каталог
Введите имя каталога, который следует использовать при соединении с поставщиком OLE DB.

Чтобы проверить возможность соединения со связанным сервером, щелкните его в обозревателе объектов правой кнопкой мыши и выберите команду Проверить соединение.

Если экземпляр SQL Server является экземпляром по умолчанию, то введите имя компьютера, на котором размещается экземпляр SQL Server. Если экземпляр SQL Server является именованным, введите имя компьютера и имя экземпляра, например Accounting\SQLExpress.

В области Тип сервера выберите SQL Server, чтобы показать, что связанный сервер является экземпляром SQL Server.

На странице Безопасность укажите контекст безопасности, который будет использоваться при подключении исходного экземпляра SQL Server к связанному серверу. В среде с доменами, где пользователи соединяются с помощью имен входа доменов, лучшим вариантом часто оказывается Выполнять с использованием текущего контекста безопасности имени входа. Если пользователи соединяются с исходным экземпляром SQL Server по имени входа SQL Server , то лучшим вариантом часто оказывается С использованием этого контекста безопасности с последующим указанием необходимых учетных данных для проверки подлинности на связанном сервере.

Локальное имя входа
Указывает локальное имя входа, с помощью которого может осуществляться соединение со связанным сервером. Локальное имя входа может представлять собой либо имя входа с использованием проверки подлинности SQL Server , либо имя входа с проверкой подлинности Windows. Используйте этот список для разрешения соединений только определенным именам входа или для разрешения некоторым именам входа подключаться в качестве другого имени входа.

Impersonate
Передает имя пользователя и пароль из локального имени входа на связанный сервер. Для проверки подлинности SQL Server на удаленном сервере должны существовать учетные данные входа с тем же самым именем и паролем. Для имен входа Windows имя входа должно быть допустимым на связанном сервере.

Чтобы использовать олицетворение, конфигурация должна соответствовать требованиям, предъявляемым к делегированию.

Удаленный пользователь
Сопоставьте удаленного пользователя c пользователями, не определенными в локальном имени входа. Удаленный пользователь на удаленном сервере должен представлять собой имя входа для проверки подлинности SQL Server .

Только пользователь SQL Server может использоваться как «удаленный пользователь» в развертывании управляемого экземпляра.

Пароль для удаленного входа
Указывает пароль удаленного пользователя.

Добавление
Добавляет новое локальное имя входа.

Удалить
Удаляет существующее локальное имя входа.

Не выполнять
Указывает, что для имен входа, не определенных в списке, соединение невозможно.

Выполняется без использования контекста безопасности
Указывает, что для имен входа, не определенных в списке, соединение будет выполняться без использования контекста безопасности.

Выполняется с использованием текущего контекста безопасности имени входа
Указывает, что для имен входа, не определенных в списке, соединение будет выполняться с использованием текущего контекста безопасности имени входа. Если установлено подключение к локальному серверу с использованием проверки подлинности Windows, то для подключения к удаленному серверу будут использоваться учетные данные Windows. При наличии соединения с локальным сервером с использованием проверки подлинности SQL Server для подключения к удаленному серверу будут использоваться имя входа и пароль. В этом случае на удаленном сервере должны существовать учетные данные входа с теми же именем и паролем.

Выполнять с использованием данного контекста безопасности
Указывает, что для имен входа, не определенных в списке, соединение будет выполняться при помощи имени входа и пароля, заданных в полях Удаленный вход и С паролем . Удаленное имя входа на удаленном сервере должно представлять собой имя входа для проверки подлинности SQL Server .

Для просмотра и установки параметров сервера можно также открыть страницу Параметры сервера .

Совместимые параметры сортировки
Влияет на выполнение распределенных запросов на связанных серверах. Если этот параметр установлен в значение true, то SQL Server предполагает, что все символы в связанном сервере совместимы с локальным сервером в зависимости от набора символов и параметров сортировки (или порядка сортировки). Это позволяет SQL Server отправлять поставщику сравнения по символьным столбцам. Если этот параметр не задан, SQL Server всегда выполняет сравнения по символьным столбцам локально.

Этот параметр необходимо задать только в том случае, если источник данных, соответствующий связанному серверу, имеет тот же набор символов и тот же порядок сортировки, что и локальный сервер.

Доступ к данным
Разрешает и запрещает доступ распределенных запросов к связанному серверу.

RPC
Включает RPC с определенного сервера.

RPC Out
Включает RPC на определенный сервер.

Использовать параметры сортировки удаленного сервера
Определяет, будут ли использоваться параметры сортировки удаленного столбца или локального сервера.

Если значение равно true, в источниках данных SQL Server используются параметры сортировки удаленных столбцов, а в не-SQL Server источниках данных — режим, заданный в имени параметров сортировки.

Если значение равно false, при распределенных запросах всегда будут использоваться установленные по умолчанию параметры сортировки на локальном сервере, в то время как имя параметров сортировки и параметры сортировки удаленных столбцов будут пропускаться. Значение по умолчанию — false.

Имя параметров сортировки
Позволяет задать имя параметров сортировки, используемое удаленным источником данных, если значение параметра «Использовать параметры сортировки удаленного сервера» равно true, а источник данных не является источником данных SQL Server . Этот имя должно быть одним из параметров сортировки, поддерживаемых SQL Server.

Этот параметр используется при доступе к источнику данных OLE DB, отличному от SQL Server, параметры сортировки которого совпадают с одним из параметров сортировки SQL Server .

Связанный сервер должен поддерживать использование единых параметров сортировки для всех столбцов на этом сервере. Не задавайте этот параметр, если связанный сервер поддерживает несколько параметров сортировки для одного источника данных, или если невозможно определить, соответствуют ли параметры сортировки связанного сервера одному из параметров сортировки SQL Server .

Время ожидания соединения
Значение времени ожидания соединения со связанным сервером.

Если значение равно 0, используется значение, заданное через sp_configure по умолчанию, — remote login timeout .

Время ожидания запроса
Значение времени ожидания для запросов к связанному серверу, в секундах.

Если значение равно 0, используется значение, заданное через sp_configure по умолчанию, — remote query timeout .

Разрешить продвижение распределенных транзакций
Используйте этот параметр, чтобы защитить действия процедуры между серверами посредством транзакции координатора распределенных транзакций (Майкрософт) ( Microsoft DTC). Если этот параметр имеет значение TRUE, то вызов удаленной хранимой процедуры приводит к запуску распределенной транзакции и прикрепляет к выполнению транзакции MS DTC. Дополнительные сведения см. в статье sp_serveroption (Transact-SQL).

Нажмите кнопку ОК.

Просмотр параметров поставщика

Чтобы просмотреть доступные параметры поставщика, откройте страницы Параметры поставщиков .

У всех поставщиков нет общего набора доступных параметров. Например, некоторые типы данных могут быть индексированы, а некоторые нет. Используйте это диалоговое окно, чтобы ознакомить службы SQL Server с возможностями поставщика. SQL Server устанавливает несколько общих поставщиков данных, однако при изменении продукта, поставляющего данные, поставщик, установленный с помощью SQL Server , может не поддерживать все новейшие функции. Лучшим источником сведений о возможностях продукта, поставляющего данные, является документация по продукту.

Динамический параметр
Указывает, что поставщик разрешает использовать синтаксис маркеров параметров «?» для параметризованных запросов. Установите этот параметр только в том случае, если поставщик поддерживает интерфейс ICommandWithParameters и символ «?» в качестве маркера параметров. Установка этого параметра позволяет SQL Server выполнять параметризованные запросы к поставщику. Возможность выполнять параметризованные запросы к поставщику может повысить производительность некоторых запросов.

Вложенные запросы
Указывает, что поставщик разрешает вложенные инструкции SELECT в предложении FROM. Установка этого параметра позволяет SQL Server делегировать поставщику определенные запросы, требующие вложенных инструкций SELECT в предложении FROM.

Только нулевой уровень
Для поставщика вызываются только интерфейсы OLE DB уровня 0.

Допускать в ходе процесса
SQL Server разрешает создание экземпляра поставщика в виде внутрипроцессного сервера. Если этот параметр не установлен, поведением по умолчанию является создание экземпляра поставщика вне процесса SQL Server . Создание экземпляра поставщика вне процесса SQL Server защищает процесс SQL Server от ошибок в поставщике. Если экземпляр поставщика создается вне процесса SQL Server , обновления или вставки, ссылающиеся на длинные столбцы (text, ntext или image), не разрешаются.

Обновления без использования транзакций
SQL Server разрешает обновления, даже если недоступен интерфейс ITransactionLocal . Если этот параметр включен, обновления поставщика необратимы, поскольку этот поставщик не поддерживает транзакции.

Индекс в качестве пути доступа
SQL Server пытается использовать индексы поставщика для выборки данных. По умолчанию индексы используются только для метаданных и никогда не открываются.

Запретить нерегламентированный доступ
SQL Server не разрешает нерегламентированный доступ с помощью функций OPENROWSET и OPENDATASOURCE к поставщику OLE DB. Если этот параметр не задан, SQL Server также не разрешает нерегламентированный доступ.

Поддерживает оператор Like.
Указывает, что поставщик поддерживает запросы с использованием ключевого слова LIKE.

Использование Transact-SQL

Создание связанного сервера для другого экземпляра SQL Server с помощью Transact-SQL

В редакторе запросов введите следующую команду Transact-SQL , чтобы установить связь с экземпляром SQL Server с именем SRVR002\ACCTG :

Выполните следующий код, чтобы настроить связанный сервер для использования учетных данных домена для имени входа, которое использует связанный сервер.

Дальнейшие действия после создания связанного сервера

Проверка связанного сервера

Выполните следующий код, чтобы проверить соединение со связанным сервером. Этот пример возвращает имена баз данных на связанном сервере.

Создание запроса, соединяющего таблицы со связанного сервера

Для ссылки на объект, расположенный на связанном сервере, используйте четырехкомпонентные имена. Выполните следующий код, чтобы получить список всех имен входа на локальном сервере и соответствующих имен входа на связанном сервере.

Если для имени входа связанного сервера возвращается значение NULL, это значит, что имя входа не существует на связанном сервере. Такие имена входа не смогут использовать связанный сервер, если на нем не настроена передача другого контекста безопасности и он не принимает анонимные подключения.

Источник