Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Normalize your Linked Server Names

This is a technique I’m sure many people are already using in their environments.  Instead of using the actual name of your SQL Server as the name of the linked server that you call, use a generic name.  For instance.  If you have a couple of servers named SQLSales02 and SQLMarketing01 that you want to set up linked servers for, consider using more generic names such as SalesServer and MarketingServer.

The main benefit we garner from this in our environment is that when we move back and forth between our Test/Development/Production environments, we don’t have to worry about changing the names of linked servers in the code.  We just point the linked server on development to the development sales server and the linked server on production points to the production linked server.  No code changes.  Another benefit this confers is that if you ever need to upgrade or replace SQLSales02 (and the name of the server changes) you don’t have to hunt down all the code pointed to it, you just change the linked server name. 

Enough selling it, here’s how you do it.

Via SSMS:

SSMSSetup

  The Generic name I’m using here is RNDSERVER

  The name of the actual server is SCDSQLDEV5

  Note that the Product name is SQL Native Client (manually typed)

  Don’t forget to set up your linked server users and configuration options on the next tabs, but those are all as normal.

 

Via T-SQL:

EXEC master.dbo.sp_addlinkedserver @server = N'RNDSERVER2',
@srvproduct=N'SQL Native Client', @provider=N'SQLNCLI', @datasrc=N'SCDSQLDEV5'

Comments

Posted by Jason Brimhall on 15 September 2011

Excellent points - good reasons to normalize those names.

Posted by Steve Jones on 15 September 2011

Not a bad idea. I don't see servers move often to a new location, but this does make sense in providing a layer of abstraction if they do.

Posted by Manish Mittal on 19 September 2011

Good idea. I am alerady following it. I have 6-7 IPs of server where I need to interact. It's become easy for me to rememebr server with a meaning full name rather than  with IP. After a long gap usually I forget IPs, then these names help me to save my time to find which server belongs to what.

Posted by Willem Gossink on 19 September 2011

Interesting concept!

If you are using logshipping/mirroring for high availibility, this kind of abstraction can also be implemented through DNS alias names. This is especially useful if you use logshipping or mirroring and have applications that do not allow you to define a second (failover) SQL server in their connection strings.

Example: the Finance DBs run on SQL Server_A. Instead of using 'Server_A' in its connection string, the Finance App uses the DNS alias name 'Finance_sqlalias' which resolves to 'Server_A' through DNS.

If SQL Server_A fails, the databases on SQL Server_B take over (through logshipping or mirroring). The only changed needed is to make the DNS alias point to 'Server_B'. No changes are needed in the Finance App (except maybe a restart of services) to reconnect to the 'new' databases.

You can specify more than 1 DNS alias to point to any given SQL server. This allows for a more modular approach, and will allow you to move (groups of) databases between servers without having to modify the underlying apps. You can even use DNS alias names inside e.g. stored procedures to access DBs on other servers without knowing where they actually reside.

And if you maintain identical alias names in your DEV/ACC/PROD environments, you can be flexible about the number of servers in each environment and their names, without having to make changes in your app environment.

Unfortunately, this technique will not work if you use SQL named instances: the DNS alias points to a 'physical' server.

Posted by jeffgonnering on 19 September 2011

I agree it is a good idea in general, but I think synonyms will serve you better than hard-coding linked server names in stored procedures.

Posted by cathy.baker on 19 September 2011

I do something similar, but instead of an ODBC data source, I create an alias using SQL Server Configuration Manager.  Same concept, we can go from DEV/TET to Production with no code changes.

Posted by doln4u on 19 September 2011

Nice idea!

Posted by Seth Phelabaum on 19 September 2011

@Jeffgonnering:  We do use some synonyms as well, but only for one particular instance where the dev/test databases on are on the same physical server (long story).  Synonyms get overwritten when the database gets restored, so you need to have an additional step after a prod -> dev(or test) restore that overwrites all your synonyms with the correct server names.

Posted by jaramillo05 on 19 September 2011

Why don't you use Cnames with de DNS of Windows Server. Example you have two servers like you describe SQLSales02 and SQLMarketing01, but the aplications who use the linked server are in the database "SALES" in SQLSales02 and "Market" in SQLMarketing so you can Normalize creating a cname in DNS For example SRVSALES and SRVMARKET, then when you have changes in youre servers you only have to do is change the server names in DNS. And for normalize youre linked server you can use names who tell more with their names for example LNK_SRVSALES_SRVMARKET this names tells you this linked server are between the database an server for SALES and database MARKET, and dosnt mathers about the Real server names.

Posted by Seth Phelabaum on 20 September 2011

@jamarillo05:  I'm not 100% sure I'm following, but wouldn't using this technique point to only 1 server?  The dev/test/prod pushes without changing code is the far bigger benefit here than the abstraction layer provided in the case of server failures.  I merely mentioned that it was also something that this helped with.  As far as telling more with the names, I'd prefer a standard name that I could use across all (or at least most) of my servers rather than having to have a different linked server name that specifies the originating source.

Leave a Comment

Please register or log in to leave a comment.