Below are the steps required to replace a Remote Transactional Replication Distributor Azure VM in an Always On Setup. Please review these steps as applicable to your specific environment configuration and make any adjustments needed.
Environment:
Cluster Node 1 Always On SQL Server 2016 STD
Cluster Node 2 Always On SQL Server 2016 STD
Remote Replication Distributor SQL Server 2016 STD
Transcational Replication Publisher on the Cluster with re-directs to the Always On Listener
Solution:
1. Shut Application Down / Web Maintenance Pages that update your Replicated Tables
2. Shrink Remote Distributor Transaction Log File
3. Shut down Remote Distributor SQL Services
4. Copy Remote Distributor Distribution/Master/MSDB database mdf and ldf files to remote share (i.e. retaining the Replication Config settings in the MASTER + DISTRIBUTION databases + retain the DISTRO SQL JOBS in MSDB databases)
* or copy the whole data folder
5. Shut Down Current Remote Distributor Server
6. Rename Current Remote Distributor Server to _OLD
7. Build a new Remote Distributor Server with SAME spec (Mandatory: SQL Server 2016 Standard SP1 with CU on Windows Server 2012 R2 Data Center – current config)(match the SQL Server Version as the old Remote Distributor especially) (Remote Replication Distributor should always be equal version or versions ahead of the Publishser Version)
* in our case - Apply the same CU that is currently on the Distributor Server (CU6 for SQL 2016 SP1) (Goal: Microsoft SQL Server 2016 Standard (SP1-CU6) (KB4037354) - 13.0.4457.0
* Must have the same drive structure for SYSTEM DATABASES and all other databases.
8. Disablr SQL Agent Service and Shut Down SQL Services
9. Rename Master and MSDB and DISTRIBUTION .MDF and .LDF files to _old
10. Copy Master / MSDB / Distributor files from remote Share and replace existing files.
- or rename the whole data folder on the new server as (data_new) and just copy over the data folder saved earlier.
11. Re-Start the Server and make sure there are no errors.
12. Enable and start SQL Agent Services and Replication should pick up where it left off.
13. If Replication does not work, investigate the errors, and possibly need to rebuild all the Publications. (not the case for us)
14. Turn Applications online and remove Maintenance Pages
15. Test Replication
16. Check Replication tables for errors
|
|
|
|
|
|