• Adam Thurgar

Backward compatibility


There have been many times when I have reviewed SQL Server installations and found databases in a backward compatibility level. Backward compatibility allows applications not to be tested or changed when the database server installation is upgraded.

https://technet.microsoft.com/en-us/library/cc707787(v=sql.110).aspx

Sometimes it is just a case that you have done an in-place upgrade of your SQL Server and you didn't change the compatibility level. Or that the restore of the database to a new server the compatibility level didn't get changed.

Or it could be just a case of being lazy and not being willing to test your applications against the current compatibility level.

The problem with backward compatibility is that your T-SQL code may be using deprecated features that at some point may longer be supported. It can also cause performance issues as the optimizer has to work differently for these different branches. Finally - sooner or later you are going to have to change that compatibility level as the version of SQL Server you upgrade to will not have that compatibility level available. You are just delaying the inevitable. It may be the case that your application will work fine, but you need to test it or check with the vendor if they support the compatibility level.

I know of one major financial institution who upgraded all of their SQL Server 2000 and 2005 servers to SQL Server 2008, but left all the databases in a backward compatibility level. They had avoided the issue of being on an unsupported version of SQL Server, but have only delayed the database level upgrade that at some time they will have to go through and test their applications against this compatibility level.


3 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t