When working with T-SQL queries, it is common to connect to local databases and process data directly. However, there may be situations where you need to connect to a remote database located in a different instance on the same server or on a different physical server. SQL Server provides several methods to connect to remote database servers and query their data within your T-SQL statements. In this article, we will discuss four of these methods and how to use them to query remote SQL Server databases.
OPENDATASOURCE
The first method to query a remote SQL Server database is the OPENDATASOURCE T-SQL function. This function requires the OLE DB provider name and the connection string of the remote server. However, before using the OPENDATASOURCE statement, you need to ensure that the DisallowAdhocAccess registry key is set to 0 for the provider you want to connect to. Additionally, you need to enable the Ad Hoc Distributed Queries advanced configuration option, which is disabled by default in SQL Server.
Here is an example of using the OPENDATASOURCE function:
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=DEV_SQL;Integrated Security=SSPI').testdb.dbo.AddressBook
OPENROWSET
The second method to query a remote database hosted in a remote SQL Server is the OPENROWSET T-SQL function. This function requires all the connection information necessary to connect to the remote SQL server. It can also be used for bulk operations through the built-in BULK provider to read data from files. Like the OPENDATASOURCE function, using OPENROWSET also requires enabling the Ad Hoc Distributed Queries advanced configuration option.
Here is an example of using the OPENROWSET function:
SELECT * 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 of SQL Server or other systems like Oracle, Microsoft Access, and Excel. It allows you to execute distributed T-SQL queries against these data sources. Unlike ad hoc queries, a linked server provides a permanent connection to the remote server.
A linked server can be configured using SQL Server Management Studio or the sp_addlinkedserver T-SQL statement. When configuring a linked server, you need to provide the necessary connection information, such as the server type, server name, data source, and provider-specific connection string. You can also specify security settings and other options based on your requirements.
OPENQUERY
The last method 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 a linked server. The OPENQUERY function can be used in the FROM clause of a SELECT, INSERT, UPDATE, or DELETE statement, replacing the table name. It takes two arguments: the linked server name and the query.
Here is an example of using the OPENQUERY function:
SELECT * FROM OPENQUERY(DEV_SQL, 'SELECT * FROM testdb.dbo.Profile')
Comparison
When using a linked server to query a remote server, the query optimizer will create an execution plan that combines local and remote queries to display the final result as a single result set. However, no filtering will be applied on the remote server if the query includes a WHERE clause, resulting in retrieving all records from the remote table and performing the filtering locally.
In contrast, the OPENQUERY function sends the query as is to the remote server, allowing the remote server to handle parsing, execution plan generation, and filtering. This makes OPENQUERY faster than using a linked server for one-time, less frequent remote connections.
Using the OPENDATASOURCE and OPENROWSET functions requires specifying all connection details, including the username and password, each time you use them. While these functions do not provide all the functionality of a linked server, they consume fewer resources from your server as they open a one-time connection to the remote server.
In conclusion, SQL Server provides several methods to connect to remote databases and query their data within your T-SQL statements. The choice of method depends on your specific requirements, frequency of remote connections, and performance considerations.