Search code examples
sql-serversql-server-2008-r2merge-replication

Resychronize merge replication

I have an issue where there was a merge replication between 2 instances for around 10 articles that has now been dropped. I want to recreate the merge replication - I am looking for inputs on the steps/ different options to set it up again and synchronize.

The subscriber is remote and not a part of the LAN. Please note that I have the scripts to create the replication.

This is what I am thinking of doing:

  • backup current publisher and restore it to the subscriber instance in a different name
  • restore a copy of the subscriber in a different name
  • run compare using a tool that generates scripts, like those from red gate
  • apply the script generated on the restored subscriber db.

After this, what do you think is the best way to set the replication back to running?

Any advise appreciated. thankyou

Solution

  • There is two thing to check before you backup and restore.

    1. Make sure that you have all data from publisher and subscriber in one database. It could be publisher. If you hadve ETLs which loading you publisher and subscribers database from diffeent source this point is pretty important.

    2. run http://technet.microsoft.com/en-us/library/ms188734%28v=sql.105%29.aspx on both publisher and subscriber

    3. Script out all your indexes if you need reduce backup file. You can create them l8r once you will be in sync.

    4. backup db on publisher and restore it pn subscriber

    Next

    1. create publication
    2. create snapshot
    3. add login to the access list of your publication
    4. add articles for publication
    5. create script drop/create indexes. Create scrip to drop/create indexes on tables classified as “big data” to prevent snapshotting indexes. Do this for constraints, too. They slow up your action..
    6. Just drop them all. From step 9
    7. Snapshot your stuff.

    Now subscriber

    1. add pull subscription. You have two steps. Script on publisher and script on subscriber.
    2. stop agents on subscriber and change GENERATION_LEVELING_THRESHOLD if you need or change subscriber agent profile.
    3. You can now start pull agents.
    4. Remember about replication index maintenance

    Hope that help