Server 2005 is about to ride off into the sunset. Before support ends on April 12, 2016, you’ll need a plan to migrate any remaining instances of SQL Server 2005. What about security? For the last 5 years of “extended support”, users have continued to receive free security updates but beyond that had to pay for all non-security updates and support. Next year, support ends completely, meaning no more hotfixes or security updates and therefore potential exposure to security vulnerabilities.
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 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.)”
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):
1.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)
2.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
3.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, then the upgrade process will do it for you.
