One important factor to contemplate during an upgrade and migration effort are your database compatibility levels, which control certain database behaviors to be compatible with a particular version of SQL Server. Unlike the database file version (which get changed when you restore a database backup in a newer version or attach an older database to a newer version of SQL Server), the database compatibility level can be changed back and forth to different values.<\/p>\n

Here is an example of how to set the database compatibility level in T-SQL<\/a>:<\/p>\n

\n

ALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 120;<\/p>\n<\/blockquote>\n

Figure 1 shows the supported database compatibility levels by major SQL Server version.<\/font><\/p>\n

<\/a><\/p>\n

  Figure 1: Supported Database Compatibility Levels By Version<\/strong><\/p>\n

By default, new user databases that are created in an instance of SQL Server are set to the version of the SQL Server instance (so a new user database in SQL Server 2017 would have a database compatibility level of 140). The exception would be if the model database has a lower database compatibility level. Upgrading a database with a database compatibility level lower than the lowest allowed level, sets the database compatibility level to the lowest compatibility level allowed for that version (as shown in Figure 1).<\/p>\n

The classic scenario for why you might want to use an older database compatibility level after an upgrade to a newer version of SQL Server is to use it as an interim migration aid to work around some application compatibility issues that might surface after a migration (which you hopefully discovered during your pre-migration testing efforts). After further testing and possible database\/application changes, it is pretty common to eventually change the database compatibility level to match the database engine version of the instance, which lets you take advantage of some of the new features and behavior differences in that version<\/a>.<\/p>\n

With SQL Server 2014 and newer, the database compatibility level also controls whether the new cardinality estimator or the legacy cardinality estimator is used. The new cardinality estimator gives equivalent or better query performance for many to most queries, but it sometimes has very significant performance regressions. It is very important<\/u><\/strong> to do as much testing as possible before you change the compatibility level to 120 or higher. My post Upgrading SQL Server\u2013Diagnosing Performance Regressions<\/a> goes into much more detail about this issue.<\/p>\n

 <\/p>\n

Additional Resources<\/font><\/u><\/strong> <\/p>\n

My new Pluralsight<\/a> course, SQL Server: Upgrading and Migrating to SQL Server 2016<\/a> has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here<\/a>. <\/p>\n

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server<\/a>, taught by myself<\/a> and Tim Radney<\/a>. The first round of this course will be taught in Chicago from October 11-13, 2017. <\/p>\n

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017<\/a> at the PASS Summit 2017<\/a> in Seattle, WA from October 31- November 3, 2017. <\/p>\n

Here is a link to the complete series<\/a> about upgrading SQL Server.<\/p>\n","protected":false},"excerpt":{"rendered":"

One important factor to contemplate during an upgrade and migration effort are your database compatibility levels, which control certain database behaviors to be compatible with a particular version of SQL Server. Unlike the database file version (which get changed when you restore a database backup in a newer version or attach an older database to […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[246,342,353],"tags":[350,352],"class_list":["post-1253","post","type-post","status-publish","format-standard","hentry","category-sql-server-2016","category-sql-server-2017","category-upgrading-sql-server","tag-upgrading-to-sql-server-2016","tag-upgrading-to-sql-server-2017"],"yoast_head":"\n