SQL Server compatibility level

Question

I inherited a database server with SQL Server 2012 Enterprise Edition. All the databases (except for the system databases) are at the 90 compatibility level.

How do I justify updating them to 110 compatibility level?

Let me rephrase my question:

The server is upgraded to SQL 2012 but user databases' compatibility levels were not updated to 110. So tempdb etc system dbs are 110 but user databases are still 90. I would like to update them all to compatibility level 110. I wish it's as simple as ALTER database compatibility level. :) But I need to justify use of resources to thoroughly test the applications out.

Am I right to say that even though the system databases were in compatibility level 110 but the user databases are not reaping the benefit of SQL Server 2012? Which means the databases were not really upgraded?

Thanks!

Answer 1

When databases are upgraded either using backup restore method or via attach/detach process, the compatibility level of the databases do not get changed automatically, and show as old comp. level. However this is not a major problem, but point to note is that you may not be able to use various features , that are available on newly upgraded SQL server.

As mentioned by owen, you should refer the MSDN doc to refer the difference between the Compatibility-level setting of 100 or lower *with* Compatibility-level setting of at least 110 .

Also, running an upgrade advisor will help you in identifying for any issues while you do that upgrade or change in compatibility level.

If you have the test environment you can always test the scenario before making any changes in prod and once you are good to go, just make sure you have the latest backup of system databases on production server. In case anything goes wrong, you can recover or rollback from the backups.

Also, an additional read here Bad habits : Clinging to old compatibility levels

Answer 2

Compatibility Levels can enable new query engine features (eg. Windows function enhancements available in SQL 2012+). Leaving databases on an older compatibility level is fine, nothing will break, but you can change to the latest supported compatibility level to gain access to new features.

Check ALTER DATABASE Compatibility Level (Transact-SQL) for more details, particularly around differences in compatibility levels. If anything in there will break your apps leave the database at compatibility level 90 for now.