Here are the steps to build at SQL Server 2017 AlwaysOn Availability Group. We will be using Windows 2016 Standard 64-bit for the operating system and SQL Server 2017 (SQL 2016 or higher is recommended).

Here is a good reference on understanding Windows FCI Clusters: Understanding Windows Server cluster quorum options

Below are the virtual machines we will need to build. Note the data-center location of the VM’s.

Architecture:
1 Primary Windows 2016 Server Standard Edition 64bit virtual machine for the SQL Primary Replica
1 Primary Windows 2016 Server Standard Edition 64bit virtual machine for the SQL Secondary Replica (Synch Mode)
1 Primary Windows 2016 Server Standard Edition 64bit virtual machine for the SQL Secondary Replica (ASynch Mode)
1 Primary Windows 2016 Server Standard Edition 64bit virtual machine Cluster File Share Witness (local drive)

Step 1 – Build the Failover Cluster Instance (FCI)

If you are building an Availablility Group across multiple datacenters, make sure you think about where to put the dynamic witness and how you will configure the dynamic quorum if your entire primary datacenter goes down. You will need a witness and dynamic quorum to available to the secondary to keep the cluster up.

1. Build the production VM’s so that there is a C:, E: (Data), L: (Logs), S: (master, msdb), and T: (tempDB) drives. The dynamic quorum witness should only have a C: and Q: drive (quorum)
2. Enable clustering services on all 4 vm’s
3. On the Primary Replica, start the process of building the Windows cluster by loading the Failover Cluster Manager
4. Run the Validate Cluster Configuration wizard
5. Fix any issues from the report
6. Run the Create Cluster wizard (the “Create Computer Objects) rights in AD needs to be enabled for the server names of the AG you are creating)
7. Add the 3 replicas
8. Create an Access Point name and assign an unused IP address(s)
9. On the Confirmation screen, UNCHECK the “Add all eligible storage to the cluster” checkbox – IMPORTANT for AG CLUSTERS
10. Complete the build of the cluster

Step 2 – Build the Dynamic Quorum Witness

1. Load the Failover Cluster Manager from the Primary Replica to add the witness to the cluster
2. Right-click on the server -> select More Actions -> select Configure Cluster Quorum Settings

3. Select Quorum Configuration Option, select the middle selection as shown below:

4. Enter the path of the file share witness. This needs to be a share either on remote or local storage NOT attached to the cluster itself. I set it to a local share on the Q: drive
5. Finish the configuration

Step 3 – Install SQL Server

1. Install SQL Server 2017 Enterprise Edition (if production, Developer Edition if non-production) separately on each replica as if it were a stand-alone SQL instance with all best practices for drive paths, folders, trace flags etc..

Step 4 – Build the AlwaysOn Availability Group

1. Launch the SQL Server Configuration Manager
2. Click on “SQL Server Services”
3. In the viewer pane on the right, right-click the “SQL Server” services and select properties
4. On the SQL Server Properties dialog, select the “AlwaysOn High Availability” tab
5. Click the checkbox labeled “Enable AlwaysOn Availability Groups” so it is checked (see below)

6. Click Ok. Notice the service restart warning. If you are able to recycle the service now, do so.

7. Start the “Availability Group Wizard” by expanding the “AlwaysOn High Availability” folder in SSMS and right-clicking on “Availability Groups” (see screenshot below)

Step 5 – Build PS scripts

The scripts we will write will be fore the following:
– Monitoring and status
– Trouble-shooting & diagnostic
– Maintenance (i.e add databases, users, AG replicas etc.)

Views – 1457