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:

• Backup and restore: This is a good option for smaller systems and if you want piecemeal migrations; you may also consider detach and attach
• Pre-staging: This means preparing the data using full, differential, and transaction log backups to minimize the data transfer; log shipping is also a consideration
• Database mirroring: This allows for easy migration of data from the old system to the new.
• Availability Groups: This is more complex than database mirroring because multiple databases can be involved

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:

1. Verify that backups exist for all databases (user and system). If you have a database that is not in SIMPLE recovery mode, make certain a transaction log backup exists. Verify that these backups can be restored.
2. Review the list of prerequisites for SQL Server 2016 and install whatever is needed.
3. Run the SQL Server 2016 installation media.
4. Perform your post-upgrade tasks. 
5. Test, test, and test to ensure that everything is working as expected.

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: 

1. Verify that backups exist for all databases (user and system). If you have a database that is not in SIMPLE recovery mode, make certain a transaction log backup exists. Verify that these backups are able to be restored.
2. Script out any and all necessary system objects.
3. Script out any and all necessary SSIS packages (either from MSDB or as flat files).
4. For a new instance on a new server:
   1. Review the list of prerequisites for SQL Server 2016, and install whatever prerequisites are needed.
   2. Install the desired version and edition of SQL Server 2016.
5. Use script(s) from old server to create necessary system objects on the new server.
6. Migrate SSIS packages to MSDB (or as flat files, if applicable).
7. Select database(s) to migrate, take offline. 
8. Migrate database to new instance. Repeat for each database.
9. Perform your post-upgrade tasks. 
10. Test, test, and test to ensure that everything is working as expected.

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:

1. Choose your high-availability method (log shipping, mirroring, Availability Groups).
2. Choose one of the following:
   1. Upgrade one of the secondary nodes following the in-place upgrade instructions above.
   2. Install SQL Server 2016 on a new server (and add it as a node if applicable).
3. Fail over to that secondary node. 
4. Perform any post-upgrade tasks.
5. Test, test, and test to ensure that everything is working as expected.
6. Repeat the upgrade for any remaining secondary nodes. 
7. Perform any post-upgrade tasks.
8. Test, test, and test to ensure that everything is working as expected for each node.
9. Repeat the upgrade for the primary node. 
10. Perform any post-upgrade tasks.
11. Test, test, and test to ensure that everything is working as expected for the primary node.

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.