Sql-server – What changes SQL Server Compatibility Level changes

compatibility-levelsql serversql server 2014sql-server-2008-r2

We had a database and lots of functionality that worked fine in SQL Server 2008 R2. We recently upgraded both our development and production servers to SQL Server 2014. Unbeknownst to us, our development server stayed in compatibility mode for 2008 R2, but the Prod box is in Compatibility Mode for 2014.

My question, is where can I find what changed between SQL/Compatibility versions so we will know what needs fixing to maintain functionality. I know I can set (or have set) the Prod server to compatibility with 2008 but since I don't administer the prod box, I cannot make that call. My google-fu seems to keep turning up tutorials on how to change the compatibility level, but nothing on what actually changed so I can't tune our queries/procs/functions/etc to work well with the new version.

Best Answer

A compatibility upgrade changes the way the SQL Engine works and has the database level improvements/changes from the other versions. This doesn't mean you can't use features outside of that though. For example having a DB in a older compatibilty mode doesn't mean you can't use mirroring or AGs.

It does mean the query optimizer, newer system provided functions, and other features will not work the same way or be available in some cases. You will want to ensure your code operates the same way but your DR/HA and operational solutions shouldn't have changed.

Run your code through the Upgrade Advisor and also read up on the breaking changes. Read up on best practices for upgrades as well, Thomas LaRock has a good article on it as an example.

Related Solutions

Sql-server – SQL Server 2008 upgrade advisor tool

The upgrade advisor will work, but you need to make sure to analyze a trace file of activity. Just pointing it to the database and running it will only analyze the TSQL code stored in the database and not any TSQL code within your application.

In addition to the upgrade advisor, you can run a Profiler trace to capture the events: "Depreciation Final Support" and "Depreciation Announcement". Both will identify code that will need to be evaluated because of syntax or reference to objects that don't exist in new version.

Check out Books Online for a decription of each of those events: Depreciation Event Category.

Sql-server – Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that’s in 2000 (80) compatibility mode

This may produce some false positives (e.g. you may have *= in a comment), but should be a good start:

SELECT 
  obj = QUOTENAME(SCHEMA_NAME(o.[object_id]))
      + '.' + QUOTENAME(o.name),
  o.type_desc
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.[object_id] = o.[object_id]
WHERE m.definition LIKE '%=*%'
OR m.definition LIKE '%*=%';

Best Answer

Related Solutions

Sql-server – SQL Server 2008 upgrade advisor tool

Sql-server – Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that’s in 2000 (80) compatibility mode

Related Question