SQL Server 2016 Step by Step: Creating AlwaysOn Availability Group
Table of Contents
- This environment:
- SQL Server Installation
- Failover Cluster Installation
- Windows Failover Clustering Configuration for Always On Availability Groups
- Configure Cluster Quorum Settings
Always On Availability Groups is an enterprise-level high-availability and disaster recovery solution introduced in SQL Server 2012 to enable you to maximize availability for one or more user databases. Always On Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes.
- Ensure that the system is not a domain controller.
- Ensure that each computer is running Windows Server 2012 or later versions.
- Ensure that each computer is a node in a Windows Server Failover Clustering (WSFC) cluster.
OS - Windows 2012 R2
SQL Server - SQL Server 2016 Enterprise Edition (Eval)
SQL Server Installation
Failover Cluster Installation
First, we need to add the Windows Failover Cluster Feature to all the nodes running the SQL Server instances that we will configure as replicas.
Install-WindowsFeature -Name Failover-Clustering
Install-windowsfeature RSAT-Clustering -IncludeAllSubFeature
Windows Failover Clustering Configuration for Always On Availability Groups
Test-Cluster -Node SQL01, SQL02
New-Cluster -Name cluster-sql -Node SQL01, SQL02 -StaticAddress 192.168.1.250 -NoStorage
Configure Cluster Quorum Settings
Quorum is that it is a configuration database for the cluster and is stored on a shared location, accessible to all of the nodes in a cluster.
In Case of Even number of nodes (but not a multi-site cluster) Node and Disk Majority Quorum configuration is recommended. If you don't have a shared storage Node and File Share Majority is recommended. Here it will be configuring a FileShare Witness quorum.
It is recommended that you configure the quorum size to be 500 MB. This size is the minimum required for an efficient NTFS partition. Larger sizes are allowable but are not currently needed.
Set-ClusterQuorum -Cluster cluster-sql-ent -NodeMajority
Set-ClusterQuorum -Cluster cluster-sql-ent -FileShareWitness \\fileshare\quorum
Enable AlwaysOn Availability Groups Feature on SQL Server 2016
We can now proceed with enabling the AlwaysOn Availability Groups feature in SQL Server 2016. This is possible after install and configuring the Windows Failover Cluster on all the nodes.
Open SQL Server Configuration Manager - > SQL Server Properties - [SQL Server (SQLAG01)]
In the Properties dialog box, select the AlwaysOn High Availability tab. Check the Enable AlwaysOn Availability Groups check box. This will prompt you to restart the SQL Server service. Click OK.
Restart the SQL Server service.
Configure SQL Server 2016 AlwaysOn Availability Groups
Go to Management Studio, right click Availability Groups and click New Availability Group Wizard.
Specify Availability Group Name . This group name is SQLAVG2016. Then click Next.
Here you can see whether the DB meets the prerequisites
- Database should be in full recovery mode.
- You should make a full backup to add the DB into the Availability Group
This page applies to the New Availability Group Wizard and the Add Replica to Availability Group Wizard of SQL Server 2016.
If a server instance that you to use to host a secondary replica is not listed by the Availability Replicas grid, click the Add Replica button.
Add Azure Replica button to create virtual machines with secondary replicas in Windows Azure.
Adding Secondary Replica
Use this tab to verify any existing database mirroring endpoints and also, if this endpoint is lacking on a server instance whose service accounts use Windows Authentication, create the endpoint automatically.
Use this tab to specify your backup preference for the availability group as a whole and your backup priorities for the individual availability replicas.
An availability group listener is a virtual network name (VNN) to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. You point applications to the listener (which is registered with DNS) and directs traffic in the AG.
Select Data Synchronization
Use the Always On Select Initial Data Synchronization page to indicate your preference for initial data synchronization of new secondary databases. This page is shared by three wizards—the New Availability Group Wizard, the Add Replica to Availability Group Wizard, and the Add Database to Availability Group Wizard.
The possible choices include Full, Join only, or Skip initial data synchronization. Before you select Full or Join only ensure that your environment meets the prerequisites.
For each primary database, the Full option performs several operations in one workflow: create a full and log backup of the primary database, create the corresponding secondary databases by restoring these backups on every server instance that is hosting a secondary replica, and join each secondary database to availability group.
Select this option only if your environment meets the following prerequisites for using full initial data synchronization, and you want the wizard to automatically start data synchronization.