none
Replication - Publisher

    Question

  • Hello:

    I have step into specific situation and need your help to see how that can be achieved.

    I have Publisher Server A which has hardware issue. We got new server B which will be used for Publisher A.

    I want to know what are steps to move publisher server A to new server B.

    i am planning following and let me know if that will work or there is better way to achieve.

    1) Once replication is in SYNC, disable Log Reader & Distributor Agent.

    2) Take Backups from Server A and then Restore on Server B

    From here I want to understand how i can set replication so my new server B becomes Publisher without creating snapshot files or running snapshot.

    Any help is greatly appreciated.

    Thanking in advance

    Thanks

    Wednesday, November 08, 2017 4:47 PM

All replies

  • Log ship everything to the new server. This means the publication databases, msdb, the distribution databases etc.  When you are ready to cutover, restore the with keep replication option.

    Restore the master database from the old server on the new server. Rename the new server the old server name and reboot.

    You might also want to ship with the option sync with backup option (a parameter of sp_replicationdboption).

    This way the transaction log will not read commands out of the tlog until the log is backed up.

    Another option is to log ship to the new server and then create a new publication and subscription but use the replication support only option of sp_addsubscription @sync_type parameter.

    Wednesday, November 08, 2017 6:27 PM
    Moderator
  • Thank you Hilary for your reply, do we have document for this kind of setup should i go with your recommendation. I do have your book but don't see any reference about this question unless i miss something.

    Thank you again for your help.

    Thanks

    Wednesday, November 08, 2017 6:32 PM
  • I believe I did document the sync with backup option in the book. The replication support only sync_type option was called none in SQL 2000.
    • Marked as answer by aaditya2000 Thursday, November 09, 2017 1:20 AM
    • Unmarked as answer by aaditya2000 Saturday, November 11, 2017 2:56 AM
    Wednesday, November 08, 2017 6:43 PM
    Moderator
  • Hilary:

    I have backup Publisher database from Server A & restore database on Server B with keeping Replication Option. After restore i am seeing publication on Replication Folder along with Subscriber but after that i am completely lost how to SYNC Replication from Server B to Distributor & Subscriber.

    Any help is greatly appreciated.

    Thanking you in advance.

    Thanks

    Saturday, November 11, 2017 2:57 AM
  • Hi aaditya2000,

    Did you only backup and restore the publisher database? Then you can create a new publisher on the Server B and add the previous distributor to it and initialize the subscriber with backup, this is the easiest way. 

    The method with keeping Replication Option requires to backup and restore databases, msdb, the distribution databases to target server.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 16, 2017 9:33 AM
  • You need to log ship the publication database and the distributor database with the sync with backup option. You also need to ship the msdb database.

    When you are ready to cut over you need to backup and copy the master databases and restore it on the new server. Then rename the new server the name of the original publisher. Then reboot and all publication, subscriptions and jobs should be in place. It sounds like you did not ship the msdb database and possibly not the distribution database as well.

    Thursday, November 16, 2017 6:41 PM
    Moderator