How to Make Link Server in SQL Server

 Creating a linked server in SQL Server allows you to connect to and query external data sources directly from your SQL Server instance. This can include other SQL Server instances, databases like Oracle, MySQL, or even non-relational data sources. Below is a guide on how to create a linked server in SQL Server.

Step-by-Step Guide

1. Open SQL Server Management Studio (SSMS):

   • Launch SSMS and connect to your SQL Server instance.

2. Open the Object Explorer:

   • In the Object Explorer, expand the node for the SQL Server instance where you want to create the linked server.

3. Navigate to Linked Servers:

   • Expand the Server Objects node.
   • Right-click on Linked Servers and select New Linked Server....

4. Configure the Linked Server:

   • In the New Linked Server dialog, you need to provide the following details:

   • General Page:

     • Linked server: Provide a name for the linked server.
     • Server type: Choose the appropriate server type.
       • If connecting to another SQL Server, select SQL Server.
       • For other data sources, select Other data source.
     • Provider: Select the appropriate OLE DB provider for the data source.
     • Product name: This can be a descriptive name of the data source (optional for SQL Server).
     • Data source: The name or network address of the server to which you are linking.
     • Provider string: Connection string information (if needed).
     • Location: For some providers, you may need to specify the location.
     • Catalog: The default database/catalog to use.

   • Security Page:

     • Configure the security settings to control how the local server will connect to the linked server.
     • You can choose to:
       • Not be made: Local logins cannot access the linked server.
       • Be made without using a security context: Connect using a self-mapping mechanism.
       • Be made using the login's current security context: Use the current login's security context.
       • Be made using this security context: Specify a remote login and password to use for the connection.

   • Server Options Page:

     • Configure additional server options if needed (e.g., collation compatibility, data access settings, etc.).

5. Create the Linked Server:

   • Click OK to create the linked server. If everything is configured correctly, the linked server should appear under the Linked Servers node in Object Explorer.

Example Using T-SQL

Alternatively, you can create a linked server using T-SQL. Below is an example for linking to another SQL Server instance:

sql
EXEC sp_addlinkedserver 
   @server=N'MyLinkedServer', 
   @srvproduct=N'SQL Server'; 

EXEC sp_addlinkedsrvlogin 
   @rmtsrvname = N'MyLinkedServer', 
   @useself = N'False', 
   @locallogin = NULL, 
   @rmtuser = 'RemoteUser', 
   @rmtpassword = 'RemotePassword';

• Replace 'MyLinkedServer' with the name you want for your linked server.
• Replace 'RemoteUser' and 'RemotePassword' with the appropriate credentials for the remote server.

For non-SQL Server data sources, the T-SQL might look like this:

sql

EXEC sp_addlinkedserver 
   @server = N'MyOracleServer', 
   @provider = N'OraOLEDB.Oracle', 
   @datasrc = N'OracleServerName';

EXEC sp_addlinkedsrvlogin 
   @rmtsrvname = N'MyOracleServer', 
   @useself = N'False', 
   @locallogin = NULL, 
   @rmtuser = 'oracle_user', 
   @rmtpassword = 'oracle_password';

• Replace 'MyOracleServer' with the name for your linked server.
• Replace 'OraOLEDB.Oracle' with the appropriate OLE DB provider for Oracle.
• Replace 'OracleServerName' with the network name of your Oracle server.
• Replace 'oracle_user' and 'oracle_password' with the appropriate Oracle credentials.

Testing the Linked Server

To test the linked server, you can run a simple

SELECT * 
FROM MyLinkedServer.DatabaseName.SchemaName.TableName;

Replace MyLinkedServer, DatabaseName, SchemaName, and TableName with the appropriate names for your setup.

Creating a linked server in SQL Server provides a powerful way to access and query data from diverse sources, enabling greater flexibility and integration in your data management workflows.