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 from | Supported upgrade path |
|---|---|
| SQL Server 2008 SP3 Enterprise | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2008 SP3 Developer | SQL Server 2016 Developer |
| SQL Server 2008 SP3 Standard | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2016 Standard | |
| SQL Server 2008 SP3 Small Business | SQL Server 2016 Standard |
| SQL Server 2008 SP3 Web | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2016 Standard | |
| SQL Server 2016 Web | |
| SQL Server 2008 SP3 Workgroup | SQL 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, and | SQL Server 2016 Business Intelligence |
| u00ffSQL Server 2008 SP3 Express with Advanced Services | SQL Server 2016 Standard |
| SQL Server 2016 Web | |
| SQL Server 2016 Express | |
| SQL Server 2008 R2 SP2 Datacenter | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2008 R2 SP2 Enterprise | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2008 R2 SP2 Developer | SQL Server 2016 Developer |
| SQL Server 2008 R2 SP2 Small Business | SQL Server 2016 Standard |
| SQL Server 2008 R2 SP2 Standard | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2016 Standard | |
| SQL Server 2008 R2 SP2 Web | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2016 Standard | |
| SQL Server 2016 Web | |
| SQL Server 2008 R2 SP2 Workgroup | SQL 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, and | SQL Server 2016 Business Intelligence |
| SQL Server 2008 R2 SP2 Express with Advanced Services | SQL Server 2016 Standard |
| SQL Server 2016 Web | |
| SQL Server 2016 Express | |
| SQL Server 2012 SP1 Enterprise | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2012 SP1 Developer | SQL Server 2016 Developer |
| SQL Server 2016 Standard | |
| SQL Server 2016 Web | |
| SQL Server 2016 Enterprise | |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2012 SP1 Standard | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2016 Standard | |
| SQL Server 2012 SP1 Web | SQL 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, and | SQL Server 2016 Business Intelligence |
| SQL Server 2012 SP1 Express Management Studio, and | SQL Server 2016 Standard |
| SQL Server 2012 SP1 Express with Advanced Services | SQL Server 2016 Web |
| SQL Server 2016 Express | |
| SQL Server 2016 Developer | |
| SQL Server 2012 SP1 Business Intelligence | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2012 SP1 Evaluation | SQL 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 Enterprise | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2014 Developer | SQL Server 2016 Developer |
| SQL Server 2016 Standard | |
| SQL Server 2016 Web | |
| SQL Server 2016 Enterprise | |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2014 Standard | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2016 Standard | |
| SQL Server 2014 Web | SQL 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, and | SQL Server 2016 Business Intelligence |
| SQL Server 2014 Express Management Studio, and | SQL Server 2016 Standard |
| SQL Server 2014 Express with Advanced Services | SQL Server 2016 Web |
| SQL Server 2016 Express | |
| SQL Server 2016 Developer | |
| SQL Server 2014 Business Intelligence | SQL Server 2016 Enterprise |
| SQL Server 2016 Business Intelligence | |
| SQL Server 2014 Evaluation | SQL 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 2016 | Sub-Feature | SQL Server 2016 | SQL Server 2014 | SQL Server 2012 | SQL Server 2008 R2 |
|---|---|---|---|---|---|
| Performance | In-memory OLTP* | Yes | Yes | No | No |
| In-memory ColumnStore* | Yes | Yes | Yes | No | |
| Real-time operational analytics* | Yes | Yes | Yes | No | |
| Resource Governor* | Yes | Yes | Yes | Yes | |
| Query Store | Yes | No | No | No | |
| Availability | AlwaysOn* | Yes | Yes | Yes | No |
| Enhanced virtualization support and live migration | Yes | Yes | Yes | Yes | |
| Security | Always Encrypted* | Yes | No | No | No |
| Transparent data encryption* | Yes | Yes | Yes | Yes | |
| Row-level security | Yes | No | No | No | |
| Dynamic data masking | Yes | No | No | No | |
| Backup encryption support | Yes | Yes | No | No | |
| Fine-grained auditing | Yes | Yes | Yes | Yes | |
| Separation of duties | Yes | Yes | Yes | ||
| Programmability | JSON support | Yes | No | No | No |
| PolyBase queries over Hadoop data** | Yes | No | No | No | |
| Temporal | Yes | No | No | No | |
| Cloud-ready | SQL Server Stretch Database | Yes | No | No | No |
| Backup to Azure | Yes | Yes | Yes | No | |
| Disaster recovery to Azure* | Yes | Yes | No | No | |
| Optimized virtual machine images in Azure gallery | Yes | Yes | Yes | No | |
| Management | Distributed replay | Yes | Yes | Yes | No |
| Policy-based management | Yes | Yes | Yes | Yes | |
| Business intelligence | Modernized reports | Yes | No | No | No |
| Mobile BI* | Yes | No | No | No | |
| Integration services managed as a server | Yes | Yes | Yes | No | |
| Pin reports to Power BI | Yes | No | No | No | |
| Multi-dimensional semantic models | Yes | Yes | Yes | Yes | |
| Enhanced tabular BI semantic models* | Yes | No | No | No | |
| Master data services* | Yes | Yes | Yes | Yes | |
| Data quality services* | Yes | Yes | Yes | No | |
| Advanced analytics | In-database advanced analytics with R Services | Yes | No | No | No |
| Multi-threaded processing of R queries and streaming memory* | Yes | No | No | No |
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:
- Supported Version and Edition Upgrades
- Upgrade to SQL Server 2016
- Prepare for upgrade by running Upgrade Advisor
We hope our post about SQL Server 2016 Pre-Upgrade Checklist was helpful. For any questions, please use the comment section below.