Post reply

Migrating replication to a newer server

  • March 25, 2019 at 11:33 pm

    #2031632

    Hello Team,
    I am in the process of migrating transactional replication between 2 databases on the same server to a newer version of SQL Server(still single SQL Server)
    1.  We have a lot of articles/publications on the old server, so doing it manually on a new server will take some time and subject to human errors. So I was thinking about scripting replication on the old server and executing it on a new server but I read some papers warning   against it.
    Did someone on this forum use scripting for copying transactional replication and can share his steps?
    2. Assuming the first step above is completed successfully and initial transactional replication setup between 2 databases on the same server is done, what should be the steps to resync\refresh transactional replication migration from old server to a new server when I am ready for final cutoff?
    Thank you

  • March 26, 2019 at 8:16 pm

    #2031849

    You have two options here:

    1. Script out all of your replication configurations and run them against the upgraded databases as part of your upgrade\migration, or
    2. Restore your migrated databases with KEEP_REPLICATION to maintain the replication configuration.

    Option 1 allows the simplest transition as you're effectively removing replication from your databases when you restore them to the new server. The high-level process would be:

    1. Configure distribution on your new server
    2. Script out Publisher and Subscriber settings on your existing server
    3. Update scripts to use the new target distributor
    4. Backup and Restore databases to the new server (except distribution)
    5. Run your scripts to deploy replication
    6. Reinitialise all subscribers

    This process should be run through several times to validate your replication is recreated correctly and functions post-restore.

    Option 2 requires a little more effort but doesn't require you to script anything out. The high-level process would be:

    1. Configure distribution on your new server
    2. Configure additional subscriber on the new server for the existing publisher
    3. Backup and Restore publisher database to the new server (use KEEP_REPLICATION option)
    4. Change the distributor settings of your new publisher database to use the new distributor.
    5. Reinitialise all subscribers
    6. Drop the old server as a subscriber

    Option 2 also has more impact on your existing environment for testing. If you're concerned about the scripting element of Option 1, look at Option 2 but Option 1 is the easiest to test repeatedly without impacting your existing databases.

  • March 27, 2019 at 11:19 pm

    #2034047

    Thank you HandyD

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

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