SQL Server 2016 Upgrade Planning update from June 2016

Congratulations! You finally received approval to upgrade to SQL Server 2016. Now the upgrade planning begins. As a follow on to the webinar last week, here are a few resources, tips and an example upgrade planning template to give you a running start.

Where to Start

The first thing that I would suggest you do is get management sponsorship for adequate upgrade planning time. Unless you have a personal instance, most real-world SQL Server environments can be complex to upgrade. If you are having a tough time getting planning time, send your manager a copy of the 429 page SQL Server Technical Upgrade Guide to review. (Note: Microsoft did not create a SQL Server 2016 version. The 2014 version guide should be similar enough for this purpose.)

To get a sense for what might be entailed in the upgrade process in your environment, you will want work through an assessment your environment. In the assessment, you will inventory SQL Server instances, document hardware specifications, operating system, software, disk space, what SQL Server functionality is being used, extended stored procedures, custom dlls., what other applications are on the box, and downstream dependencies.

After you have an inventory, you will then continue to run Best Practices Analyzer if exists for your current version of SQL Server. By scanning for best practices, you avoid upgrading bad practices. If bad or questionable practices exist, you should address them before the upgrade, moving the fixes through test and into production. Using best practices on the legacy SQL Server systems first will help ensure a smoother upgrade.

Then work though the SQL Server 2016 Upgrade Advisor tasks, review deprecated features and any blocking issues that need to be resolved prior to upgrade. You can add these results to your inventory as it will evolve into a wonderful checklist in the end.

For SQL Server business intelligence applications, the upgrade process will be a bit more difficult. The SQL Server 2016 Upgrade Advisor no longer includes the business intelligence applications like it used to in the past. Most business intelligence investment is targeted to move you to the cloud. If you do have SQL Server 2008R2, you could run the SQL Server 2012 Upgrade Advisor to get a much better idea of what needs to be done to upgrade your Integration Services packages, Reporting Services reports and Analysis Services cubes. Typically, Analysis Services is the easiest business intelligence area to upgrade. Integration Services and Reporting Services involve more steps.

Supported Upgrade Versions

SQL Server 2016 supports direct upgrades from the following versions:

If you want to upgrade a long overdue SQL Server 2005 server, it will require migration. Please refer to the online documentation for that process.

Other unsupported scenarios include:

Upgrade Approaches

With a better understanding of current state, now you will explore various upgrade approaches.

•       In-place upgrade

•       Side-by-side installation

•       Rolling upgrade

With an in-place upgrade, SQL Server setup program replaces the existing instance bits with the SQL Server 2016 bits and then upgrades each of the system and user databases. The upgrade in-place approach is easiest, requires some amount of downtime, takes longer to roll back if necessary, and it is not supported for all scenarios. The in-place approach is frequently used in a development environment without a high-availability (HA) configuration or in a non-mission critical production environment that can tolerate downtime and that is running on a recent hardware and software.

In a side-by-side upgrade, instead of directly replacing the older instance of SQL Server, required database and component data is transferred from a legacy instance of SQL Server 2005/2008/2008 R2/2012 to a separate instance of SQL Server 2016. It is called a "side-by-side" method because the new instance of SQL Server 2016 runs alongside the legacy instance of SQL Server, either on the same server or on a different server.

There are two important options when you use the side-by-side upgrade method. You can transfer data and components to an instance of SQL Server 2016 that is located on a different physical server or on the same physical server. In a side-by-side upgrade is that you must manually transfer data files and other supporting objects from the older instance of SQL Server to the instance of SQL Server 2016. The SQL Server Setup program will not perform that task.

Rolling upgrade

Last but not least, you may need to consider a rolling upgrade if your environment has multiple SQL Server instances that need to be upgraded in a specific order to maximize uptime, minimize risk, and preserve functionality.

If you are unsure what to choose, check out the SQL Server Upgrade online documentation. There are helpful decision workflow diagrams this time around to help you choose the right approach.

Tips by SQL Server Area

For each area of SQL Server, review what you are using and the available online documentation. Here are a few tips by area. These are by no means comprehensive. Your environment is likely different than mine.

For SQL Server Database engine, if possible, test the upgrade method before trying it in a production environment. After databases are upgraded to SQL Server 2016, there are extra steps to do such as taking a new backup, enabling new features, and re-populating full-text catalogs. Also in this version, you can review query performance with the  Query Store feature. Don’t forget to change database compatibility mode and make sure downstream applications update connection strings.

If you are using Master Data Services or Data Quality Services, keep in mind an customization will get overwritten. You must back up your MDS and DQS databases before upgrading to prevent any accidental data loss. In SQL Server 2016 , those applications do have schema upgrade level changes. After upgrading to SQL Server 2016, any earlier version of the Add-Ins for Excel will no longer work. You will need to tell your users to download the SQL Server 2016 versions of the Master Data Services or Data Quality Services Add-In for Excel.

Integration Services packages do not get automatically updated in a SQL Server upgrade. You will need to migrate packages afterward service upgrade completes with the Integration Services Package Upgrade Wizard. Developers can upgrade 2012 or 2014 projects to 2016 without manual adjustments after upgrade. They can also choose to incrementally update without deploying the whole project. After upgrade, you will want to check if shared connection managers upgraded successfully.

For Analysis Services, be sure to check the known problems and workarounds before you upgrade in the SQL Server 2016 Release Notes especially since Upgrade Advisor is missing Analysis Services this version. Analysis Services Backward Compatibility summarizes discontinued, deprecated, and changed features. o upgrade an Analysis Services Tabular model, use the correct version of SQL Server Data Tools (SSDT)  to change the Compatibility Level property to SQL Server 2016 RTM (1200). Do not use SQL Server Management Studio (SSMS), code, or script to change the CompatibilityLevel.

To prepare for a Reporting Services upgrade, read about upgrading reports in documents online. It is not straight-forward. Also use System Configuration Checker (SCC) to scan your Report Server. Don’t forget to back up your encryption keys, Report Server databases, configuration files and any customizations to IIS. Other files MVP, Paul Turley has an excellent blog on that topic.

Last but not least, upgrading SharePoint SQL Server business intelligence is beyond the scope of this article. Those upgrades require even more cross organizational planning. In the latest releases of SharePoint, the Excel Services application is no longer hosted on SharePoint Server. It is now part of Excel Online 2016. That change seemed to confuse a lot of people earlier this year.  The new Office Online Server is a mandatory server. Excel workbooks in SharePoint won’t open in the browser without it. Excel Web Parts won’t work without it. Power Pivot, Visio Services and Reporting Services will have limited functionality without it.

Also in SharePoint hosted SQL Server hosted business intelligence requires upgrades of all areas. You need to deploy the complete SharePoint 2016 and SQL 2016 business intelligence stack in a bottom-up approach. The recommended upgrade sequence is 1) Upgrade Database 2016 engine 2) Upgrade Analysis Services 2016 3) Install Office Online Server 4) Upgrade SharePoint 2016  5) Install the SQL Server add-ons for SharePoint 2016 6) Upgrade your user Excel clients if desired. Another MVP, John White, has excellent details on this process.

Developing an Upgrade Plan

Now you are ready to putting your findings all together in an upgrade plan. Here is a link to a cleansed example upgrade plan from a successful project that I worked on years ago. In my upgrade, I treated it as an IT Project, minimized variables, evaluated and resolved upgrade issues early, and reviewed an ecosystem of downstream apps, developers and connections. I worked with my team to establish success criteria and a rollback process. From there, I used my plan as an upgrade checklist, went to work and celebrated the completion of a successful upgrade project. 

Additional Resources

Here are other resources that you might like to review to plan your upcoming SQL Server 2016 upgrade.

•       Upgrade Webinar - http://forms.windowsitpro.com/whats-new-SQL-WIN-agenda

•       Upgrade Advisor - https://www.microsoft.com/en-us/download/details.aspx?id=48119   

•       SQL Server 2014 Upgrade Technical Guide - http://download.microsoft.com/download/7/1/5/715BDFA7-51B6-4D7B-AF17-61E78C7E538F/SQL_Server_2014_Upgrade_technical_guide.pdf

•       Product Team Upgrade Video - https://www.youtube.com/watch?v=ZF3AU6ydBF4  

•       SQL Server - https://msdn.microsoft.com/en-us/library/bb677622.aspx   

•       Analysis Services - https://msdn.microsoft.com/en-us/library/ms143686.aspx

•       Reporting Services - https://msdn.microsoft.com/en-us/library/ms143747(v=sql.130).aspx

•       Deprecated Features - https://msdn.microsoft.com/en-us/library/ms143729(SQL.130).aspx

•       Community Blogs - https://www.mssqltips.com/sqlservertip/3688/prepare-for-an-upgrade-with-the-sql-server-2016-upgrade-advisor/