How to create linked server to SQL Server instance itself (loopback)

I’ve been using this configuration at least for 10 years starting with SQL Server 2000 and was surprised when realized that not many DBAs know about it.

Basically, the idea is to have one or several linked servers pointing to the local instance itself. There are several situations when it might be helpful:

1. In a test environment we can use it for learning of different features of linked servers. This is how I found out about this trick.

2. On development servers we can simulate distributed nature of a production environment. An alternative approach would be using named instances on the same server, but it would require more resources on the development server, especially if your environment consist of more than 2 instances.

3. In production we can use it for a consolidation of several distributed databases in one instance. So, if you have an application that references databases on 2 separate instances of SQL Server and you need to move them to one server you don’t need to change application code right away.

It doesn’t mean that I advise to use it in a production environment instead of changing code of an application. Using a linked server in your code will always have some additional overhead comparing to local calls, but I’ve been to situations when changing all code required longer time than was allocated to this task and the following trick was handy.

So, if you try to use a “Create Linked Server” window in Microsoft SQL Server Management Studio and provide a local name or alias you will get the following message:
“Microsoft SQL Server Management Studio.
You cannot create a local SQL Server as a linked server.”

You have 2 options to solve this problem:

1. You can connect to the server by using a different name or alias. Let’s say you need to reference server X with 2 additional aliases: Y and Z. So, to test a distributed application using just one server X you must create 3 linked server objects: X,Y,Z. If you connect to the server using it’s name (X), you will have no problems creating Y and Z linked server objects, but creation of the linked server named ‘X’ will fail with error message mentioned above. All you need to do is to connect to server by using one of aliases (Y or Z) and you will be able to create ‘X’ linked server object without any problems.

2. Just use a stored procedure instead of SSMS. Here is a code for example above:

EXEC master.dbo.sp_addlinkedserver @server = N'X', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'X',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedserver @server = N'Y', @srvproduct=N'Any', @provider=N'SQLNCLI10', @datasrc=N'X'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Y',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedserver @server = N'Z', @srvproduct=N'Any', @provider=N'SQLNCLI10', @datasrc=N'X'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Z',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

Assuming that all aliases for X,Y,Z were configured properly in DNS it should work like a charm.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>