November 5, 2009 at 1:49 pm
I've got two servers that use transactional replication to send data to a reporting server. I've been thinking if either publisher crashed that I could point my applications to the subscriber until the publisher was rebuilt or brought back online. It's been brought to my attention that its not quite that simple. Does anybody know of any documentation on how you can turn a subscriber into a writable copy?
November 6, 2009 at 2:47 am
A few random thoughts:-
Transactional replication doesn't render your reporting database unwritable, so you are probably able to make changes there already.
Unless you are using immediate upating, there's going to be a time lag between the data being entered at your publishing servers, and arriving at your subscriber. How are you going to handle that missing data.
Once your broken publisher is back up, how are you going to get the data entered at the subscriber back to the publisher. You say you have 2 publishers, so how would you know which of the 2 to send the data back to?
If you want to do disaster recovery, there are better solutions e.g. database mirroring.
There's a lot more to disaster recovery than I've mentioned above, but that should be a few things to get you thinking.
November 6, 2009 at 5:36 am
I agree with Ian's suggestion to consider Database Mirroring as ur disaster recovery solution. You may also consider Log shipping (only coz of its ease of implementation and maintainance)..
November 6, 2009 at 10:09 am
Thank you both for your replies I am fairly new to the DBA role and don't know all of the ins and outs of what is and isn't possible with replication. The previous DBA had figured that in a pinch we could use a replica as the master until the master was back online.
My boss had told me that our subscribers don't hold any identities so an insert to the subscriber would not insert data into identity columns (I am working on building an environment to test this). My subscriber updates immediately from the publisher so the replication is real time so I don't fear much if any data loss.
Once the broken publisher is back up I figured I could restore a backup from the subscriber during down time and rebuild replication. My two publishers house different databases and the report server has copies of everything so it's easy to figure out what server gets what data.
I agree that long term mirroring is the way to go and I will work in that direction but with a lack of hardware resources I don't have much to create a test environment with.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply