Upgrading to SQL Server 2016: Pre-upgrade tasks

In our previous post on upgrading to SQL Server 2016 we talked about the reasons you might have for wanting to upgrade. For this post, we will look at the pre-upgrade checklist items you should consider before upgrading to SQL Server 2016.

After you have decided that upgrading is something you want, you will need to start putting together a project plan. The simplest plan involves three steps:

1. Pre-upgrade tasks
2. Upgrade tasks
3. Post-upgrade tasks

Seems simple, right? Well, it can be, especially if you take the time to review the details. Before the upgrade project begins you need to do a lot of legwork. Trust me when I tell you that the extra legwork now will save you headaches later. Here is a checklist of items to review before your data is migrated.

1. Know your path(s)

To get to SQL Server 2016 you can upgrade directly from:
• SQL Server 2014
• SQL Server 2012 SP2
• SQL Server 2008R2 SP3
• SQL Server 2008 SP4

If you are running SQL Server 2005 or earlier, you need to upgrade to an intermediate version before upgrading to SQL Server 2016.

For those folks running SQL Server 2000 instances (yes we KNOW you still exist) you are not able to upgrade directly to SQL Server 2016 without first upgrading to an intermediary version.

2. Licensing changes

Starting with SQL Server 2012 licensing is done per-core, not per-socket. There will result in licensing changes for SQL Server 2016 compared to your current version. But SQL Server 2016 Standard edition does allow for Server + CAL licensing, too. Because of the change from socket to core, in-place upgrades may come with a hefty cost increase.

Also worth mentioning is that SQL Server 2016 SP1 allows for many features that were once Enterprise-only such as Availability Groups, data compression, partitioning, Columnstore, etc. Evaluate licensing costs and the list of features now available in Standard edition prior to starting any upgrade project.

3. Know your options

4. Gather inventory details

You must gather information about the servers and the database instances that are in scope for upgrading to SQL Server 2016. There are many tools available to help you collect these details such as Microsoft Assessment and Planning (MAP) Toolkit and SQL Power Doc, as well as 3rd party tools.

Gathering a list of server and database names may not be enough, you will want to collect details about the databases as well. Even a simple count of tables is a valuable piece of information to have. If you have 873 tables at the start, then you need to verify you have 873 when you are done. Same for stored procedures. Consider collecting details at the column level, too. Make certain that datatypes and collations are intact, view definitions haven’t changed, etc.

You need to collect details on the in-house and third party vendor applications using the database server. List out the availability requirements for every application using the instance and you need to include the applications that are connecting remotely.

Also worth noting is any application specific configurations that are applied to the server O/S, the database instance, and the database itself. You need to know what non-default configurations are in use by the application using the instance.

This might seem like overkill for many reading this, but if you have ever had the unpleasant situation where a table was missed because of a migration and/or upgrading to SQL Server 2016, you will know it can be quite valuable.

5. Data Migration Assistant

6. Deprecated features

With each new version of SQL Server there are some features that are marked as deprecated. Deprecated does not mean the features have been removed, it means that the features will possibly be removed in a future version. You should not use deprecated features for any new development work. The list of deprecated database engine features for SQL Server 2016 is here.

7. Discontinued features

With SQL Server 2016, Microsoft started publishing a list of discontinued items. These items are removed from SQL Server. You can see the list of discontinued items here. You should review these items and make certain your applications are not relying on a feature that will not be there.

8. Breaking changes

Did you know that Microsoft publishes a list of breaking changes for each version of SQL Server? Well, you do now. You should review them to the point that they are familiar to you. You don’t have to memorize them all. Become familiar with them so that if something odd happens you can think to yourself “…hey, is this odd behavior listed in the breaking changes section of the Books Online (BOL)”?

9. Behavioral changes

Previous versions of SQL Server have published a list of behavioral changes for the database engine. Similar to the breaking changes, the behavioral changes are changes that could still affect you in an adverse way. They are worth reviewing, and they are also things that the DMA is likely to never report back to you about because they aren’t things that *will* break, but things that *could* break.

I was not able to find a BOL entry for SQL Server 2016 for the database engine. There are pages for Analysis Services, Integration Services, and Reporting Services. You could always review the previous versions over at the SQL Server 2012 page and use that as a starting list.

10. Read the release notes

Because you’re a geek, that’s why. Take a few minutes and read the release notes. No, they aren’t as funny as the release notes for apps on your phone, but they can be useful for you to review anyway. It’s good to have as complete a picture as possible for the new version should something not work as expected, and there are details in the release notes you may not find elsewhere.

11. New environment requirements

Updating your server O/S should be part of the upgrading to SQL Server 2016 project plan.

Microsoft lists the minimum requirements for installing SQL Server 2016 on this page. But, those are the *minimums* there. Chances are if your servers don’t already meet those requirements then you aren’t looking to upgrade anytime soon anyway. But if you are upgrading, then it might be time to upgrade your hardware as well. Heck, you may even consider going virtual (if you aren’t already), which still requires you to examine your hardware requirements.

But here’s the real reason you will want to upgrade your hardware: new features. Let’s say that you are thinking of upgrading to SQL Server 2016 to take advantage of Hekaton. Considering there is a lot of shiny new things in SQL Server 2016, you’ll want to do the extra legwork here to scope out what hardware you’ll need. Otherwise, you won’t be able to leverage many of the new features.

12. Take baselines

Collect performance baselines before you begin the upgrade process. If you don’t then you won’t have any way of knowing if performance is better or worse when the upgrade is complete. Since each SQL Server implementation is unique, there will be different performance metrics that are illbe to you and your business users.

You must also include a baseline of the current server operating system. Perfmon works well for this, but there are many 3rd party tools that can capture these details as well. If you are using Perfmon, you can output the counters to a file and then use the Performance Analysis of Logs (PAL) tool to analyze the output.

Know how your system is expected to grow over time. For physical implementations, this means you will try to size the physical server for end-of-life expectations. For virtualized servers, you will try to size for your performance needs now and expand later as needed.

13. Capture workloads

You can use the Distributed Replay feature to capture a production workload from a source server and replay it on a target server. Doing so will help to assess the impact of upgrading SQL Server by comparing the workload performance against both systems. Distributed Replay is most useful for scenarios that have high concurrency and a single client cannot simulate the workload properly.

The Database Experimentation Assistant is a new tool currently available in Technical Preview. It uses Distributed Reply along with R services to give the user a way to do A/B testing of workloads. Using statistical analysis of workloads allows for greater confidence when upgrading to newer versions of SQL Server.

14. Testing the Server O/S

Tools like iPerf and DskSpd can test the server network and disk performance to verify it is as expected before installing SQL Server. These tools are good at helping to identify if there are any possible configuration issues with the network and disk layout. It is better to check for such issues now before the installation of SQL Server 2016 begins.

15. Take backups

Before you start any upgrade process make certain you take backups of everything; databases, application files, and the server O/S. Sometimes you can utilize a VM snapshot (or checkpoint) to help with this process. I would recommend that when it comes to backups to consider the Computer Backup Rule of Three.

Also worth knowing: backups are only good if they can be restored. So, you will want to test the restore process before you move forward with upgrading.

In the event of a rollback during the upgrade process, you must decide how to handle potential data loss. For example, if you are running a production parallel scenario the business may need to redo a full day’s worth of data entry. It’s better to have those discussions now, not later.

Summary

The above pre-upgrade checklist items are a great starting point for any upgrading to SQL Server 2016 project. They form the foundation for gathering information about your server and databases you want prior to any upgrade taking place. With this information, you will save yourself time and avoid frustrations with upgrading to SQL Server 2016.

In the next post, we will look at the tasks that take place during the upgrade itself.

Don’t forget that you can also download and read the upgrade whitepaper I wrote for SolarWinds. It contains additional information and a set of reference links that I believe you will find useful.