Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
Please add some details about what you're trying to accomplish and why you're stuck using SQL 2000. Is it a vendor server? Are you looking for fail-over, disaster recovery, load-balancing, or something else? –  SqlRyan Jun 29 '09 at 21:40
    
The most important thing for a high availability is for disaster recovery. As far as the metric are concerned, a snap shot of real time data in a disaster situation. Our clients dont have $$ for the upgrade –  Greens Jun 29 '09 at 21:46
    
I would expect that any third-party tools that would make this possible are also out of their price range. Do they have the money to pick up another server so they can do a Windows cluster? –  SqlRyan Jun 29 '09 at 22:51
    
Which edition dude? –  SuperCoolMoss Jul 2 '09 at 21:55

6 Answers 6

If you're looking for an introduction to what's involved, there are a number of articles out there that will cover 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:

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.

share|improve this answer
    
The most important thing for a high availability is for disaster recovery. As far as the metric are concerned, a snap shot of real time data in a disaster situation. Our clients dont have $$ for the upgrade. –  Greens Jun 29 '09 at 21:45
    
If they're not willing to spring for another SQL license, and you're currently using SQL 2000 Enterprise, then clustering might be an option for you, and it provides instant failover, though it will cost you a second SQL Server to set it up. I'd recommend the book I provided a link for above, as it covers all your options with SQL 2000. –  SqlRyan Jun 29 '09 at 22:04

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.

share|improve this answer

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.

share|improve this answer
    
are there any 3rd party tools that would do that... –  Greens Jun 29 '09 at 21:30
    
Since your client does not have that much cash. I would recommend implementing HA through good routines: take backup often, having routines in place to get up and running fast in the event of a hardware failure. –  Shiraz Bhaiji Jun 29 '09 at 21:56

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.