SQL Server 2016 Upgrade Planning

It has been just two years since we upgraded our SQL Server 2012 databases to SQL Server 2014, and here we go again! Everyone is pretty excited to take a peek at the cool new features of SQL Server 2016 and start using them in their enterprise database applications.

Usually, before you start any upgrade, you have no idea what obstacles and troubles are waiting for you during the process. Here are some useful tips and tricks to ensure a smooth SQL Server 2016 upgrade.

Where to begin with SQL Server 2016?

First and foremost, plan thoroughly for your upgrade. Most likely, you’re upgrading your enterprise database application, which is already live and which thousands of customers are already using. Upgrading such an environment will require adequate planning, monitoring, configuring, and comprehensive testing before it goes live again. The upgrade is likely to consume a significant amount of time, so you will need to come up with an estimate for management and the client services team as to when the upgrade will be completed.

In order to provide this estimate, you need to have a good understanding of what lies ahead. It’s recommended to perform an assessment on the database environment of the organization, which includes:

• Taking inventory of the available SQL Server instances and their versions
• Identifying the SQL Server functionalities currently being used
• Documenting hardware, OS, and software specifications of the system
• Identifying other application dependencies

Once you have the above to-do list, you will scan your current SQL Server version and instances to identify good and bad practices that are currently being used. This can be done using the Best Practices Analyzer which helps you avoid upgrading any existing bad practices in to SQL Server 2016.

Next, run the Upgrade Advisor (UA) for SQL Server 2016. This will review the existing SQL Server version and determine which blocking issues will need to be resolved before upgrading. Keep in mind: the UA won’t fix what’s wrong; it will only advise as to what measures you’ll have to take to complete the SQL Server 2016 upgrade.

The UA recommendations come in two stages: things to do prior to the migration and things to do post-migration. The output of the Upgrade Advisor can be added to the above to-do list, so that you have a comprehensive checklist of things to do when you start the actual upgrade.

Choosing an SQL Server upgrade strategy

Once you are done with the initial preparations, you will need to decide on an upgrade strategy. You have three options to choose from:

1. In-place upgrade
2. Side-by-side upgrade
3. Rolling upgrade

Which one is best for you? This is where your DBA skills come in. Let’s look at the pros and cons of each of these strategies.

With an in-place upgrade, the setup will replace the bits of the existing SQL Server instance with that of SQL Server 2016. The advantage here is that the security and other configurations of the SQL Server will remain intact before and after migration. However, you won’t be able to roll back easily if any issues arise after the upgrade.

The side-by-side upgrade is quite popular, but it’s more difficult than the other two options. Here, you install SQL Server 2016 as a new instance on a separate server alongside the older version. Then a database migration will be done from the existing instance to the 2016 one.

A rolling upgrade can be used if there are several SQL Server instances in the organization that should be upgraded one by one in order to minimize risks and downtime.

Microsoft “breaking changes” for SQL Server 2016

Microsoft usually publishes a list of “breaking changes” for each SQL Server update. The list for SQL Server 2016 can be found here. It is recommended that you familiarize yourself with this list before the upgrade. If (and when) something goes wrong, this will enable you to know if it’s a result to one of these breaking changes.

Completing your SQL Server 2016 upgrade plan

Once you have an upgrade to-do list prepared and an upgrade strategy finalized, you are almost ready to move forward with your upgrade plan. As mentioned earlier, good planning will help the database administration to perform a smooth migration of enterprise database applications to the new SQL Server 2016 instance.

If you need help upgrading to SQL Server 2016, or to learn more, contact Datavail today. With more than 600 database administrators worldwide, Datavail is the largest database services provider in North America. With 24×7 managed services for applications, BI/Analytics, and databases, Datavail can support your organization, regardless of the build you’ve selected.