Merge Replication + Log Shiiping at the Subscriber
-
Hey All
Ok ... he's the scenario i have (need!) :
Primary Server and Publisher at site one (call it "Factory1"). At site 2, we have two other SQL Servers ("Track1" and "Track2"). The link between Site1 and Site2 could be quite flakey. Applications at Site 2 talk to "Track1" which is a subscriber of a Merge Publication with Factory1. If "Track1" fails, then Track2 has to take over the subscription and all the applications then talk to that.
The worst case scenario is 1) We loose the WAN link and therefore contact with the distributor and THEN 2) Track1 continues to receive updates from applications, but then fails at a later point in time! As such, Track 2 cannot initially be a subscriber in this scenerio, as it will become out of sync with Track1 if the WAN link goes down.
I've read the BOL info about backing up the database (T-Logs - very regularly!) on Track1 and then restoring this on Track2 which can then subscribe to the publication (they make it sound easy!?) ... What i would like to know, however, is whether Track2 can be kept in sync with Track1 using Log-Shipping ... or is there some reason i have to do the regular T-Log backups??
All hints welcome ... and do look forward to my next posting, where Track1 (or Track2 in the event of failure!) also have to RE-Publish!?
God I'm having fun!
Cheers
Vinny
-
Make Track1 a re-publisher and Track2 its' subscriber.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Track2 would need to have a Merge Relationship also with Track1 (then also alternate sync partners) ... i didn't think this was allowed!??
ie Factory1 ---Merge Repl ---> Track1 ---Merge Repl ---> Track2
am sure EM blocked me doing this and forced a Snapshot replication at the re-publisher!?
-
I may not be understanding the scenario correctly. It looked like Track2 is basically a standby for Track1. I beleive you can do Transactional Repl to it and that should keep it in sync with Track1.
Log shipping would be another option and the one I prefer for a standby.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
I think it's the failure scenario ur not understanding and that is ALL has to be merge replicating. As i mentionioned, worse case scenrio is the WAN link to the factory goes down ... Track1 carries on collecting data, but then fails. At this point, Track2 must be able to continue to collect data from where Track1 left off, and then when the WAN link re-opens it must also then take over the merge replication process back to Factory1 (where changes could also have been occuring!). This wouldn't be possible if Track2 was simply Snapshot or Transaction replicating with Track1 ... if it was Merge Replicating, then yes ... but as i mentioned, EM prevented me doing that so i assume it isn't allowed!
-
OK - I've got it now but unfortunately, I haven't got an answer for it.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Sounds like you might need to write a lot of custom scripts to accomplish this... Use uniqueidentifier columns liberally to at least make life easier (not needing to deal with duplicates in identity columns). You could have each server have linked server entries to all other servers. Then either run scripts on a frequent basis that does a
if not exists(select * from linkedserverA.db.dbo.table where ...) insert into linkedserverA.db.dbo.table....
But that would probably be very inefficient, especially over a WAN link - it may be helpful if they all get out of sync though.
Your best bet could be to emulate what MS' merge replication does - add triggers to tables and store that changes that need to be pushed out in another table. Periodically clean this table out by performing the deletes, inserts and updates that the table dictates. You would have X entries for each change in your log table - X being the number of servers - 1. For a nice algorithm, I was reading Mark Minassi's Windows Server 2003 book about how MS & Active Directory does multi-master replication. It's a reasonably simple algorithm and should fit what you want to do - either get a copy of the book (useful to have anyway IMHO) or look up via Google how AD multi master replication works...
-
u just HAD to confirm it for me, didnt u!?
Ah well ... if there was an easy(er) answer i'd have figured it out by now! Think it's back to the business, see how much data they're willing to loose ... and have a really big shovel at the ready just in case the word "none" comes out of their mouths!?
If they're happy with 5 mins, i can just go down the "backing up the subscriber database" route as per BOL ... but gonna hang around and see if anyone's done anything "clever" with a setup like this!
-
I don't envy your next couple of weeks
If it's "none", then ask for lots of cash for servers.... And then move to another country with it
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply