| Author |
Topic |
|
Van
Constraint Violating Yak Guru
462 Posts |
 Posted - 2009-11-20 : 08:32:27
|
| I had never heard of bidirectional replication but have been asked to look into it. Here is our situation. Prod and DR. If Prod goes down they want it to automatically (with no human intervention) to pick up at DR. When Prod comes back up they want it to automatically (with no human intervention) pick back up at Prod. The way I've always done a DR situation is when prod goes down I have log shipping going to DR and I then take the dbs at DR out of readonly mode. When Prod comes back up, I backup the DR dbs and restore them to Prod and continue. With bidirctional replication evidently Prod and DR can constantly keep each other up to date. Can anyone help me here? Are there other better options that are out there for this type of situation? 2-way database mirroring maybe? SQL 2005 is what we use. Again they want it to be totally automated. Prod goes down, DR is up to date already and takes over. Prod comes back up, it gets anything that happened at DR applied automatically and takes back over. Any help is appreciated.Van |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-20 : 09:54:57
|
| look at peer to peer replication. |
 |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-11-20 : 10:15:06
|
| Thanks for the info. Will certainly look into it. Anyone know of any other options out there for the type of situation I described? I'm just trying to gather all I can about this and choose what I think is the best route to go and it's kind of a new type of scenario for me. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-20 : 10:34:33
|
| We use peer to peer as a scaleout solution, not a DR/HA soultion. Log shipping is likely more appropriate for what you need to accomplish. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-11-20 : 10:47:01
|
| I totally agree with the log shipping, but they want it to be totally automated and instant in that Prod goes down, DR picks right up, Prod comes back up, any changes that had been made at DR get pushed to Prod and Prod immediately takes over...all automated with no human intervention. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-20 : 11:02:11
|
| how many production servers need to be synched with DR site?with replication you'll have to license servers in dr site. don't have to with log shipping i believe.peer to peer is pretty easy to set up and maintain. be careful with identity fields. you'll need to seed them differently so they don't overlap. for instance if 2 peers, seed on odd the other even |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-11-20 : 11:39:18
|
| Right now I'm just looking at doing it with one of our systems so just one Prod server with one db. It's purchased software so I don't think I'd have much say so in the identity fields but since it's a Prod/DR setup only one should be publishing at a given time. I don't have a great deal of experience with replication. If Prod and DR are up and Prod is publishing and DR is accepting all should be good. If Prod goes down and DR takes over and then becomes the publisher, what happens since Prod is down and can't accept the published data? Does it queue up so that when Prod comes back up (maybe hours later) it gets all the published data? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-20 : 12:48:33
|
| yes, it'll queue up on the distributor. you should use a dedicated machine for the distributor by the way.if u don't manage the identity fields you'll get errors after failover if any inserts wind up getting queued. also, every published table must have a primary key.by the way, you can script the recovery of the target db in log shipping and automate the failover/recovery.since you're only dealing with a single db, should have a close look at mirroring. i wouldn't suggest that if the application(s) had multiple dependant databases though |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-11-20 : 14:00:34
|
| Great stuff so far. I appreciate the help. The only thing about log shipping automation that may be an issue is that when Prod does come back up, the DR dbs would need to be backed up and restored to Prod. That can be automated, but wouldn't be instant like they are wanting with bidirectional replication. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-11-30 : 15:19:30
|
| Quick question. I've set up log shipping before and understand how it works and all. However it was a manual process for me when Prod went down. I had a script that I ran to take the databases out of read-only mode at DR and all. Can someone tell me the best or standard method of automating this? When Prod goes down it needs to automatically have DR take over. And when Prod comes back up, it needs to automatically have Prod take over (I assume I'd need to have it backup and restore the DR dbs to Prod...automatically of course). Any suggestions or advice is much appreciated. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2009-11-30 : 15:35:56
|
| Also, would database mirroring be a better solution. I haven't used it but here it's like real-time log shipping. |
|
|
|
|
Bidirectional replication