It can be hard to say goodbye to old friends and SQL Server 2000 is definitely an old friend of mine. It was a huge improvement over SQL Server 7.0. Sure it still had some quirks (remember the pain of setting up SQL Mail?), but all in all, it offered a huge improvement over version 7.0, whom I always regarded as a frenemy. Some of the new features that SQL 2000 gave us:

• Support for 64 GB of RAM with AWE
• Parallel DBCC operations
• Parallel index creation operations
• Support for log shipping
• Native support for SANs
• Improvements in replication
• Support for multiple instances on the same computer
• The BIGINT and TABLE data types
• User defined functions
• INSTEAD OF triggers
• Indexed views

Plus many others. Imagine how more difficult a DBA’s job would be today without those features. I’d be willing to bet many of you still have instances of SQL 2000 running in production now. I know I do.

But it’s been 12 years and SQL 2000 is getting a bit long in the tooth. It’s time to say goodbye. When SQL 2012 is released later this year, there will be no upgrade path from SQL 2000 to SQL 2012. That means if you currently have some SQL 2000 servers, you’ll need to either retire them and move the databases to more recent versions, or upgrade them soon to 2005, 2008, or 2008 R2.

That’s relatively straightforward to understand and it’s easy to spot which machines are still running SQL 2000. Here’s something you might not catch: the compatibility levels of databases on ALL your SQL Servers. Check every one.

Not only is there no upgrade path from SQL 2000 to SQL 2012, but SQL 2012 will not support SQL 2000 compatibility mode (i.e. compatibility level 80). Determine the compatibility level your databases are in with the following code:

SELECT name,
       database_id,
       compatibility_level
FROM   sys.databases

(This only works on 2005+ servers, but if you are running 2000, you are in 80 compatibility mode (or earlier) by default.)

You may think your databases are OK because they are hosted on a 2005 or later server, but here’s the thing: the database compatibility level is NOT changed when you take a database from an older version of SQL Server to a newer version, either by moving the database to a new server or by upgrading the server itself. So all those 2000 databases you already migrated to a newer SQL version could very well still be in 2000 compatibility mode. The only way the compatibility level will change is if you change it manually. You can do it through SSMS and the Options page of the Database Properties screen or via the following SQL code:

ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = nn

Appropriate values for nn are “90” for SQL 2005 and “100” for 2008 and 2008 R2.

If you have databases on a 2005 or greater server that are in 2000 compatibility mode, DO NOT MAKE THIS CHANGE WITHOUT TESTING! Especially on production systems! Why? Because the compatibility mode determines how the SQL Server engine interprets T-SQL. Probably one of the biggest changes between 80 and 90 compatiblity mode is that the *= and =* join syntax that was valid in 80 will generate in error in 90 compatibility mode. A complete list of the differences between 80 and 90 compatibility modes can be found here. That same page also lists the differences between earlier versions and 100 compatibility mode. Even if your databases that have been developed in house can be changed, beware of vendor databases from any third party software your company might be using. Make sure they support a higher compatibility mode before making any changes. And FYI, the compatibility level change takes place the next time the database is made current, i.e., upon a new connection or execution of a USE statement. Microsoft’s Best Practice is to put the database into single user mode before making the change to prevent sub-optimal query plan generation and possible inaccurate results.

And look, while you’re going through all this work, don’t just stop at SQL 2005. Try to get to at least SQL 2008 and compatibility level 100. Why? Because with SQL 2012, compatibility level 90 is deprecated. And upgrades to the next version of SQL Server from SQL 2005 will not be supported. Do a bit of extra work now and save yourself some time in the future. You’ll thank me later.