Monday, February 17, 2014

Using DNS to virtualize SQL Server naming

One very common problem is when you want to move you databases to a new SQL Server or create a new SQL Server for your datatabases: All the current connection strings, .ini files, ODBC links and so on, in the often many applications scattered throughout the organization needs to be updated with the new SQL Server name. Eg. Going from SQLSERVEROLD1 to SQLSERVERNEW1.

How to change SQL Server connections centrally

The simple solution to this problem is using DNS. Create a CNAME record in your DNS for each application that uses SQL Server. Point the CNAME record to the OLD server and start changing your connection strings, .ini files and so on for the last time.

Eg. your current SQL Server is named SQLSERVEROLD1. You have a CRM application that have one or more databases on the server.

You then create a CNAME record in DNS for the CRM application. A proposed naming could be: SQL-CONN-CRM, that points to SQLSERVEROLD1.

You then change the connection information in the CRM application from SQLSERVEROLD1 to SQL-CONN-CRM - and the next time you move the CRM databases, or upgrade the server - you just change the CNAME record to point at the new SQL Server - no application change needed.

What about named instances?
The CNAME trick will only work for the server part of a named instance - Eg. SQLSERVEROLD1\INSTANCE =>  SQL-CONN-CRM\INSTANCE.

If you would like the SQL-CONN-CRM\INSTANCE to virtualize instance naming as well, you will need to use SQL Server Aliases. (Another blog post to come)

No comments: