How to Configure Generic SQL Server Linked Server Names

Problem

Every DBA is faced with the task of occasionally refreshing their databases from Staging to QA to Development. A while back I started working on a project where we were building out a new environment and our DBA team was frequently tasked with refreshing the new environment with data from the existing one. In this environment there were quite a few linked servers and every time we would restore a database to the new environment testers would start to get errors similar to the following:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'STAGING_CRMDBSERVER' in sys.servers. Verify that the correct server
name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add 
the server to sys.servers.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'AllContacts' because of binding errors.

Solution

Initial solution to find and replace SQL Server Linked Servers

On the surface this is a pretty simple issue to fix. First thing I would do is search all of my stored procedures, functions, views and triggers for the linked server listed in the error using the following TSQL:

SELECT name,type_desc
  FROM sys.objects
 WHERE OBJECT_DEFINITION(object_id) LIKE '%LINKEDSERVER%'
   AND type IN ('FN','IF','TF','V','P','TR')

Related Tips:

• Different Ways to Search for SQL Server database objects

After I had found all the objects, assuming that the corresponding linked server is already created in the new environment, you can script the update of all the objects to use this linked server as follows:

SELECT name,type_desc,
       OBJECT_DEFINITION(object_id) AS oldcode, 
       REPLACE(OBJECT_DEFINITION(object_id),'[LINKEDSERVER]','[NEWLINKEDSERVER]') AS newcode
  FROM sys.objects
 WHERE object_definition(object_id) LIKE '%LINKEDSERVER%'
   AND type IN ('FN','IF','TF','V','P','TR')

Now, if your environment is fairly stagnant and you aren’t refreshing databases very often, the above process is a good solution, but during this project we were refreshing one database or another daily. Also factor into the equation that we couldn’t just script all the objects one time and reuse the script since our current environment was still being updated by developers. This meant we had to complete this entire process for every restore which became quite time consuming.

---

Final solution to address SQL Server Linked Server in Multiple Environments

The final solution I came up with was to recreate all the linked servers in our current environment using generic names. After that I created linked servers in the new environment with the same generic names pointing to the correct server in the new environment. Now my linked server names are standardized across my entire environment so I can restore any database to/from any environment without the need to update any of my database objects. You can create a linked server with a generic name using SQL Server Managment Studio as follows:

Note: You have to put single quotes in the product name text box otherwise you will get an error saying this value can not be NULL.

You can also create a generic linked server using TSQL with the following:

EXEC master.dbo.sp_addlinkedserver @server = N'GenericLinkedServer', 
                                        @provider=N'SQLNCLI10', 
                                        @datasrc=N'SQLSERVERNAME\INSTANCENAME', 
                                        @srvproduct=''
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GenericLinkedServer',
                                     @useself=N'False',
                                     @locallogin=NULL,
                                     @rmtuser='Username',
                                     @rmtpassword='Password'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'collation compatible',
                                @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'data access',
                                @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'dist',
                                @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'pub',
                                @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'rpc',
                                @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'rpc out',
                                @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'connect timeout',
                                @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'collation name',
                                @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'lazy schema validation',
                                @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'query timeout',
                                @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'use remote collation',
                                @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GenericLinkedServer',
                                @optname=N'remote proc transaction promotion',
                                @optvalue=N'true'
GO

There is however one small drawback to having linked servers with generic names. This is that at first glance you can’t tell which SQL Server instance the linked server is pointing to. Using the following query we can list all the linked servers in our instance and show where they are pointing to and which user context they are using on the remote server:

SELECT s.name,data_source,provider,
       is_remote_login_enabled,is_rpc_out_enabled,
       is_data_access_enabled,uses_self_credential,
       remote_name
  FROM sys.servers s INNER JOIN
       sys.linked_logins ll on s.server_id=ll.server_id
 WHERE s.server_id != 0