SQL Server 2005 upgrade to SQL Server 2014 and compatibility levels
A database currently attached to an instance of SQL Server 2005 could be either backed up (or detached), and later restored (or attached) on top of an instance of SQL Server 2014, and SQL Server 2014 won’t complain as it typically did in the past when you tried to restore (or attach) databases across database engines more distant than n-2 major versions.
With SQL Server 2014, it is also possible and supported by the Setup code, to do an in-place upgrade of an existing instance of SQL Server 2005 without having to upgrade it first to any intermediate version (2008/2008R2/2012) which would put it in the n-2 range.
The supportability of those upgrade methods (backup/restore, detach/attach, or in-place upgrade) are documented in the “SQL Server 2014 Support for SQL Server 2005” section of the Supported Version and Edition Upgrades topic in he documentation.
And there is a note in it which reads “When a SQL Server 2005 database is upgraded to SQL Server 2014, the database compatibility level will be changed from 90 to 100. (In SQL Server 2014, valid values for the database compatibility level are 100, 110 and 120.)”
Because I had to investigate how this worked internally to provide an authoritative answer to the question asked by a customer who wanted to know what happened with databases he had in instances of SQL Server 2005 whose compatibility level set to 80, I’ll share my findings here.
When a database is upgraded from a previous version to SQL Server 2014, there’s an upgrade process which sequentially executes a chain of upgrade processes to get it from whatever version it was, up to what the storage engine in 2014 is coded to work with.
The more recent the version of the legacy database is the lesser processes will have to be executed during that upgrade phase.
Assuming your starting version is SQL Server 2005, it will execute (as far as the compatibility level upgrade goes):
- one function (the one that would bring it to the SQL Server 2008/2008R2 level) which upgrades the compatibility level of the database to 80 if it was set to a value lower than that. Or to 100 if the database being upgraded is either TEMPDB or MODEL (these cases only happen during an in-place upgrade)
- one function (the one that would bring it to the SQL Server 2012 level) which upgrades the compatibility level of the database to 90 if it was set to a value lower than that. Or to 110 if the database being upgraded is either TEMPDB or MODEL
- one function (the one that would bring it to the SQL Server 2014 level) which upgrades the compatibility level of the database to 100 if it was set to a value lower than that. Or to 120 if the database being upgraded is either TEMPDB or MODEL
So, for example, if your database in SQL Server 2005 had its compatibility level set to 70 or 80, and you don’t bump it up before upgrading it to SQL Server 2014, the upgrade process will do it for you.
Comments
Anonymous
April 17, 2015
Hi Nacho. The BOL documentation at msdn.microsoft.com/.../bb510680(v=sql.120).aspx seems to indicate the lowest compatibility level supported in SQL 2014 is 90: ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 | 120 } Could you provide clarification? Thanks.Anonymous
April 18, 2015
Hi Norm, Notice that further down in that topic (in the "Arguments" section) there's a table describing what versions support each of those values. User Education team has lately been working on merging those topics which are common to different versions of the product into one only. That way they don't have to maintain multiple sets of documentation which is over 60% common across versions. Only that we have to get used to reading and interpreting these topics which have been written using that new style. Thanks, Nacho