Setting database compatibility to SQL 2000 on a SQL Server 2005 instance?
No doubt that as compare to previous version the current SQL Server 2005 provides more new language constructs and primitives for the T-SQL language than can be utilized. Still there are many out there not entirely ready to upgrade from SQL Server 2000 to 2005. In this case you can take few advantages of SQL 2005 by keeping the user databases in SQL 2000 (80) mode.
As explained SQL Server 2005 has a "compatibility mode" that you can set "back" to 8.0 (SQL Server 2000). By intiating this feature it will NOT change the internal workings of 2005, with the new optimizer and so on. The execution of query will be as similar to 2000 but with few added advantages from optimizer. We have performed such upgrades and left the database compatibility to 80 due to the legacy applications issue, they are working well. Few things you need to consider when upgrading to SQL 2005 are:
- If the user database contains a user named "sys", it will not upgrade. "sys" is a reserved schema name in SQL Server 2005
- If you are using any object type code as “S” this will not work. System tables (S) are not exposed
- Updating system tables is not supported. Direct catalog updates are not supported in SQL Server 2005.
- Code will not work if it tries to GRANT, DENY, REVOKE, and DROP on system objects. Catalog and system objects are not modifiable.
- Code will not work if it accesses undocumented tables or columns
Best advice is don't rush your upgrade better to have a desktop or your testing environment with an already installed instance of SQL Server 2000 and you want to try the in-place upgrade or maybe you just want to install a new instance of SQL Server 2005. There may be issues that will arise when installing the new instance and also analyzes options for interaction between the two versions, such as: linked servers, multi-server administration and log shipping.
When you are running the user databases in SQL 2000 mode on a SQL 2005 machine will have little confusion when they ask you what version of SQL you are running. The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to. If you run SELECT @@VERSION it will return the SQL Server version and not the database version, you could run SELECT DatabaseProperty ('dbccpagetest', 'version');
You cannot attach a database that was created on an earlier version without going through the proper upgrade procedures. Forcibly attaching a database using various hacky methods will result in all kinds of weird errors, and possibly crashes. In this case using the database in 80 compatibility mode doesn't take advantage of OUTER JOIN system from SQL 2005, so better to check with in the Books Online section for sp_dbcmptlevel - the SP used to set the compatibility level. Further for DTS and SSIS migration have a look at this TechnetArticle reference.