Post reply

SQL 2008 Replication - dummy update of 100+ subscribers within a time frame

  • July 25, 2011 at 5:47 am

    #254336

    Morning all!

    I have successfully created a process to only replicate merge changes if there is pending data changes on either the subscriber or publisher via an SP call - rather than running the merge agent continuously! Works great and reduces the amount of merge agents running (3 publications x 100+ subscribers is..ouch!)

    However.. I now need to do a dummy update for each subscriber to ensure that the replication doesn't expire! so for those 100+ subscribers i want each one to replicate once within every hour to ensure we haven't got any sites that genuinely have comms issues..

    Scratching my head on how to do this without setting a job on the subscriber to do the dummy update - I'd rather control that from the publisher.

    Thoughts welcomed 🙂

  • July 25, 2011 at 7:14 am

    #1357748

    Distribute a single "dummy" table to each subscriber that has been included in the articles for the publisher "Replication_Dummy" or whatever you want and just send a record every hour into the publisher to be picked up by your sp (modified of course) that will kick off the agent and send it across and everyone's happy...

    Within the job you could include a delete and an insert to keep that table small.

    Would that work?

  • July 25, 2011 at 7:59 am

    #1357787

    hiya!

    Thanks, interesting point - avoids the messiness of an update to each published article so that's a huge plus. The main issue (and reason for making repl 'on-demand') was to reduce the number of agents running and filtering the data - so this approach would still mean a block of subscribers connecting all at once to check for changes..

    so I think a new table filtered for all the subscribers might be the way to go, that way i can control the sites that need to replicate, however.. that still leaves a sticky issue of making sure all sites have replicated once within an hour and not all en-mass

  • July 25, 2011 at 5:13 pm

    #1358192

    Hi

    I don't have much experience specific to merg replication.

    But why don't you increase the retention period.

    This will prevent anything from expiring.

    Cheers

    Jannie

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

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