SQL Server Upgrade - A new Approach for Upgrading

Question

Microsoft's technical guide for SQL Server upgrade references two upgrade paths: in-place and side-by-side.

I don't see a third, visibly more viable/convenient option, which is to do an in-place upgrade of a cloned version of the server/SQL Server. Here is what I mean: why not clone the server in question, then perform an in-place upgrade, and if all goes well, then one could simply create an ALIAS and then have all applications and users automatically re-routed to the new server. Are there any downsides to this? Is there something I am not seeing? Is using an alias to redirect users and apps not wise?

ASSUMPTIONS:

1. I am assuming that this is a simple case of upgrade: no mirroring, no replication, etc. This is just one server with a number of databases that need to be upgraded.
2. I am assuming that direct upgrade is supported. Specifically, in this case, it's from SQL Server 2008R2 to 2016.

Answer 1

Since one of the primary tenets of SQL Server, transactional consistency, is difficult to get right when performing a clone operation, I'd recommend against using a clone to perform an upgrade in the way you suggest. A clone of the underlying machine while the SQL Server service is running and accepting client connections might result in the clone containing invalid data. If you can create a clone of the machine while the SQL Server service is turned off, your proposed solution might be a viable method of performing an upgrade. Having said that, I'd tend towards not using a VM/SAN clone since the potential for it to go badly wrong is pretty high. As any DBA worth their money will tell you, unnecessary risk is unacceptable.

Having said that, using a DNS alias is an excellent way of abstracting away the machine name from a server, and is what Windows Failover Clustering uses to direct clients to the active node in a clustered SQL Server installation.

The high-level steps to effect an upgrade where you have a DNS alias in place look like:

1. Ensure the DNS alias has a short TTL value that will allow clients to "see" changes to the IP address pointed at by the alias quickly enough to connect to the new machine in the desired period of time. I'd consider 1 minute.

2. Create a new machine, or VM, with the latest version of Windows and SQL Server as desired.

3. Backup the databases on the old server, then restore them to the new server.

4. Test all software/website/applications against the new server to ensure compatibility.

5. Once testing is complete, and you're happy with the new server, disable user-connections to the old server to ensure no one makes updates that won't be seen on the new server.

6. Take a final backup of the databases on the old server, then restore those databases to the new server.

7. Modify the DNS alias to point to the new server's IP address.

At this point client applications should be able to use the new server without any changes required at the client end.

The steps above assume you have an upgrade window long enough for you to perform a backup-and-restore of the databases involved in the upgrade process. If you have no upgrade window, i.e. you require 99.999% uptime, you'll need to consider using mirroring, log shipping, or failover clustering to minimize downtime during the cutover to the new machine.