none
Question on Rebuilding existing DB Replication to New Servers RRS feed

  • Question

  • Fellow SQLers.

    I am looking for some suggestions for this scenario.

    We have Server A on SQL 2008 R2. It has the db and distributor. Server B is the subscriber - also on SQL 2008 R2.

    We need to migrate the main database on SQL 2008 R2 to SQL 2016. Pretty much start over because we are having to change the db collation as well. yes, Subscriber will be on SQL 2016 as well. Yes, a mixed bag here.

    Our focus was the best way to deal with setting up replication again.  I am not planning on migrating the subscriber db because it too has to have the new collation. We believe it is likely best to just start fresh.

    My first thought was when we copy the table schemas to the new box, before I transfer the data, I would think I would set/make sure the tables/scripts are NOT part of replication so I can rebuild the replication model from scratch (or maybe use a script from Server A's replication to help kick start this after the transfer is done)

    Is this approach reasonable?

    MG





    • Edited by mg101 Thursday, November 7, 2019 6:37 PM wording
    Thursday, November 7, 2019 6:35 PM

Answers

  • Hi mg101,

     

    Agreed with your thought, and you can change the database collation after your migrating the database and recreate the replication.

     

    Best regards,

    Dedmon Dai


    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


    Tuesday, November 26, 2019 8:48 AM

All replies

  • Hi mg101,

     

    Would you please refer tohttps://am2.co/2014/07/moving-replication-to-a-new-server-without-re-snapshotting/?

     

    Best regards,

    Dedmon Dai


    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

    Friday, November 8, 2019 9:43 AM
  • Thanks Dai.

    Not what I am looking for. I am talking about "starting" fresh and seeing if a can borrow the existing data from the original pub and sub.

    MG

    Friday, November 8, 2019 12:02 PM
  • The no sync option (replication support only) might be your best option here.

    If your subscriber already has the data and the objects it needs there, you can just configure your subscription using the no-sync option and all new changes should flow to the new subscriber.

    Tuesday, November 12, 2019 4:58 PM
    Moderator
  • Thanks for info Hillary.

    It is a bit more complex than that.  To make matters worse, the db collation has to change. So we are not only migrating a db from 2008 r2 to 2016 but also converting dbs/data (export) to different collation on new server. that would mean the subscriber would need to be converted as well. It is more direct to just rebuild the subscriber from snapshot after we get the publisher in shape. At least that is our opinion. thoughts?

    MG.


    • Edited by mg101 Tuesday, November 12, 2019 5:59 PM wording
    Tuesday, November 12, 2019 5:57 PM
  • Hi mg101,

     

    Agreed with your thought, and you can change the database collation after your migrating the database and recreate the replication.

     

    Best regards,

    Dedmon Dai


    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


    Tuesday, November 26, 2019 8:48 AM
  • thx Dedmon

    MG

    Tuesday, November 26, 2019 3:43 PM