Allen Kinsel - SQL DBA

Even an old dog can learn new tricks

I had an Aha! moment recently.  For my entire career as a DBA I have generally considered aliases for connections a workaround for bad behaving applications.  Whenever someone said «alias» my mind immediately heads to SQL Server client configuration aliases which I try to avoid if at all possible (since they are configured on each client)  It never snapped to me until recently that DNS aliases may be a good solution to a few problems  we're currently experiencing.

For disaster recovery reasons, as well as for manageability reasons we have decided to start using DNS aliases for every application connection to database servers.  This should allow us to have the luxury of moving databases from server to server without having to reconfigure multiple applications which would normally be a whole process in itself since the code was already migrated to production which is locked.

Using DNS aliases should also allow us to swap highly important applications over individually to a remote datacenter, which could have less computing power, without having to switch every application and thus kill the performance of that standby server.

There are certainly limitations to this, if for instance you want to move applications from one named instance to another.  In our current environment this isnt much of an issue since many of our production instances are indeed default.  The other major limitation to this is any change will have a small amount of downtime while the DNS changes are propgated throughout the network.

One more «gotcha» that weve already run into is vendor applications (surprise surprise) that resolve the DNS name to an ip address and then store that inside the application configuration.

We decided on working out a naming standard that looks like this:
For direct database access, where an application only connects to 1 database the following is used
dsDBNameEnvName
i.e. dsCustomerProd
for an application (like sharepoint) where many databases are going to be accessed we change it a bit
dsAppNameEnvName
i.e. dsSharepointProd

While this isnt necessarily a «new» idea, it was certainly a different idea in our environment and I suspect there are other «DBA's»  out there like myself that dont have a habit for using our network skills on a regular basis

Photo courtesy: Ronn Ashore