Understanding SQL Server Compatibility Levels
This article aims at describing the importance of compatibility levels when it comes to SQL Server. Hopefully, by the time you finish reading, you’ll understand why you get errors in your applications when you upgrade your database from one version of SQL Server to another. It will help you determine if you did a valid upgrade or not. The compatibility level will tell you.
What is a compatibility Level?
Let’s say you have SQL Server 2012 installed on one of your new servers. This instance of SQL Server 2012 contains one or more databases that you need to maintain from time to time. But just because SQL Server 2012 is running on this server, it does not mean that all the databases inside that instance are all SQL Server 2012 databases.
If you previously had an older SQL Server 2008 database and restored it on this new SQL Server 2012 instance, does that mean you converted the database to an SQL Server 2012 database? The answer is No. Sure the database may run well on this new instance but did you really upgrade anything? The answer lies on whether the compatibility level changed during the restore process. To know if you really converted anything, you need to look at the compatibility level of the database itself. A compatibility level is associated with each database. It allows the behaviour of the database to be compatible with the specific version of SQL Server it is running on.
To see the compatibility level of each database, right click the database in SQL Server Management Studio and select Properties, then click the Options tab. See highlighted field below:
In the image above, I’m running a SQL Server 2008 database, which has compatibility level of 100, on a machine that is running an instance of SQL Server Express 2014 (see the Connection section on the bottom left, my instance name is SQLEXPRESS2014).
Another way to look at the compatibility level of each database on your server is to query the compatibility_level column of the sys.databases table:
USE YourDatabaseName; GO SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; GO
The main reason for having compatibility levels is to allow for Backward Compatibility. Each compatibility level has its own set of features and you must program against those features when developing an application that uses the database. If you want to move your database to a newer instance of SQL Server but at the same time have it backwards compatible, you need to make sure the compatibility level stays the same. But beware, sometimes you may not have a choice and it must be changed because the new instance of SQL Server may no longer support it.
Know your instance
To understand compatibility levels, you must first understand what an instance is. An SQL Server instance is related to the version of SQL Server you have installed on your server. For example, if you installed SQL Server 2008, you have an instance of SQL Server 2008. If you installed SQL Server Express 2014, you have an instance of SQL Server Express 2014. Pretty straightforward don’t you think? During installation you are given the chance to name your instance. It is highly recommended naming it in such a way so that you know which version of SQL it is, especially if you intend on having multiple versions on the same machine. I recommend including the version in your instance name, like SQLEXPRESS2008, or SQL2012, that way you immediately know which compatibility levels it supports just by looking at the name.
So what’s the relationship?
There is a limited number of compatibility levels that each version of SQL Server can support. For example, databases that have a compatibility level of 80, can only be installed on SQL Server 2000 instances up to SQL Server 2008 instances. Below is a summary of what each instance can support:
|Database Compatibility Level||Description||Instances that support the compatibility level|
|130||SQL Server 2016|| |
SQL Server 2016 Community Technology Preview 3.2 (CTP 3.2) to SQL Server 2016
|120||SQL Server 2014||SQL Server 2014 to SQL Server 2016|
|110||SQL Server 2012||SQL Server 2012 to SQL Server 2016|
|100||SQL Server 2008 and SQL Server 2008 R2||SQL Server 2008 to SQL Server 2016|
|90||SQL Server 2005||SQL Server 2008 to SQL Server 2012|
|80||SQL Server 2000||SQL Server 2008 to SQL Server 2008 R2|
The above table immediately tells us, for example, that if you have an old SQL Server 2000 database and you want to migrate it to SQL Server 2012. You can’t! The minimum compatibility level supported by SQL Server 2012 is 90, which is a SQL Server 2005 database. You will eventually get an error similar to:
The database was backed up on a server running version 8.00.0760. That version is incompatible with this server, which is running version 11.00.3128. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Don’t wait too long to migrate
So what do you do in a case like that? Well, some people end up taking two steps. They first migrate the SQL Server 2000 database to, say SQL Server 2005, and then migrate it to SQL Server 2012. This is the one reason NOT to wait too long before migrating a SQL Server database to a newer version. Sooner or later your compatibility level will have to change and this will impact your applications. By doing it sooner, you will reduce the amount of work you’ll have to do later.
Migrating the easy way
Some developers or administrators take the short route when they migrate an old database to a newer version of SQL Server. That is, they backup the old database, and restore it on a newer instance that supports the compatibility level of the old database. But after restoring, they keep the old compatibility level and continue using the database with the same applications. Then they tell their users that the migration is “complete”.
In some cases, this may be the best choice if the application is complex, especially if the goal is just to eliminate the old instances of SQL Server, or if you have no time to make changes to the application. To some database administrators, the easy way is not really a migration or an upgrade. To them you’re simply moving the database.
If you opt for the easy migration, be aware that what you are really doing is kicking the can down the road. Eventually Microsoft will come out with a version of SQL Server that won’t support your old database’s compatibility level. So you will have no choice but to migrate and change the compatibility level to a higher number. You may also want to do that if you want to take advantage of the newest features of SQL Server.
Impact of changing the compatibility level
The act of changing the compatibility level tells the database to change its feature set. That is, some features will be added, but at the same time some old features will be removed. For example, The FOR BROWSE clause is not allowed in INSERT and SELECT INTO statements at compatibility level 100 but it is allowed but ignored at compatibility level 90. So if your application uses this feature, this change may introduce unexpected results.
For a list of changes and an updated version of the above table, see the appropriate MSDN library page based on your version of SQL Server. The links below are some examples:
Before changing the compatibility level
Never change the compatibility level while users are connected. It is preferable that you follow these steps to properly do the job:
- Change the database to single-user mode.
- Change the compatibility level.
- Put the database in multi-user mode.
Essentially, changing the compatibility level to a higher level is where the real work is involved and considered to be a real upgrade. Doing this forces you to scan and test your application and find any SQL features it uses and subsequently update it if problems occur. Try and get a super-user of the application to run a complete test from A to Z of the application, and at the same time, you as the developer, need to look at the code to see what problems may have been introduced by the upgrade.
Beware the silent upgrade
Compatibility levels get deprecated eventually. When a new version of SQL Server no longer supports an old compatibility level, databases with that old compatibility level that are restored may get silently upgraded to the lowest compatibility level the server supports. For example, if you restore a SQL Server 2005 database (with compatibility level 90) onto a SQL Server 2014 instance, it will be silently upgraded from 90 to 100. That’s because 100 is the lowest level supported by SQL Server 2014. If you look at the table above, 80 and 90 are not supported by SQL Server 2014.
Test your application!
Even when you don’t change the compatibility level, you should always test your application. Because chances are high that it will give unexpected results when running it and you will have to fix them before declaring a successful migration. It all depends on the data features the application uses. Use the links above to prepare in advance.
The compatibility level is also used when Stored Procedures are executed. In fact they are all recompiled when it changes. So test any and all Stored Procedures that your application executes.
Never assume that backing up and restoring a database to a newer instance is all that is needed to migrate a database. This is the cause of many hidden and unexpected application errors. If your application gives errors after a database restore, look at its compatibility level. If it has changed, you now know why. In my opinion, the compatibility level is more important than the SQL Server version itself.