链接服务器(数据库引擎)Linked Servers (Database Engine)

适用对象: 是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

通过链接服务器,SQL Server 数据库引擎SQL Server Database EngineAzure SQL 数据库托管实例可从远程数据源中读取数据,并针对 SQL ServerSQL Server 实例之外的 OLE DB 数据源等远程数据库服务器执行命令。Linked servers enable the SQL Server 数据库引擎SQL Server Database Engine and Azure SQL Database Managed Instance to read data from the remote data sources and execute commands against the remote database servers (for example, OLE DB data sources) outside of the instance of SQL ServerSQL Server. 通常,配置链接服务器是为了支持 数据库引擎Database EngineTransact-SQLTransact-SQL 实例或诸如 Oracle 等其他数据库产品上执行包含表的 SQL ServerSQL Server语句。Typically linked servers are configured to enable the 数据库引擎Database Engine to execute a Transact-SQLTransact-SQL statement that includes tables in another instance of SQL ServerSQL Server, or another database product such as Oracle. 许多类型的 OLE DB 数据源都可配置为链接服务器,包括 MicrosoftMicrosoft Access、Excel 和 Azure CosmosDB。Many types OLE DB data sources can be configured as linked servers, including MicrosoftMicrosoft Access, Excel, and Azure CosmosDB.

备注

SQL Server 数据库引擎SQL Server Database Engine 和 Azure SQL 数据库托管实例中提供链接服务器。Linked servers are available in SQL Server 数据库引擎SQL Server Database Engine and Azure SQL Database Managed Instance. Azure SQL 数据库单一实例和弹性池中未启用链接服务器。they are not enabled in Azure SQL database Singleton and Elastic pools. 这些是部分可在此处查找到的托管实例中的约束There are some constraints in Managed Instance that can be found here.

何时使用链接服务器?When to use Linked Servers?

通过链接服务器,能够实现可在其他数据库中提取和更新数据的分布式数据库。Linked servers enable you to implement distributed databases that can fetch and update data in other databases. 对于需要实现数据库分片的场景,它们是很好的解决方案,让你无需创建自定义应用程序代码或从远程数据源直接加载。They are a good solution in the scenarios where you need to implement database sharding without need to create a custom application code or directly load from remote data sources. 链接服务器具有以下优点:Linked servers offer the following advantages:

  • 能够访问 SQL ServerSQL Server之外的数据。The ability to access data from outside of SQL ServerSQL Server.

  • 能够对企业内的异类数据源发出分布式查询、更新、命令和事务。The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

  • 能够以相似的方式确定不同的数据源。The ability to address diverse data sources similarly.

你可以使用 SQL Server Management StudioSQL Server Management Studiosp_addlinkedserver (Transact-SQL) 语句配置链接服务器。You can configure a linked server by using SQL Server Management StudioSQL Server Management Studio or by using the sp_addlinkedserver (Transact-SQL) statement. OLE DB 访问接口的类型和所需的参数的数量大不相同。OLE DB providers vary greatly in the type and number of parameters required. 例如,一些访问接口要求你使用 sp_addlinkedsrvlogin (Transact-SQL)之外对 OLE DB 数据源执行命令。For example some providers require you to provide a security context for the connection using sp_addlinkedsrvlogin (Transact-SQL). 某些 OLE DB 访问接口允许 SQL ServerSQL Server 更新数据 OLE DB 源上的数据。Some OLE DB providers allow SQL ServerSQL Server to update data on the OLE DB source. 其他访问接口可能仅提供只读数据访问权限。Others provide only read-only data access. 有关每个 OLE DB 访问接口的信息,请查看该 OLE DB 访问接口的文档。For information about each OLE DB provider, consult documentation for that OLE DB provider.

链接服务器组件Linked Server Components

链接服务器定义指定了下列对象:A linked server definition specifies the following objects:

  • OLE DB 访问接口An OLE DB provider

  • OLE DB 数据源An OLE DB data source

“OLE DB 访问接口” 是管理特定数据源并与其交互的 DLL。An OLE DB provider is a DLL that manages and interacts with a specific data source. “OLE DB 数据源” 标识可通过 OLE DB 访问的特定数据库。An OLE DB data source identifies the specific database that can be accessed through OLE DB. 虽然通过链接服务器定义查询的数据源通常是数据库,但 OLE DB 访问接口对各种文件和文件格式仍可用。Although data sources queried through linked server definitions are ordinarily databases, OLE DB providers exist for a variety of files and file formats. 这些文件和文件格式包括文本文件、电子表格数据和全文内容搜索的结果。These include text files, spreadsheet data, and the results of full-text content searches.

MicrosoftMicrosoftSQL ServerSQL Server Native Client OLE DB 提供程序 (PROGID:SQLNCLI11) 是适用于 SQL ServerSQL Server 的官方 OLE DB 提供程序。The MicrosoftMicrosoft SQL ServerSQL Server Native Client OLE DB Provider (PROGID: SQLNCLI11) is the official OLE DB provider for SQL ServerSQL Server.

备注

SQL ServerSQL Server 分布式查询旨在与任何实现所需 OLE DB 接口的 OLE DB 访问接口一起使用。distributed queries are designed to work with any OLE DB provider that implements the required OLE DB interfaces. 但是, SQL ServerSQL Server 仅针对 SQL ServerSQL Server Native Client OLE DB 访问接口和特定访问接口进行过测试。However, SQL ServerSQL Server has been tested against only the SQL ServerSQL Server Native Client OLE DB Provider and certain other providers.

链接服务器详细信息Linked Server Details

下图显示了链接服务器配置的基础。The following illustration shows the basics of a linked server configuration.

客户端层、服务器层和数据库服务器层Client tier, server tier, and database server tier

通常,链接服务器用于处理分布式查询。Typically, linked servers are used to handle distributed queries. 当客户端应用程序通过链接服务器执行分布式查询时, SQL ServerSQL Server 将分析命令并向 OLE DB 发送请求。When a client application executes a distributed query through a linked server, SQL ServerSQL Server parses the command and sends requests to OLE DB. 行集请求的形式可以是对该访问接口执行查询或从该访问接口打开基表。The rowset request may be in the form of executing a query against the provider or opening a base table from the provider.

备注

为使数据源能通过链接服务器返回数据,该数据源的 OLE DB 访问接口 (DLL) 必须与 SQL ServerSQL Server的实例位于同一服务器上。For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL ServerSQL Server.

重要

使用 OLE DB 提供程序时,运行 SQL ServerSQL Server 服务的帐户必须具有对安装提供程序的目录及其所有子目录的读取权限和执行权限。When an OLE DB provider is used, the account under which the SQL ServerSQL Server service runs must have read and execute permissions for the directory, and all subdirectories, in which the provider is installed. 这包括 Microsoft 发布的提供程序和任何第三方提供程序。This includes Microsoft-released providers, and any third-party providers.

管理访问接口Managing Providers

有一组选项可以控制 SQL ServerSQL Server 如何加载和使用注册表中指定的 OLE DB 访问接口。There is a set of options that control how SQL ServerSQL Server loads and uses OLE DB providers that are specified in the registry.

管理链接服务器定义Managing Linked Server Definitions

设置链接服务器时,请在 SQL ServerSQL Server中注册连接信息和数据源信息。When you are setting up a linked server, register the connection information and data source information with SQL ServerSQL Server. 完成注册后,可以用单个逻辑名称来引用该数据源。After being registered, that data source can be referred to with a single logical name.

可以使用存储过程和目录视图来管理链接服务器定义:You can use stored procedures and catalog views to manage linked server definitions:

  • 通过运行 sp_addlinkedserver创建链接服务器定义。Create a linked server definition by running sp_addlinkedserver.

  • 通过对 SQL ServerSQL Server sys.servers 系统目录视图执行查询,查看有关在 的特定实例中定义的链接服务器的信息。View information about the linked servers defined in a specific instance of SQL ServerSQL Server by running a query against the sys.servers system catalog views.

  • 通过运行 sp_dropserver删除链接服务器定义。Delete a linked server definition by running sp_dropserver. 还可以使用此存储过程删除远程服务器。You can also use this stored procedure to remove a remote server.

还可以使用 SQL Server Management StudioSQL Server Management Studio 来定义链接服务器。You can also define linked servers by using SQL Server Management StudioSQL Server Management Studio. 在对象资源管理器中,右键单击“服务器对象” ,选择“新建” ,再选择“链接服务器” 。In the Object Explorer, right-click Server Objects, select New, and select Linked Server. 通过右键单击链接服务器名称并选择“删除” ,可以删除链接服务器定义。You can delete a linked server definition by right-clicking the linked server name and selecting Delete.

对链接服务器执行分布式查询时,请对每个要查询的数据源指定由四个部分组成的完全限定的表名。When you execute a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query. 这个四部分名称格式应为 linked_server_name.catalog . schema . object_nameThis four-part name should be in the form linked_server_name.catalog.schema.object_name.

备注

可以定义链接服务器指回(环回)到在其上定义它们的服务器。Linked servers can be defined to point back (loop back) to the server on which they are defined. 当在单服务器网络中测试使用分布式查询的应用程序时,环回服务器是很有用的。Loopback servers are most useful when testing an application that uses distributed queries on a single server network. 环回链接服务器专用于测试,许多操作(如分布式事务)不支持该服务器。Loopback linked servers are intended for testing and are not supported for many operations, such as distributed transactions.

创建链接服务器(SQL Server 数据库引擎)Create Linked Servers (SQL Server Database Engine)

sp_addlinkedserver (Transact-SQL)sp_addlinkedserver (Transact-SQL)

sp_addlinkedsrvlogin (Transact-SQL)sp_addlinkedsrvlogin (Transact-SQL)

sp_dropserver (Transact-SQL)sp_dropserver (Transact-SQL)

sys.servers (Transact-SQL)sys.servers (Transact-SQL)

sp_linkedservers (Transact-SQL)sp_linkedservers (Transact-SQL)