2

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.

2
  • 3
    When you go to do the switch over, create a DNS CNAME to point to the new server using the old servers' name. DNS can do this, load balancers as well. You could use Always On, but I think it's overkill for just network name redirection. Additionally you can use sql aliases but the CNAME would work better with less admin effort. Jun 12, 2017 at 23:53
  • this is the answer ^ we're testing it now and everything seems to work with the CNAME positioned correctly. Since all of our applications and connection strings are pointing to the old name, we just created a CNAME with "oldServerName = newServerName" and everything works as it should after moving over all of the databases. I wish your comment was an answer so I could mark it as the answer and give you those sweet internet points
    – blsub6
    Jun 22, 2017 at 0:42

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.