SQL Server – One Name for Use in a Connection String

availability-groupshigh-availabilitysql server

I'm not primarily a dba so I apologize if this question is high-level and please provide feedback if I'm asking the wrong question or going about this the incorrect way.

I currently have one database (virtual) server that many applications point to. Some are line of business apps and some are custom applications using connection strings. The server is old and I want to upgrade the OS and SQL Server version but I don't want to do an in-place upgrade, I'd rather build a new server and migrate all of the applications and I'm realizing that it's going to be a gargantuan task. We're going to have to go through all of the settings in our applications but also all of the code in-house applications to change the name of this server in every connection string.

My question is:

  • How do I go about this so that this is the last time I ever have to
    go through this task?

  • Should I be using alwayson clustering and use the listener in my
    connection strings?

  • Is there a way I can do this and use the name of my server as the
    virtual network name so that I don't have to change all of my
    connection strings?

  • Should I be using something else entirely?

Thank you for any info provided.

Best Answer

I'll expand my comment into a proper answer, as requested.

Since, in this situation, the desired result is more of an automatic redirection to the new server without needing to change connection strings then the following options are available:

  1. DNS Alias (CNAME)
  2. Hardware Load Balancer
  3. SQL Alias
  4. Local Host Record

Each of these has its' own merits, however since this change is environment wide you'll want to have the least amount of moving parts and administrative overhead.

A Local Host Record (editing the HOSTS file) would have to be completed on each server so that it could resolve the proper address. This would be bother administratively burdensome and also riddled with troubleshooting issues.

A SQL Alias would also work but would fall under the same set of issues as the Host Record.

Hardware Load Balancers (or software load balancers, depending on implementation) are a great alternative as they can do the DNS swap for you. This could bring up some issues with Kerberos or Certificate signing, however those application could be singled out and fixed (potentially, depending on your environment and resources).

The DNS Alias is roughly the same as the Hardware Load Balancer option and the same set of issues would occur. However this option does not require specialized hardware or software in order to complete.

I would recommend either the HLB or DNS Alias options.

Related Question