Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Using Friendly Names for SQL Servers via DNS


By:   |   Last Updated: 2012-03-28   |   Comments (17)   |   Related Tips: More > 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


Last Updated: 2012-03-28


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

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

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

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

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 06, 2012 - 12:26:36 PM - John Dennis Back To Top

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 04, 2012 - 7:39:14 AM - K. Brian Kelley Back To Top

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 04, 2012 - 4:53:35 AM - EdVassie Back To Top

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 04, 2012 - 2:40:34 AM - Perry whittle Back To Top

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 04, 2012 - 12:01:31 AM - Mitch Miller Back To Top

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 02, 2012 - 2:48:22 AM - C. Anderson Back To Top

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

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


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

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

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

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

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.


Learn more about SQL Server tools