0

I want to create 3 nodes availability group on SQL Server 2016.

  • Node 1 and Node 2 is my primary server and stays in primary data center. Each nodes set to readable secondary replica.
  • Node 3 stays in DR Data Center. I wanna use node 3 when servers in primary data center (node 1 and node 2) are down.

How can I set up this configuration? Should I use 2 quorum disks and uncheck the server in preferred owners (in WSFC configuration)?

0

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.

  • if node 1 and 2 down, will WSFC cluster become offline? because there are only 1/3 (less than one-half) of the voting nodes in the cluster – ktsrg Jul 23 '18 at 5:23
  • 2 of 3 votes are needed in above scenario to keep the cluster online. So if you lose two voting members at the same time in the primary data center the cluster goes down. – Goforebroke Jul 23 '18 at 13:56
  • You will also want to monitor the AO synchronization status and latency between the nodes and generate alerts as needed. This can be done with home grown scripts and native sql alerts or with 3rd party monitoring tools – Goforebroke Jul 23 '18 at 14:06
0

The important part is deciding where you want the server to run when something happens. File share witnesses can help you there if you can start the computer running the share in either of the data centers after a disaster.

You could use a cloud witness and remove the vote from either of the main data center servers. Then one server in each location has a single vote and as long as either of the data centers has internet access the cluster will stay there. But the system will fail over to the secondary site if the voting server goes down so you have to take care in transferring the vote before scheduled downtime of that server.

0

I've done something similar to this. I set up two AlwaysOn clusters with each one having two nodes in the primary data center with a common secondary at a DR location.

I don't remember all the details, but I believe I set them up as one Windows Cluster and two different AlwaysOn groups. One group per primary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.