0

We have large databases that are being kept in sync using Transactional Replication. They are all running in VMs and we are going to move to a new provider and reorganize the network structure and application distribution.

Is it possible to migrate the Transactional Replication: Publisher, Subscriber, Distribution Database without having to take a new Snapshot? Currently we have in Server_1 the Publisher and in Server_2 the Subscriber and Distribution Database. We are going to move the Publisher to Server_3 and the Subscriber and Distribution Database to Server_4

I don't have much experience with SQL Replication so I have been reading a lot. I found this answer here. Reading it, I understand that if I follow these steps, moving the Replication to the new 2 servers should be possible:

  1. During downtime we wait until all objects are in sync
  2. Then Script out the Replication (does this include, Publisher, Subscriber and Distribution Database?),
  3. Migrate the Publisher and Suscriber Databases to their new Locations
  4. Create new Transactional Replication from Scratch (using the scripts generated from the existing Replication) with replication support only
  5. Run snapshot jobs to ensure the creation of any System Objects missing.

However I don't know If I have all the data/steps/understanding required and if I am missing anything important.

Also, would it be possible not to wait for all the objects to be in sync? I would like to test the process but I don't know if I will be able to shutdown the databases for that long just to test. So could We migrate both databases without having them perfectly in sync and regenerate the Replication without Creating a Snapshot? Maybe without the "replication support only" option and running the snapshot Jobs. If we run the snapshot jobs in that situation will they create a whole snapshot since the DBs won't be in Sync or will it only look for the differences?

I am sorry if my questions seem rather obvious, I haven't worked with this Technologies before and I want to make sure to do everything right.

2

I would like to test the process but I don't know if I will be able to shutdown the databases for that long just to test

You should test and automate this process in a non-production environment.

5
  • But do the steps I described seem sound to you? I am no DBA. And Also do I need to have both Database be perfectly in sync as first step? – Dzyann Jun 23 '20 at 13:34
  • How much downtime do you have for the migration? How big is the database? You say "I am no DBA", yet you propose to execute a procedure, during an outage window, that I would only trust to an experienced DBA. – David Browne - Microsoft Jun 23 '20 at 14:21
  • The actual migration won't be a problem, I think. This is "pre-migration". There are several DBs they didn't know they have, and they have to migrate. They don't know the actual structure and scripts and stuff was lost. So I am researching the steps required. One of the things we discovered is this Transactional Replication. So to do a test run I was wondering if it is possible to do it without having downtime. Otherwise I have to request the downtime so we can backup the databases and test the whole process as you suggested. – Dzyann Jun 23 '20 at 14:43
  • You don't test during downtime. You test in a non-production environment ahead of downtime. You can build the new environment, populate it from database backups and test and validate the process for setting up transactional replication. Then, once you have the process perfected, and you know how long it will take, then you schedule downtime, copy the final backups and execute the production cutover. – David Browne - Microsoft Jun 23 '20 at 14:54
  • 1
    Yes, that is what I meant, I am sorry. What I meant is that I need to take the backups from production. But from what I read for this whole process to work, I need both backups to be syncronized, so that is why I was wondering if we could use not totally sync backups. But I think I understand you better now, I create the new environment and there I can make a snapshot from scratch. Since it is test environment I can configure the transactional replication from scratch and then start testing the process to move it. I am sorry I didn't understand you properly first. – Dzyann Jun 23 '20 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.