Friday, April 27, 2007

What's the difference between database version and database compatibility level?

Paul Randal, over at SQL Server Storage Engine blog, discusses in his post the difference between database version and database compatibility level. When working with ApexSQL Log this difference is important since any db on SQL Server 2005, even with compatibility level of 70 (SQL Server 7) or 80 (SQL Server 2000), still has the same structure of system tables as a db with the level of 90 (SQL Server 2005). This matters in three cases:
1. When we are doing DDL analysis/recovery since we have to look for changes in different tables under SQL Server 7/2000 and SQL Server 2005 due to complete redesign of system tables in the latter version.
2. When users are directly auditing changes made to system tables (which is sometimes necessary.)
3. When auditing transaction log backups from one version of SQL Server on another. In ApexSQL Log 2005.03 transaction logs from SQL Server 7/2000 could not be read at SQL Server 2005 or vice versa. With the upcoming 2005.04 version this will be done seamlessly.

Of all of these cases only the 3rd is really problematic since we depend on SQL Server to provide us with meta-data for all the tables including system tables. So when we see an operation on say "sysobjects" table from a SQL Server 2000 transaction log attached to SQL Server 2005, we aren't able to reconstruct it since SQL Server 2005 lacks meta-data for "sysobjects" table. This case is very rare but even so we will try to solve it after 2005.04 by building in meta-data for system tables of all three versions of SQL Server that we support.

Btw, in case anyone is wondering how you can audit SQL Server 2005 transaction logs on SQL Server 7/2000 database (considering that it cannot be attached), it can be done by auditing transaction log backups or detached transaction logs.

No comments: