SQL Server: How to create a linked server pointing to a server different from its name

Background

The purpose of this article is to provide a step-by-step method of creating a linked server that point to a server different from its name. Configuring a linked server in this manner is very handy when we need to execute SQL scripts containing linked server names in our different environments.

Suppose there are servers ‘A’ and ‘B’. You want to make server ‘A’ as linked server on your machine. Once completed the configuration of linked server for server ‘A’. It should be points to itself (Server ‘A’) only. But instead of pointing the same server (server ‘A’), you can also point it to some other server (server ‘B’). If you point the Linked server ‘A’ to server B‘, then Linked server ‘A’ will extract all the data from server ‘B’.

Where to use?

This technique is very useful while working in different environments (dev, test, pre-production) with scripts having linked server names added at different places.

Let’s assume that our scripts have linked server name referring to a production server mentioned at many different places. We may not be able to test the script with respect to the production server. Also every time changing the code (e.g.renaming the linked server for testing) is not good. In this scenario, we can use our technique to create a linked server (may be our production server) that points to our development server but with a name same as the production server. Thus, we will be able to use the same script in different environments without changing the linked server names.

What is a Linked Server?                        

Linked Server is a mechanism in SQL Server by which we can add other SQL Server to a Group on a different SQL Server instance and query both the SQL Server DBs using T-SQL Statements. A linked server definition specifies an OLE DB provider and an OLE DB data source. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.

Linked Server Configuration

Approach 1 – Using Script to create Linked Server

/****** Object:  LinkedServer ******/

--New Linked Server with Windows Authentication

EXEC master.dbo.sp_addlinkedserver

--Provide a Name for Linked server by which we want to create it       
@server = N'BIMSQL01',
@srvproduct=N'SQL Native Client',
@provider=N'SQLNCLI',
/*Name of the server that we want to point. Please note that the name of the linked server and the actual server it points to are different.*/
@datasrc=N'BIMSQL02', 

@provstr=N'Provider=SQLOLEDB.1; /*complete connection string for a server that we want to point*/

  Integrated Security=SSPI;

  Persist Security Info=False;
  Initial Catalog=ITSDB;
  Data Source=BIMSQL02'                          

--If you have SQL login, provide credentials else make it as NULL.

EXEC master.dbo.sp_addlinkedsrvlogin

       @rmtsrvname=N'BIMSQL01',   --Name for Linked server

       @useself=N'False',

       @locallogin=NULL,

       @rmtuser=N'sa',

       @rmtpassword='Q!W@E#R$'

 GO

Approach 2 – Using Linked Server Wizard

Follow the steps below for configuring the linked server that points to a server different from its name:

1)      For creating the linked server, go to Start, All Programs, Microsoft SQL Server 2008, SQL Server Management studio. It will open connection window. Check the server name & connect it.

2)      In Management studio, go to Server Objects, Linked Servers. Right click on the Linked server folder and click on New Linked Server…  

3)      It will open the Linked server configuration window.

4)      For configuring the linked server, fill the  following required  information about the linked server:

Ø  Linked server: Provide the name with which you are going to refer the linked server. For Ex: BIMSQL01

Ø  Server Type: Other data source

Ø  Provider: SQL Native Client

Ø  Product: SQL Native Client

Ø  Data Source: The data source is usually the name of the database server. So, here you need to give the name of the actual server (BIMSQL02) on which you want to query data remotely. Please note that the name of the linked server and the actual server it points to are different.

Ø  Provider string:  Here you need to provide the complete connection string for the server (BIMSQL02). The format of connection string is shown below:

Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=ITSDB; Data Source=BIMSQL02

Ø  Catalog:  It is optional field. You can mention the database name here.

Ø  Go to Security tab

Here you need to choose the security context in order to make connection with the linked server. For this document, we are using windows authentication. For windows authentication, you can choose connection will “Be made using the logins current security context”.

 For SQL Server authentication, we need to choose “Be made using this security context” and provide the required SQL login and password.

Ø  After giving the complete information, click Ok. It will create the Linked server named ‘BIMSQL01’ that will actually extract the data from BIMSQL02 server.

Ø  For checking the Linked server, go to Server objects, Linked server, Expand the linked server folder. It enlists the entire linked servers. Verify that ‘BIMSQL01’ should be in the list.

Ø  For testing the linked server connection, right click on the linked server (BIMSQL01) and click Test Connection.

Ø  Test the data retrieval using T-SQL:

 SELECT * FROM BIMSQL01.ITSDB.dbo.Region

 It will extract the data from BIMSQL02 server instead of BIMSQL01 server.

Conclusion 

By using the above steps, we can create a linked server that point to a server different from its name.

----------------------------------End of Document----------------------------------------