Deploying A SQL 2012 AlwaysOn Cluster

This document will cover the design and deployment of a SQL Server 2012 AlwaysOn cluster. SQL Server 2012 AlwaysOn clusters are a high availability technology. They build mirrored database instances across a Windows cluster. This provides the benefits of both technologies with clustering providing a single point of access and the mirroring providing redundant copies of the databases.

WINDOWS CLUSTER BUILD

SQL 2012 AlwaysOn clusters are built on top of standard Windows clusters. Refer to the document  Building A Windows Cluster Using Windows Server 2008 R2 to deploy Windows Clustering.

SQL SERVER INSTALLATIONS

Once the Windows cluster has been deployed SQL Server must be installed on each server node. Refer to the document DBA - SQL Server Standard Build to deploy the SQL Server Engine install. Note that the SQL Server Edition deployed must be Enterprise.

ENABLING ALWAYS ON

The following steps must be run on each cluster node:
Open SQL Server Configuration Manager
Select SQL Server Services
Right click SQL Server (MSSQLSERVER) --> Properties
On the AlwaysOn High Availabiliity tab ensure Enable AlwaysOn Availability Groups is checked. If not check the box and restart the SQL Server service.

CREATING AN ALWAYSON ENDPOINT

An endpoint is required by the availability group to allow mirroring of databases between cluster nodes. Run the following on each server node to create the mirroring endpoints:

use [master]
CREATE ENDPOINT [Hadr_endpoint]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES)

CREATING AN AVAILABILITY GROUP

Using SQL Server Management Studio connect to the first node in the cluster.
Expand AlwaysOn High Availability
Right click Availability Groups --> New Availability Group
The databases can be added to the availability group at any point in time. It's generally easier to do this as a separate step.
By default the Availability Replicas section at the bottom will only contain the primary database node - Add the secondary nodes manually.
Availability Mode is set to Synchronous for all servers in the same primary site and Asynchronous for those servers in another data centre
We always set Failover Mode to Manual. Ie. We want to control the failover
Both nodes should have Readable Secondary set to Yes. This will allow read only connections to the secondary database for reporting purposes.

The availability group has now been created however the secondary node hasn't properly joined the cluster. As shown below there is a red down arrow on the secondary node and the availability group doesn't appear on the secondary node.

Right click on the secondary node and select Join to Availability Group. Follow the prompts to add the secondary node to the availability group.

CREATING AN AVAILABILITY GROUP LISTENER

The availability group listener is used to route connections to the SQL Server cluster nodes. Standard connections are routed to the active primary replica whilst read-only connections are routed to the secondary replica. The availability group listener updates the routing based on availability group failover and provides a single point for database connections to occur.

Expand the availability group and right click on Availability Group Lister --> Add Listener

Configure the availability group listener as shown below. Setup the listener name, port, and IP address as per organizational standards. Note - The IP address must be unique - an IP cannot be reused even if another port number has been chosen.

ADDING DATABASES TO AN AVAILABILITY GROUP

Expand the availability group. Right click on Availability Databases --> Add Database

Select the databases to add to the availability group. Only databases that meet prerequisites (Full recovery, full backup taken, and not part of an availability group) can be added.

Add in a networked file share that is accessible by all nodes in the SQL Always On cluster.

Follow the prompts to finish adding the database. Once complete the Availability Databases should look like the screenshot below:

TESTING AVAILABILITY GROUP FAILOVER

Under Availability Replicas check which nodes hold the primary and secondary roles.

Right click on the availability group and select Failover

Select the node to fail over to and follow the prompts.


Check that the secondary replica is now the primary replica.

Also check the databases on the new primary replica. They should be in a Synchronized state (as opposed to Synchronizing on the secondary)

As a final check, review the SQL Server error logs on each node. There may be detailed errors in the log that do not show up elsewhere.

TROUBLESHOOTING

ENDPOINT PERMISSIONS

The computer account for each node in the cluster requires permissions to the HA database mirroring endpoints on every other node in the HA cluster. When these permissions are missing the following error will be displayed in the SQL error logs:
Database Mirroring login attempt by user '<Computer Account>' failed with error: 'Connection handshake failed. The login '<Computer Account>' does not have CONNECT permission on the endpoint. State 84.
To resolve this first create the SQL login for the computer account. This account requires no permissions other than those given by the following script:
Grant CONNECT ON ENDPOINT::HaDR_Endpoint to <Computer Account>