Создание связанных серверов (ядро СУБД SQL Server)

  • Статья

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

В этой статье показано, как создать связанный сервер и получить доступ к данным из другого SQL Server, Управляемый экземпляр SQL Azure или другого источника данных с помощью SQL Server Management Studio (SSMS) или Transact-SQL. Связанные серверы позволяют ядру СУБД SQL Server и управляемым экземплярам SQL Azure выполнять чтение данных из удаленных источников данных и выполнять команды на удаленных серверах баз данных (например, источниках данных OLE DB) за пределами экземпляра SQL Server.

История

Связанные серверы настроены на включение ядра СУБД для выполнения инструкции Transact-SQL, включающей таблицы в другом экземпляре SQL Server или другом продукте для работы с базами данных, например Oracle. В качестве связанных серверов можно настроить источники данных многих типов, в том числе сторонних поставщиков баз данных и Azure Cosmos DB.

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

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

Разрешения

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

Создание связанного сервера с помощью SSMS

Порядок создания связанного сервера с помощью SSMS:

Открытие диалогового окна "Создание связанного сервера"

В SQL Server Management Studio (SSMS):

  • Откройте обозреватель объектов.
  • Разверните узел Объекты сервера.
  • Щелкните правой кнопкой мыши пункт Связанные серверы.
  • Выберите Создать связанный сервер.

Изменение свойств связанного сервера на странице "Общие"

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

Примечание

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

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

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

  • Другой источник данных
    Укажите тип сервера 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 , либо имя входа с проверкой подлинности Windows. Использование группы Windows не поддерживается. Используйте этот список для разрешения соединений только определенным именам входа или для разрешения некоторым именам входа подключаться в качестве другого имени входа.

Примечание

Распространенные проблемы со связанными серверами при использовании проверки подлинности Windows для удаленного экземпляра SQL Server возникают из-за проблем с именами субъектов-служб. Дополнительные сведения см. в статье Поддержка имени субъекта-службы в клиентских соединениях. Диспетчер конфигурации Microsoft Kerberos для SQL Server — это диагностическое средство, которое помогает устранять неполадки Kerberos, связанные с проблемами подключения при использовании SQL Server. Дополнительные сведения см. в разделе Диспетчер конфигурации Microsoft Kerberos для SQL Server.

  • Выберите Олицетворение (необязательно).

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

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

  • Укажите Удаленный пользователь, если олицетворение не используется.

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

  • Укажите Удаленный пароль, если олицетворение не используется.

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

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

Укажите контекст безопасности по умолчанию для имен входа, отсутствующих в списке сопоставления

В среде с доменами, где пользователи соединяются с помощью имен входа доменов, лучшим вариантом часто оказывается Выполнять с использованием текущего контекста безопасности имени входа. Если пользователи соединяются с исходным экземпляром 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).

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

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

Просмотр или изменение параметров поставщика связанного сервера в SSMS

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

  • Откройте обозреватель объектов.
  • Разверните узел Объекты сервера.
  • Разверните узел Связанные серверы.
  • Разверните узел Поставщики.
  • Щелкните правой кнопкой мыши поставщик и выберите пункт Свойства.

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

  • Динамический параметр
    Указывает, что поставщик разрешает использовать синтаксис маркеров параметров «?» для параметризованных запросов. Установите этот параметр только в том случае, если поставщик поддерживает интерфейс 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

Чтобы создать связанный сервер с помощью Transact-SQL, используйте инструкции sp_addlinkedserver (Transact-SQL),CREATE LOGIN (Transact-SQL) и sp_addlinkedsrvlogin (Transact-SQL).

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

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

    USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver   
    @server = N'SRVR002\ACCTG',   
    @srvproduct=N'SQL Server';  
GO

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

    EXEC master.dbo.sp_addlinkedsrvlogin   
    @rmtsrvname = N'SRVR002\ACCTG',   
    @locallogin = NULL ,   
    @useself = N'True';  
GO

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

Ниже представлен порядок выполнения проверки связанного сервера.

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

Для проверки подлинности связанного сервера в текущем контексте безопасности можно использовать один из следующих двух подходов.

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

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

    SELECT name FROM [SRVR002\ACCTG].master.sys.databases;  
GO

Присоединение таблиц со связанного сервера

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

SELECT local.name AS LocalLogins, linked.name AS LinkedLogins  
FROM master.sys.server_principals AS local  
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked  
     ON local.name = linked.name;  
GO

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

Связанные серверы с Управляемым экземпляром SQL Azure

Если вы используете Управляемый экземпляр SQL Azure, см. следующие примеры из статьи sp_addlinkedserver (Transact-SQL):

Следующие шаги

Дополнительные сведения об управлении связанными серверами см. в следующих статьях: