Fault Tolerant Strategy?
-
I am relatively new to the DBA world and have been learning as I go with books and this site. I have set up a working test system with SQL 2000 standard and Windows 2000 server standard for our redesigned web site and things are running smoothly. My new question is: We want to set up an offsite server as a "hot box" to use in case of system failure for the web site. Is replication the answer for this type of fault tolerance. If so, what type of replication? If somebody could point me in the right direction I'm sure I could pick it up. Thanks in advance.
-Pat
-
Usual options are replication and log shipping. For replication you can choose any of the three types, generally its either transactional or merge. I like transactional for its simplicity.
Andy
-
I prefer log shipping or some variant I've developed. Easier to roll over/back to me, but I'm not a replication fan.
Steve Jones
-
Thanks for responding so quickly guys. As I understand it, log shipping would make the off-site DB read-only, whereas transactional replication would enable the DB to be written to and updated in case of system failure. If this is correct, than I think I would go with transactional repl. What do you guys think?
-
Its read only until you finish recovery.
Andy
-
Andy is correct. The main issue for me has been that I may rollover to another system and then want that to be the primary until I decide to move back. If I use replication, then I may have to set that back up on a new server or correct it. Hate doing that.
Either method can also give you a reporting server as your secondary.
Steve Jones
-
I doubt you could get a query to go much faster than replication, especially once you build in all the required fault tolerance. Log shipping moves changes in bulk, the frequency of the shipping and the number of transactions plus hardware determine how long it takes to ship/apply. Replication moves the transactions typically within a few seconds so you have less latency.
Andy
-
quick question. is log shipping available only in enterprise edition, and not standard?
-
Log shipping is only supported in Enterprise, but the scripts to do it where included in the SQL7 section of the BORK (BackOffice Resource Kit). It's fairly mechanical - do the backup, copy the file, restore, repeat.
Andy
-
I am surprised there is no mention in this topic of transactional replication with immediate updating subscribers and queued updating in case of fail-over (optionally using MSMQ).
Its more complicated to explain than it is to set up, and meets all of Steve’s criteria.
But then I'm a replication fan
Tim
-
Guess I forget to mention all the fine points - at least I did mention replication early in the thread!
Queued updates is definitely interesting. No one ever seems to like replication, though its about as mechanical as log shipping.
Andy
-
quote:
Guess I forget to mention all the fine points - at least I did mention replication early in the thread!Queued updates is definitely interesting. No one ever seems to like replication, though its about as mechanical as log shipping.
Andy
http://www.sqlservercentral.com/columnists/awarren/
-
Doh! Lost my original message, so here we go again...
Just so people know this stuff is being used successfully...
We replicate a 14GB database to 5 queued updating subscriber sites around the world, using log shipping for the publisher. Soon to add second log shipping stand by server since if we loose both publisher/distributor and stand by we would have to rebuild from a subscriber which means flying tapes round the world.
Went to this setup in Nov 2000 and we had a bit of a mare with the bugs which now are thankfully mostly sorted. Funniest thing was MS saying at one stage you need to initialise the subscribers, with 14GB of data I dont think so.
Runs very smoothly now on the whole. Log shipping can be quite sensitive towards server time alterations if you're having a time service sync your servers.
We went for queued updating subscribers since immediate we coudlnt tolerate because of the 2PC round trip to the publisher was too much for some of our more remote offices.
Anyway thought this might be of interest.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply