1

We have a primary OLTP server in the cloud.

We intend to have Synchronous Alwayson Availability Group for Disaster Recovery, and another Availability Group AAG for Ad-hoc Query Reporting. This way if our Primary Server goes down, there will not be contention between our Second DR server and Reporting queries. Reporting will be a third node. We will not require different schemas or indexes on the Reporting Server. Do you see any flaws with this design pattern or is it generally common practice?

enter image description here

Improve this question
3
  • How do you propose to keep the reporting system up to date in the event that the primary goes down? This might be a good candidate for a distributed availability group.
    – George.Palacios
    Commented Sep 3, 2018 at 9:22
  • added a listener-watcher, correct DAG would be the option
    – user158823
    Commented Sep 3, 2018 at 9:31
  • Do you know that since you have AG on SQL FCI you do not have automatic failover. Rest seems fine with me if you take care of witness, you also did not gave any information about witness,quorum and how it is configured ?
    – Shanky
    Commented Sep 3, 2018 at 9:54

1 Answer 1

Reset to default
1

This would be one AG with 3 replicas, not two separate AGs.

In general is fine, but probably overkill. Normally if you have an FCI and an AG replica for DR you would use the DR node for reporting. In a disaster you wouldn't have a separate reporting replica, but disasters are, you know, bad.

For Quorum, a cluster with a single FCI should normally use Node and Disk Majority (or Node and File Share if the databases are stored on SMB shares). Neither of the other nodes should have a vote.

See WSFC Quorum Modes and Voting Configuration (SQL Server)

Improve this answer
1
  • 1
    It all depends on your business requirements for DR. Can you limp along with everyone sharing the DR node until you fail back, or build out a new reporting replica in the DR data center?
    – David Browne - Microsoft
    Commented Sep 3, 2018 at 16:01

Your Answer

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