SQL-Ex blog

При установке связанного сервера нужно знать несколько важных настроек. Здесь мы обсудим создание связанного сервера с другим экземпляром SQL Server, используя SSMS GUI и соответствующие опции безопасности. Будет показано, как использовать связанный сервер в операторах T-SQL. Имеется отличная библиотека более продвинутых статей, которые рекомендуются в прочтению после освоения основ.

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

В SQL Server Management Studio в панели Object Explorer разверните раздел "Server Objects", выполните щелчок правой кнопкой на "Linked Servers" (связанные серверы) и выберите в меню "New Linked Server…" (новый связанный сервер...).

Новый связанный сервер - страница General

Появится экран, который выглядит как на картинке ниже. Выберите типом сервера SQL Server, и впечатайте имя другого экземпляра SQL Server. В данном примере будет выполнено соединение с именованным экземпляром SQL Server на той же машине. Это допускается, но обычно соединение выполняется к совсем другой машине. Имя связанного сервера указывается в форме ИмяСервера\ИмяЭкземпляра. В примере ниже я подключаюсь к локальному серверу и экземпляру с именем "SECURITY_TEST".

Новый связанный сервер - страница Security

Затем переходим со страницы Ganeral на страницу Security, используя меню слева. Эта страница позволяет администратору сказать SQL Server как авторизоваться на другом экземпляре. В верхней половине экрана можно предоставить отдельным логинам доступ к использованию связанного сервера.

Есть два метода авторизации пользователей. При первом методе для них выбирается логин, который используется на связанном сервере. На скриншоте ниже пользователь Eric будет автоматически заходить на вторичный сервер под логином LinkedServerTest на другом экземпляре. Эти учетные данные сохраняются в ядре SQL Server и шифруются. Только для второй строки, логин LinkedServerTest (логин SQL), устанавливается флажок Impersonate. Для аккаунта SQL это сообщает SQL Server пытаться заходить с использованием того же имени и пароля на связанном сервере, что и на исходном сервере. Сопоставление логина Windows также может быть выполнено, но требует надлежащих Kerberos, SPN и установки надлежащих правил делегирования на домене.



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

Новый связанный сервер - страница Server Options

Еще есть установки на странице Server Options, но для простых запросов эти установки можно оставить со значениями по умолчанию, которые показаны ниже.



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

Запрос данных со связанного SQL Server

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

Например,

SELECT * FROM [.\SECURITY_TEST].master.sys.databases

SELECT * FROM [.\SECURITY_TEST].WideWorldImporters.Sales.Orders	

• Сначала идет имя связанного сервера, которым в нашем примере является [.\SECURITY_TEST]. В этом примере следует использовать квадратные скобки из-за формата. Не все имена должны быть в скобках.


• Затем идет имя базы данных - master и WideWorldImporters, соответственно.


• Третья часть - это имя схемы, sys и Sales, соответственно.


• Наконец, следует имя объекта. В этих примерах объектами являются databases и Orders.

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

Однажды упомянутые в предложении FROM эти таблицы и представления могут обрабатываться точно так же, как и любая другая локальная таблица. К ним можно обращаться в запросах, давать алиасы, соединять и, если даны соответствующие разрешения, они могут модифицироваться с помощью операторов insert, update и delete.

Вызов хранимых процедур на связанном сервере

Для демонстрации на целевом связанном сервере в базе данных master была создана эта небольшая процедура.

USE master

GO

CREATE PROCEDURE dbo.SQLTips1 

AS 

SELECT 'I was able to be called'; 

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

EXEC [.\Security_Test].master.dbo.SQLTips1

Однако вместо ожидаемого результата возникает ошибка.



RPC - это сокращение от вызова удаленных процедур. По умолчанию связанные серверы не могут вызывать хранимые процедуры. Это легко исправить, включив единственную установку на связанном сервере. Откройте свойства связанного сервера, выберите Server Options и четвертая опция в списке должна быть RPC Out. Просто измените ее значение на True и щелкните ОК.

Установку с надписью RPC менять НЕ нужно. Это может смутить, поскольку в сообщении об ошибке указано именно это название, но фактически это унаследованная установка, которая не включает вызовы RPC.



Повторное выполнение процедуры теперь даст значительно лучший результат.

Ограничения связанного SQL Server

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

Случаи использования связанного SQL Server

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

Иногда требованиям ETL для небольших наборов данных можно удовлетворить, используя связанный сервер, а не ETL-инструмент, подобный SSIS. Когда число задействованных строк измеряется тысячами и меньше десятков тысяч, ETL на базе связанного сервера может потребовать меньше времени, чем для движка SSIS.