Selecting data from two different servers in SQL Server

sql

linked-servers

cross-server-communication

sql-server

byAnton Shumikhin·Sep 17, 2024

Swiftly access data across two SQL Server instances by setting up a Linked Server. Specify server connection with sp_addlinkedserver and handle authentication with sp_addlinkedsrvlogin. Use the four-part name convention to execute cross-server queries: [Server].[Database].[Schema].[Table].

-- Setting up a Linked Server - as easy as pie!
EXEC sp_addlinkedserver 'RemoteServer';
-- Please don't forget your manners - thank you, user and password! 
EXEC sp_addlinkedsrvlogin 'RemoteServer', 'false', NULL, 'User', 'Password';

-- Querying from your brand new Linked Server
SELECT * FROM [RemoteServer].[DBName].[dbo].[TableName];

For one-time queries, opt for OPENROWSET() to directly retrieve data without a permanent link:

SELECT * FROM OPENROWSET(
    'SQLNCLI', 
    'Server=RemoteServer;Trusted_Connection=yes;',
    'SELECT * FROM DBName.dbo.TableName'
);

Replace RemoteServer, DBName, dbo, and TableName with your setup. Also, ensure the necessary permissions for cross-server data operations.

Getting hands-on with Linked Servers: A step-by-step guide

When dealing with multiple SQL Server instances, setting up a Linked Server is the first step towards unity! Here's how:

Run sp_addlinkedserver to register the other server instance.
Use sp_addlinkedsrvlogin for setting up authentication credentials.
Use the four-part name convention for queries - [ServerName].[DatabaseName].[SchemaName].[TableName].

Be aware of authentication and permissions to ensure secure access.

Balancing efficiency and security in cross-server communication

Cross-server communication is a balancing act between chasing efficiency and maintaining security. Here are some approaches:

Streamline queries: Set default databases per server for simplified querying.
Handle authentication: Protect your data with proper password handling and login credentials.
Structure queries: When joining cross-server data, use efficient structuring to mitigate performance impacts.

Adventures in Ad hoc querying

For ad hoc, as-needed queries across server instances, two methods shine - OPENROWSET and OPENDATASOURCE:

OPENROWSET lets you fire off a one-off query and offers functionality for BULK operations.
OPENDATASOURCE allows similar functionality but without the need to define the data provider and connection initiative at the start.

Choose based on performance demand, installments, security, and, of course, data needs.

Tackling heterogeneous data across servers

When selecting data across servers, one often encounters diverse systems and data types. Here are some tips:

Deal with data type compatibility issues and scheme differences.
Tackle collation conflicts in string comparisons.
Keep in mind time zone differences across servers.
Recognize and manage data type limits that vary between servers.

These tricks will save your day (and maybe your career)

Here's the golden nuggets of wisdom for those seeking career-level proficiency in fetching data from different servers:

Distributed transactions: These will help you handle complex operations across servers.
Synonyms: Use these to create local aliases for remote objects.
In-depth system procedures: Get familiar with these to dig deeper for diagnostics and settings.
Dynamic SQL: Flexibility is the key to writing adaptable code for different server environments.