Replication Monitoring with Always On Availability Group

Question

Replication Monitoring with Always On Availability Group

sqlpops

SSC Enthusiast

Points: 195
More actions
December 6, 2023 at 8:15 pm

#4341055

Hey Folks,
So I have SQL Transactional Replication set up on a database that's part of an AOAG. Everything is working fine except for one thing. After a failover, the Replication Monitor is not able to show any info since the primary is where the replication was configured and now the secondary is the new primary. Replication continues but the Monitor is not able to show anything. I was wondering if there was a workaround for this? TIA
December 30, 2023 at 4:29 pm

This was removed by the editor as SPAM
January 18, 2024 at 12:45 pm

This was removed by the editor as SPAM
January 24, 2024 at 6:48 pm

This was removed by the editor as SPAM
February 1, 2024 at 2:42 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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

Did you add the original publisher to the monitor from each replica in the AG as per the documentation?

sqlpops SSC Enthusiast Points: 195 More actions · Answer 2

Yes, and that only works when the original is available. I'm speaking more about when a failover occurs because it's not available. How can the replication be monitored?

sqlpops SSC Enthusiast Points: 195 More actions · Answer 3

Ok so it looks like I am able to use the replication monitor on the other replicas after failover but it's VERY laggy.

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 4

Why would you use a transactional replication in AG? I see the benefit. However, the replicas would do the same thing. right?

DBASupport

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

There are many business reasons why you might do this.

We have edge nodes where web servers sit in places closest to our customer base and to reduce latency we have edge SQL servers with web content so it doesn't have to traverse the WAN, but the main e-commerce site sits centrally, so any product, stock, price information is replicated out from central to edge, I don't want to replicate the whole DB to these edge sites, and I have over 200 edge SQL servers, how is an AOAG going to work there?

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 6

Makes sense...

Here is a comment: If you have a cloud platform, could you not use DW servers within the cloud with proper firewall rules. This would be a different way of doing things... A better way?

If you have 200 edge servers, I can understand why you want to use transactional replication. This is massive.

DBASupport

sqlpops SSC Enthusiast Points: 195 More actions · Answer 7

In this situation, Replication is being used to support backend databases which are not part of the AG.

This reply was modified 11 months ago by sqlpops.