1

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.
  • 3
    If you're upgrading from SQL 2008 to SQL 2016, now would be a great time to spin up a brand new server with the latest version of Windows that you can tolerate and the latest version of SQL Server that you can tolerate. You can restore backups from the 2008 server to the new server and test things out before redirecting users and applications to the new server. – Scott Hodgin Aug 28 '18 at 19:13
  • 1
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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