Using host names? You are doing it wrong!

This is one of my pet peeves on how many people seem to set up their environment and in particular what they use in their connection strings.

It comes down to this: If you are using the actual host name to connect to your database, you are doing it wrong!


What do I mean?  Simple if your database lives on a server named WINSQL01 you should never use that name for your applications.  Why?  Simple - what if you want to move your database to WINSQL02 through database growth, a fail-over to your disaster recovery server, a hardware refresh or server consolidation?  You will now need to reconfigure all your application connections to point the new name (WINSQL02).  That is potentially going to require a new application release and that means yet another round of application testing.

From something that could have involved just the DBA (moving the database) we now have to involve the developers or application vendor and your test team.  1 person to potentially dozens.  A cheap change to something downright expensive when you add up the time required by all the people.

So how do we avoid this scenario.  Its actually quite simple and I'm amazed how few companies are doing this in my experience: You use a DNS CNAME (aka alias).

Examples

Lets say we are using the AdventureWorks database that resides on WINSQL01. You would probably have a connection string something like this:

Data Source=WINSQL01; Initial Catalog=AdventureWorks; Integrated Security=SSPI;

We might then create a CNAME record similar to this:

NAME                  TYPE     VALUE
--------------------- -------- ---------------------
AdventureWorksDB      CNAME    winsql01.contoso.com

We then simply update the connection string to:

Data Source=AdventureWorksDB; Initial Catalog=AdventureWorks; Integrated Security=SSPI;

You'll notice that I have simply added "DB" to the end of the database name. This is a standard you may wish to use, although I prefer another one I will demonstrate shortly.

If we then move our database, for whatever reason, to WINSQL02 we simply update the DNS entry!

NAME                  TYPE     VALUE
--------------------- -------- ---------------------
AdventureWorksDB      CNAME    winsql02.contoso.com

This is almost always a far simpler change to make!

I personally like to set up DNS names for both production and disaster recovery databases. Especially if I'm using database mirroring. For that I'd do something like this:

NAME                  TYPE     VALUE
--------------------- -------- ---------------------
AdventureWorksProd    CNAME    winsql01.contoso.com
AdventureWorksDR      CNAME    winsql02.contoso.com

Then the connection string would be:

Data Source=AdventureWorksProd; Failover Partner=AdventureWorksDR;
Initial Catalog=AdventureWorks; Integrated Security=SSPI;

Note: The connection string wrapping is simply to fit on the screen.

What about named instances?

These are a little trickier I will admit. However; you can still use a DNS CNAME. Following the examples above, if our AdventureWorks database lived on WINSQL01\SALES we would use the connection string:

Data Source=AdventureWorksDB\SALES; Initial Catalog=AdventureWorks; Integrated Security=SSPI;

The problem comes when moving to another server. If the instance name is the same then you are good to go. I would try and do this if you have named instances and you want to have DR equivalents. Everything above still holds true. If you move the database to a server with another instance name then you have update the connection string. I know of no easy way around that and I would love to hear any suggestions you have.

Arguments against this

Personally I think there is every reason to do this and absolutely no reason not to do this. However; here are a few of the "arguments" I've heard against using it:

• It takes an extra network lookup to do this
  WRONG! You have to query DNS to get the IP address for the host name anyway
  
• Updating the DNS name for a failover takes too long
  Does it take longer than updating the connection strings on all your application servers or users workstations?  Why not script it?
  
• I can't remember all the CNAMES we have created
  You could always query the DNS to find out!  In a few places I've worked with the DNS admins to export the DNS tables to a text file that we then load into a table and create a Reporting Services report over that.
  
• I cant (or don't want to) create a CNAME for every database
  You don't have to.  Indeed I recommend you create one per application rather than database.  Typically an application that uses many databases will need those databases on the same server.  Take SharePoint as an example; you often have those DBs on the same server so create a single SharePointDB alias.

Conclusion

This really is a no brainer to me, but all too often I see this approach ignored or overlooked.  Its such a simple change to the way you work, it costs you nothing but it will save you a huge amount of time and effort when you want to move your databases.