A common activity when writing T-SQL queries is connecting to local databases and processing data directly. But there will be situations in which you need to connect to a remote database that is located in a different instance in the same server or in a different physical server, and process its data in parallel with the local data processing.
編寫T-SQL查詢時的常見活動是連接配接到本地資料庫并直接處理資料。 但是在某些情況下，您需要連接配接到位于同一伺服器或不同實體伺服器中不同執行個體中的遠端資料庫，并與本地資料處理并行地處理其資料。
SQL Server provides us with four useful methods to connect to the remote database servers, even other database server types, and query its data within your T-SQL statement. In this article, we will discuss these four methods and how to use it to query remote SQL Server databases.
SQL Server為我們提供了四種有用的方法來連接配接到遠端資料庫伺服器，甚至其他資料庫伺服器類型，并在您的T-SQL語句中查詢其資料。 在本文中，我們将讨論這四種方法以及如何使用它來查詢遠端SQL Server資料庫。
OPENDATASOURCE (OPENDATASOURCE)
The first method to query a remote SQL Server database is the OPENDATASOURCE T-SQL function below:
查詢遠端SQL Server資料庫的第一種方法是下面的OPENDATASOURCE T-SQL函數：
OPENDATASOURCE ( provider_name as char, init_string )
OPENDATASOURCE（provider_name為char，init_string）
Where the provider_name is the OLE DB provider used to access the data source. And the init_string is the connection string of the remote server.
其中provider_name是用于通路資料源的OLE DB提供程式。 init_string是遠端伺服器的連接配接字元串。
To be able to use the OPENDATASOURCE statement, you need to make sure that the DisallowAdhocAccess registry key is set to 0 for the provider you want to connect to other than the SQL Server, which can be found in the below path of the Registry Keys :
為了能夠使用OPENDATASOURCE語句，您需要確定将要連接配接到除SQL Server之外的提供程式的DisallowAdhocAccess系統資料庫項設定為0，該系統資料庫項可以在系統資料庫項的以下路徑中找到：
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\<ProviderName>
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ Providers \ <ProviderName>
Also you need to enable the Ad Hoc Distributed Queries advanced configuration option which is disabled by default in SQL Server. If you try to run the below simple query that is using the OPENDATASOURCE T-SQL statement you will get the error:
另外，您還需要啟用Ad Hoc Distributed Queries進階配置選項，該選項在SQL Server中預設為禁用。 如果您嘗試運作以下使用OPENDATASOURCE T-SQL語句的簡單查詢，則會收到錯誤消息：
SELECT *
FROM OPENDATASOURCE('SQLNCLI',
'Data Source=DEV_SQL;Integrated Security=SSPI')
.testdb.dbo.AddressBook
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.
消息15281，第16級，狀态1，第1行
SQL Server阻止通路元件“臨時分布式查詢”的STATEMENT“ OpenRowset / OpenDatasource”，因為此伺服器的安全配置已将此元件關閉。 系統管理者可以使用sp_configure啟用“臨時分布式查詢”的使用。 有關啟用“臨時分布式查詢”的更多資訊，請在SQL Server聯機叢書中搜尋“臨時分布式查詢”。
As you can see from the error message, the Ad Hoc Distributed Queries advanced configuration option should be enabled in order to open connection to a remote server using the OPENDATASOURCE. This can be achieved by using the sp_configure query below:
從錯誤消息中可以看到，應啟用“臨時分布式查詢”進階配置選項，以便使用OPENDATASOURCE打開到遠端伺服器的連接配接。 這可以通過使用以下sp_configure查詢來實作：
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully. OPENDATASOURCE can replace the server name in the four-part name of the table or view in a SELECT, INSERT, UPDATE, or DELETE statement. It can be also used in the EXECUTE statement to run a remote stored procedure.
啟用“分布式查詢”進階配置選項後，上一個查詢将成功運作。 OPENDATASOURCE可以在表的四部分名稱中替換伺服器名稱，也可以在SELECT，INSERT，UPDATE或DELETE語句中檢視。 也可以在EXECUTE語句中使用它來運作遠端存儲過程。
開放行 (OPENROWSET)
The second way to query a database hosted in a remote SQL Server is the OPENROWSET T-SQL function. In order to use the OPENROWSET ad hoc method, you need to provide all connection information that is required to connect to the remote SQL server and many other resources. It also can be used for a bulk operation through the built-in BULK provider to read data from files. OPENROWSET is used in the FROM clause as a table name in the SELECT, INSERT, UPDATE, or DELETE statements. Although the query might return multiple result sets, OPENROWSET returns only the first one.
查詢托管在遠端SQL Server中的資料庫的第二種方法是OPENROWSET T-SQL函數。 為了使用OPENROWSET ad hoc方法，您需要提供連接配接到遠端SQL Server和許多其他資源所需的所有連接配接資訊。 也可以通過内置的BULK提供程式将其用于批量操作，以從檔案中讀取資料。 OPENROWSET在FROM子句中用作SELECT，INSERT，UPDATE或DELETE語句中的表名。 盡管查詢可能傳回多個結果集，但OPENROWSET僅傳回第一個結果集。
Using OPENROWSET requires enabling the Ad Hoc Distributed Queries advanced configuration option same as the OPENDATASOURCE function.
使用OPENROWSET需要啟用與OPENDATASOURCE函數相同的“臨時分布式查詢”進階配置選項。
You need to provide the provider name, the connection string and the query as follows:
您需要提供提供者名稱，連接配接字元串和查詢，如下所示：
OPENROWSET('providername', 'datasource','query’)
You can write the previous OPENDATASOURCE query using the OPENROWSET function as follows:
您可以使用OPENROWSET函數編寫以前的OPENDATASOURCE查詢，如下所示：
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=DEV_SQL;Trusted_Connection=yes;',
'SELECT * FROM testdb.dbo.Profile') AS a;
連結伺服器 (Linked Server)
A SQL Server Linked Server is used to access remote OLE DB data sources such as a SQL Server instance located outside the SQL Server or other systems such as Oracle, Microsoft Access and Excel, and execute the distributed T-SQL queries against them.
SQL Server連結伺服器用于通路遠端OLE DB資料源（例如位于SQL Server外部SQL Server執行個體）或其他系統（例如Oracle，Microsoft Access和Excel），并對它們執行分布式T-SQL查詢。
A SQL Server Linked Server is different from ad hoc queries in that ad hoc queries open a temporary connection with the remote server and close it, where the permanent linked server is always available for use. When the user executes a distributed query against a remote data source using a linked server, the SQL Server Engine parses that command and sends the requests to OLE DB. This request can be a query to execute or a table to be opened on that remote server.
SQL Server連結伺服器與臨時查詢的不同之處在于，臨時查詢會打開與遠端伺服器的臨時連接配接并關閉它，而永久連結伺服器始終可以使用。 當使用者使用連結的伺服器對遠端資料源執行分布式查詢時，SQL Server引擎将解析該指令并将請求發送到OLE DB。 該請求可以是要執行的查詢，也可以是要在該遠端伺服器上打開的表。
A Linked Server can be configured by using SQL Server Management Studio or using the sp_addlinkedserver T-SQL statement.
可以使用SQL Server Management Studio或sp_addlinkedserver T-SQL語句來配置連結伺服器。
In order to configure a linked server using SQL Server Management Studio, expand the Server Objects node from the Object Explorer window. Right-click on the Linked Server node and choose New Linked Server.
為了使用SQL Server Management Studio配置連結伺服器，請從“ 對象資料總管”視窗中展開“ 伺服器對象”節點。 右鍵單擊“ 連結伺服器”節點，然後選擇“ 建立連結伺服器” 。
In the General tab of the New Linked Server window, choose a name for your linked server, then choose the type of the server you need to connect to using that linked server. Select SQL Server if you manage to connect to a remote SQL Server instance, or choose Other Data Source to select from the available OLE DB server types from the Provider drop down list other than SQL Server. If you choose SQL Server as the Server Type, the Linked Server name should be the network name of the remote SQL Server.
在“ 建立連結伺服器”視窗的“ 正常”頁籤中，為連結伺服器選擇一個名稱，然後選擇需要使用該連結伺服器連接配接到的伺服器的類型。 選擇SQL Server，如果你管理連接配接到遠端SQL Server執行個體，或選擇其他資料源 ，以從供應商下拉SQL Server之外的清單中提供的OLE DB伺服器類型中進行選擇。 如果選擇SQL Server作為伺服器類型，則連結伺服器名稱應為遠端SQL Server的網絡名稱。
Fill the Product Name field with the product name of the selected OLE DB data source, such as SQL Server if you are connecting to a remote SQL Server. Type the name of the selected data source in the Data Source field, such as the SQL Server Instance name if you are connecting to a remote SQL Server instance. Fill the Provider String field with the OLE DB provider-specific connection string that identifies a unique data source. The Location field can be filled with the remote database location as interpreted by the OLE DB provider. Type the name of the catalog to be used by the OLE DB provider in the Catalog field.
如果要連接配接到遠端SQL Server，請在“ 産品名稱”字段中填寫所選OLE DB資料源（例如SQL Server）的産品名稱。 如果要連接配接到遠端SQL Server執行個體，請在“ 資料源”字段中輸入所選資料源的名稱，例如SQL Server執行個體名稱。 用辨別唯一資料源的OLE DB提供程式特定的連接配接字元串填充提供程式字元串字段。 可以使用OLE DB提供程式解釋的遠端資料庫位置填充“ 位置”字段 。 在目錄字段中鍵入OLE DB提供程式要使用的目錄的名稱。
You are not required to use all the described arguments together, as the necessary arguments depend on the selected provider. For example, using SQL Server provider, you will use only two arguments.
您不需要一起使用所有描述的參數，因為必要的參數取決于所選的提供程式。 例如，使用SQL Server提供程式，您将僅使用兩個參數。
On the Security page of the New Linked Server window, specify the security context that the linked server will use to connect the original SQL Server to the remote data source. As the name explains, in the Local server logins to remote server logins mappings part of the Security window, you can specify a list of users that can use the linked server to connect to the remote server by mapping these local users with remote server logins. These users can be via SQL Server Authentication or a Windows Authentication login.
在“ 建立連結伺服器”視窗的“ 安全性”頁面上，指定連結伺服器用于将原始SQL Server連接配接到遠端資料源的安全上下文。 顧名思義，在“ 安全性”視窗的“ 本地伺服器登入名到遠端伺服器登入名映射”部分中，可以通過映射這些本地使用者和遠端伺服器登入名來指定可以使用連結伺服器連接配接到遠端伺服器的使用者清單。 這些使用者可以通過SQL Server身份驗證或Windows身份驗證登入。
For the logins that haven’t been defined in the mapping list, you have to choose the security context for their connections to the remote server using that linked server. Choosing Not be made will prevent any user not included in the previous list from using that linked server. Selecting Be made without using a security context, the users not included in the previous mapping list will connect to the remote server using that linked server without specifying a security context for them. If you choose Be made using the login’s current security context, then the connection to the remote server will be established using the connecting user, which is the best choice. In the Be made using this security context option, specify the SQL Server Authentication credentials that will be used to establish connection to the remote server for the users not defined in the mapping list.
對于尚未在映射清單中定義的登入名，您必須選擇使用該連結伺服器連接配接到遠端伺服器的安全上下文。 選擇“ 不進行”将阻止以前清單中未包括的任何使用者使用該連結伺服器。 選擇“不使用安全性上下文進行” ，不包含在先前映射清單中的使用者将使用該連結伺服器連接配接到遠端伺服器，而無需為其指定安全性上下文。 如果選擇“使用登入名的目前安全上下文進行連接配接”，則将使用連接配接使用者來建立到遠端伺服器的連接配接，這是最佳選擇。 在“ 使用此安全上下文進行連接配接”選項中，指定SQL Server身份驗證憑據，該憑據将用于為未在映射清單中定義的使用者建立到遠端伺服器的連接配接。
There are many options that you can tune depending on your requirements from the Server Options page of the New Linked Server window. For example, set the Collation Compatible option to true if you are sure that the remote data source has the same character set and sort order as the local server. By default, SQL Server evaluates comparisons on character columns locally if you don’t use this option. The Data Access option is used to enable and disable the distributed query access for the linked server. To enable the remote procedure call from the specified server set the RPC to true, and to enable the remote procedure call to the specified server set the RPC Out to true.
您可以根據需要從“ 建立連結伺服器”視窗的“ 伺服器選項”頁面中調整許多選項。 例如，如果您确定遠端資料源具有與本地伺服器相同的字元集和排序順序，則将“ 排序規則相容”選項設定為true。 預設情況下，如果不使用此選項，SQL Server将在本地評估字元列的比較。 “ 資料通路”選項用于為連結伺服器啟用和禁用分布式查詢通路。 要啟用從指定伺服器進行的遠端過程調用，請将RPC設定為true，并且要啟用對指定伺服器的遠端過程調用，請将RPC Out設定為true。
Deciding if the local or remote server’s collation will be used in the query is determined by the Use Remote Collation option. If this option’s value is true, you can specify the collation name that will be used by the remote server in the Collation Name option. This is applicable if the remote data source is not SQL Server, where you can specify any collation name supported by SQL Server in that field.
由“ 使用遠端排序規則”選項決定是否在查詢中使用本地或遠端伺服器的排序規則 。 如果此選項的值為true，則可以在“ 排序規則名稱”選項中指定遠端伺服器将使用的排序規則名稱 。 如果遠端資料源不是SQL Server，則适用此規則，您可以在該字段中指定SQL Server支援的任何排序規則名稱。
You can override the default server’s remote login timeout for that linked server by changing the Connection Timeout option to any value larger than 0.This value specifies the time-out in seconds for connecting to the linked server. Also you can specify the time-out in seconds for the queries connecting to the linked server by changing the Query Timeout option to any value larger than 0. This will override the server’s remote query timeout for the linked server.
您可以通過将“ 連接配接逾時”選項更改為大于0的任何值來覆寫該連結伺服器的預設伺服器的遠端登入逾時 。此值指定連接配接到連結伺服器的逾時（以秒為機關）。 您還可以通過将“ 查詢逾時”選項更改為大于0的任何值來指定連接配接到連結伺服器的查詢的逾時（以秒為機關）。這将覆寫連結伺服器的伺服器遠端查詢逾時 。
Another useful option that is used to start a distributed transaction when calling a remote stored procedure, in which this transaction will be manage and protect by the MS DTC. This option is called Enable Promotion of Distributed Transactions.
另一個有用的選項，用于在調用遠端存儲過程時啟動分布式事務，該事務将由MS DTC管理和保護。 此選項稱為啟用促進分布式事務。
You can make sure that the linked server is working fine by right-clicking on that linked server and choose Test Connection as follows:
您可以通過右鍵單擊該連結伺服器并選擇“ 測試連接配接”來確定連結伺服器正常工作，如下所示：
If the connection to the remote server is opened successfully, you will receive the below message, otherwise an error message will be displayed showing that there is something preventing the connection from being opened:
如果與遠端伺服器的連接配接成功打開，您将收到以下消息，否則将顯示一條錯誤消息，表明存在某些因素阻止打開連接配接：
You can easily create the previous linked server using the sp_addlinkedserver T-SQL statement passing the required arguments as follows:
您可以使用sp_addlinkedserver T-SQL語句通過傳遞所需的參數來輕松建立先前的連結伺服器，如下所示：
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'DEV_SQL', @srvproduct=N'SQL Server'
GO
Once the linked server is created successfully, you will be able to use it by specifying the four-part name that includes: Linked_Server_Name.Remote_Database_Name.Schema_Name.Table_Name as in the below example:
成功建立連結伺服器後，您可以通過指定以下四部分的名稱來使用它：包含以下名稱的Linked_Server_Name.Remote_Database_Name.Schema_Name.Table_Name ：
SELECT * FROM DEV_SQL.testdb.dbo.Profile
GO
公開查詢 (OPENQUERY)
The last SQL Server method that is used to connect to a remote data source is the OPENQUERY function. It is an alternative one-time ad hoc method to connect to a remote server using the linked server. For more frequent connections to the remote server, it is better to use the linked server instead of the OPENQUERY function.
用于連接配接到遠端資料源的最後一個SQL Server方法是OPENQUERY函數。 使用連結伺服器連接配接到遠端伺服器是一種一次性的臨時方法。 為了更頻繁地連接配接到遠端伺服器，最好使用連結伺服器而不是OPENQUERY功能。
The OPENQUERY function can be used in the FROM clause of the SELECT, INSERT, UPDATE, or DELETE statement replacing the table name. It takes two arguments; the linked server name and the query. These parameters can’t be variable, as follows:
可以在SELECT，INSERT，UPDATE或DELETE語句的FROM子句中使用OPENQUERY函數來替換表名。 它有兩個參數。 連結的伺服器名稱和查詢。 這些參數不能是變量，如下所示：
OPENQUERY ( linked_server ,’query’ )
OPENQUERY（linked_server，'查詢'）
Below is a simple example of the OPENQUERY usage:
以下是OPENQUERY用法的簡單示例：
SELECT * FROM OPENQUERY(DEV_SQL,'SELECT * FROM testdb.dbo.Profile')
比較方式 (Comparison)
When using the Linked Server to query a remote server, the query optimizer will create the execution plan after classifying and dividing the query into local and remote queries, where the local queries will be executed locally and the remote queries will be sent to the remote server, then combined together to display the final result to the user as single result set. Another disadvantage of the Linked Server is that, no filtering will be applied on the remote server if the query has WHERE clause, where it will retrieve all the records from the remote table and do the filtering and the joining locally.
使用連結伺服器查詢遠端伺服器時，查詢優化器将查詢分類後分為本地查詢和遠端查詢，進而建立執行計劃，本地查詢将在本地執行，遠端查詢将發送到遠端伺服器，然後合并在一起以将最終結果作為單個結果集顯示給使用者。 連結伺服器的另一個缺點是，如果查詢具有WHERE子句，則不會在遠端伺服器上應用任何篩選，在WHERE子句中它将從遠端表中檢索所有記錄，并在本地進行篩選和聯接。
In the OpenQuery function case, the SQL Engine will not try to classify the query or check what it will do, simply it will send the query as is to the remote server. Parsing the SQL query, generating the execution plan and all filtering will be performed on the remote server.
在OpenQuery函數的情況下，SQL引擎将不會嘗試對查詢進行分類或檢查查詢将執行的操作，隻是将查詢原樣發送到遠端伺服器。 解析SQL查詢，生成執行計劃，所有篩選将在遠端伺服器上執行。
In general, OpenQuery is faster than the linked server as the SQL Engine will not break the query before sending it to the remote server, but useful only for one-time less frequent remote connections.
通常，OpenQuery比連結伺服器快，因為SQL引擎在将查詢發送到遠端伺服器之前不會中斷查詢，但僅對一次性通路頻率較低的遠端連接配接有用。
Using the OPENROWSET and OPENDATASOURCE functions, you have to specify all the connection details including the username and password each time you use it. Although these functions don’t provide all linked server functionality such as the security management, it consumes less resources from your server. As these functions open a one-time connection to the remote server, it is better to use the linked server for frequent remote server access.
使用OPENROWSET和OPENDATASOURCE函數，您必須在每次使用時指定所有連接配接詳細資訊，包括使用者名和密碼。 盡管這些功能不能提供安全管理等連結伺服器的所有功能，但它消耗的伺服器資源更少。 由于這些功能打開了到遠端伺服器的一次性連接配接，是以最好使用連結伺服器進行頻繁的遠端伺服器通路。
翻譯自: https://www.sqlshack.com/querying-remote-data-sources-in-sql-server/