Bad habits: Clinging to old compatibility levels - SQL Sentry

Bad habits : Clinging to old compatibility levels

Some things are deprecated for a reason, folks

[See an index of all bad habits / best practices posts]

I often see people struggling with inexplicable syntax issues, or behavior that works differently on two different servers running the same version of SQL Server (or even two different databases on the same instance). Since error messages aren't generally written to take new versions in old compatibility levels into consideration, and since behavior differences without error messages are hard to notice (never mind debug), it's very hard to classify and catalog every single thing that could go wrong because the compatibility level is not current. But a lot of things can do exactly this.

The documentation lists a slew of things that can change between compatibility levels, sometimes producing error messages that didn't exist before, but sometimes changing behavior in much more subtle ways:

I also tried to put together a thorough list of the highest impact and/or most common scenarios in this dba.stackexchange answer:

That is not, by any means, an exhaustive list; just some of the more common examples.

I didn't want to try to repeat all of those specific issues here, but rather talk about this habit in a more general sense. There are many reasons why you might still be in an older compatibility mode, and please don't take any of these as derogatory:

  • Known or perceived risk in the change – you might have old code that will break (or you're not sure), or even the necessary recompilation of all stored procedures as a result of the change introduces a potential performance impact.
  • Lack of budgeted time or effort to test the change – if it ain't broke, don't fix it, right? (Until you're not able to use some functionality because you're still in an old compatibility level.)
  • Ignorance – not the bad kind, you just might not even know that some database is in an older compatibility level. You don't know what you don't know, right? That's why this post is here. :-)
  • Vendor enforcement – which, quite frankly, is often due to one or more of the above reasons on their side.

Between all of these resources, you should be able to determine what kind of things you'll need to watch out for (and please note that not all of these things will ever be caught by the SQL Server Best Practices Analyzer or the SQL Server Upgrade Advisor – not that those are bad ideas either, if you are planning an upgrade). Identifying everything that might break is going to be an extremely tedious ordeal, even if all of your T-SQL code is static and inside the database. There are further complications from dynamic SQL, encrypted SQL, ad hoc SQL in the application, ORMs, etc.

A much easier way to ensure moving forward will not break your application(s) would be to take a backup of your database, restore it as a different name, change the compatibility level, and run your unit tests against it (or just unleash your application on it). Be sure to test a full business cycle, because you might have breaking changes in that report that your CFO runs once a quarter.

But why should I bother?

So why is it a problem having the odd database in an older compatibility level? Well, one issue is consistency, especially for the same database in your dev, test, staging, QA, and production environments. The other, far more important issue, is that as long as your database is in an old compatibility level, you could continue writing code that only works, or only works the way you observe, because of the compatibility level. This is just piling up what many people might call technical debt – you might be able to skip some work now, but you'll pay for it later. And you might keep making this database harder and harder to upgrade, which will really hurt when there is some new feature that is blocked because of the compatibility level.

See, there's one other important point to remember: you can't keep old compatibility levels forever. Today if you backup a SQL Server 2005 database (or a database on SQL Server 2012 in 90 compatibility level), and restore it on SQL Server 2014, it will be silently upgraded from 90 to 100 – if this is what you are going to do in production, better test it somewhere else first, because it's something you will need to deal with. If you never plan to move your database to a more modern version of SQL Server, there's *less* to worry about, but it is not zero. You could still be suffering (or learning from!) one of the dozens of things that behave a certain way solely because of the compatibility level, and might be surprised, frustrated or worse to find that it doesn't work the same way elsewhere.

Even if you don't plan to do anything about it in the short term, at least take an inventory to see where you might run into this issue in the future:

DECLARE @v TINYINT;
 
SET @v = PARSENAME(CONVERT(SYSNAME,SERVERPROPERTY(N'ProductVersion')),4) + '0';
 
SELECT name, [compatibility_level]
  FROM sys.databases
  WHERE [compatibility_level] < @v
  ORDER BY [compatibility_level];
 
-- note: this will break when we hit compat level 260 (but so will sys.databases)

[See an index of all bad habits / best practices posts]

Comments ( 11 )

        • Bennett says:

          Hi Aaron,

          I think that I get everything you are saying here. Suppose we are dealing with a third party vendor that states that their application supports/requires SQL Server 2008 (ick) and the compatibility level of the database is 100. I interpret that to mean that I can put the database on any server that supports the 100 compatibility level, provided that level is maintained. Is there ever a case where I have to adhere to the vendor's version specification and put a database on a specific version of SQL Server?

        • Aaron Bertrand says:

          Typically vendors say that not because they think (or know) that something is going to break on a higher version or compatibility level, but simply because that was the last version/compat level they bothered testing on. This would make me nervous, as 2008 / 2008 R2 are long out of mainstream support, and will be out of *extended* support soon.

          But, I can't tell you whether your vendor means:

          • If you use a higher version at 100 compat level:
            • things will break
            • you won't have support
            • they have no idea simply because they haven't tested it
          • If you use a higher version and a higher compat level:
            • things will break
            • you won't have support
            • they have no idea simply because they haven't tested it

          There is very little since 2008/100 that will cause backward compatibility issues (I forget when *= join syntax stopped working). My *guess* is that the vendor is being cautious and/or hasn't updated their disclaimers, but nothing bad will happen if you follow any of these paths.

          Still, I have to suggest you talk to the vendor and push for more details.

          • Why is the support level so ancient?
          • Do they have any plans to advance it?
          • What specific things dictate that support level?
          • Or is the real problem just that they haven't tested anything newer?
        • Vinoth says:

          MSDB(120) but User DB in com level 100, is that causes, not running SSIS packages migrated to 2012 version.

          the same package run fine in local machine, but when i run it from scheduled JOb it throws the error

          is there any fix for this?

        • Hailegziabher Dechassa says:

          Nice article. I have seen this different time folks upgrade sql server but keep the compatibility to the older version

        • Johnson Weltch says:

          Great article!!!!! fond this interesting to read. I gone through one more article where it was discussed briefly:
          http://www.sqlmvp.org/sql-server-database-compatibility-level-performance/

        • Paul says:

          I think the outfits in the picture look pretty good. Very colorful. They should bring that back.

        • Joe says:

          I've inherited some 30 odd databases running on SQL Server 2005, with many of them running in compatibility for SQL Server 2000 (80). I'm trying to find out the exact repercussions of changing the compatibility level and how reversible it is.
          I keep backups of the databases and could test the change there, but it would be much easier if I could change the compatibility level on the live database safe in the knowledge I could just set it back to its previous value (or restore from a backup).

        • Aaron says:

          We have a couple of databases running on SQL Server 2014 that are running in SQL 2008 (100) compatibility level that we restored from SQL 2005. Running SQL 2014 Upgrade Advisor gives the error "SQL Server version: 12.00.2254 is not supported by this release of Upgrade Advisor, only SQL2005, SQL2008 or SQL2008 R2 is supported.". Is this an unnecessary step given that the databases are already running on SQL 2014? Is there any way to run an upgrade analysis short of re-creating the database using SQL scripts on an older version of SQL Server?

        • Aaron Bertrand says:

          Aaron, I suspect what happened is that you ran an older version of the Upgrade Advisor. Are you sure you the 2014 version is installed and that you ran that version explicitly?

        • Alberto De Rossi says:

          Hi, Same problem here. Wanted to run SQL 2012 upgrade advisor to a 90 Comp Level DB runing on SQL Server 2012. It looks like the DB have to be on its original version server to run UA. Now what??

        Leave A Comment

        Your email address will not be published.

        This site uses Akismet to reduce spam. Learn how your comment data is processed.