Once in a while I’ll run into syncing issues with my subscriber server in a transactional replication setup.
Taking a new snapshot of the publisher causes significant downtime on my subscriber server.
If my subscriber server was part of an AlwaysOn AG as the primary, could I failover to the secondary server while the primary is being synced up to a new snapshot on the publisher server, and mitigate the downtime? (Then I can switch back to the primary once it’s finished syncing with the publisher server?)
E.g. Server Z is my publisher in the transactional replication, Server A is my subscriber. Then I create an AlwaysOn AG on Server A and a new server called Server B. If Server A falls out of sync with Server Z and I need to take a new snapshot on Server Z to re-sync Server A to it, can I failover to Server B in the meantime?
In general, no, this is not going to work because the databases in the availability groups are read-only on the secondary. If SQL Server somehow allowed other processes (the replication) to change data on the secondary, the database on the secondary would no longer be in sync with the primary, right? And the way SQL Server AG synchronization (mirroring) works, it would have to be about completely rewritten to even think of supporting something like this.
However, if the subscriber is read-only, you could use a process similar to the following to accomplish what you want, but I do not recommend doing so as it is getting away from What It Is Designed To Do, and the longer you’re in IT, the more you’ll know why that is a problem.
Once the situation occurs where you need to resync:
- Fail over to Server B
- Remove the database(s) from the availability group that are part of the Server Z replication scheme
- Bring the databases on Server A out of recovery and let your replication resync from Server Z
- Once the data on Server A is in sync with Server Z, fail back to Server A
- Drop the databases that are not in the availability group from Server B
- Add the databases back into the availability group
Again, this would work only if Server B is used for read-only during this time. Any changes made to the databases that were removed from the availability group while Server B is primary will be lost.