Upgrading to SQL Server 2016, Part One

Have you ever been asked for your thoughts on upgrading a database server? If you’re like most data professionals, your response probably included something along the lines of, “it’s difficult” and “everything breaks.” 

Yes, when it comes to upgrading a database server, there is a lot of uncertainty. With data and databases being the most critical asset that any company has, it’s easy to understand why we always want to be extra cautious with regards to upgrades. 

So, what I aim to do over the next few months is write a series of blog posts that when taken as a whole, provide a guide for upgrading to Microsoft^® SQL Server^® 2016.  

But remember, no two upgrades are the same. Just because someone else failed (or succeeded) does not mean you will have the same result. 

Why Upgrade?

“Why upgrade?” is the first question whenever an upgrade project is talked about. Someone, somewhere, wants to know why they should take a perfectly good system that runs just fine and make a bunch of changes. 

Here’s a short list of reasons why someone might consider upgrading to SQL Server 2016: 

1. New features in SQL Server 2016

With any new version of SQL Server, we always get something shiny and new to play with. In SQL Server 2016, we get the following new features:

We also get enhancements to features introduced in other recent versions:

For a complete list of all the things that have been enhanced in SQL Server 2016, check out “SQL Server 2016: It Just Runs Faster.” 

2. Supportability

End of support is fast approaching for earlier versions of SQL Server. This means no new service packs or updates. Yes, you can purchase extended support, but it’s costly. And yes, Microsoft has extended support for Win2008 and SQL 2008, but that doesn’t mean it’s a good thing to keep using them. 

3. Vendor requirements

You may be using software from a third-party vendor that has strict requirements about which version of SQL Server you can use. Yes, this goes both ways—it could require newer versions, or it could require older versions. 

4. Company or industry standard

Some companies may not allow for you to run more than one full major version behind any software product. And some industries may have those requirements, too. And don’t forget the auditors—they like to have their own suggestions. 

5. Scalability

The SQL Server engine has had many enhancements over the past 10 years to address scalability concerns. I listed a few of those above (Columnstore, Availability Groups, In-Memory OLTP), but the engine itself has been updated to include things like new cardinality estimation techniques to help build better query plans based upon the distribution of your data. Upgrading to SQL Server 2016 will bring you greater scalability opportunities than previous versions.

So, now what?

If any of those reasons are compelling enough to help you decide that upgrading is something you want or need to do, you now 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 I’ll be providing in this series. In this post specifically, we’ll look at the pre-upgrade tasks. 

Before you upgrade

Before your upgrade 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’s my list of things you will want to review before any data is migrated.

1. Know your path(s)

To get to SQL Server 2016, you can upgrade directly from:

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

2. Research licensing changes

You should seek to understand the licensing changes that may be in place with SQL Server 2016 compared to your current version. As of SQL Server 2012, licensing is done per core, not per socket. 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. You should check out all the details on SQL Server 2016 licensing. 

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

3. Know your options 

As complex as upgrades may appear, all upgrades can be placed into one of two scenarios: in-place or side-by-side. 

In-place upgrades are when you upgrade the current instance of SQL Server by running the installation wizard. These are the easiest to perform, but the hardest to rollback. 

Side-by-side upgrades are when you install the new version of SQL Server as a new instance on the existing server (or a new server, which is what I prefer especially for production scenarios) and migrate databases over as necessary. The fresh SQL Server installation allows for better testing of the system before bringing it online for production. 

There is also the concept of a rolling upgrade. This is when you use a high-availability feature such as mirroring, clustering, or Availability Groups. The idea is that you 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. 

I’m a huge fan of rolling upgrades, but only when it’s necessary to minimize downtime due to a business requirement. If no such requirement exists, then my preference is to do a side-by-side upgrade and migrate the data by taking a backup and restoring it to the new server. But that is my preference and doesn’t mean it will be yours. You should do what’s best for your shop. 

4. Gather inventory details 

You will also want to collect information about the servers and database instances that are considered in scope for the upgrade. 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 third-party tools. 

5. Get familiar with Data Migration Assistant

Formerly known as the SQL Server Upgrade Advisor, the Data Migration Assistant (DMA) will help you identify any breaking or behavioral changes as well as deprecated features. You can use DMA to identify issues that need to be resolved before upgrading to the desired version of SQL Server. The DMA can also help identify parts of your database that may benefit from new features in SQL Server 2016. And the DMA can also migrate your data for you.

6. Be aware of 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. Deprecated means the features will possibly be removed in a future version, and you should not use these features for any new development work. You can find the list of deprecated database engine features for SQL Server 2016 here.

7. Identify discontinued features 

With SQL Server 2016, Microsoft has started publishing a list of items that are discontinued. This means the items have been removed completely. 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 after the upgrade.

8. Know breaking changes

Did you know that Microsoft publishes a list of breaking changes for each version of SQL Server? Well, now you do. You should review them to the point that they are familiar to you. You don't have to memorize them all, just be 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)?” I would like to think the DMA will alert you to many of these breaking changes, but the truth is that the DMA is not as dynamic as the BOL. That means the BOL may have an entry or two that doesn't make it into the DMA checklist, and that’s why you should review this section.

9. Know 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 the DMA is likely to never report back to you because they aren’t things that will break, but merely things that could break.

However, 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. I suspect that there could be one coming for the database engine at some point as issues are tracked and become known. 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

Take a few minutes to 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 of 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. Discover new environment requirements 

Microsoft lists the minimum requirements for installing SQL Server 2016 on this page. However, those are the minimums. 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 will still require you to examine your hardware requirements.

But here’s the real reason you will want to upgrade your hardware: new features. Let’s say you’re thinking of upgrading to SQL Server 2016 in order 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

You’ll also want to collect performance baselines before you begin the upgrade process. If you don’t, 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 important to you and your business users.

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 you 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. Test the Server O/S

You can use tools like iPerf^® and DskSpd to 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. 

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 virtual machine snapshot (or checkpoint) to help with this process. I recommend considering the Computer Backup Rule of Three  when it comes to backups.

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

Summary

Upgrades are a necessary part of any development lifecycle. The chances of having a successful upgrade increase proportionally with the amount of planning and preparation you invest in building a proper upgrade process. In the next post, we’ll look at the types of upgrades and the tasks you should do during the upgrade process. Stay tuned. 

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.