Befriending Dragons

Transform Tech with Anti-bullying Cultures

SQL Server lifecycle and the compatibility level settings

3 Comments

I’ve had several people ask how the compatibility mode/level fits in with the SQL Server lifecycle. For example, the question might be: once we no longer support SQL Server 2000, does that mean we don’t support databases restored/attached to later versions of SQL and still set to 80 compatibility mode for example? The answer is that each version of SQL Server has a set of compatibility levels it supports. That support does not change based on the lifecycle. So for example, SQL Server 2008 R2 supports compatibility levels 80 (SQL 2000), 90 (SQL 2005), and 100 (SQL 2008). When you restore a SQL Server 2000 database to SQL Server 2008 R2 it remains in 80 compatibility mode until you issue ALTER DATABASE {dbname} SET COMPATIBILITY LEVEL = 100 (in older versions use sp_dbcmptlevel). When extended support for SQL Server 2000 ends in 2013, Microsoft will not support SQL Server 2000 any more. But we will still support a database on SQL Server 2008 R2 that is in 80 compatibility mode because that is a supported level/mode for R2. So the lifecycle refers to the actual instance, not the database level.

I want to add some information here that a lot of people don’t understand. The compatibility level affects only some of the behavior of that database, mostly related to syntax and available features. A database set to 80 compatibility on SQL Server 2008 R2 is NOT the same as a database actually running on SQL Server 2000. For example, you can NOT restore the 2008 R2 80 compatibility mode database back to a 2000 instance. The optimizer is the 2008 R2 optimizer, not the 2000 optimizer. The compatibility mode/level is meant to be a temporary workaround while you fix syntax or keyword issues.

3 thoughts on “SQL Server lifecycle and the compatibility level settings

  1. Added thoughts –

    It is critical that folks understand that this should not be a permanent solution as you stated in the second paragraph and it is incumbent on any app vendor to certify/test/update their software on the latest versions where feasible if that is the blocker for migration.  ^au

  2. Hello ,

    I am new to Sql server DB administration.Could you please tell me what is compatibility mode with useful examples & available modes.

  3. @Kunal – Read more about compatibility level settings here: technet.microsoft.com/…/bb510680.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s