Pointing a SQL Server Linked Server to Different Data Sources

By: Ranga Babu   |   Updated: 2016-12-20   |   Comments (1)   |   Related: > Linked Servers

Problem

Most of us use SQL Server Linked Servers in our environment to fetch data from remote databases. The linked server names may be different in our QA and production environment, but we want to use the same code without having to make changes to the linked server name on the different servers.  In the below example we will cover how to create a linked server with a constant name that points to different servers depending on what servers we are working with.

Solution

Here is my scenario:

• QA Servers - QASERV1 and QASERV2
• Production Servers - PRODSERV1 and PRODSERV2

I have a linked server to QASERV2 from QASERV1 and used the linked server in the code. When moving from QA to production we have to change the server names, but to avoid having to change the code I want to use a standard name.  So, for this example I created a linked server named TESTLINKEDSERVER and this will be used to point to either QASERV2 or PRODSERV2 depending on what servers we are working with.

Here is the code to create the linked server.

use master
GO

EXEC master.dbo.sp_addlinkedserver @server = N'TESTLINKEDSERVER', @srvproduct=N'SQL Server'
GO

Now we can see TESTLINKEDSERVER under linked server list.

We will use sp_setnetname to point to the correct data source.  So for this first example we will point to QASERV2.

use master
GO

exec sp_setnetname 'TESTLINKEDSERVER', 'QASERV2'

You can use the server name or the IP address and port number as shown below.

use master
GO

exec sp_setnetname 'TESTLINKEDSERVER', '192.168.1.2,1433'

Now for linked server TESTLINKEDSERVER the data source is now QASERV2.

Now we need to setup the security by right clicking on the Linked Server and selecting Properties.  On the Security page we need to give the login and password to connect to QASERV2.

We can use the below T-SQL code to add a login and password.

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TESTLINKEDSERVER', @locallogin = NULL, 
   @useself = N'False', @rmtuser = N'Test', @rmtpassword = N'Password'
GO

On PRODSERV1 we can create the linked server TESTLINKEDSERVER and point it to PRODSERV2 using sp_setnetname as shown below.

USE master
GO

EXEC master.dbo.sp_addlinkedserver @server = N'TESTLINKEDSERVER', @srvproduct=N'SQL Server'
GO

exec sp_setnetname 'TESTLINKEDSERVER', 'PRODSERV2'

We also need to setup the security for this linked server and provide the login and password like we did on the QA server.

Summary

This way we can create a linked server with the same name and point it to different data sources such as dev, QA, production, etc.

I prefer using synonyms to make code the same between QA and production instead of using the four part linked server name, so now you have different options.

Next Steps

• Read these additional tips about Linked Servers.
• Read these additional tips about Synonyms.

About the author

Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips