Sql-server – Compare the database compatibility_level to the server version

compatibility-levelsql server

I have a mixed environment of servers from 2000 to 2012, and I'd like to find any databases that have a compatibility level set less than the version of the server it is running on.

Ideally, I'd like to do something like:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level <> SERVERPROPERTY('productcompatibilitylevel')

but that unfortunately doesn't exist. Instead I'm having to do a big CASE statement based on SERVERPROPERTY('productversion'). Any better ways to do this?

Best Answer

I found a couple of options that I think will work:

First, you could multiply the first two digits of SERVERPROPERTY('productversion') by 10:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level <>
    10 * convert(int,CONVERT(char(2),SERVERPROPERTY('productversion')))

Or, probably easier, you could just compare to the highest compatibility_level on the server:

SELECT name, compatibility_level
FROM master.sys.databases
WHERE compatibility_level < (SELECT max(compatibility_level) FROM sys.databases)

FYI, you can't just compare to the compatibility_level of MASTER or MODEL, as those can be left lower after an upgrade, or deliberately changed. TempDB, however, I think is always the current version.

Related Solutions

Sql-server – Which PowerShell technique should I use to talk to SQL Server

Obviously, a lot of this devolves to simple personal choice. Here are my own, personal, rationalizations.

I've been using Powershell with SQL SQL since PSH v 1.0, and before SQL Server started officially integrating it. (When I started with PSH, I was administering SQL Server 2000 and 2005 servers.) So, I learned with SMO (or it's slightly older incarnation, the name of which escapes me at the moment) and .Net and I'm used to them. I'd generally lean towards SMO, since it makes some things a lot easier, like scripting out objects. My own code uses SMO some times and .Net some times. I think it's handier to use .Net to get simple result sets, for instance.

I think that Invoke-SQLCMD makes more sense if you have lots of existing TSQL scripts. If you are creating strings and executing them through -Query, that's going to be messy. If you have a good grasp of how Powershell works with .Net and SMO, using Invoke-SQLCMD occasionally, when you have a script file to run, is easy.

I've always found the PSDrive thing clunky and felt that they implemented it because they got caught up in the "everything can look like a file system" idea. I know that the *nix guys love \proc and such, but I feel that this implmentation feels sort of forced. I think that PSDrive is OK, maybe even good if you hate the UI, for exploring things but I've never written a script that uses it.

I have never seen anyone use the WMI provider. So, that would be my last choice.

So, I'd lead with SMO and fall back to .Net when it's handier to.

Sql-server – How to detect compatibility errors – SQL Server 2012

Can you show the query? Just because it runs doesn't mean it's correct. :-) For example this is not legal, but it works:

SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY o.modify_date;

Technically, it should be as follows, since o.modify_date is not in the SELECT list:

SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY modify_date;

In this Connect bug, it was stated that this rule would be removed from the Upgrade Advisor. I suspect that one of the following (or both) happened:

They forgot to remove the rule as they said they would
They had other complications which prevented the above syntax from being blocked by the parser

That all said, there are some breaking changes the upgrade advisor will never catch. For example, this will work on 2005, 2008 and 2008 R2:

CREATE TABLE #foo(id INT);

IF OBJECT_ID('tempdb..#foo') > 0
BEGIN
  DROP TABLE #foo;
END
GO

CREATE TABLE #foo(id INT);

This will break in SQL Server 2012, however, since #temp tables now get a negative object_id. The proper way to test is:

IF OBJECT_ID('tempdb..#foo') IS NOT NULL

I go over a few other issues in this blog post. A couple of other breaking changes:

If you use any of the memory-related DMVs there has been a major overhaul to some of the columns:

https://blogs.msdn.microsoft.com/sqlosteam/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012/

Again, most of these are not caught by the upgrade advisor, so running that tool and not doing any thorough testing could really put you in a bad spot.

And I agree with Eric's answer - you should try to track deprecated events. For completeness, here are three approaches:

Best Answer

Related Solutions

Sql-server – Which PowerShell technique should I use to talk to SQL Server

Sql-server – How to detect compatibility errors – SQL Server 2012

Related Question