SQL Client Aliases - Follow-up

Well, after much debate, we've decided to ditch SQL Client Aliases and go with DNS CName records instead. This will mean assigning separate IP addresses to each instance on our multi-instance servers and changing every non-standard port in the enterprise back to 1433, but we feel it's ultimately worth the effort. We're even going as far as setting up a separate DNS zone for our SQL Servers and adding that as a DNS search suffix enterprise-wide such that we DBAs can manage the CNames ourselves rather than relying on the 24-hour SLA of the infrastructure team to get anything done for us :) That said, I'm still kind proud of the Client Alias solution I developed, so I'll still be polishing it up and posting it here, though likely not for a few weeks as I'm taking a couple of weeks off to recharge.

SQL Client Aliases: Pros, Cons, How to Manage Them + One Big Gotcha

One pain point here in our organization is moving databases from one server and/or instance to another. There are two items, specifically, that are most painful.

First, since many of our applications are developed in-house, and the environment-specific configurations are source-controlled and are part of the application deployment, changing a connection string means re-deploying the application and taking an outage of longer duration than would be otherwise.

Second, many third-party applications make changing connection strings phenomenally difficult. Anyone who has tried to move Sharepoint databases can relate this this pain-point.

We're looking at having to do a production hardware and platform refresh over the coming months, including moving to VMWare for our production SQL Servers. Since every database in the organization is going to be moving, we decided to solve this problem once and for all by abstracting the SQL Server Host and Instance details from the applications. In a SQL Server-only environment, there are really two ways to do this. The first is using DNS CNAME entries, the second is to use SQL Client Aliases. There are pros and cons to each.

DNS CNAME: Essentially 'aka' entries in DNS. You map the CNAME to the actual hostname in DNS and then refer to the CNAME in your connection string. If the database moves, simply change the associated hostname and clients will redirect.

Pros:

• Central management. Make the change once and it's changed for the entire enterprise.
• Works on all SQL Server Clients

Cons:

• No support for named instances or non-standard ports(non-1433). If you use non-standard ports or named instances, such as in a multi-instance Windows Failover Clustering setup, you must still hardcode the instance name and/or port in the connection string. 
• Due to DNS caching, zone transfer timing, etc, you may not have complete control over when a changed hostname in the CNAME will be picked up and used by all clients.
• A Domain (or DNS) admin is necessary to make the change for you.
• No knowledge of all affected systems as the CNAME is 'global'.

SQL Client Aliases: Acts kind of like a 'hosts' file for SQL Server, providing SQL Server-specific name translation for the local machine. 

Pros:

• Support for named instances and non-standard ports.
• No latency issues from zone transfers or DNS caching. Change is immediately effective once made.
• Server Admin or DBA can create and manage as necessary.
• Knowledge of all affected systems as the Alias is 'local'.

Cons:

• No central management. If a database moves, you need to know where all the aliases live and update them. 
• Difficult to change all aliases at precisely the same time.
• Works only with the Microsoft SQL Client. (no JDBC, even with MS drivers)

We'll come back to that last Con for SQL Client Aliases in a second...

We settled on using SQL Client Aliases, primarily for the support for named instances and non-standard ports. The big Con, though, for us, was no centralized management, so we rolled our own. I created a database to track application-to-alias-to-SQLServer mapping and a set of powershell scripts to ease the deployment of Aliases. The scripts would take a target SQL Instance or AliasName as a parameter, read the details from the database and create or update all the affected aliases as appropriate. I plan to blog about this solution in the near future after we've worked the kinks out of it a bit. It was remarkably easy to do, since Aliases are just registry entries. 

This all worked great until we got to our first Java applications, that happened to be our main business application suite. Turns out SQL Client Aliases only work with the SQL Client, not JDBC. You'd think I'd have realized this given the name 'SQL Client Alias', but somehow that escaped me until this morning, after I was about 100 hours of development into this project. 

At this point I'm not sure what we'll do. We've determined that only about 5% of our total application inventory uses JDBC, so the problem is contained, but the affected apps are very high priority and have complex development cycles (5 QA environments + 2 shared integration environments + staging + production, etc). Perhaps we'll go ahead and use CNAMEs for that 5% and continue to use SQL Client Aliases for the remainder or perhaps we'll go entirely CNAME, I'm just not yet sure.