Transactional replication and updating subscribers

Question

Transactional replication and updating subscribers

ChrisMoix

SSCommitted

Points: 1568
More actions
September 7, 2004 at 9:18 am

#163131

Hello,
I am having a problem with transactional replication and updating subscribers (Windows 2003 Server with SQL Server 2000). I have a publisher (Server A) and a distributor/subscriber (Server B) set up with transactional replication with updating subscribers. The front end application is smart enough to switch over to Server B if Server A is down. The only problem is, if you try to update Server B while Server A is down, the transaction fails (due to MSDTC on Server A being unavailable).
Is there a way to allow updates to Server B when Server A is unavailable? I am thinking that I am going to have to turn off updating subscribers and just go with straight transactional replication from Server A to Server B. If Server A failed, then we would run off server B until A was fixed, restore Server B's copy of the database over Server A's, then restart the replication. I am hopiong that there is a better way to than the above. Does anyone have any experience/advice?
Chris

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 1

Try reading up on the new option for queued updates when using transaciton replication with auto updating subscribers. I've not used this option yet but have experience with the auto updating subscribers - mainly on SQL 7 though where the queued option is not available. They must be able to make the link back to the publisher but I believe the queued updating option allows this update back to the subscriber to be done when the it is available again.

ChrisMoix SSCommitted Points: 1568 More actions · Answer 2

I looked into queued updates, but it looks like it requires MSMQ. I don't want to further complicate matters with another Server/Service.

Thanks.

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 3

I also chose to steer away from when upgrading from SQL7 to SQL2000 due to the complications of keys. I have also used a merge replication instead of transactional replication for a scenario similiar to what you are mentioning. The only problem was that you might wind up failing to the second database and it not being exactly in synch. In my case, the applciation made allowances for this possiblity. Not always possible though.