Post reply

Merge Replication

  • August 23, 2004 at 5:34 am

    #86497

    Hi All

    I have been running a countrywide implementation using merge replication to sysnc the databases at our branch offices with individual DB's at our HO. This has been working for us like a dream....except.

    Initially we our implementation was configured by default to re-run the snapshots for the publications once a week. We found however that this caused performance issues and since we operate 24/7 we decided to turn this off and not re-run the snapshots. Can anyone give me some best practises and guide lines for snapshot maintanence etc...

    Further more...We have been busy upgrading the servers at our branch with all new hardware and so forth. To achieve this we disabled the mergeagent at HO, backedup both DB's (HO and Branch), removed the Branch Server and put the new server in place. We then created an new DB on the NEW branch server (DB and Servername match the old names) and restored the DB from the backup. Now we simply enable the merge agent at HO again and most of the time all runns 100% and replication continuews.

    BUT

    Sometimes we get the message "The subscription to the publication"????" is invalid". We then drop the subscription and Push a new subsription. This allways results in a message "The Snapshot is invalid" and needs to be rerun". We rerunn the snapshot and then everything works fine.

    My Question is this...Is this the best/most elegant way of doing this or are there other best practises and guidelines to follow.

    I appreciate any help, pointers or other information you can give me on maintaining merger replication.

    Many Thanks

    Jens Uffhaus

  • August 23, 2004 at 8:30 am

    #520183

    Hmmm, I hope I get this right. The reason why you got the message the subscription is invalid I suppose has something to do that you replced the server. Even though the names are the same the sid for the remote server is probably different.

    The snapshot is invalid message came simply because your snapshot was too old. SQL Server runs a snapshot every week for two reasons. First in case you add a new subscriber only the changes since thalst snapshot need to be applied and not all the changes since the original snapshot. Second the MsMerge... systemtables only store the changed records for a certain time (you can configure this somewhere but I forgot the exact spot.) So when you tried to set up a new subscription with the old snapshot SQL server just couldn't apply the changes anymore.

    So I would suggest to run the snapshot again every week. Maybe you can schedule it in a way that the performance impact won't be so bad. Or maybe you can analyze you performance problem further in detail and then try to solve it.

    M

    [font="Verdana"]Markus Bohse[/font]

  • August 24, 2004 at 4:24 am

    #520282

    Hi Markus

    Thanks for the input, I agree on the snapshot point, We dont have general performance issues but there are about 35 publications and the snapshots really tax the server. I will just have to work out a clever schedule for each snapshot to minimize the impact.

    Regading the invalid subscription, I also keep thinking the same thing, but some how cant sell it to myself as it only happens every 2nd or 3rd time and works the other times. Any other possible ideas why this happens ?

    I also have another problem. Under Replication monitor, Publications. I click on a publication and then it lists the different agents (snapshot, merge). I have one publication where it lists a second merge agent where the subscriber no longer exists, but the subscription is also not listed in the publicatins "subscriptions" tab . All attempt to delete it result in SQL telling me it doesnt exist, Its like an orfaned subscription "link". Any Idea how to get rid of it?

    Thanks

  • August 24, 2004 at 4:41 am

    #520284

    Hi Jens,

     

    about the "orphaned" agent you can try running the "distribution clean up" agent under Miscelleanous agents. This might solve the issue but personally I think the you have to manually delete the entry in the distribution database. In the past I had several issues with removing subscriptions. It happens quite often that not all the objects and records belonging to a subscription are removed. You than can delete the entries manually after setting the "allow updates" option on. But of course before you do this make sure you know what you're doing. This is definetly not supported by Microsoft and can mess up your replication if you make any mistakes.

    M

     

    [font="Verdana"]Markus Bohse[/font]

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

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