Data is often spread across several SQL Servers or even distinct database systems (such as Oracle, MySQL, or PostgreSQL) in enterprise settings.  SQL Server provides a robust feature called Linked Server that enables you to access and manipulate data across several servers as if they were a single database, eliminating the need for manual data exporting and import.

Using real-world examples, this article describes what a linked server is, how to set it up, and how to query remote data.

What is a Linked Server?
A Linked Server in SQL Server allows you to connect to another database server instance (on the same network or remote) and execute distributed queries (SELECT, INSERT, UPDATE, DELETE) against OLE DB data sources outside of the local SQL Server.

It enables:

• Cross-server queries
• Centralized data access
• Remote procedure execution (EXECUTE AT)
• Joining tables from different servers

Setting Up a Linked Server
You can create a Linked Server via SQL Server Management Studio (SSMS) or T-SQL script.

Method 1: Using SSMS GUI
Open SSMS → Expand Server Objects → Right-click on Linked Servers → Choose New Linked Server

In the dialog box:

• Linked server: Enter an alias name (e.g., LinkedServer_Prod)
• Server type: Choose SQL Server or Other data source
• Provider: Select Microsoft OLE DB Provider for SQL Server
• Data source: Enter remote server name or IP

Go to the Security tab and configure credentials:

• Option 1: Use the current user’s security context
• Option 2: Specify a remote login and password

Click OK to create the Linked Server.

Method 2: Using T-SQL Script
Here’s how to create a Linked Server using SQL script:
EXEC sp_addlinkedserver
    @server = 'LinkedServer_Prod',
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = '192.168.1.100'; -- Remote Server IP or Name

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'LinkedServer_Prod',
    @useself = 'false',
    @locallogin = NULL,
    @rmtuser = 'sa',
    @rmtpassword = 'StrongPassword123';

Example: Querying Data from a Linked Server
Once the Linked Server is created, you can query it using four-part naming convention:
[LinkedServerName].[DatabaseName].[SchemaName].[TableName]

Example 1: Simple SELECT query
SELECT TOP 10 *
FROM LinkedServer_Prod.SalesDB.dbo.Customers;

Example 2: Joining Local and Remote Tables
SELECT
    a.OrderID,
    a.CustomerID,
    b.CustomerName
FROM LocalDB.dbo.Orders a
INNER JOIN LinkedServer_Prod.SalesDB.dbo.Customers b
    ON a.CustomerID = b.CustomerID;

Example 3: Executing Remote Stored Procedure
EXEC LinkedServer_Prod.SalesDB.dbo.sp_GetTopCustomers @TopCount = 5;

Updating Remote Data
You can even insert or update remote tables via Linked Server.

Example 4: Insert into remote table
INSERT INTO LinkedServer_Prod.SalesDB.dbo.Customers (CustomerName, City)
VALUES ('Peter', 'London');

Example 5: Update remote data
UPDATE LinkedServer_Prod.SalesDB.dbo.Customers
SET City = 'Udaipur'
WHERE CustomerID = 101;

Best Practices

• Use SQL authentication with strong passwords for remote login.
• Enable RPC and RPC Out only if needed.
• Use OPENQUERY() for performance optimization with complex joins.
• Limit access by creating specific database roles and permissions.

Using OPENQUERY (Performance Tip)
Instead of the four-part naming method, use OPENQUERY to push the query execution to the remote server:
SELECT *
FROM OPENQUERY(LinkedServer_Prod, 'SELECT CustomerName, City FROM SalesDB.dbo.Customers WHERE City = ''London''');

This approach reduces data transfer and often performs faster.

Removing a Linked Server
When you no longer need a Linked Server, remove it safely:
EXEC sp_dropserver 'LinkedServer_Prod', 'droplogins';

Conclusion
Linked Servers in SQL Server are a powerful way to integrate and access distributed data sources without complex ETL processes. With proper configuration, they can significantly improve data collaboration and reduce maintenance efforts across multiple systems.

However, always monitor performance and secure connections to prevent unauthorized access.