0

I'm currently doing a bit of testing with alwayson and replication. I currently have the below:

  • Fully working cluster and Always on environment; DCTEST1, DCTEST2 and the listener name DCTEST_L. The cluster is healthy.
    • Fully working transactional replication to another server - DCREPL
    • All is SQL2017 Enterprise

Replication to DCREPL works regardless of which node is the primary. I make data changes on DCTEST_L and they are successfully passed to the subscriber.

The issue I'm having is when I shut down the primary (DCTEST1). The availability group fails over successfully. However, when I make data changes on the listener - DCTEST_L; they are not replicated to DCREPL. When DCTEST1 is powered back on, then the changes are successfully replicated. Is this expected behaviour? I would like the replication to continue should the primary ever go down, and not have to stall until the node is powered back on.

Is there a step I have missed? I have followed the microsoft documentation:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-2017

Improve this question
2
  • How is the transactional replication set up? Which server is the publisher?
    – George.Palacios
    Commented Apr 16, 2019 at 9:27
  • Both DCTEST1 and DCTEST2 are publishers to DCREPL
    – Ricky
    Commented Apr 16, 2019 at 10:18

1 Answer 1

Reset to default
0

Actually according to Microsoft this is expected behaviour:

When the publisher has only two availability replicas (one primary and one secondary) and a failover happens, the original primary replica remains down because the logreader does not move forward until all secondary databases are brought back online or until the failing secondary replicas are removed from the availability group. The logreader, now running against the secondary database, will not proceed forward since Always On cannot harden any changes to any secondary database. To allow the logreader to proceed further and still have disaster recovery capacity, remove the original primary replica from the availability group using ALTER AVAILABITY GROUP REMOVE REPLICA. Then add a new secondary replica to the availability group.

Quoted from:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability?view=sql-server-2017

Improve this answer

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.