AlwaysOn technology in MS SQL Server is designed to increase the availability of your database, it is another step toward the fail-safe system. For the 1st time it appeared in MS SQL Server 2012 and was going to replace mirroring in future. But now we see both technologies in MS SQL Server 2014, which, of course, pleases.

AlwaysOn technology contains the elements of several high availability solutions of MS SQL Server:

Clustering

AlwaysOn the same as MS SQL Server cluster has a common name and IP address for MS SQL Server instances. Besides, AlwaysOn operates as Windows cluster service. To connect clients to the database only one IP address must be entered.

Mirroring

AlwaysOn technology is developed on the basis of mirroring. There is a primary replica and up to 4 (3 for in-sync operation) secondary replicas (8 replicas in MS SQL Server 2014). Asynchronous and synchronous modes are available.

Log shipping

While initializing the database is delivered to secondary replicas as in log shipping, i.e. backup copies and transaction log are sent automatically to a common resource and then restored in a secondary replica. Secondary replicas are readable.

A short list of AlwaysOn advantages:

– supports automatic redirection to secondary replicas

– secondary replicas can be used for data reading

– secondary replicas can be used for backup without loading the primary replica

– supports automatic page repair

– no need to use total storage space for database (for geo-clusters it eliminates the possibility to use a license for the data-storage systems replication, e.g. on HP hardware)

After having learnt some theory and benefits of MS SQL Server AlwaysOn let’s try to configure it.

Requirements:

1) Windows cluster (WSFC) – MS SQL Server AlwayOn relies on windows cluster

2) MS SQL Server Enterprise Edition, which must be installed on each Windows cluster node (WSFC)

3) Presence of AD. You can’t configure WSFC without it.

4) On the installed MS SQL Servers the same sort parameters must be set

For more information on the limitations and opportunities, please visit MSDN website.

After MS SQL Server is installed you should enable AlwaysOn High Availability Groups on the cluster nodes: open SQL Server Configuration Manager, select the tab «AlwaysOn High Availability», activate the option «AlwaysOn High Availability Groups».

Here we check again whether MS SQL Server runs under a domain account. Now create AlwaysOn group: Open the tab AlwaysOn High Availability, right-click on «Availability Groups», choose «New AvailabilityGroup Wizard». Specify Availability Group Name:

Under this name AlwaysOn cluster group will run. The name will also serve for availability control. In the next window select the database that will be included in the availability group. If the database can not be included into the group, the matter will be shown on the right. Then you must include replicas into AlwaysOn group and make settings:

This figure shows that the server srv1 \ sql2012 is a primary replica, srv2 \ sql2012 is a secondary one, for both automatic failover is set, synchronous mode is chosen and replicas are readable. Settings for mirroring endpoints, their ports and the ability to encrypt transmitted data are specified in the tab «Endpoints».

The next tab contains backup preferences:

It is better to choose the option to perform backups on any replica or alternatively to put restrictions on backup of the most important replica or where the channel for backup copy transmission is limited. The next tab is very important – here you enter the name of the listener, i.e. the network name users will connect to, its port and IP address.

You must specify the name of the listener and assign an unused IP address – preferably static, not  DHCP address IP is better. You’d better put the default MS SQL server port – 1433, or another for safety. If the nodes of Windows cluster are connected to different subnets, you need to specify two IP addresses. In the next window we enter the name of the network directory where the database backups will be kept to initialize AlwaysOn availability groups.

You can state that there are backup copies or just skip this point. The parameters are checked for validation in the next window

After this availability group is created.

AlwaysOn availability group is created.

The warning in the screenshot is about inaccurate configuration of quorum vote.

This message appears when you configure AlwayOn on OS Windows 2008 r2 servers. To resolve this error you must download the update KB2494036. You must install the update for all nodes in WFCS and set the voting parameters for one of the nodes.

According to the specification vote= 0 must be set, but it doesn’t work and with vote=1 it does. As a result of the query:

SELECT member_name, member_state_desc, number_of_quorum_votes FROM sys.dm_hadr_cluster_members;

the parameter number_of_quorum_votes must indicate «1» for all the nodes and cluster witness. In Windows 2012 these parameters are specified at once.

One more important note here – when specifying the name of the listener (in the example it is “alwayslistener1″), the computer’s DNS name in AD is registered in directory Catalogue. To do this, the cluster name must have full rights to create an object in the directory. The rights are assigned in ADSI console.

After we’ve created the availability group the service of our availability group appears in the console of the cluster:

But do not manage AlwaysOn availability group with snapin of the cluster service. MS SQL Server Management Studio is designed for managing AlwaysOn – right click on «AlwaysOn High Availablity», choose «Show Dashboard»

Basic parameters:

1 – name of the availability group

2 – name of the listener

3 – configuration of the availability group

4 – type of failover

5 – configuration of the primary replica

6 – configuration of database in availability group

7 – redirection to the secondary replica upd. 04.06.2014

One more important step was missed To make secondary replicas readable for a connection with the parameter applicationintent=readonly the routing must be configured For each replica AlwaysOn availability group execute the script: ALTERAVAILABILITY GROUP [srv_alwaysOn Group] MODIFY REPLICA ON 'srv1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('srv2,'srv1'))); ALTERAVAILABILITY GROUP [srv_alwaysOn Group] MODIFY REPLICA ON 'srv2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://srv1.domain.net:1433'));

After that you can check if the connection is read-only. For this purpose open MS SQL Management Studio, specify the listener and open the menu Options:

Use the query below to check the connection:

select SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') 

as a result we should get the server name with a secondary replica.

We’ve looked into the key aspects of AlwaysOn availability group settings.