Связать два сервера sql
Обновлено: 19.04.2024
Создает связанный сервер. Связанные серверы позволяют выполнять распределенные разнородные запросы к источникам данных OLE DB. После создания связанного сервера с помощью sp_addlinkedserver распределенные запросы могут выполняться на этом сервере. Если связанный сервер определен в качестве экземпляра SQL Server, на нем могут выполняться удаленные хранимые процедуры.
Синтаксис
Аргументы
[ @server =] ' сервер '
Имя создаваемого связанного сервера. Аргумент Server имеет тип sysname и не имеет значения по умолчанию.
[ @srvproduct =] ' product_name '
Введите название продукта для источника данных OLE DB, который добавляется в качестве связанного сервера. Значение product_name имеет тип nvarchar (128) и значение по умолчанию NULL. если значение равно SQL Server, то provider_name, data_source, расположение, provider_string и каталог не должны быть указаны.
[ @provider =] ' provider_name '
Введите уникальный программный идентификатор (PROGID) поставщика OLE DB, соответствующий этому источнику данных. Provider_name должны быть уникальными для указанного поставщика OLE DB, установленного на текущем компьютере. Значение provider_name имеет тип nvarchar (128) и значение по умолчанию NULL. Однако если provider_name опущен, используется sqlncli.
Предыдущие поставщики Microsoft OLE DB для SQL Server (SQLOLEDB) и собственный клиент OLE DB для SQL Server (SQLNCLI) объявляются нерекомендуемыми для новых разработок. Вместо этого используйте новый драйвер Microsoft OLE DB для SQL Server (MSOLEDBSQL), который будет обновлен с самыми последними серверными компонентами.
[ @datasrc =] ' data_source '
Имя источника данных, как оно интерпретируется поставщиком OLE DB. Значение data_source равно nvarchar ( 4000 ). data_source передается как свойство DBPROP_INIT_DATASOURCE для инициализации поставщика OLE DB.
[ @location =] ' расположение '
Введите местонахождение базы данных, понятное поставщику OLE DB. Значение Location имеет тип nvarchar ( 4000 ) и значение по умолчанию NULL. Расположение аргумента передается как свойство DBPROP_INIT_LOCATION для инициализации поставщика OLE DB.
[ @provstr =] ' provider_string '
Строка подключения для конкретного поставщика OLE DB, указывающая уникальный источник данных. Значение provider_string имеет тип nvarchar ( 4000 ) и значение по умолчанию NULL. Аргумент provstr либо передается в IDataInitialize, либо устанавливается в качестве свойства DBPROP_INIT_PROVIDERSTRING для инициализации поставщика OLE DB.
При создании связанного сервера для SQL Server поставщика OLE DB собственного клиента экземпляр можно указать с помощью ключевого слова Server как SERVER=servername\\instancename для указания конкретного экземпляра SQL Server . ServerName — это имя компьютера, на котором SQL Server выполняется, а instanceName — имя конкретного экземпляра, SQL Server к которому будет подключен пользователь.
Чтобы получить доступ к зеркальной базе данных, строка соединения должна содержать имя базы данных. Это имя необходимо, чтобы предоставить поставщику доступа к данным возможность пытаться отработать отказ. База данных может быть указана в параметре @ provstr или @ Catalog . Кроме того, строка соединения может содержать имя партнера по обеспечению отработки отказа.
[ @catalog =] ' каталог '
Каталог, который должен использоваться при подключении к поставщику OLE DB. Параметр Catalog имеет тип sysname и значение по умолчанию NULL. Каталог аргументов передается как свойство DBPROP_INIT_CATALOG для инициализации поставщика OLE DB. Если связанный сервер определен для экземпляра SQL Server, то каталог ссылается на базу данных по умолчанию, с которой сопоставлен связанный сервер.
Значения кода возврата
0 (успешное завершение) или 1 (неуспешное завершение)
Результирующие наборы
Remarks
В следующей таблице показаны способы настройки связанного сервера для источников данных, доступных через поставщик OLE DB. Связанный сервер может быть настроен несколькими способами для конкретного источника данных; для одного типа источника данных возможны несколько строк. В этой таблице также показаны sp_addlinkedserver значения параметров, которые будут использоваться для настройки связанного сервера.
| Удаленный источник данных OLE DB | Поставщик OLE DB | product_name | provider_name | data_source | расположение | provider_string | catalog |
|---|---|---|---|---|---|---|---|
| SQL Server | MicrosoftSQL ServerПоставщик собственного клиента OLE DB | SQL Server 1 (по умолчанию) | |||||
| SQL Server | MicrosoftSQL ServerПоставщик собственного клиента OLE DB | SQLNCLI | Сетевое имя SQL Server (для экземпляра по умолчанию) | Имя базы данных (необязательно) | |||
| SQL Server | MicrosoftSQL ServerПоставщик собственного клиента OLE DB | SQLNCLI | имя сервера \ instanceName (для конкретного экземпляра) | Имя базы данных (необязательно) | |||
| Oracle, версия 8 или более поздняя | Поставщик Oracle для OLE DB | Любой | OraOLEDB.Oracle | Псевдоним для базы данных Oracle | |||
| Access/Jet | Поставщик OLE DB для Jet (Майкрософт) | Любой | Microsoft.Jet.OLEDB.4.0 | Полный путь к файлу базы данных Jet | |||
| Источник данных ODBC | Поставщик Microsoft OLE DB для ODBC | Любой | MSDASQL | Системный DSN источника данных ODBC | |||
| Источник данных ODBC | Поставщик Microsoft OLE DB для ODBC | Любой | MSDASQL | Строка подключения ODBC | |||
| Файловая система | Поставщик Microsoft OLE DB для службы индексирования | Любой | MSIDXS | Имя каталога службы индексирования | |||
| Электронная таблица Microsoft Excel | Поставщик Microsoft OLE DB для Jet | Любой | Microsoft.Jet.OLEDB.4.0 | Полный путь к файлу Excel | Excel 5,0 | ||
| База данных IBM DB2 | Поставщик Microsoft OLE DB для DB2 | Любой | DB2OLEDB | См Microsoft . документацию по поставщику OLE DB для DB2. | Имя каталога базы данных DB2 |
1 такой способ настройки связанного сервера приводит к тому, что имя связанного сервера будет совпадать с сетевым именем удаленного экземпляра SQL Server . Укажите сервер с помощью data_source .
2 "Any" означает, что название продукта может быть любым.
Microsoft SQL Server Поставщик OLE DB собственного клиента — это поставщик, который используется с, SQL Server Если имя поставщика не указано или указано в SQL Server качестве имени продукта. Даже если указано имя предыдущего поставщика (SQLOLEDB), оно все равно будет изменено на SQLNCLI при сохранении в каталог.
Параметры data_source, Location, provider_string и Catalog указывают на базу данных или базы данных, на которые указывает связанный сервер. Если значение одного из этих аргументов равно NULL, то соответствующее свойство инициализации поставщика OLE DB не установлено.
В кластеризованной среде при указании имен файлов для указания источников данных OLE DB используйте формат UNC или общие диски для указания расположения.
Хранимая процедура sp_addlinkedserver не может быть выполнена в пользовательской транзакции.
Azure SQL Управляемый экземпляр в настоящее время поддерживает только SQL Server, База данных SQL и другие SQL в качестве удаленных источников данных.
Когда связанный сервер создается с помощью sp_addlinkedserver , для всех локальных имен входа добавляется автоматическое сопоставление по умолчанию. Для пользователей, не являющихся SQL Server поставщиками, SQL Server проверенные имена входа могут иметь возможность получить доступ к поставщику в SQL Server учетной записи службы. Администраторам нужно рассмотреть применение процедуры sp_droplinkedsrvlogin , NULL для удаления глобального сопоставления.
Разрешения
sp_addlinkedserver Инструкции требуется разрешение ALTER ANY Linked Server . (Элемент SQL Server Management Studio Диалоговое окно Создание связанного сервера реализуется таким образом, что требует членства в sysadmin предопределенной роли сервера.)
Примеры
A. использование поставщика Microsoft SQL Server OLE DB
В следующем примере показано создание связанного сервера с именем SEATTLESales . Название продукта — SQL Server , имя поставщика не используется.
В следующем примере создается связанный сервер S1_instance1 на экземпляре с SQL Server помощью SQL Server драйвера OLE DB.
В следующем примере создается связанный сервер S1_instance1 на экземпляре с SQL Server помощью SQL Server собственного клиента OLE DB Provider.
Собственный клиент OLE DB для SQL Server (SQLNCLI) объявляется нерекомендуемым для новых разработок. Вместо этого используйте новый драйвер Microsoft OLE DB для SQL Server (MSOLEDBSQL), который будет обновлен с самыми последними серверными компонентами.
Б. Использование поставщика Microsoft OLE DB для Microsoft Access
Поставщик Microsoft.Jet.OLEDB.4.0 соединяется с базами данных Microsoft Access в формате 2002–2003. В следующем примере показано создание связанного сервера с именем SEATTLE Mktg .
в этом примере предполагается, что Microsoft доступ и образец Northwind базы данных установлены и что Northwind база данных находится в к:\мсоффице\акцесс\самплес на том же сервере, что и экземпляр SQL Server.
В. Использование поставщика OLE DB Майкрософт для ODBC с параметром data_source
В следующем примере создается связанный сервер с именем SEATTLE Payroll , который использует Microsoft поставщик OLE DB для ODBC ( MSDASQL ) и параметр data_source .
Указанный источник данных ODBC должен быть определен как системный DSN на сервере до того, как будет использоваться связанный сервер.
Г. использование поставщика OLE DB майкрософт для Excel электронной таблицы
чтобы создать определение связанного сервера с помощью Microsoft поставщика OLE DB для Jet для доступа к электронной таблице Excel в формате 1997-2003, сначала создайте именованный диапазон в Excel, указав столбцы и строки листа Excel, которые нужно выбрать. Затем на имя диапазона можно будет ссылаться в распределенном запросе как на имя таблицы.
Для доступа к данным в электронной таблице Excel требуется указать имя для диапазона ячеек. Следующий запрос используется для получения доступа к указанному диапазону ячеек SalesData как к таблице с помощью предварительно настроенного связанного сервера.
Если SQL Server выполняется под учетной записью домена, имеющего доступ к удаленной общей папке, то вместо сопоставленного диска может использоваться путь в формате UNC.
Д. Доступ к текстовому файлу с помощью поставщика Microsoft OLE DB для Jet
Данный пример показывает, как создать связанный сервер для прямого доступа к текстовым файлам без соединения с ними как с таблицами MDB-файла СУБД Access. Поставщик Microsoft.Jet.OLEDB.4.0 и строка поставщика Text .
Источник данных — это полный путь к каталогу, который содержит тестовые файлы. Файл schema.ini, который описывает структуру текстовых файлов, должен находиться в том же каталоге, что и текстовые файлы. дополнительные сведения о создании schema.iniного файла см. в документации по Jet ядро СУБД.
Сначала создайте связанный сервер.
Настройка сопоставлений имен входа.
Перечислите таблицы на связанном сервере.
Е. использование поставщик OLE DB для DB2 (Майкрософт)
В следующем примере показано создание связанного сервера с именем DB2 , который использует Microsoft OLE DB Provider for DB2 .
Ж. Добавление в База данных SQL Azure качестве связанного сервера для использования с распределенными запросами в облачных и локальных базах данных
Можно добавить в База данных SQL Azure качестве связанного сервера, а затем использовать его с распределенными запросами, охватывающими локальные и облачные базы данных. Это компонент для гибридных решений баз данных, охватывающих локальные корпоративные сети и облако Azure.
SQL ServerПоле Product содержит функцию распределенных запросов, которая позволяет создавать запросы для объединения данных из локальных источников данных и данных из удаленных источников (включая данные из источников, не являющихся SQL Server источниками данных), определенных в качестве связанных серверов. Каждую База данных SQL Azure (за исключением master базы данных логического сервера) можно добавить в качестве отдельного связанного сервера, а затем использовать непосредственно в приложениях базы данных в качестве любой другой базы данных.
Преимущества использования База данных SQL Azure включают управляемость, высокий уровень доступности, масштабируемость, работу с привычной моделью разработки и реляционную модель данных. Требования приложения базы данных определяют, как она будет использоваться База данных SQL Azure в облаке. Вы можете переместить все данные одновременно в База данных SQL Azure или последовательно перемещать некоторые данные, сохраняя оставшиеся данные в локальной среде. Для такого гибридного приложения базы данных База данных SQL Azure теперь можно добавить в качестве связанных серверов, а приложение базы данных может выдавать распределенные запросы для объединения данных из База данных SQL Azure локальных источников данных.
Ниже приведен простой пример, объясняющий, как подключиться к с База данных SQL Azure помощью распределенных запросов.
сначала добавьте один База данных SQL Azure в качестве связанного сервера с помощью SQL Server Native Client.
Добавьте учетные данные и параметры для этого связанного сервера.
Теперь используйте связанный сервер для выполнения запросов с использованием имен из четырех частей, даже для создания новой таблицы и вставки данных.
Выполните запрос к данным, используя имена из четырех частей:
З. Создание связанного сервера с Управляемым экземпляром SQL с помощью аутентификации Azure AD с использованием управляемого удостоверения
Чтобы создать связанный сервер с управляемым удостоверением аутентификации, выполните следующую инструкцию T-SQL. Метод проверки подлинности использует ActiveDirectoryMSI в @provstr параметре. При необходимости можно использовать @locallogin = NULL для разрешения всех локальных имен входа.
если Azure SQL Управляемый экземпляр управляемое удостоверение (прежнее название — управляемое удостоверение службы) добавляется в качестве имени входа в удаленный управляемый экземпляр, проверка подлинности управляемого удостоверения возможна для связанного сервера, созданного как в предыдущем примере. Поддерживаются как назначенные системой, так и управляемые удостоверения, назначенные пользователем.
Если задано Основное удостоверение, оно будет использоваться, в противном случае будет использоваться управляемое удостоверение, назначенное системой. Если управляемое удостоверение создается повторно с тем же именем, необходимо повторно создать имя входа на удаленном экземпляре, так как новый идентификатор приложения управляемого удостоверения и идентификатор безопасности субъекта-службы Управляемый экземпляр больше не совпадают. Чтобы проверить совпадение этих двух значений, преобразуйте SID в идентификатор приложения с помощью следующего запроса.
И. Создание связанного сервера с Управляемым экземпляром SQL с помощью сквозной аутентификации Azure AD
Чтобы создать связанный сервер с сквозной проверкой подлинности, выполните следующую инструкцию T-SQL.
При сквозной проверке подлинности контекст безопасности локального имени входа переносится на удаленный экземпляр. для сквозной проверки подлинности требуется, чтобы участник AAD был добавлен в качестве имени входа как для локального, так и для удаленного Управляемый экземпляр Azure SQL. Оба управляемых экземпляра должны находиться в группе доверия сервера. При соблюдении требований пользователь может войти в локальный экземпляр и запросить удаленный экземпляр через объект связанного сервера.
Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by using sp_addlinkedserver , distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.
Syntax
Arguments
[ @server = ] 'server'
Is the name of the linked server to create. The argument server is sysname, with no default.
[ @srvproduct = ] 'product_name'
Is the product name of the OLE DB data source to add as a linked server. The value product_name is nvarchar(128), with a default of NULL. If the value is SQL Server, provider_name, data_source, location, provider_string, and catalog do not have to be specified.
[ @provider = ] 'provider_name'
Is the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source. The provider_name must be unique for the specified OLE DB provider installed on the current computer. The value provider_name is nvarchar(128), with a default of NULL; however, if provider_name is omitted, SQLNCLI is used.
Using SQLNCLI will redirect SQL Server to the latest version of SQL Server Native Client OLE DB Provider. The OLE DB provider is expected to be registered with the specified PROGID in the registry.
The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remain deprecated and it is not recommended to use either for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.
[ @datasrc = ] 'data_source'
Is the name of the data source as interpreted by the OLE DB provider. The value data_source is nvarchar(4000). data_source is passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.
[ @location = ] 'location'
Is the location of the database as interpreted by the OLE DB provider. The value location is nvarchar(4000), with a default of NULL. The argument location is passed as the DBPROP_INIT_LOCATION property to initialize the OLE DB provider.
[ @provstr = ] 'provider_string'
Is the OLE DB provider-specific connection string that identifies a unique data source. The value provider_string is nvarchar(4000), with a default of NULL. The argument provstr is either passed to IDataInitialize or set as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider.
When the linked server is created against the SQL Server Native Client OLE DB provider, the instance can be specified by using the SERVER keyword as SERVER=servername\\instancename to specify a specific instance of SQL Server. The servername is the name of the computer on which SQL Server is running, and instancename is the name of the specific instance of SQL Server to which the user will be connected.
To access a mirrored database, a connection string must contain the database name. This name is necessary to enable failover attempts by the data access provider. The database can be specified in the @provstr or @catalog parameter. Optionally, the connection string can also supply a failover partner name.
[ @catalog = ] 'catalog'
Is the catalog to be used when a connection is made to the OLE DB provider. The value catalog is sysname, with a default of NULL. The argument catalog is passed as the DBPROP_INIT_CATALOG property to initialize the OLE DB provider. When the linked server is defined against an instance of SQL Server, catalog refers to the default database to which the linked server is mapped.
Return Code Values
0 (success) or 1 (failure)
Result Sets
Remarks
The following table shows the ways that a linked server can be set up for data sources that can be accessed through OLE DB. A linked server can be set up more than one way for a particular data source; there can be more than one row for a data source type. This table also shows the sp_addlinkedserver parameter values to be used for setting up the linked server.
| Remote OLE DB data source | OLE DB provider | product_name | provider_name | data_source | location | provider_string | catalog |
|---|---|---|---|---|---|---|---|
| SQL Server | Microsoft SQL Server Native Client OLE DB Provider | SQL Server 1 (default) | |||||
| SQL Server | Microsoft SQL Server Native Client OLE DB Provider | SQLNCLI | Network name of SQL Server (for default instance) | Database name (optional) | |||
| SQL Server | Microsoft SQL Server Native Client OLE DB Provider | SQLNCLI | servername\instancename (for specific instance) | Database name (optional) | |||
| Oracle, version 8 and later | Oracle Provider for OLE DB | Any | OraOLEDB.Oracle | Alias for the Oracle database | |||
| Access/Jet | Microsoft OLE DB Provider for Jet | Any | Microsoft.Jet.OLEDB.4.0 | Full path of Jet database file | |||
| ODBC data source | Microsoft OLE DB Provider for ODBC | Any | MSDASQL | System DSN of ODBC data source | |||
| ODBC data source | Microsoft OLE DB Provider for ODBC | Any | MSDASQL | ODBC connection string | |||
| File system | Microsoft OLE DB Provider for Indexing Service | Any | MSIDXS | Indexing Service catalog name | |||
| Microsoft Excel Spreadsheet | Microsoft OLE DB Provider for Jet | Any | Microsoft.Jet.OLEDB.4.0 | Full path of Excel file | Excel 5.0 | ||
| IBM DB2 Database | Microsoft OLE DB Provider for DB2 | Any | DB2OLEDB | See Microsoft OLE DB Provider for DB2 documentation. | Catalog name of DB2 database |
1 This way of setting up a linked server forces the name of the linked server to be the same as the network name of the remote instance of SQL Server. Use data_source to specify the server.
2 "Any" indicates that the product name can be anything.
The Microsoft SQL Server Native Client OLE DB provider is the provider that is used with SQL Server if no provider name is specified or if SQL Server is specified as the product name. Even if you specify the older provider name, SQLOLEDB, it will be changed to SQLNCLI when persisted to the catalog.
The data_source, location, provider_string, and catalog parameters identify the database or databases the linked server points to. If any one of these parameters is NULL, the corresponding OLE DB initialization property is not set.
In a clustered environment, when you specify file names to point to OLE DB data sources, use the universal naming convention name (UNC) or a shared drive to specify the location.
The stored procedure sp_addlinkedserver cannot be executed within a user-defined transaction.
Azure SQL Managed Instance currently supports only SQL Server, SQL Database, and other SQL Managed Instance as remote data sources.
When a linked server is created by using sp_addlinkedserver , a default self-mapping is added for all local logins. For non- SQL Server providers, SQL Server Authenticated logins may be able to gain access to the provider under the SQL Server service account. Administrators should consider using sp_droplinkedsrvlogin , NULL to remove the global mapping.
Permissions
The sp_addlinkedserver statement requires the ALTER ANY LINKED SERVER permission. (The SQL Server Management Studio New Linked Server dialog box is implemented in a way that requires membership in the sysadmin fixed server role.)
Examples
A. Use the Microsoft SQL Server OLE DB Provider
The following example creates a linked server named SEATTLESales . The product name is SQL Server , and no provider name is used.
The following example creates a linked server S1_instance1 on an instance of SQL Server by using the SQL Server OLE DB driver.
The following example creates a linked server S1_instance1 on an instance of SQL Server by using the SQL Server Native Client OLE DB provider.
SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use it for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.
B. Use the Microsoft OLE DB Provider for Microsoft Access
The Microsoft.Jet.OLEDB.4.0 provider connects to Microsoft Access databases that use the 2002-2003 format. The following example creates a linked server named SEATTLE Mktg .
This example assumes that both Microsoft Access and the sample Northwind database are installed and that the Northwind database resides in C:\Msoffice\Access\Samples on the same server as the SQL Server instance.
C. Use the Microsoft OLE DB Provider for ODBC with the data_source parameter
The following example creates a linked server named SEATTLE Payroll that uses the Microsoft OLE DB Provider for ODBC ( MSDASQL ) and the data_source parameter.
The specified ODBC data source name must be defined as System DSN in the server before you use the linked server.
D. Use the Microsoft OLE DB Provider for Excel spreadsheet
To create a linked server definition using the Microsoft OLE DB Provider for Jet to access an Excel spreadsheet in the 1997 - 2003 format, first create a named range in Excel by specifying the columns and rows of the Excel worksheet to select. The name of the range can then be referenced as a table name in a distributed query.
To access data from an Excel spreadsheet, associate a range of cells with a name. The following query can be used to access the specified named range SalesData as a table by using the linked server set up previously.
If SQL Server is running under a domain account that has access to a remote share, a UNC path can be used instead of a mapped drive.
E. Use the Microsoft OLE DB Provider for Jet to access a text file
The following example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is Text .
The data source is the full path of the directory that contains the text files. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. For more information about how to create a schema.ini file, see the Jet Database Engine documentation.
First, create a linked server.
Set up login mappings.
List the tables in the linked server.
F. Use the Microsoft OLE DB Provider for DB2
The following example creates a linked server named DB2 that uses the Microsoft OLE DB Provider for DB2 .
G. Add a Azure SQL Database as a Linked Server For Use With Distributed Queries on Cloud and On-Premises Databases
You can add a Azure SQL Database as a linked server and then use it with distributed queries that span the on-premises and cloud databases. This is a component for database hybrid solutions spanning on-premises corporate networks and the Azure cloud.
The SQL Server box product contains the distributed query feature, which allows you to write queries to combine data from local data sources and data from remote sources (including data from non- SQL Server data sources) defined as linked servers. Every Azure SQL Database (except the logical server's master database) can be added as an individual linked server and then used directly in your database applications as any other database.
The benefits of using Azure SQL Database include manageability, high availability, scalability, working with a familiar development model, and a relational data model. The requirements of your database application determine how it would use Azure SQL Database in the cloud. You can move all of your data at once to Azure SQL Database, or progressively move some of your data while keeping the remaining data on-premises. For such a hybrid database application, Azure SQL Database can now be added as linked servers and the database application can issue distributed queries to combine data from Azure SQL Database and on-premises data sources.
Here's a simple example explaining how to connect to a Azure SQL Database using distributed queries.
First, add one Azure SQL Database as linked server, using the using SQL Server Native Client.
Add credentials and options to this linked server.
Now, use the linked server to execute queries using four-part names, even to create a new table and insert data.
Query the data using four-part names:
H. Create SQL Managed Instance linked server with managed identity Azure AD authentication
To create a linked server with managed identity authentication, execute the following T-SQL. The authentication method uses ActiveDirectoryMSI in the @provstr parameter. Consider optionally using @locallogin = NULL to allow all local logins.
If Azure SQL Managed Instance managed identity (formerly called managed service identity) is added as login to a remote managed instance, then Managed Identity authentication is possible with linked server created as in the previous example. Both system assigned and user assigned managed identities are supported.
If primary identity is set, it will be used, otherwise system assigned managed identity will be used. If managed identity is recreated with the same name, login on the remote instance also needs to be recreated, because new managed identity Application ID and Managed Instance service principal SID no longer match. To verify these two values match, convert SID to application ID with following query.
I. Create SQL Managed Instance linked server with pass-through Azure AD authentication
To create a linked server with pass-through authentication execute following T-SQL.
With pass-through authentication, security context of the local login is carried over to a remote instance. Pass-through authentication requires the AAD principal to be added as login on both local and remote Azure SQL Managed Instance. Both Managed Instances need to be in a Server Trust Group. When the requirements are met, user can sign in to a local instance and query the remote instance via the linked server object.
В этой статье демонстрируется, как создать связанный сервер и получить доступ к данным из другого 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. При соединении с экземпляром служб 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
В этом примере создается связанный сервер для другого экземпляра SQL Server с помощью Transact-SQL:
В редакторе запросов введите следующую команду Transact-SQL , чтобы установить связь с экземпляром SQL Server с именем SRVR002\ACCTG :
Выполните следующий код, чтобы настроить связанный сервер для использования учетных данных домена для имени входа, которое использует связанный сервер.
Дальнейшие действия после создания связанного сервера
Ниже представлен порядок выполнения проверки связанного сервера.
Проверка связанного сервера
Для проверки подлинности связанного сервера в текущем контексте безопасности можно использовать один из следующих двух подходов.
Чтобы проверить возможность соединения со связанным сервером в SSMS, щелкните его в обозревателе объектов правой кнопкой мыши и выберите команду Проверить подключение.
Чтобы проверить возможность подключения к связанному серверу в T-SQL, выполните простую инструкцию SELECT, например, чтобы получить базовые сведения о каталоге базы данных. Этот пример возвращает имена баз данных на связанном сервере.
Присоединение таблиц со связанного сервера
Для ссылки на объект, расположенный на связанном сервере, используйте четырехкомпонентные имена. Выполните следующий код, чтобы получить список всех имен входа на локальном сервере и соответствующих имен входа на связанном сервере.
Если для имени входа связанного сервера возвращается значение NULL , это значит, что имя входа не существует на связанном сервере. Такие имена входа не смогут использовать связанный сервер, если на нем не настроена передача другого контекста безопасности и он не принимает анонимные подключения.
Связанные серверы с Управляемым экземпляром SQL Azure
Если вы используете Управляемый экземпляр SQL Azure, см. следующие примеры из статьи sp_addlinkedserver (Transact-SQL):
Следующие шаги
Дополнительные сведения об управлении связанными серверами см. в следующих статьях:
Связанные серверы позволяют Компонент SQL Server Database Engine и Управляемый экземпляр SQL Azure вести чтение данных из удаленных источников данных и выполнять команды на удаленных серверах баз данных (например, источниках данных OLE DB) за пределами экземпляра SQL Server. Обычно связанные серверы настроены на включение компонента Компонент Database Engine для выполнения инструкции Transact-SQL , включающей таблицы в другом экземпляре SQL Serverили другом продукте для работы с базами данных, например Oracle. В качестве связанных серверов можно настроить источники данных OLE DB многих типов, в том числе сторонних поставщиков баз данных и Azure Cosmos DB.
Связанные серверы доступны в Компонент SQL Server Database Engine и Управляемый экземпляр SQL Azure. Они не включены в отдельные базы данных База данных SQL Azure и эластичные пулы. Существуют некоторые ограничения для управляемых экземпляров, описание которых можно найти здесь.
В каких случаях следует использовать связанные серверы?
Связанные серверы позволяют реализовать распределенные базы данных, которые могут извлекать и обновлять данные в других базах данных. Они являются прекрасным решением в сценариях, где необходимо реализовать сегментирование баз данных без необходимости создания кода пользовательского приложения или прямой загрузки из удаленных источников данных. Концепция связанных серверов имеет следующие преимущества.
Возможность доступа к данным за пределами SQL Server.
Возможность осуществлять распределенные запросы, обновления, команды и транзакции на разнородных источниках данных по всему предприятию.
Возможность единообразной адресации разных источников данных.
Можно настроить связанный сервер, используя инструкцию SQL Server Management Studio или sp_addlinkedserver (Transact-SQL) . Поставщики OLE DB существенно различаются по типу и количеству необходимых параметров. Например, некоторые поставщики требуют предоставить контекст безопасности для соединения с помощью sp_addlinkedsrvlogin (Transact-SQL). Некоторые поставщики OLE DB разрешают использовать SQL Server для обновления данных в источнике данных OLE DB. Другие предоставляют доступ к данным только для чтения. Для информации о каждом поставщике OLE DB обратитесь к документации об этом поставщике OLE DB.
Компоненты связанных серверов
Определение связанного сервера задает следующие объекты.
Поставщик OLE DB
Источник данных OLE DB
Поставщиком OLE DB является динамическая библиотека, осуществляющая управление и взаимодействие с определенными источниками данных. Источник данных OLE DB определяет конкретную базу данных, доступ к которой выполняется через интерфейс OLE DB. Хотя источники данных, запросы к которым выполняются с помощью определений связанных серверов, являются обычными базами данных, существуют поставщики OLE DB для разнообразных файлов и форматов файлов. Сюда входят текстовые файлы, данные электронных таблиц и результаты поиска полнотекстового содержимого.
Начиная с SQL Server 2019 (15.x), драйвер Microsoft OLE DB для SQL Server (MSOLEDBSQL) (PROGID: MSOLEDBSQL) является поставщиком OLE DB по умолчанию. В более ранних версиях поставщик OLE DB SQL Server Native Client (SQLNCLI) (PROGID: SQLNCLI11) являлся поставщиком OLE DB по умолчанию.
Майкрософт поддерживает связанные серверы для источников Microsoft Access и Excel только при использовании 32-разрядного поставщика OLE DB Microsoft.JET.OLEDB.4.0.
SQL Server предназначены для работы с поставщиком OLE DB, реализующим необходимые интерфейсы OLE DB. Однако SQL Server была протестирована с поставщиком OLE DB по умолчанию.
Сведения о настройке связанных серверов
На следующей иллюстрации показаны основы настройки связанных серверов.
Обычно связанные серверы используются для обработки распределенных запросов. Если клиентское приложение выполняет распределенный запрос через связанный сервер, SQL Server проводит синтаксический анализ команды и передает запросы поставщику OLE DB. Запрос на набор строк может быть в форме выполнения запроса к поставщику или в форме открытия базовой таблицы из поставщика.
Чтобы источник данных мог вернуть данные при помощи связанного сервера, поставщик OLE DB (динамическая библиотека) для этого источника данных должен присутствовать на том же сервере, что и экземпляр SQL Server.
При использовании поставщика OLE DB учетная запись, под которой выполняется служба сервера SQL Server, должна иметь разрешения на чтение и выполнение для каталога и всех его подкаталогов, в котором установлен поставщик. Сюда входят поставщики, выпущенные корпорацией Майкрософт, и любые сторонние поставщики.
Связанные серверы поддерживают сквозную проверку подлинности Active Directory при использовании полного делегирования. Начиная с SQL Server 2017 (14.x); CU17, также поддерживается сквозная проверка подлинности с ограниченным делегированием. Однако ограниченное делегирование на основе ресурсов не поддерживается.
Управление поставщиками
Имеется набор параметров, определяющих, как SQL Server загружает и использует поставщики OLE DB, заданные в реестре.
Управление определениями связанных серверов
При установке связанного сервера зарегистрируйте параметры соединения и источника данных при помощи SQL Server. После регистрации к этому источнику данных можно обращаться по одному логическому имени.
Для управления определениями связанного сервера можно использовать хранимые процедуры и представления каталога.
Создайте определение связанного сервера, выполнив процедуру sp_addlinkedserver .
Просмотрите сведения о связанных серверах, определенных в конкретном экземпляре SQL Server, выполнив запрос к представлениям системного каталога sys.servers .
Удалите определение связанного сервера, выполнив процедуру sp_dropserver . Эта хранимая процедура может также использоваться для удаления удаленного сервера.
Определять связанные серверы можно также в среде SQL Server Management Studio. В обозревателе объектов щелкните правой кнопкой мыши Объекты сервера, выберите Создать и выберите Связанный сервер. Определение связанного сервера можно удалить, щелкнув правой кнопкой мыши имя связанного сервера и выбрав Удалить.
При выполнении распределенного запроса к связанному серверу необходимо указать полное, состоящее из четырех частей имя таблицы для каждого источника данных, к которому выполняется запрос. Это четырехкомпонентное имя должно быть в форме linked_server_name.catalog . schema . object_name.
Связанные серверы могут быть определены таким образом, чтобы указывать на сервер, на котором они определены (обратная связь). Серверы с обратной связью наиболее полезны для тестирования приложения, в котором используются распределенные запросы в односерверной сети. Серверы, связанные с помощью петлевого адреса, предназначены для тестирования и не поддерживаются во многих операциях, таких как распределенные транзакции.
Проверка подлинности связанных серверов Управляемого экземпляра SQL Azure
Связанные серверы Управляемого экземпляра SQL Azure поддерживают как проверку подлинности SQL, так и проверку подлинности Azure AD (AAD). Поддерживаются два режима проверки подлинности AAD: управляемое удостоверение и сквозная проверка подлинности. Проверку подлинности на основе управляемого удостоверения можно использовать для того, чтобы разрешить локальным именам входа выполнять запросы к удаленным связанным серверам. Сквозная проверка подлинности позволяет субъекту, который может пройти проверку подлинности на локальном экземпляре, получить доступ к удаленному экземпляру через связанный сервер. Предварительные требования для сквозной проверки подлинности включают добавление этого субъекта в качестве имени входа на удаленном сервере и добавление обоих экземпляров в группу доверия SQL.
Существующие определения связанных серверов, настроенных для сквозной проверки подлинности, будут поддерживать проверку подлинности Azure AD. Единственным требованием для использования сквозной проверки подлинности остается добавление Управляемых экземпляров в группу доверия сервера.
Читайте также: