SQL Server AlwaysOn Step by Step Setup

This guide will walk you through step by step to setup SQL Server AlwaysOn on SQL Server Server 2016 running on Windos Server 2016.

Prerequisites

  • Windows Server 2016

  • SQL Server Developer/Standard Edition

  • Three machines running Windows 2016 (WESTEROS, CASTLE-BLACK, WITNESS)

Step 1 - Enable Failover Clustering

AlwaysOn requires Failover Clustering feature to be added to machine that will have SQL Server installed. You can add this feature through the server manager :

Open Server Manager

Open Server Manager

Select Installation Type

Add Roles Installation Type

Choose the Server to Install the Feature on

You will need to repeat this process for each on the servers, that is for WESTEROS and CASTLE-BLACK which will be nodes in the cluster.

Choose Server

Don’t choose any Server Roles

Click next on the Server Roles, we are not adding anything from this page, we are adding features that are found on the next screen.

Choose Server Roles

Add the Failover Clustering

Failover clustering features

Add the feature

Installation will start and the results will be displayed once install is complete.

Failover Clustering

Creating the Cluster

The next step is to create the cluster and add the nodes to it. This step can be performed from any one of the nodes.

Create Cluster

Add the nodes and run the validation tests. Once the tests are done you will have a cluster created.

Add a File Share Quorum Witness

Since we only have two nodes, we need a third node to vote so that the server can stay online with a majority vote of two.

Quorum Witness Wizard

Choose Quorum Option

Select the File Witness quorum option as we need to use a shared folder as a witness.

Quorum Option

Configure File Share Witness

Configure File Share Witness

Choose the Shared Folder Path

Choose the Shared Folder Path

The quorum should now a witness and it will be brought online.

Step 2 - Enable AlwaysOn

Now that we have a working cluster, we can now enable AlwaysOn in the SQL Server Configuration Tool.

Enable AlwaysOn

Step 3 - Create AlwaysOn Availability Groups

AlwaysOn Availability Groups is a set of database that are always failed over to another server in the cluster together.

New Availability Group

Sepcify the Name of the AG

Give the AG a Name

Add Database to the AG

Add Database to the AG

Choose Replica Servers

Replica Server

Change Backup Preferences

Backup Preferences

Add the AG Listener

This is the hostname used to connect to the availability group.

Availability Group Listener

Choose Synchronization Option

Select how the initial synchronization of the database would happen, the easiest is to use a file share.