Pointing a SQL Server Linked Server to Different Data Sources

By:   |   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.

Add a SQL Server Linked Server in Management Studio

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.

SQL Server Linked Server Authentication

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ranga Babu 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



Comments For This Article




Monday, November 13, 2017 - 1:37:53 PM - Jay Back To Top (69672)

 

 OLE DB provider "DB2OLEDB" for linked server "SQL2014TODB2" returned message "The user account does not have permission to create the necessary DB2 packages, which are used to execute queries. Use the Data Access Tool and an account with permissions to create packages. Native error: The authorization given does not have the privilege BINDADD and cannot invoke the subcommand BIND against the package DST2.DDB3.MSCS001.(). SQLSTATE: 42501, SQLCODE: -567".

 















get free sql tips
agree to terms