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.
A blog worth reading
Regarding this line – “If you are running SQL Server 2005 or earlier, you need to upgrade to an intermediate version before upgrading to SQL Server 2016” – there are (limited) options for migration from 2005 to 2016 though:
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades?view=sql-server-2016#SupportFor2005
Thanks for the great posts.
THANKS FOR USEFUL INFORMATION.
hello,
great guide and contribution to the MS SQL community 🙂
I have a scenario which i wish to move a local DB to a cloud provider for the same software, however they use SQL 2017 (dont ask!) on their cloud platform and we have MS SQL 2019, is there some method to either downgrade or export in some manner which can be re-imported back in SQL 2017?
For this scenario, I recommend using Visual Studio and the Data Tools extension. The idea is to point VS at your 2019 database and create a build with SQL 2017 as the target. This way you can find any features incompatible and need to be fixed prior to any migration. Once you fix the schema, you can then deploy to the 2017 instance. Then you need to export the data from SQL 2019 and import into SQL2017. HTH