This is a post that describes how to configure Microsoft SQL Always on databases. We will be using SQL Server 2019 standard edition so this only gives you a read-only copy of your replica. However this is perfect for uses such a Remote Desktop Farm where we just need a standby database to failover to.

First we need to add Windows Failover Cluster (WSFC) to each replica

On each replica, open Server Manager > click Add Roles & Features > select Add Failover Clustering > click Install. Proceed through the wizard, and when you get to the Select Features page, select the Failover Clustering checkbox.

If you do not already have .NET Framework 3.5.1 or greater installed on your server, select that checkbox as well to install. (If you do need to install the .NET Framework, you will need to reboot the server after installing).

Proceed next through the wizard and click Install to finish the wizard. You will need to do this on every replica in your AG.

Open the Add Roles and Features Wizard and add the Failover Clustering Role

Now we need to configure WSFC on primary replica

From Administrative Tools, open Failover Cluster Manager and click on Validate Configuration

Add the names of all the SQL Servers you want to configure as replicas in your AlwaysOn group

On the Testing Options page, click Run all tests (recommended). It is normal to see some warning messages. Make sure to review the warnings and correct anything necessary.

If you are using ESXi you will probably get the warning about using a single NIC. This is fine as the test doesn’t see the multiple NICs you have running on your hypervisor.

After the validation and summary is complete, the Create Cluster Wizard will open. In the Access Point for Administering the Cluster dialog box, enter the virtual cluster name (not the server or instance name), and the virtual IP address of your cluster. For this I just used an available IP in the same subnet as the primary SQL node.

Click Next

The cluster is created

You should get a success confirmation

Proceed next through the wizard, and your cluster will be created. The secondary nodes will be added to the cluster, and your cluster should now show up on all replicas (through Failover Cluster Manager). You do not have to go through these steps on the other replicas…you’re all done with setting up the cluster.

Next on the OU where the cluster AD account resides setup the following permissions.

Create Computer Objects

Read/Write all properties

We now need to configure a file share witness

Create a file share on a file server and add your cluster (computer) account with full control to the share

Right click on the cluster and click More Actions then Configure Cluster Quorum Settings

Click Select the quorum witness and click Next

Select Configure a file share witness and click Next

Enter the network path to your file share location and click Next

Confirm and click Next

Click Next

Open the SQL Server Configuration Manager on the primary node. Find the SQL Server Service and go to Properties

Check the box for Enable Always On Availability Groups

Login to SQL Management Studio and select the database you want to be Always On. Set the Recovery Model to Full. Then Take a full backup of the database.

Open the New Availability Group Wizard

Give your Availability Group a name and select Windows Server Failover Custer

Select the database you want to be Always On (I am choosing an RDS DB)

Select the replica sever, it will then ask you to login

Specify the replica servers

You will need to click the Listeners tab and create a listener. I needed a listener so that I could create a connection string to the availability group for a Remote Desktop Farm. I used two new IPs for this one for the subnet of Server 1 in the cluster and one for the subnet of Server 2

Create two DNS A records with your listener IPs and the name of your Availability Group. This will ensure a DNS round robin of both nodes in the cluster.