Prepare SQL Server for using AlwaysOn feature

  • Article

Prepare SQL Server for using AlwaysOn feature

To support AlwaysOn Availability Groups feature, an instance of SQL Server must meet the following prerequisites:

  • The server instance must be running an edition of SQL Server that supports AlwaysOn Availability Groups. see https://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx
    for details.
  • The SQL server instance can not be run under WOW64 mode.
  • The server instance must reside on a Windows Server Failover Clustering (WSFC) node with QFE 2494036 installed. You can find QFE 2494036 at https://support.microsoft.com/kb/2494036. The OS must be Windows Server 2008 or above. It does not matter if you install WSFC before or after installing the SQL Server. hough QFE 2494036 is not required on Windows Servers 2008 R2 or later, Microsoft recommends that you install the QFE so that you can get accurate information about
    WSFC quorum using SQL server store procedure.

For additional essential information, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) in SQL Server Books Online.

Below are the steps you can follow to enable SQL Server Instance for AlwaysOn Availability Group:

Note: You can perform step1-3 in any order

  1. Installed the supported SQL Server instance on Windows Server 2008 or later. On 32-bit OS install 32-bit SQL server, on 64-bit OS install 64-bit.
  2. Install Failover Clustering feature  on Windows server as follows ( For more details, please see Install the Failover Clustering Feature):
    1. In Server Manager, click Features. Thenunder Features Summary, click Add Features.
    2. In the Add Features Wizard, click Failover Clustering and then click Install.
    3. When the wizard finishes, close it.
    4. Repeat the process for each server that you want to include in the cluster.
  3.  Create a Failover Cluster

    1. Go to Administrative Tools, find Failover Cluster Manager.

    2. Follow the instructions in the wizard to specify:

    • The servers to include in the cluster.
    • The name of the cluster.

 For more details, see Create a New Failover Cluster

You can also add the node into an existing failover cluster. For information about adding a node into a failover cluster, see Add a Server to a Failover Cluster.

Note: The nodes on which SQL server instance resides on must be in the same failover cluster if the SQL server instances will be in the same availability groups.

After finishing step 1-3, go to SQL Server Configuration Manager to enable the AlwaysOn feature:

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2012, point to Configuration Tools, and click SQL Server Configuration Manager.
  2. In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (<instance name>), where <instance name> is the name of a local server instance for which you want to enable AlwaysOn Availability Groups, and click Properties.
  3. Select the AlwaysOn High Availability tab.
  4. Select the Enable AlwaysOn Availability Groups check box, and click OK.

SQL Server Configuration Manager saves your change. Then, you must manually restart the SQL Server service. This enables you to choose a restart time that is best for your business requirements. When the SQL Server service restarts, AlwaysOn will be enabled, and the IsHadrEnabled server property will be set to 1.

You can also use the SQL Powershell or WMI provider to enable SQL server for AlwaysOn. For more details, see Enable and Disable AlwaysOn Availability Groups (SQL Server)

Note: After enabling the AlwaysOn feature, if you destroy the failover cluster and create a new failover cluster or add the node to another failover cluster, you MUST disable AlwaysOn and enable it again. The reason is because Enabling AlwaysOn does a lot of other things besides "just flip a registry key", like grant the right permission to SQL server on cluster and cluster registry. So if you destroy the cluster, these permission setting will also be destroyed. You need to disable AlwaysOn to clean up the old local registry key setting and then enable Alwayson again.