posts - 230, comments - 423, trackbacks - 27

My Links



Follow billgraziano on Twitter

Article Categories


Post Categories


SQL Server

How to Name Linked Servers

I did another SQL Server migration over the weekend that dealt with linked servers.  I’ve seen all kinds of odd naming schemes and there are a few I like and a few I suggest you avoid.

Don’t name your linked server for its IP address.  At some point whatever is on the other end of that IP address will move.  You’ll probably need to point your linked server to a new IP address but not change the name of the linked server.  And then you’ve completely lost any context around this.  Bonus points if a new SQL Server eventually ends up at the old IP address further adding confusion when you’re trying to troubleshoot.

Don’t name your linked server based on its instance name.  This one is less obvious.  It sounds nice to have a linked server named [VSRV1\SQLTRAN01].  You know what it is and it’s easy to use.  It’s less nice when you’ve got 200 stored procedures that all reference this linked server but the database they reference has moved to a new instance.  Now when you query this you’re actually querying a different instance.

(Please note: I’m not saying it’s a good idea to have 200 stored procedures that all reference a linked server.  I’m just saying it’s not all that uncommon.)

Consider naming your linked server something that you can easily search on.  See my note above.  You can also get around this by always enclosing the name in brackets.  That is harder to enforce unless you use some odd characters in it.

Consider naming your linked server based on the function.  For example, I’ve had some luck having a linked server named [DW] that points to our data warehouse server.  That server can change names or physically move and all I need to do is update the linked server to point to the new destination.  The descriptive name of the linked server is still accurate.  No code needs to change and people still know what it is just by looking at it.

Consider naming your linked server for the database.  I’m still thinking through this one.  It may mean you have multiple linked servers that point to the same instance.  I’ve found that database names rarely change.  It also makes it easier to move individual databases to new servers.

Consider pointing your linked servers to DNS entries and not IP addresses.  I’ve done this for reporting databases and had some success.  Especially for read-only snapshots that can get created on the main database or on the mirror. 

What issues have you had with linked server names?  What has worked for you?  Where are the holes in my approach?

Print | posted on Monday, August 15, 2011 4:03 PM | Filed Under [ SQL Server Stuff ]



# re: How to Name Linked Servers

These are excellent recommendations. I have come to similar conclusions after several years of working with SQL Server.

Always think about, "if something changes/moves in the future, what do I need to modify to cope with it?"
8/16/2011 12:24 AM | Ken

# re: How to Name Linked Servers

I deal with linked servers mostly for Oracle instances. I would recommend to use the Microsoft OLE DB Provider for ODBC Drivers, and then setup a system data source. This can also make things easier when you have development, test, and production environments.

As for the naming convention, if the database has been in use for a while chances are that people within the organization are already referring to it with a name that you can reuse.
8/16/2011 9:22 AM | @ggponti

# re: How to Name Linked Servers

>> I’m saying it’s a good idea to have 200 stored procedures that all reference a linked server

you must mean this:

I’m NOT saying it’s a good idea to have 200 stored procedures that all reference a linked server
8/21/2011 10:29 PM | Jesse

# re: How to Name Linked Servers

Just suppose you *did* have 200 procs referencing dozens of objects on a linked server, why not use synonyms to refer to those objects? When you update the target of the synonym then the procs will continue to work.

I'm with you on naming linked servers according to the function they serve.

Thanks for an interesting article :-)
8/22/2011 6:49 AM | John Neville

# re: How to Name Linked Servers

We do ours by DB name (or the application which is the primary owner of the DB), and you are correct, it does result in multiple linked server entries to the same server, but it allows for the ultimate in flexibility if one of your application databases experiences a massive increase of volume (or someone applies some really bad code, or you are slowly moving to single-purpose virtualized instances) and you need to move just that one DB to another instance.

I agree with using OLE DB Provider for ODBC. Back with SQL 2000 we went to direction of using SQL Native Client, and we stuck with it through SQL 2005, only to realize after the cat that as we migrate, we ar eunable to update the linked server entries and so all the stupid security entries have to be redone because you have to drop/recreate the linked server in order to update its destination.

Great suggestions Bill.
8/24/2011 3:53 PM | Joe Fleming @muaddba

# re: How to Name Linked Servers

Im a fan of naming linked servers by their instance name. It makes it very easy to know at quick glance where the view, SP etc is pointing to. In my last job I dealt with a lot of sql servers that all pointed to each other and had to move databases around a lot and found it quick and easy to just fix the SP's or views within the DB being moved through a couple simple queries like:

-- replace all views
'[server01\instance01].', '[server02\instance02].')
FROM information_schema.views
WHERE view_definition LIKE '%server01\instance01%'
10/4/2011 9:17 AM | Jason Smith
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET