10

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?

2
  • I'm totally baffled by a downvote. I wanted to understand the reasons. I've got some great answers. If there's a problem with the question, then please don't assume I know what the problem is - tell me! I don't usually bite over the Internet. Jul 3, 2009 at 1:23
  • i think the fundamental problem with the question is that by giving reasons not to continually upgrade software, your putting sysadmins out of jobs! boo to you sir! Oct 28, 2009 at 13:23

14 Answers 14

15

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.)?

5
  • 1
    True, and anything they can't actually measure is a problem. How do you measure the fact that new developers prefer not to work on software eight years old; that examples increasingly don't work on SQL Server 2000, and that lack of features hinders development to meet new requirements? Jun 28, 2009 at 19:34
  • 1
    That is so. Unfortunately, many shops prefer lying about the technology they use to catch the fish to work for them.
    – Mastermind
    Jun 28, 2009 at 19:46
  • 1
    In companies where IT reports in to say a Finance Director (or even HR!) getting the case heard and understood is the hardest part. Talking to a CEO recently he said (his) problem was so many developers just want new toys and fail to explain the business case if there really is one. Senior management don't fully trust their IT.
    – Dan
    Jun 28, 2009 at 21:41
  • 2
    @Dan, We actually have to admit we do want new toys. :) But there is a real reason behind: we want to keep up with technology to keep or increase our value on job market. That is however an anti-case for your current CEO.
    – Mastermind
    Jun 28, 2009 at 21:48
  • @Mastermind - I'm sure you can see why those are bad reasons for a company that is otherwise happy with its current platform to invest in an upgrade.
    – Rob Moir
    Jun 29, 2009 at 15:23
9

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.

2
  • Thanks, but what kind of errors? I haven't been able to think of anything that would work on 2000 but not on 2005. Jun 28, 2009 at 19:14
  • 6
    See serverfault.com/questions/30499/sql2005-vs-sql2008/30512#30512 for a problem we found when first moving one of our apps from SQL2000 to SQL2005. You should never blindly upgrade a system component without thoroughly testing the apps on the new version first (or getting your suppliers to say, on paper and signed, that they have done such testing). Jun 28, 2009 at 19:20
4

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.

1
  • You can benefit from an upgrade (to SQL 2008) even if the apps doesnt use SQL 2005 specific code, and that is only by activating datacompression (see my earlier post). And you can decrease the locks in during heavy load by activating "row versioning". Another thing to think of is when you buy or build another app that is using SQL 2005/2008 specific code, do you want to support two different environments? And my last argument in this comment, you can rebuild your indexes online, without the need for a service window. This will have effect at once. /Håkan Winther Jun 29, 2009 at 5:39
4

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.

2
  • Worse, Microsoft doesn't appear to have upgrade pricing for SQL Server, so every new edition is full cost even for existing customers. Somebody please tell me I'm wrong? :-( Jun 28, 2009 at 23:26
  • You're not wrong, unless you purchased Software Assurance with the initial purchase.
    – SqlACID
    Jun 29, 2009 at 0:01
4

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.

4

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.

3

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.

2

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

2

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.

2

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.

2

In our organization, all new development is supposed to be on SQL 2005/2008, but we have so many legacy 2000 apps, its just not worth the time/energy/money to convert them all.

In addition to the compatability issues (DTS to SSIS is the biggest one for us), we also implemented a set of guidelines/security restrictions on SQL 2005 that we hadn't enforced for SQL 2000. (no dbo users, schema permissions only, etc.)

1

In our organization, there are several reasons. A few are pretty specific to our case, and others a bit more generic.

1) Incompatibilities. We have had a few cases where software that was written in-house for SQL2005 has issues when installing on SQL2000. The case I saw most recently ended up being due to parameters being explicitly declared that don't exist in 2000, and a difference in the name of the system index table. (sys.indexes vs sysindexes)

2) Training. Our developers certainly know 2005, and would prefer to be developing on it, or 2008 already. However, the job of keeping everything operational falls to the NOC, not the developers. Nobody in our NOC has had any formal SQL training, and the differences between the two, just from an administrative tool standpoint, are enough to make this a consideration.

3) Cost of upgrading existing products. For us, this is a big one. I'm not talking the cost of the license from Microsoft here. In our business, an upgrade on any of our existing products (even if we didn't retroactively upgrade everything already in the field) would require an expensive and lengthy re-certification process through several different regulatory and certifying test labs. We are building new products on SQL2005, but not upgrading older ones for this reason.

The certification process also means we would end up with a mixture on new builds, where certain jurisdictions would be getting SQL2000 and other would be getting SQL2005, based on if the approvals had been received yet or not. We prefer, for reason #2, to keep our production environment as consistent as possible.

4) General differences. This is really an extension of #1. There are lots of little things that changed, some of which cause us headaches. Example, SQL2005 on Server2003 will enforce Windows password policies on sql accounts. Not a bad thing by itself, but it breaks almost all of our (and much third-party) software because of the way we interact with the database.

In short, inertia.

1

A good blog post which already asked the same question.

1

I know there are people who have to use .NET 1.1 because they have to support Windows 2000 systems.

Following that logic, then a practical reason to run MSSQL 2000 is because you can support Windows NT 4 systems!

Believe it or not, but SQL Server 2005 actually runs on Windows Server 2000.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .