An Overview of AlwaysOn Availability Groups
An AlwaysOn Availability groups in SQL Server provides high availability for a set of user databases called availability databases that failover together. Each group supports a set of primary databases and multiple secondary databases. The availability database set is hosted by availability replica. Single primary replica hosts the primary databases and multiple secondary replica to host sets of multiple secondary databases. An availability replica provides redundancy only at the database level for set of databases one availability group.
Prerequisites before configuring AlwaysOn Availability Groups
Before configuring AlwaysOn Availability Groups in SQL Server 2012, we need the following prerequisites to be present:
- Windows Server Failover Cluster (WSFC)
- SQL Server 2012 Enterprise Edition
- Same SQL Server Collation for all replicas
- Multiple SQL Server Instances acting as Replicas
Configuring AlwaysOn Availability Groups depend on Windows Server Failover Clustering (WSFC) for management and failure detection of Availability Groups replicas. Earlier SQL Server versions need shared storage for creating failover clusters. However, we do not need shared storage for creating WSFC for AlwaysOn Availability Groups. The cluster will monitor resource group created for each availability group in SQL Server made to check the primary replica’s health.
Earlier database mirroring technique can be configured with Standard Edition but feature of AlwaysOn Availability Group is only available in an Enterprise Edition. If the user wants to use other replicas for read-only workloads and offloading their backups, he would need the licenses of those SQL server instances.
Creating databases with different collations in same SQL Server instance is not recommended because of issues caused by applications using temporary tables. If user wants to configure AlwaysOn Availability Groups for their databases, they all should be running the same collation on all SQL Server instances that are acting as replicas.
All those SQL Server instances that are used to standby for high availability and disaster recovery are called replicas. Mirroring used to deal with only one copy of the database but AlwaysOn Availability groups allow user to create up to five copies of databases running on five replicas among which three can be used for synchronous commit mode and two for asynchronous-commit mode.
STEPS: Configure AlwaysOn Availability Groups
Steps for configuring AlwaysOn Availability Groups in SQL Server are as follows:
- Installation of Windows Failover Cluster Feature
- Open Server Manager console and select Add roles and features
- Add Roles and Features Wizard will be opened and select Failover Clustering
- When asked to add features in Add Features that are required for Failover Clustering, click Add Features and Next
- Select Install for installing the Failover Clustering Feature
- Open Server Manager console to launch Failover Cluster Manager
- Select Validate Configuration inside the Failover Cluster Manager
- A dialog box of Validate a Configuration Wizard will be opened
- To add the server hostnames of SQL Server instances that will act as replicas in Availability groups, click on Select servers or a cluster and Next
- Click Testing Options in the wizard, Select Run all tests and Next
- Click Next in confirmation box and Finish to create Windows Failover Cluster
- In Create Cluster Wizard, select Access Point for Administering the Cluster and input the virtual server name and virtual IP address of WSFC created.
- Click Next, which will create Windows Failover Cluster using servers as nodes of cluster, add DNS & Active Directory entries for cluster Hostname.
- – To configure the cluster quorum configuration to use file share, we will use the steps shown. The wizard will configure cluster to use Node Majority, by default.
- On the Select Quorum Configuration page, select the Add/change the quorum witness option and Click Next.
- Select the Configure a file share witness in the Select Quorum Witness page, option. Click Next.
- Path of the file share will be inserted in the Configure File Share Witness page.
- Click Next in confirmation page and Finish
- Select the SQL Server services in SQL Server Configuration Manager to see the properties dialog box.
- Select the AlwaysOn High Availability tab and check Enable AlwaysOn Availability Groups in the Properties dialog box.
- Restart the SQL Server service.
- Launch SQL Server Management Studio and connect to the SQL Server instance
- Click AlwaysOn High Availability and Right-click on the Availability Groups and select the New Availability Group Wizard to launch it.
- Click Next and enter the name of Availability Group in Specify Availability Group Name page.
- In Select Databases in New Availability Group, select the check boxes and Next.
- In Specify replicas, select add replicas button and connect to other SQL Server instances.
- Configure these options:
Automatic Failover: Checked
Synchronous Commit: Checked
Readable Secondary: No