none
Migrating SQL Server 2008 R2 Publisher database to a new server

    Question

  • Hi, I have a production server that is only migrating the publisher server to a 2012 windows server for TLS 1.2 compliance. The SQL Server is staying the same: 2008 R2. The distribution server and the subscription servers are not being moved. I have one subscription server with 48 separate subscriptions. 

    I need to find out what is the best way to deal with the replication. The new publication server will not have the same name. So what changes do I need to make on the distribution/subscription servers? Can I generate the script out on the publication server, run it on the new migrated server? I have been discouraged to do the system db restores on the new servers. So I am restoring the publisher databases on the new migrated server. Do I need to do the subscription database from scratch or leave it as is, and once I run the scripts on the publisher server and change some settings on the distribution and subscription server, it will catch on? I'm not sure. Please help if you can. Thanks.

    Wednesday, May 23, 2018 6:08 PM

All replies

  • Hi ImranH2018,

    Please have a check:

    Keep Publication while migrating database in SQL Server 2008 R2 [closed]

    Scripting Replication

    In short, you need script out all setup from original server, then reproduce replication by script.

    Regards,

    Pirlo Zhang  


    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, May 24, 2018 8:06 AM
  • It sounds like you have a published database on a SQL 2008 r2 server. You wish to move this published database to a SQL Server server running on a Windows 2012 server. As you are changing the server name you can't use the sync with backup option.

    What you need to do is to recreate the subscriptions as no-sync subscriptions from the new server. You can do this with no downtime or data loss.

    If this is not an option, you will need to use the initialize from backup option to create the new subscriptions on the new server. This will minimize the downtime.

    Tuesday, May 29, 2018 2:41 PM
    Moderator