Transferring aliases from Sql Server Configuration manager between machines

Ever since I have been working with Named Sql Instances using static ports, it has been a pain. 

Connection strings need to be recoded to include the port syntax:

  • <ServerName>[\InstanceName],<Port>
  • <IP Address>[\InstanceName],<Port>

This can be a bit of a drag when you have multiple config files on an application server for example. Setting up client side aliases avoids the need to manually recode connection strings. However if you have multiple servers and client machines to manage, this can be a pain, you dont want to manually set up every server on every machine. I was recently searching for a way to transfer these aliases around and was assisted by Joe Stefanelli who informed me the aliases were actually stored in the registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

To transfer these entries to another machine:

Navigate to that location in regedit and use the File -> Export... menu option to export the branch to a .REG file. Then copy that file to the new machine and use File -> Import... to load the keys you just exported into the new machine's registry.

Mark Broadbent (blog | twitter) also gave me a few ideas about how to take this registry file and roll it out via group policy. As Mark informs me these aliases can also be used for DR purposes by repointing the alias at a new server. This is a pretty neat trick I thought

Published Thursday, November 4, 2010 12:41 PM by blakmk

Comments

No Comments