升级你的数据库兼容性级别

微软使用数据库Campatability水平,以确保您的应用程序继续工作甚至在SQL服务器的升级。有很多困惑在这个数据库属性。例如,当我升级数据库和兼容性水平仍处于以前版本我真正升级吗?让我们来看看……有四个主要的升级方式在SQL Server数据库。当然,你可以升级整个服务器包括所有服务器的数据库连接;这就是所谓的就地升级。其他三个方法允许单个数据库通过迁移升级:1)复制数据库向导2)附加/分离3)备份/恢复。在所有这些方法数据库的新副本出现在新的SQL Server是一个升级到新版本。即数据库的内部格式的新版本。 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