By: K. Brian Kelley | Last Updated: 2012-03-28 | Comments (17) | SQL Server Configurations
At my organization, servers have a very cryptic name or a name where you can't immediately tell the function. For instance, Amazon is the name of the SQL Server which our SharePoint uses as a back-end and Nile is the SQL Server hosting the databases for our HR application. This isn't intuitive and it makes upgrading the servers a challenge because we always have to go back and touch the applications where they specify database connections. Is there an easier way?
Yes, with the help of your DNS administrator there is an easier way. Wouldn't it be great if your HR folks only had to put in HR-SQL.mydomain.com for the database connection in their reports? They wouldn't have to remember it was on server Nile and they certainly wouldn't have to change their reports if you migrated their database from the Nile server to the server named Danube. In DNS there are two easy ways to do this. Either way is effective.
Your DNS administrator can do one of two things:
- Create an A record with the name that you want to use (HR-SQL) and the IP address of the server (the IP address for Nile).
- Create a CNAME entry with the name that you want to use (HR-SQL) and the DNS entry it's an "alternate" name for (in this case, Nile.mydomain.com)
What's the difference? An A record is like an address entry. Every server and workstation in an Active Directory domain should be registering an A record which comprises the computer name as well as the IP address. The CNAME is very much like an alias. In this case, a client would try to find the IP address for HR-SQL and would be told, "Oh, it's the same as Nile, and by they way, Nile's IP address is A.B.C.D." Now there are times when A records are mandatory. Case in point, when dealing with SharePoint sites. But typically CNAME records work fine to handle this sort of thing.
After you've transitioned your database(s) to a new server, you'll need to get back together with your DNS admin. Either the IP address for the A record will have to be changed, or the DNS entry the CNAME is pointing to will, depending on what option your DNS administrator chose. The beauty of this solution is you've just swapped SQL Servers and your end user doesn't feel a thing. He or she can fire up that report and everything works. The report says to connect to HR-SQL and the end user doesn't have to know you slaved over the weekend to migrate from the old, decrepit Nile server to the new, shiny Danube server. All he or she knows is the report functions as expected.
There is one thing to watch out for and that's the DNS record's TTL or Time To Live. This tells a system how long the DNS entry should be cached for before a trip back to the DNS server is required. If you have an exceptionally long TTL, you're DNS administrator will probably need to adjust its TTL in advance so that when you do make the switch, you don't have client systems that have the old entry cached, meaning they will go to the wrong place. While there is a simple command to flush the cache, it must be run on every computer individually.
A Caveat - SSL Certificates
If you're using an SSL certificate to encrypt the connection with the SQL Server, the SSL certificate must match the name of the server, whether the short name (Nile) or the fully qualified domain name (FQDN), which in the case of the Nile server would be Nile.mydomain.com. This doesn't allow for a friendly name. There are other means of encrypting the connection, chief among them the use of IPSEC.
A Second Caveat - Named Instances
While DNS will change the server name to something more friendly or recognizable, it won't do anything for named instances. So if you've got a SQL Server at Nile\HR, then users would have to connect to HR-SQL\HR to connect. See how the instance name is preserved? That means when you move from Nile to Danube, you'll have do so with a named instance on Danube matching the name of the named instance on Nile, in this case HR. If it's different, it won't work.
For instance, if Nile had multiple instances and one of them was HR, and you don't use a named instance on Danube, then if they were used to connecting to HR-SQL\HR, when you move to Danube, they won't connect. There is no instance named HR. Therefore, instance names must be preserved if you're using named instances.
A Third Caveat - Kerberos and SPNs
If you've set up Kerberos authentication to your SQL Server via the use of Service Principal Names (SPNs), realize that the SPN must have the name that the user is connecting to. So that means an SPN will have to be created for HR-SQL.mydomain.com, not just Nile.mydomain.com or Danube.mydomain.com. If you do load balancing or use friendly names for apps in your environment, this is probably old hat to your Active Directory administrator. It's not that complicated. Just remember that whatever the user types in for the name in order to connect must have a proper SPN.
- Read up on SQL Server name resolution troubleshooting.
- Learn how to do the same sort of thing for a SQL Server Reporting Services farm.
Last Updated: 2012-03-28
About the author
View all my tips