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/