SQL Server 2000 High Availability

Question

How do you implement high availability on SQL Server 2000? Are there any 3rd party tools out there or any other ideas?

Answer 1

If you're looking for an introduction to what's involved, there are a number of articles out there that will cover the basics:

• http://technet.microsoft.com/en-us/library/cc966499.aspx
• http://www.amazon.com/Microsoft®-Server-2000-High-Availability/dp/0735619204 (Great book that covers the basics)

Also, though I've never used it, there's a product called "Double-Take" which appears to provide the same functionality as Database Mirroring in SQL 2005+ (providing a hot, instant-failover standby server), and it might be worth a look. I can't tell if they have a current SQL Server product, but I know they've had one in the past:

• http://www.databasejournal.com/features/mssql/article.php/3338081/High-Availability-with-SQL-2000-and-Double-Take.htm

As other have suggested, if you post some details here about what you're trying to do (load balance, disaster-recovery, etc) and why you're stuck using SQL 2000, we can provide some better solutions.

Answer 2

If you're on a budget, and your only concern is disaster recovery, and not HIGH availability (they are NOT the same thing).

Disaster recovery implies that there is a short period of time that you have to restore from backup or cold standby.

High availability means that there is ZERO tolerance for downtime.

If you're willing to live with a few hours of downtime, than you can do what I've done in numerous moderate SLA environments: backup the server nightly to a NAS storage location, and then periodically backup transaction logs or differential/incremental backups depending on your tolerance for data loss.

If you have requirements to never lose a transaction, you don't have a choice - you have to spend money. Either on upgrades to clustered hardware, or on a backup server to which you can do log shipping or similar.

Answer 3

First, upgrade to SQL 2008, since Server 2000 is now out of mainstream support. Then, select one of the myriad new HA options that come with 2008.

Seriously, You're gonna have to be more specific about what you mean by "high availability." Hot standby? warm standby? metrics for recovery? availability targets? etc etc. It's a complex topic.

Answer 4

You can use clustering, but that requires Enterprise Edition. There are several other alternatives, although they are fewer for SQL Server 2000 than 2008.

High availability costs. The most cost effective thing you can do to begin with is to upgrade to 2008.

Answer 5

In addition to clustering you can also configure replication with SQL Server 2000, but then again I have to harken back to the other posters who have asked why not upgrade to SQL 2008 or at the least SQL 2005. Mirroring becomes an option with 2005 in addition to clustering and replication.

Clustering is the only form of these HA solutions in which there is a single copy of the database. It only protects you from server failure, you're still exposed to data and site failure so I am always skeptical about including clustering in HA, but it is more HA than a stand-alone instance, that is for sure.

All options do require additional hardware, so keep that in mind. SQL licensing may be required for additional targets as well depending on whether they are in standby mode.

We can give you better answers if you give us more information.

Answer 6

If you're looking at 2000 and you're looking at a geographically distributed solution and don't have the money to upgrade, you're probably looking at either

• replication
• home grown log shipping solution

But that's an off-the-cuff answer given the fact that you've not given much detail about your exact requirements. As the other posters have indicated, that's really essential to answering your question properly because certain solutions work better (or don't work at all) depending on the parameters.