SQL Server2016 Pre-upgrade self-test

SQL Server2016 has been out for some time, and the latest SP1 package was officially released on November 18, 2016, and a variety of new features were launched to make us eager. So for our real business environment, especially the production environment to "follow the bandwagon", upgrade to the latest version? In order to avoid the risk, the production environment before upgrading we certainly must recognize whether there is the need to upgrade.

Today we don't talk about this, but technically, if we need to upgrade, we have to go through repeated testing to make sure there are no big problems in the production environment. The following points can help us to check the necessary conditions before upgrading to SQL 2016, so as to avoid the difficult situation in the upgrade.

1. Upgrade to SQL Server 2016 is supported only for the following list versions

• SQL Server version SP3 or later
• SQL Server version R2 SP2 or later
• SQL Server version SP2 or later
• SQL Server 2014 or later

2. SQL Server 2005 upgrade to SQL Server 2016 can be implemented in the following ways

• Attaches a SQL Server 2005 database (Mdf/ldf file) to the SQL Server 2016 instance of the database engine.
• Restores a SQL Server 2005 database from a backup to a SQL Server 2016 instance of the database engine.
• Back up the SQL Server 2005 Analysis Services (SSAS) cube and restore on SQL Server 2016

Note When you upgrade a SQL Server 2005 database to SQL Server 2016, the database compatibility level changes from 90 to 100, and the compatibility level changes the way that the SQL Server application may be affected.

3. Original SQL2016 upgrade plan

Pre-upgrade versions	Upgrade to a version
SQL Server Enterprise (server +cal and kernel) * *	SQL Server Enterprise
SQL Server Evaluation enterprise**	SQL Server Enterprise (server +cal or kernel license)
	SQL Server Standard
	SQL Server Developer
	SQL Server Web
	For stand-alone installations, it is supported to upgrade from Evaluation (free version) to any paid version, but this upgrade is not supported for clustered installations.
SQL Server standard**	SQL Server Enterprise (server +cal or kernel license)
SQL Server developer**	SQL Server Enterprise (server +cal or kernel license)
	SQL Server Web
	SQL Server Standard
SQL Server Web	SQL Server Enterprise (server +cal or kernel license)
	SQL Server Standard
SQL Server express*	SQL Server Enterprise (server +cal or kernel license)
	SQL Server 2016 developer
	SQL Server Standard
	SQL Server Web

4. First enable Windows authentication for SQL Server Agent and verify the default configuration: SQL Server Agent. Whether the service run account is a member of the SQL Server sysadmin group.

5. Confirm that the Windows Installer service is turned on before upgrading.

6. Windows Installer creates temporary files in the system drive, check to see if there is at least 6.0 GB of free disk space in the system drive to store these files.

Function	Disk Space Requirements
Database engine and data files, replication, full-text search, and quality Services	1480 MB
Database engine (as shown above) with R Services (in-database)	2744 MB
Database engine (shown above) with PolyBase query service for external data	4194 MB
Analysis Services and data files	698 MB
Reporting Services	967 MB
Microsoft R Server (standalone)	280 MB
Reporting Services-sharepoint	1203 MB
Reporting Services Add-in for SharePoint products	325 MB
Data Quality Client	121 MB
Client Tool Connection	328 MB
Integration Services	306 MB
Client components (other than the SQL Server Books Online component and the Integration Services tool)	445 MB
Master Data Services	280 MB
SQL Server Books Online components for viewing and managing Help content *	MB
All features	8030 MB

7. If there is a pending restart operation, the upgrade is unlikely to be expected.

8. If you are installing SQL Server Management Studio,. NET 4.6.1 installation as a prerequisite.

9. The server operating system version of SQL Server 2016 upgrade is preferably Windows Server 2012.

SQL Server 2016 supports only the same version instance upgrade.

One. SQL Server 2016 can only be installed on a 64-bit platform.

Note: You cannot use SQL Server Setup to upgrade a 32-bit instance of SQL Server to a native 64-bit.

The workaround is to back up or detach the database from a SQL Server 32-bit instance and then restore or attach them to a new instance of SQL Server (64-bit), and then recreate any other user objects, such as any logins, in the master, msdb, and model system databases, with a previous The library is not published during the copy process.

12. You cannot add new features while upgrading an existing instance of SQL Server. After you upgrade an instance of SQL Server to SQL Server 2016, you can add features by using SQL Server 2016 setup.

12. Failover clustering is not supported in WOW mode.

13. Previously, SQL Server version Evaluation (non-SQL2016) cannot be upgraded.

......

The new version does give us a lot of surprises, in the experience, learning new features, as a DBA we have to protect and manage the database itself. All in all, the use of any new things must be tested and validated many times, early detection of problems and resolved, with a sense of mission to let our data, the system smooth transition to the new version.

Sam.zhao

SQL Server2016 Pre-upgrade self-test