Как создать связанный сервер (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
Исходные данные для примеров
Подготовка к созданию связанного сервера
Создание связанного сервера на T-SQL
Создание связанного сервера с помощью SQL Server Management Studio
Использование связанного сервера в Microsoft SQL Server
Обращение к связанному серверу с помощью OPENQUERY
Обращение к связанному серверу с помощью указания полного имени объекта

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

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

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

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

Заметка! Обзор функционала SQL Server Management Studio (SSMS).

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

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

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

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

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

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

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

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

   
   EXEC sp_enum_oledb_providers;

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

Ссылка на скачивание – https://www.microsoft.com/en-us/download/details.aspx?id=13255

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

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

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

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

   
   --Создание связанного сервера
   EXEC sp_addlinkedserver @server = 'TEST_EXCEL',
                           @srvproduct = 'Excel',
                           @provider = 'Microsoft.ACE.OLEDB.12.0',
                           @datasrc = 'D:\TestExcel.xlsx',
                           @provstr = 'Excel 12.0;IMEX=1;HDR=YES;';
   
   --Настройки безопасности (авторизации)
   EXEC sp_addlinkedsrvlogin @rmtsrvname='TEST_EXCEL',
                             @useself= 'False',
                             @locallogin=NULL,
                             @rmtuser=NULL,
                             @rmtpassword=NULL;

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

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

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

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

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

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

Заметка! Транзакции в T-SQL – основы для новичков с примерами.

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

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

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

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

Заметка! Статистика в Microsoft SQL Server – что это такое и для чего она нужна.

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

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

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

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

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

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

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

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

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

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

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

   
   SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM [Лист1$]');

Заметка! Назначение хранимых процедур в языке T-SQL.

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

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

   
   SELECT * FROM TEST_EXCEL...[Лист1$];

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

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

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