連結的伺服器 (Database Engine)Linked Servers (Database Engine)

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否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 Database EngineSQL Server Database EngineAzure SQL Database 受控執行個體,以從遠端資料來源讀取資料,並針對 SQL ServerSQL Server 執行個體外部的遠端資料庫伺服器 (例如 OLE DB 資料來源) 執行命令。Linked servers enable the SQL Server Database EngineSQL 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 EngineDatabase Engine 執行 Transact-SQLTransact-SQL 陳述式,而此陳述式包含另一個 SQL ServerSQL Server執行個體中的資料表或另一個資料庫產品 (例如 Oracle) 中的資料表。Typically linked servers are configured to enable the Database EngineDatabase 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 Database EngineSQL Server Database Engine 和 Azure SQL Database 受控執行個體。Linked servers are available in SQL Server Database EngineSQL 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 Studio 或使用 sp_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 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. 資料列集要求可能是採用對提供者執行查詢的形式,也可能是開啟提供者的基底資料表 (Base Table)。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.

注意

使用完整委派時,連結的伺服器支援 Active Directory 傳遞驗證。Linked servers support Active Directory pass-through authentication when using full delegation. 從 SQL Server 2017 CU17 開始,也支援具有限制委派的傳遞驗證;不過,不支援以資源為基礎的限制委派 (部分機器翻譯)。Starting with SQL Server 2017 CU17, pass-through authentication with constrained delegation is also supported; however, resource-based constrained delegation is not supported.

管理提供者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.

注意

連結的伺服器可以定義為指回 (回送,Loopback) 到定義它們的伺服器上。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 Database Engine)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)