Build the cluster right so you don't run around frantically like this guy does for a living.
So most DBA's know that AlwaysOn Availability groups were introduced in SQL 2012. I know it was the most exciting feature for me a few years ago and I couldn't wait to implement it. i'm going to share how I setup my home lab and thus used this as a template in a professional environment. To make this all readable I need to split it up into multiple parts. This first part will cover how to setup a two node cluster.
Build the cluster
Install Cluster Features:
Perform these steps on all nodes that you want in the cluster.
Go to Server Manager Dashboard and Select "Add roles and features"
Just press next at Server roles, and under Features select Failover Clustering.
It may take a few moments for the installation to complete.
Create the cluster:
When ready open the "Failover Cluster Manager" and select "Create Cluster" on the right hand side.
Add your servers, I just simply typed them rather than browse.
Next, the wizard will ask to validate the cluster. I suggest you do this especially if you ever need to contact Microsoft support in the future. Note that this is NOT a true failover cluster, we simply need the feature set to enable our AlwaysOn capabilities. Therefore do not be concerned when the disk tests failed. We do not have shared disks.
Also, we will get some networking warnings. As this is being built in my home lab, I do not have any type of NIC teaming or redundant networking setup. In a real production environment I would highly suggest you consult with your Server or networking team regarding this matter. But for the purposes of our tests it is safe to proceed.
On the next screen just select "Run all Tests". Feel free to view the report once finished to see of any errors or warnings that were not expected.
Finally once that is finished you can go ahead and give the cluster a name and an IP Address.
This error here is regarding the quorom, which we should probably address.
Addressing the Quorum:
So without going too in depth into the quroum, it basically exists to avoid a split brain problem.
Since this is a 2 node cluster, we have an even number of votes. So there is nothing to break a tie.
So right click on the Failover Cluster, go to more actions and select "Configure Cluster Quorum Settings".
I'm going to keep the votes as is and select next.
I'm going to select a file share witness and use a fileshare I created on another server. In a production environment work with your windows admin teams to get this setup. Since this is my lab I had limited options and just used my SQL 2017 test box.
You can leave that there indefinitely even if you add nodes. The fileshare witness is dynamic and will only vote if it needs to break a tie.
That's it for Part 1 of setting up an availability group. As you noticed this first part didn't involve any SQL configuration! In fact, as a DBA it's likely that you won't have to do any of this as it will be handled by a different team. None the less, I feel it's important to know even if it's just to be able to communicate more effectively with your Systems Engineering team.
#AlwaysOn #sqlserver #TSQL #microsoft #Highavailability #DisasterRecovery #failoverclustering