Upgrading to SQL Server 2016, Part Two

In the first post of this series on upgrading to Microsoft SQL Server 2016, I addressed reasons for upgrading, as well as some pre-upgrade tasks to be mindful of. In this installment, I’m going to focus on tasks that should take place during the upgrade. 

During the Upgrade    

I mentioned previously that there are two primary types of upgrades: in-place or side-by-side. With in-place upgrades, there is no need to worry about the transferring of data. Side-by-side upgrades, however, will require you to move data from one server to another. With that said, there are four main options for you to consider when it comes to data migrations:

I also discussed the concept of rolling upgrades in my first post. This is when you use a high-availability feature such as mirroring, clustering, or availability groups. The idea is that you can upgrade a secondary node, failover, and continue upgrading all nodes in this manner until you upgrade the primary node, and then fail back if needed.

Let’s look at the steps involved for each.

Steps for an In-Place Upgrade 

In-place upgrades are the easiest to perform, but the most difficult to rollback should there be any issues. The steps involved in an in-place upgrade are as follows:

Steps for a Side-by-Side Upgrade

Side-by-side upgrades have more steps and are considered more complex, but they also give you more flexibility for rolling back, because you’re not going to be touching the original system while it’s still in use. 

The steps involved for a side-by-side upgrade are similar for both an existing and new database server. The only difference is that for a new server, you will need to install SQL Server. Here are the steps: 

Steps for a Rolling Upgrade

Rolling upgrades can minimize downtime during upgrades. A rolling upgrade using database mirroring is my preferred method for doing SQL Server upgrades, but you could also use log shipping or availability groups—the choice of which feature you want is up to you. Just make sure you have a solid rollback plan for whichever feature you are using. 

The steps involved for rolling upgrades are as follows:

With rolling upgrades, you don’t have to fail back to the original server (the primary node). It’s perfectly fine to configure database mirroring just for the single purpose of a rolling upgrade. After you’ve failed over to the secondary, you then break the mirror and remove the server from your inventory. This is the same result as a side-by-side migration, but with less downtime than the traditional method of backup/restore or detach/attach. And for very large databases, this concept is crucial because restoring can be a cumbersome task.

It’s important to note that data movement in a rolling upgrade is in one direction only. You can migrate from an older version to a newer version of SQL Server, but not the other way around. So, if you’re doing a rolling upgrade and you move your data to an upgraded node, you can’t go back without recovering from backups on the original server. You’ll see error messages indicating this as well, so don’t be alarmed when they start. Just recognize that SQL Server is telling you that you cannot migrate down to an earlier version.

Summary

The SQL Server upgrade process itself is not difficult if you build a proper plan, but there are still additional tasks for you to do after the upgrade is complete and before you hand the system over to your end-users for testing. We’ll cover these post-upgrade tasks in the final installment of this series next month. 

Thomas LaRock is Head Geek at SolarWinds. Industry Perspectives is an occasional series of guest expert contributors talking about their craft and industry. Learn more about contributing to IT Pro Industry Perspectives.