Configure AlwaysOn Availability Groups: Step by Step

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)
  • 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.

  • SQL Server 2012 Enterprise Edition
  • 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.

  • Same SQL Server Collation for all replicas
  • 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.

  • Multiple SQL Server Instances 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:

  1. 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
    • Failover Clustering

    • Select Install for installing the Failover Clustering Feature

  2. Configuring Failover Clustering for AlwaysOn Availability Groups
    • 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
    • Validate Configuration Wizard

    • 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.
    • Create Cluster Wizard

    • 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.
    • Configure AlwaysOn Availability Groups

    • 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

  3. Enabling Feature of AlwaysOn Availability Groups Feature
    • Select the SQL Server services in SQL Server Configuration Manager to see the properties dialog box.
    • SQL Server Services

    • Select the AlwaysOn High Availability tab and check Enable AlwaysOn Availability Groups in the Properties dialog box.
    • Restart the SQL Server service.

  4. Create and Configure AlwaysOn Availability Groups in SQL Server 2012
    • 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.
    • New Availability Group Wizard

    • 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.
    • Configure AlwaysOn Availability Groups

    • In Specify replicas, select add replicas button and connect to other SQL Server instances.
    • Configure these options:
      1. Automatic Failover: Checked
        Synchronous Commit: Checked
        Readable Secondary: No
    • Verify that the port number value as 5022 in the Endpoints tab
    • Select the Create an availability group listener in the Listener tab. Put the listener DNS name and Port as 1433
    • Provide IP address and enter the preferred virtual IP address in IPv4 address. Proceed and click Next
    • In Select Data Synchronization, Select Full option and browse the shared folder that is accessible to replicas and set give write permissions to SQL Service account. Click Next
    • Configure AlwaysOn Availability Groups

    • Verify that all validation checks in the Validation page.
    • Click Finish. This will create and configure AlwaysOn Availability Groups and join the databases.

One thought on “Configure AlwaysOn Availability Groups: Step by Step

Add yours

  1. Hi Andrew,
    I have a situation where secondary goes into resolving state during automatic failover. Node1 to node2 automatic failover works fine but node2 to node1 does not work and node1 goes into resolving state forever. Manual failover works fine both way. Automatic failover and synchronization is on for both servers. Here are the error messages I see in event viewer.

    Event ID: 1069
    Cluster resource ‘XEN_DESKTOP’ of type ‘SQL Server Availability Group’ in clustered role ‘XEN_DESKTOP’ failed.

    Event ID: 1205
    The Cluster service failed to bring clustered role ‘XEN_DESKTOP’ completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.

    Event ID: 1254
    Clustered role ‘XEN_DESKTOP’ has exceeded its failover threshold.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at

Up ↑

%d bloggers like this: