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)?

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
3
  • 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
    Commented Jul 23, 2018 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
    Commented Jul 23, 2018 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
    Commented Jul 23, 2018 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.

Improve this answer
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.

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.