SQL Server  

Using Linked Servers in SQL Server: A Complete Guide with Examples

In many enterprise environments, data is distributed across multiple SQL Servers or even different database systems (like Oracle, MySQL, or PostgreSQL). Instead of manually exporting/importing data, SQL Server offers a powerful feature called Linked Server that lets you query and manipulate data across multiple servers β€” just like they were part of the same database.

This article explains what a Linked Server is, how to configure it step-by-step, and how to query remote data using practical examples.

πŸ” 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

  1. Open SSMS β†’ Expand Server Objects β†’ Right-click on Linked Servers β†’ Choose New Linked Server

  2. 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

  3. 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

  4. 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 ('Vipin Mittal', 'Jaipur');

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 = ''Jaipur''');

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';

πŸ“š References (External Links)

Here are some official and community resources for further reading:

  • πŸ”— Microsoft Docs – sp_addlinkedserver (Transact-SQL)

  • πŸ”— Microsoft Docs – Linked Servers Overview

  • πŸ”— SQLShack – Querying remote data sources in SQL Server

  • πŸ”— Redgate – Managing Linked Servers securely

🏁 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.

Membership not found