In the previous article on AlwaysOn Availability Groups in SQL Server 2012, we looked at The Environmental Setup for An AlwaysOn Availability Group. In this article we turn our attention to the creation and configuration of the Availability Group.
There are two primary steps to creating an AlwaysOn availability group which has to be done only after successful creation of the Windows failover cluster as discussed in the previous article. Firstly enable AlwaysOn on each instance and then create the AlwaysOn Availability Group.
Enabling AlwaysOn High Availability on each SQL Server Instance
First of all you will need to enable AlwaysOn each participating SQL Server instance of the cluster or availability group (by default AlwaysOn is not enabled). Open SQL Server Configuration Manager (Start -> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools) as shown below:
In SQL Server Configuration Manager, select and right-click on the SQL Server service (select the appropriate instance service) and click Properties. On the Properties page, select AlwaysOn High Availability tab and enable AlwaysOn as shown below; notice that the name of the Windows failover cluster in which this instance participates will automatically appear :
After enabling the AlwaysOn High Availability at instance/service level, you will need to restart the service for changes to take effect. Once enabled, the IsHadrEnabled property of the instance will be set to 1. Please note, if there is any changes in the Windows failover cluster that you created, you must disable and enable AlwaysOn High Availability feature once more for the changes to take effect. Also, ensure that the TCP/IP net library/protocol is enabled on the instance as only the TCP/IP protocol is supported by the availability group listener (I will discuss more about availability group listener later in this series).
Creating an AlwaysOn Availability Group
Now to create an availability group. Connect to the primary instance/replica in SSMS (SQL Server Management Studio) and then right-click on the Availability Groups node under the AlwaysOn High Availability node and click the New Availability Group Wizard as shown below:
In the New Availability Group Wizard, skip the introduction page and on the second page, specify a unique name for the availability group that you want to create, in this case I have used AlwaysOn-Demo-AG for the availability group:
