0

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?

Improve this question
3
  • Server A would become read-only once you'd failed over meaning you could not apply the snapshot to Server A, you would have to apply it to Server B and this would be the same as applying it to Server A without failing over.
    – HandyD
    Commented Mar 15, 2019 at 4:31
  • ...and you might want to delete either this question or the previous one that you posted as they are duplicates.
    – Tony Hinkle
    Commented Mar 15, 2019 at 19:49
  • Your replicated server would be equally out of sync as the first one. It would probably be better to have two subscribers. What’s the actual reason why you need to resymc?
    – eckes
    Commented Mar 15, 2019 at 21:30

1 Answer 1

Reset to default
1

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:

  1. Fail over to Server B
  2. Remove the database(s) from the availability group that are part of the Server Z replication scheme
  3. Bring the databases on Server A out of recovery and let your replication resync from Server Z
  4. Once the data on Server A is in sync with Server Z, fail back to Server A
  5. Drop the databases that are not in the availability group from Server B
  6. 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.

Improve this answer
4
  • I didn't know the databases in the secondary server are read-only. So even removing my replication issue out of the question, if my primary server had an issue and I failover to the secondary, do the secondary databases remain as read-only or they're only read-only while the primary is up and running?
    – J.D.
    Commented Mar 16, 2019 at 12:22
  • 1
    When you fail over, you are switching the secondary to be primary and vice versa. I recommend that you do some reading as at this point you're missing some fundamental understanding of how availability groups work.
    – Tony Hinkle
    Commented Mar 16, 2019 at 12:47
  • Yea that's what I assumed. I'm just starting to look into AGs and I was hoping one benefit of them would solve the problem in my original question but o well. Any recommend resources to read?
    – J.D.
    Commented Mar 16, 2019 at 14:41
  • 1
    learn.microsoft.com/en-us/sql/database-engine/…
    – Tony Hinkle
    Commented Mar 16, 2019 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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