Moving to SQL Server 2014/2016

As people are coming to end of life on their hardware they are migrating to later versions of SQL Server. This means that 2016 and 2014 are coming into vision for many companies. But when the upgrade date comes, there are a few things to be aware of.

New Query Optimizer – Cardinality Estimator

SQL Server 2014 introduced a new cardinality estimator that changes how the query optimizer estimates row counts. This can significantly affect query plan choices.

• SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast – post about the benefits of the new cardinality estimator in 2014
• Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator – covers the changes you would want to make and how to do them, including how to roll back to the old estimator if needed

After testing database workloads, you may choose to revert to the legacy CE behavior for sessions connecting to a database. You can do so by changing the database compatibility level to a level lower than 120:

1
2
3
4
5
6

USE [master];
GO

-- SQL Server 2012 compatibility level
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 110;
GO

Moving Between Editions (Same Version)

Changing SQL Server Editions: Standard, Enterprise, Evaluation and More – shouldn’t be a problem especially if using an evaluation edition. The worst case scenario is to uninstall and reinstall a lower version.

Downgrading Versions

How to Migrate a SQL Server Database to a Lower Version – does take some time but can be done using the Generate Scripts wizard inside SSMS.