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.
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
Select 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.
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.
Add the Failover Clustering
Add the feature
Installation will start and the results will be displayed once install is complete.
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.
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.
Choose Quorum Option
Select the File Witness quorum option as we need to use a shared folder as a witness.
Configure File Share Witness
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.
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.
Sepcify the Name of the AG
Add Database to the AG
Choose Replica Servers
Change Backup Preferences
Add the AG Listener
This is the hostname used to connect to the availability group.
Choose Synchronization Option
Select how the initial synchronization of the database would happen, the easiest is to use a file share.