SQL Server
Microsoft uses the Database Campatability Level to make sure your applications continue to work even after an upgrade of SQL Server. There has been much confusion over this database property. For instance, when I upgrade a database and the compatibility level remains at a prior release have I truly upgraded? Let’s take a look… There are four main ways of upgrading a database in SQL Server. Of course, you can upgrade the whole server including all the databases attached to that server; this is called an in-place upgrade. The other three methods allow an individual database to be upgraded through migration: 1) Copy Database Wizard 2) Attach/Detach 3) Backup/Restore. In all these methods the new copy of the database present on the new SQL Server is an upgrade to the new release. i.e. the internal format of the database is that of the new release. However, the Compatibility Level property for that database remains at the release it was upgraded from. For instance, if you copy a SQL 2000 database to SQL Server 2008, the new copy is an upgrade to the new internal format but retains a Compatibility Level of “80” which represents SQL 2000 aka SQL 8.0. This property is really just a flag to say to the Database Engine to evaluate SQL syntax against this database according to the SQL 2000 level. This is a good thing. It means that all your application code, stored procedure code, trigger code etc should continue to work even though the database has been upgraded. That’s called backward compatibility. I say “should”. Of course, you need to test thoroughly to make sure. The drawback of having the Compatibility Level at a prior release is that you cannot use many of the new statements of the latest release. If you raise the Compatability Level, you have to recognize that certain code may cease to work. An example may be if you used the DUMP command that was officially deprecated in 2008 (after many years on the chopping block) and since replaced with the BACKUP statement. But you want to use the new MERGE statement only in 2008. Also maybe you already have a table called MERGE. You can change the name or use square brackets to continue using the new reserved word as an object name. In other words, you will need to allocate some extra time for existing code changes before changing your code to use the new statements. But don’t worry. If you change the Compatability Level and it causes more trouble than its worth, you can just change it back again and everything will be fine. It’s just a flag. The internal structure remains the same. Of course, you would only do that for a database on a test server, wouldn’t you? One extra note: The Upgrade Advisor is a good tool to give you fore-warning of such situations without changing a thing before an upgrade. It generates reports highlighting code issues that may occur when upgrading your databases, with a rich Knowledge Base to assist you with expert advice. You will always get at least one error first time through, namely: “Other Database Issues”. Don’t take it personally. Microsoft doesn’t want us to be too complacent about the upgrade so they throw this one out there for free, just to keep us honest. cheers Brian
