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).
ExamplesLets say we are using the
AdventureWorksdatabase 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\SALESwe 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 thisPersonally 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.