October 24, 2019 at 1:29 pm
I'm pretty well versed in old-school failover clusters. I'm pretty well versed in old-school transactional replication. I've gotten a little experienced with Availability Groups, more so once someone else has already set them up. So now I'm the lone DBA. I just successfully built my 1st AG cluster from scratch. But I also want to replicate to a reporting server.
MyServer consists of NodeA and NodeB nodes, using AG. There's just 1 database in the group for now, but there will absolutely be 1, possibly 2 more added. That's all good and running like a champ. The complication comes with MyReportingServer. I need a read-only copy of that 1 AG database on the reporting server. Even after the other db's are added to the AG, I will still only need that 1 on the reporting server. I was thinking set up replication from MyServer to MyReportingServer and the AG "engine" would deal with traffic control from whichever node is active to the reporting server. However, when I try to configure that concept from MyServer, I'm forced to name a specific node. If I name NodeA (Primary), what happens to replication when B becomes the active node?
How should I do this? Am I barking up the wrong tree entirely? Am I misunderstanding how AG works? Is AG active/passive?
October 24, 2019 at 2:18 pm
this is where mirroring with a witness used to be a great tool....I don't really know AG that much, but i'm guessing there is some DNS trickery that you could use????
i'm all ears on this one as I want to learn
MVDBA
October 24, 2019 at 2:59 pm
It's little ugly, but how we did this in the past is by creating linked server on all replicas for our listener name IIRC. I don't have the exact steps on how we achieved this(been 3 years), but we were able to make it work.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply