Using Friendly Names for SQL Servers via DNS

By:   |   Comments (17)   |   Related: > SQL Server Configurations


Problem

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?

Solution

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:

  1. 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).
  2. 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.

Changing Servers

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.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Saturday, January 18, 2014 - 11:16:28 AM - Davey Back To Top (28132)

Couldn't you do something similar to this with clustering.  You would cluster SX and SY and use the SV alias for the cluster name.  You then have each instance run on a separate machine.

Monday, October 21, 2013 - 8:38:34 AM - K. Brian Kelley Back To Top (27210)

It looks like you're going from one server containing two instances to two servers containing one instance a piece. Is that correct? If so, there's no way to make that work without some sort of front-end load balancer that can know which server to route to. Even with that said, because you're talking named instances, your clients would need to know what port to connect to because the traffic to locate an instance isn't instance specific, but server specific.

Saturday, October 19, 2013 - 7:02:21 PM - Sundar Back To Top (27198)

Databases from 02 SQL instance of a box SV\A and SV\B have been migrated to SX\A and SY\B respectively.

We need alias for both new Server with thier original name, for App using it in Hard code manner.

  • Alias for SX\A as SV\A works fine from all other boxes too.
  • Alias for SY\B as SV\B works only from local box, not from others

All setting are identical (SQL edition, version, TCP [dynamic for both], SP). What could be wrong?



Note: SX and SY are virtual Server on same node 

 

Friday, April 27, 2012 - 11:12:04 AM - KMC Back To Top (17158)

In regards to the first caveat, when setting up your SSL certificate if you specify the correct Subject Alternative Name(s), will the new CNAME of A record work then?

Tuesday, April 10, 2012 - 10:17:19 AM - K. Brian Kelley Back To Top (16834)

Reuben, you could easily accomplish the same thing by using default instances and changing the static port. The reason I don't like named instances has nothing to do with functionality but end user support. If you're talking about apps, the port is an easy way to go. When you're talking about end users, ports can be hard because they have nothing to tie the numbers to for their memory.

 

Tuesday, April 10, 2012 - 5:36:18 AM - Reuben Sultana Back To Top (16830)

An excellent technique to avoid hard-coding server names.  The only part I do not agree with is about the instance names (more below).  I prefer installing SQL Server instances as named instances on all environments.  Applications are then each allocated an individual DNS and port number, and the connection string Server parameter is set to (for example) "APPDNS.contoso.com,9252".  This technique is described in detail in my blog post titled SQL Server Connection Strings, Unique Application DNS and Listening Ports

With regards to the instance names, I noticed that when connecting to an SQL Server instance "SQLSRVR1\INST1,9252" (example) the instance name can be replaced by any alpha characters!  Thus "SQLSRVR1\INSTX,9252", "SQLSRVR1\DUMMY,9252", or "SQLSRVR1,9252" will all connect to "SQLSRVR1\INST1" on port 9252.

Keep up the good work!

Reuben

Friday, April 6, 2012 - 12:26:36 PM - John Dennis Back To Top (16803)

We've been doing this for years as well.  To go one step further--we create double CNAME records.

App01sql.domain.com, app02sql.domain.com, app03sql.domain.com CNAMEs get pointed to sql01.domain.com CNAME gets points to host01.domain.com HOST record.

In this way we can move a single application database by repointing application CNAME to sql02.domain.com CNAME as an example. Or, move all applications by repointing sql01.domain.com CNAME to a new host alltother.

 

John

 

Wednesday, April 4, 2012 - 7:39:14 AM - K. Brian Kelley Back To Top (16772)

Mitch, this works if you statically assign the port for a named instance, but some users find it hard to use because they've gotten used to servername\instance. That's the only draw back. We've fought that battle.

Perry, if they have SMS. :-) And GPO can be broke at a workstation. That's why it's easier to plan for the change and set TTL appropriately low in advance. That's foolproof for an internal DNS with respect to Windows and Windows DNS.

Wednesday, April 4, 2012 - 4:53:35 AM - EdVassie Back To Top (16769)

I have used this for many years and have seen its benefits over and over.

At my last place we wanted to automate failover, which included automating changing the DNS aliases.  The Network people did not want to give anyone outside their area authority to make the DNS changes.  We told them their bit of the failover had to be complete within 30 seconds of the phone starting to ring or they had to explain to the business why they were delaying the process.  Almost the next day they granted the necessary access so that changing the DNS aliases could be automated.

Wednesday, April 4, 2012 - 2:40:34 AM - Perry whittle Back To Top (16768)

Great article detailing the tricks possible with DNS, The only problem with option 1 is if you create many aliases and you change the nodes IP address you have many entries to maintain. With option 2 it's just the one, the actual node record. Also, don't lose sight of the reverse lookup zone either it's just as important and needs to be maintained. 

The ipconfig/flushdns may very easily be pushed out via SMS or GPO

Regards

Perry

Wednesday, April 4, 2012 - 12:01:31 AM - Mitch Miller Back To Top (16764)

Alternately, when using named instances, you could connect to the server/instance using the domain.name:port-num, where port-num is the TCP/IP port number assigned to the named instance.  Every named instance communicates using TCP/IP over an assigned and dedicated TCP port number.  

Monday, April 2, 2012 - 2:48:22 AM - C. Anderson Back To Top (16726)

Looks like a great idea.  Thank you for framing some discussion around this concept.  Very similar to the use case of a hosted web app and a domain registrar.  As SQL Server and any database evolves to a data-as-a-service architecture.  I'm even seeing less benefit to the uber-friendly name than to the support this offers for a migration among logical servers (physical or virtual).  The obvious constraint in a moderate to large organization is explaining and getting buy-in from the team that would support DNS vs. the team that would support the databases 

Thursday, March 29, 2012 - 5:27:17 AM - Dharmendra Rai Back To Top (16696)

Its realy a fantastic article, Thanks for this artilce Brian.

Wednesday, March 28, 2012 - 2:22:25 PM - Robert Back To Top (16684)

Fantastic article, had no idea you could use DNS entires for SQL machines like you can websites.  Now, just to get the Network Admins to "buy" into it.

Wednesday, March 28, 2012 - 12:57:10 PM - K. Brian Kelley Back To Top (16683)

Iggie, if that doesn't work, have them drop me a line and I can probably walk them through any difficulties or understand where they're coming from and translate. kbriankelley {at} acm {dot} org.

 

Wednesday, March 28, 2012 - 10:53:59 AM - Iggie Back To Top (16675)

Very timely article.  I've been beating my head against the wall with my network admins on this very issue for last few weeks, and this may finally give me some terminology they can understand, even if I don't (not being a network guy).

Wednesday, March 28, 2012 - 8:48:14 AM - BobC Back To Top (16669)

We've been using this technique for a few years now.  Each app gets it's own dns-sql name.  We've only run in to one app that could not find the server via the dns name.














get free sql tips
agree to terms