I have a database setup to do Transactional Replication from an On-Premise Database running SQL Server 2014 to an Azure SQL Database. The replication is set to run continuously and also set to replicate Schema changes. It is a Push Replication. The replication works correctly.
The issue I have is that I now need to add a new column to the source table which I want to be replicated to the Azure database. I tried this in my development environment and it took approximately 3 hours to replicate the change because the table has over 1 million records. However, it would be ok for me to generate a new snapshot and deliver the data that way to initially get the column changes to Azure. The column changes infrequently so it will only cause a load on the initial sync. I've done this in the past and a snapshot will replicate in about 20 minutes.
I think generating a new snapshot to get the data up there will be the fastest approach to replicate the data, but I'm not sure what steps to take to ensure that my changes to the source table are not replicated as soon as they are applied. Below are the steps that I'd like to take, but I'm just not sure how to accomplish this so that I don't have to wait for the transactions to be replicated for my new snapshot to be applied.
- Add my new column to my source table
- Let Replication push my schema change to the destination table
- Disable transactional replication
- Apply updates to the source table column
- Re-initialize the subscription with a new snapshot
- Enable transactional replication
I'm not 100% sure if I truly need to disable transactional replication. Ultimately, I just want to ensure that I don't have to wait 3 hours to replicate the change because it maxes out the Azure SQL Database DTUs and makes it unusable. If I can go the snapshot approach then the 20 minute downtime is acceptable.
I've found the following articles which explain how to reinitialize subscriptions so I think I understand that. However, when I apply my changes prior to re-initializing my subscription, won't I be stuck waiting the 3 hours for those changes to replicate before the subscription would try to reinitialize? Or does the re-initialize stop any replication in progress at the subscriber?
