From the questions on StackOverflow and elsewhere, I notice that there are still people using SQL Server 2000. To be honest, the greatest effect on me is that I have to remember how we used to do things without Common Table Expressions.
Still, I'd like to know if there are actual reasons (beyond "it ain't broken") to still use SQL Server 2000. For instance, I know there are people who have to use .NET 1.1 because they have to support Windows 2000 systems. Are there any similar, practical reasons to not upgrade a SQL Server 2000 system to SQL Server 2008 or 2005?
Business mind is usually very different from yours. Why use something newer if it won't generate more revenue, but only expenses (migration, retraining etc.)?
Best reason I can think of is a variation of "it ain't broken" that goes like this: You have a business application that works with SQL 2000 but generates errors with SQL 2005. It may not be the right business priority for your organization at this time to open the hood on that application (which ain't broke) and make it work with a newer database.
Legacy support
SQL Server 2005 is not totally backwards compatible with SQL Server 2000. Analysis Services has major incompatibilities. Moving to SQL Server 2005 has a non-zero cost from regression testing and porting. Many organisations don't have a requirement to move, so they won't move until they have to.
Most DBMS vendors (MS included) will support a version of a DBMS for 10 years or so - which is longer than most other types of software. If you cross their palms with silver (in sufficient quantity) they will also enter specific contracts to extend support on a specific version longer than that.
Other reasons to stick with older versions are really driven by specific circumstances such as avoiding a known botched release (e.g. MySQL 5.1 or pre-SP3 SQL2000) or certification or compatibility issues.
Maintaining a SQL Server 2000 production database
For an operational system that works and is in a mature phase of its life cycle without a lot of major changes going on, there is probably no compelling reason to upgrade before the DBMS goes out of mainstream support. However, you shoud plan an orderly upgrade path for that eventuality. Oracle is quite renowned for people maintaining production systems on ancient versions.
SQL Server 2000 is getting close to its end of life, so you wouldn't want to be doing new development work on it. However, a production application should be maintained with a plan to move off when you need to. You will probably have a rewrite on your hands if your app is written in VB6 or classic ASP - but that's a different issue ;-}.
The counter case
If I had a greenfield project I would typically recommend the latest version of the DBMS platform simply because it gives you the longest window of vendor support. No-one should still have SQL Server 2000 as a corporate standard for new projects - the EOL is too close. For a new project, this is by far the strongest argument to move to a newer version. Arguments about saving money don't hold water; the app will incur unnecessary porting costs within a couple of years if you start on SQL2000 now.
The key point for greenfield work is that an overly conservative selection shortens the service life of the application before an upgrade is required. One would normally want a specific reason not to go for the current version of a DBMS platform.
We don't upgrade because we have financial software that relies on it, and the vendor is only just thinking about supporting this new "SQL 2005" they've been hearing the crazy kids blog about lately.
Frankly, it hurts me to have a mixture of stuff to support, but not nearly as much as it would hurt everyone in the company to have a problem with the finance system pop up, and then to have the finance software vendor point at us and laugh when we asked for help and mentioned that the problems started since we upgraded to SQL 2005 before they've ratified it.
There is also a lot to be said for not fixing things if they're not actually broken. I do want to upgrade all my SQL servers to 2008 sometime... I really do. But there's so much more stuff I could be doing that actually improves the bottom line of the business in ways that are understandable and quantifiable to my managers and the users... well that's always going to come first.
You mention developers not liking working with SQL 2000 in your reply to Mastermind's answer. I suspect one method of dealing with that would be the one my bosses would use on me if I decided I "didn't like" supporting SQL 2000: "Thanks for your time working here, door's to your left". At the end of the day we're all being paid to make stuff work whether we like it or not.
In our case, it's less a case of 'why keep using 2000' than it is a case of 'the upgrade costs HOW much?'. Most of our apps don't use anything that needs any 2005-specific features, so there's no compelling reason, and probably won't be until Microsoft cease support for it.
Cost.
Newer software is still additional software, which costs additional money.
This may be, and often is, a false economy due to the many down sides of using outdated systems, but it's the only real reason that makes sense.
New licence for SQL Server 2008 (standard) costs a little less than $2,000 for 1 server with 5 CALs or $6,000 for 1 CPU licence. I can hear my old boss saying "it ain't broke, but it's going to cost how much to upgrade those 2 servers?" (at that time 2 x dual CPU requiring CPU licences).
Having used SQL2000 for seven years, my first experience of 2005 was in a VMWare ESX environment. Performance sucked (the ISP was providing the VMWare expertise and when we started load testing we found they were having major problems not just with our server but impacted our experience), and combined with everything being moved around/renamed (the old enterprise manager was sooo much faster) - we were happier to postpone upgrading. Then we were waiting for the release of 2008 so we're only now upgrading to 2008 (or migrating some data to MySQL/PostgresSQL).
Microsoft was still giving mainstream support for 2000 until some time last year so it's only been recently that we've had a more compelling reason to upgrade.
Small companies have a resources problem too - learning, planning, testing the upgrade takes time and impacts on other operations. If you have to upgrade some other application that sits on SQL Server at the same time this can get very expensive too.
For a while we flirted with moving to open-source, and while that was a possibility it put a freeze on any upgrade too.
Why not upgrade to sql server 2008, then you actually can lower your cost, due to datacompression, easier maintenance and improved performance (filtered indexes)? Recently we upgraded an sql server 2000 installation and saved 75% of diskspace, and the performance increased dramaticly.
Another improvement was the concurrency due to the new locking mechanism introduced in sql server 2005.
/Håkan Winther
Large organizations are risk adverse and ignorant, which leads to some really dumb decisions. When I was doing Unix stuff around 2003/2004, we were stuck with a Solaris 2.5.1 environment that was released when I was in 10th grade or so because it was "proven" technology and it was "too expensive" to upgrade.
Too expensive is often PHB-speak for "I was do dumb to budget for a $1000 upgrade in a $10M project".
In the case of Microsoft stack software, people are often upgrade-adverse because they didn't want to deal with the licensing troll within the company and purchased an OEM license from Dell/IBM/etc, and doesn't feel like dealing with the bureaucratic hoops involved in upgrading.
I don't buy (pun intended) the "upfront cost" argument, but unfortunately upfront cost is something that a lot of PHBs do obsess over so it's valid to let it stand.
For me the sole compelling reason would be the apps that use it. If you're a SharePoint 2003 house, for example, there is NO WAY you're going to upgrade to SQL 2005 or 2008 without also undertaking a SharePoint upgrade, which is not a trivial matter.