Pointing a SQL Server Linked Server to Different Data Sources


By:   |   Updated: 2016-12-20   |   Comments (1)   |   Related: More > 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



Last Updated: 2016-12-20


get scripts

next tip button



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

View all my tips
Related Resources





Comments For This Article




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

 

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

 



download


Recommended Reading

Querying Active Directory Data from SQL Server

Understanding SQL Server Linked Servers

Access MySQL data from SQL Server via a Linked Server

Create a linked server to MySQL from SQL Server

How to setup linked servers for SQL Server and Oracle 64 bit client





get free sql tips
agree to terms


Learn more about SQL Server tools