New AG environment

Question

New AG environment

tcronin 95651

SSCertifiable

Points: 6735
More actions
August 13, 2018 at 2:05 pm

#360191

Looking a new 2016 build, all enterprise Edition licensed at VM host. Was going to have AG with readable secondary for reporting and 3rd node for DR. Looking at following setup, anyone see any holes? Thanks
0
down vote
I would set up your configuration as follows...
Node and File Share Majority quorum model
Node 1, Node 2 and file share witness at primary dc
Node 3 at DR dc
1 vote to each node in the primary data center & fileshare witness
0 vote to node in the DR data center
preferred owners node 1 & node 2
AG synchronous mode between node 1 & 2 automatic failover
AG asynchronous mode to node 3 manual failover
primary AG on node 1 with readable secondary replicas at node 2. Since you plan to use readable secondary replica, you will have to license node 2 as well as node 1. Node 3 will be covered by software assurance. I assume this is a production environment.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

I would add make sure you test this fully before going live, because long-running reports run against a readable secondary can block the redo thread of database in a "synchronous" AG, causing it to stop being synchronous.

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2011/12/22/alwayson-impact-on-the-primary-workload-when-you-run-reporting-workload-on-the-secondary-replica/

This could also cause your transaction log on the read/write replica to grow because it can't be truncated due to this issue.

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 2

other choice was an active passive cluster with AG for the read only node. interested in how many people have tried that approach

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 3

tcronin 95651 - Wednesday, August 15, 2018 7:21 AM
other choice was an active passive cluster with AG for the read only node. interested in how many people have tried that approach

I have this setup - but all nodes in the same data center. It works quite well as long as the readable secondary has no quorum votes and is not synchronous. No need for a listener in this configuration either - as all report users will access the secondary directly.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

eshults5 SSChasing Mays Points: 645 More actions · Answer 4

Beatrix Kiddo - Wednesday, August 15, 2018 5:50 AM
I would add make sure you test this fully before going live, because long-running reports run against a readable secondary can block the redo thread of database in a "synchronous" AG, causing it to stop being synchronous.
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2011/12/22/alwayson-impact-on-the-primary-workload-when-you-run-reporting-workload-on-the-secondary-replica/
This could also cause your transaction log on the read/write replica to grow because it can't be truncated due to this issue.

+1 I've ran into this myself.