SQL Server 2016 Pre-Upgrade Checklist

SQL Server 2016 Pre Upgrade Checklist and Unsupported Scenarios

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Before upgrading to the newest version of SQL Server, you would ideally need to ensure that the transition is smooth. You can check out our SQL Server 2016 Pre-Upgrade Checklist to know more about the upgrade paths. Read along to know more.

SQL Server 2016 Pre-Upgrade Checklist

SQL Server 2016 introduced a significant number of enhancements and feature releases, which have prompted corporations to envision for an upgrade.

For a successful upgrade and migration path to SQL Server 2016, you need to ensure that you have followed all the SQL server 2016 pre-upgrade checklist and unsupported scenarios.

Firstly, you need to make sure that your edition of SQL Server is eligible for a direct upgrade path to SQL Server 2016. You can upgrade to SQL Server 2016 from the following versions:

  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014

Check your SQL Server Edition to see if you have a direct upgrade path:

Upgrade fromSupported upgrade path
SQL Server 2008 SP3 EnterpriseSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2008 SP3 DeveloperSQL Server 2016 Developer
SQL Server 2008 SP3 StandardSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2008 SP3 Small BusinessSQL Server 2016 Standard
SQL Server 2008 SP3 WebSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2016 Web
SQL Server 2008 SP3 WorkgroupSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2008 SP3 Express,SQL Server 2016 Enterprise
u00ffSQL Server 2008 SP3 Express with Tools, andSQL Server 2016 Business Intelligence
u00ffSQL Server 2008 SP3 Express with Advanced ServicesSQL Server 2016 Standard
SQL Server 2016 Web
SQL Server 2016 Express
SQL Server 2008 R2 SP2 DatacenterSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2008 R2 SP2 EnterpriseSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2008 R2 SP2 DeveloperSQL Server 2016 Developer
SQL Server 2008 R2 SP2 Small BusinessSQL Server 2016 Standard
SQL Server 2008 R2 SP2 StandardSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2008 R2 SP2 WebSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2016 Web
SQL Server 2008 R2 SP2 WorkgroupSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2008 R2 SP2 Express,SQL Server 2016 Enterprise
SQL Server 2008 R2 SP2 Express with Tools, andSQL Server 2016 Business Intelligence
SQL Server 2008 R2 SP2 Express with Advanced ServicesSQL Server 2016 Standard
SQL Server 2016 Web
SQL Server 2016 Express
SQL Server 2012 SP1 EnterpriseSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2012 SP1 DeveloperSQL Server 2016 Developer
SQL Server 2016 Standard
SQL Server 2016 Web
SQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2012 SP1 StandardSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2012 SP1 WebSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2016 Web
SQL Server 2012 SP1 Express,SQL Server 2016 Enterprise
SQL Server 2012 SP1 Express with Tools, andSQL Server 2016 Business Intelligence
SQL Server 2012 SP1 Express Management Studio, andSQL Server 2016 Standard
SQL Server 2012 SP1 Express with Advanced ServicesSQL Server 2016 Web
SQL Server 2016 Express
SQL Server 2016 Developer
SQL Server 2012 SP1 Business IntelligenceSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2012 SP1 EvaluationSQL Server 2016 Evaluation
SQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
u00ffSQL Server 2016 Web
SQL Server 2016 Developer
SQL Server 2014 EnterpriseSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2014 DeveloperSQL Server 2016 Developer
SQL Server 2016 Standard
SQL Server 2016 Web
SQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2014 StandardSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2014 WebSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2016 Web
SQL Server 2014 Express,SQL Server 2016 Enterprise
SQL Server 2014 Express with Tools, andSQL Server 2016 Business Intelligence
SQL Server 2014 Express Management Studio, andSQL Server 2016 Standard
SQL Server 2014 Express with Advanced ServicesSQL Server 2016 Web
SQL Server 2016 Express
SQL Server 2016 Developer
SQL Server 2014 Business IntelligenceSQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2014 EvaluationSQL Server 2016 Evaluation
SQL Server 2016 Enterprise
SQL Server 2016 Business Intelligence
SQL Server 2016 Standard
SQL Server 2016 Web
SQL Server 2016 Developer

Before moving to SQL Server 2016, ensure that all the features that you are using are supported in the current release. Search the below table to find out.

Features of 2016Sub-FeatureSQL Server 2016SQL Server 2014SQL Server 2012SQL Server 2008 R2
PerformanceIn-memory OLTP*YesYesNoNo
In-memory ColumnStore*YesYesYesNo
Real-time operational analytics*YesYesYesNo
Resource Governor*YesYesYesYes
Query StoreYesNoNoNo
AvailabilityAlwaysOn*YesYesYesNo
Enhanced virtualization support and live migrationYesYesYesYes
SecurityAlways Encrypted*YesNoNoNo
Transparent data encryption*YesYesYesYes
Row-level securityYesNoNoNo
Dynamic data maskingYesNoNoNo
Backup encryption supportYesYesNoNo
Fine-grained auditingYesYesYesYes
Separation of dutiesYesYesYes
ProgrammabilityJSON supportYesNoNoNo
PolyBase queries over Hadoop data**YesNoNoNo
TemporalYesNoNoNo
Cloud-readySQL Server Stretch DatabaseYesNoNoNo
Backup to AzureYesYesYesNo
Disaster recovery to Azure*YesYesNoNo
Optimized virtual machine images in Azure galleryYesYesYesNo
ManagementDistributed replayYesYesYesNo
Policy-based managementYesYesYesYes
Business intelligenceModernized reportsYesNoNoNo
Mobile BI*YesNoNoNo
Integration services managed as a serverYesYesYesNo
Pin reports to Power BIYesNoNoNo
Multi-dimensional semantic modelsYesYesYesYes
Enhanced tabular BI semantic models*YesNoNoNo
Master data services*YesYesYesYes
Data quality services*YesYesYesNo
Advanced analyticsIn-database advanced analytics with R ServicesYesNoNoNo
Multi-threaded processing of R queries and streaming memory*YesNoNoNo

Current Limitations and Unsupported Scenarios

  • All the SQL Server components must have the same version numbers. You cannot use different build numbers for Reporting, Analysis, Integration or Database Services. They all need to have the same version numbers.
  • Upgrade from 32 Bit to 64 Bit in-place upgrade is not supported. You need to do a side by side upgrade to a 64 Bit instance since both backup-restore or detach-attach is fully supported.
  • Cannot add additional feature during the in-place upgrade process. Newer features can only be added after the upgrade is complete.
  • 32-bit Fail-over Clusters are not supported for a direct upgrade to SQL 2016.
  • Evaluation versions need to be upgraded to a licensed edition before upgrading to SQL Server 2016.
  • Evaluation versions cannot be upgraded to a clustered installation. Only standalone versions are supported.
  • 64 Bit Editions of earlier versions can only be upgraded to a 64 bit SQL Server 2016 Edition.
  • SQL Server 2005 can only be restored with compatibility level 100 and above.
  • Changing of Editions of SQL Clusters in 2016 is not supported.

I hope this gives you enough information to get you started with SQL Server 2016. Follow the Official documentation from Microsoft as given below:

We hope our post about SQL Server 2016 Pre-Upgrade Checklist was helpful. For any questions, please use the comment section below.

Disclaimer: The Questions and Answers provided on https://datacompute.in are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.

Related Post