0

As a DBA I would like to include a read only replica into the always on availability group. XSLT / Crystal Reports will only be used.

Please see below

1) Primary Replica (Same Data center) Synchronous Mode Automatic Failover

2) Secondary Replica (Same Data center) Synchronous Mode Automatic Failover

3) Secondary Replica (Read Only , Same Data center for Reporting ) Asynchronous Mode

4) Secondary Replica (Off Site , DR) Asynchronous Mode

The will be a file share witness to form the quorum

My questions is will there be any problem if the read only replica for reporting is included as part of the always on availability group instead of setting Transaction Replication with the Listener.

The secondary replica (Read Only) will be used for reporting only mainly XSLT and Crystal Reports , SSRS will not be used.

Any expert feedback will be highly appreciated.

Improve this question
2
  • You can add the node to the WSFC, but not give it any voting rights if you don't want it to be an active part of the Always On AG. The down side of adding an async node could be your log files (on primary + secondaries) growing due to transactions not being applied to the async nodes fast enough (due to network or hardware, or other). This needs to be monitored after adding the async nodes. I would do it with an always on over Transactional replication, but that's just my opninion.
    – Randi Vertongen
    Commented Dec 14, 2018 at 23:58
  • 1
    Thanks for the advice. From my personal experience i am reluctant to implement transactional replication ...
    – SQL_NoExpert
    Commented Dec 15, 2018 at 0:00

1 Answer 1

Reset to default
1

My questions is will there be any problem if the read only replica for reporting is included as part of the always on availability group instead of setting Transaction Replication with the Listener.

Nothing you're not already dealing with such as an extra copy of the database, more networking IO, disk IO, etc., so that's kind of a moot point assuming the reporting server isn't massively underpowered for the reports you're running.

To be quite up front about it, I don't believe using transactional replication with Availability Groups is a good idea - it's technically possible, but beyond a pain to administrate over the long term unless you expect to never have a failover or swap in and out servers in the AG. The only time I'd go this route is if you only needed an extremely mall subset of data with vastly different indexes for reporting and couldn't do this on your primary. Outside of that I'd try my best to stay away from AGs + TR.

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.