I have two azure SQL managed instances in different region and configured fail-over group between the same MI instances. Also configured the transactional replication between on-prem sql \ Azure IaaS VM to primary managed instance. Now want to test fail-over group by failing over to secondary and then to primary. What's the best way \ possible way so that replication should not get disturbed.
Asked
Active
Viewed 162 times
2 Answers
0
If geo-replication is enabled on a publisher or distributor instance in a failover group, the managed instance administrator must clean up all publications on the old primary and reconfigure them on the new primary after a failover occurs. Please refer MS doucumentation for more information.
Shashikant Shakya
- 114
- 5
0
When you configure the subscriber, use the failover group read/write listener endpoint instead of the primary managed instance name.
The following information is available in the Microsoft documentation on this subject:
"If a subscriber SQL Managed Instance is in a failover group, the publication should be configured to connect to the failover group listener endpoint for the subscriber managed instance. In the event of a failover, subsequent action by the managed instance administrator depends on the type of failover that occurred:
- For a failover with no data loss, replication will continue working after failover.
- For a failover with data loss, replication will work as well. It will replicate the lost changes again.
- For a failover with data loss, but the data loss is outside of the distribution database retention period, the SQL Managed Instance administrator will need to reinitialize the subscription database."
Dragos Miron
- 111
- 3