How to create a microsoft 2012 Failover cluster for msSQL2014 AlwaysON?

Briefly:

A complete windows failover cluster installation and configuration is not needed for msSQL2014 AlwaysON feature. Rather winows failover cluster service configure with 2 or more nodes added in cluster is sufficient.

Quick node:

• Join 2 or more nodes into a domain.
• Install Failover cluster service.
• Configure a cluster using failover cluster manager

In detail:

1. Join nodes into a domain and login as domain administrator into each server and install failover cluster service.

2. Add the nodes and finish the wizard.

Points to note:

A quorum share for cluster is for node voting. Cluster voting is a way for the cluster joined node to identify who is primary. However for msSQL2014 AlwaysON this is not needed as AlwaysON and windows cluster are two different technology.

• windows cluster is node level availability which uses a quorom disk/share to vote and decide who is primary and runs services on the primary node.
• msSQL2014 AlwaysON uses windows failover cluster feature  to identify node status for automatic sqlserver failover and manage sync commits.
• use of windows cluster and msSQL AlwaysON together is redundant. If you want high availability on SQLserver along with node then first you need a windows cluster configured with quorum, and virtual disks then install msSQL2014 Failover cluster. unlike in AlwaysON you just need to install msSQL2014 in standalone mode.

How to configure AlwaysON Availability Group(AG) in msSQL2014?

Prerequisite:

• Microsoft Cluster configured and multiple nodes joined into the cluster. Click here to know how to do that.

Quick note:

1. Enable AlwaysON availability group from sqlserver configuration manager.
2. create and Grant host node login into sqlserver.
3. Follow new AlwaysON availability wizard from sqlserver management studio.

In detail: ( 9 steps)

1. Enable AlwayON from SQL configuration manager.

2. Enable contained database by running the following SQL:

sp_configure ‘contained database authentication’, 1;
GO
RECONFIGURE;
GO

3. select the database that would be in alwaysON group and enable contained type =partial and take a full backup of the database.

4. Allow grant permission for the primary replica by the following SQL

USE master;
GO
CREATE LOGIN [Domain/Primary_node$] FROM WINDOWS;
GO
GRANT CONNECT on ENDPOINT::Hadr_endpoint TO [Domain/Primary_node$];
GO

5. Create a new availability group

6. select the intended database

7. Add the nodes which are joined in the cluster as database replica. Choose the Listener tab and set the AG virtual IP address and database port. This IP will act as virtual IP for applications/clients to connect to database.

 

8. Select Data synchronous type, choose share drive( which is the most real time and most recommended). Select the share drive which is not in the cluster host,( typically would be in the file server), assign cluser nodes full access to the share so that cluster nodes can read/write into the share drive.

9. Done ! The msSQL2014 AG should be configured successfully. Below screen shot is from my actual installation, which is a 4 node cluster, so ignore extra details, however the main idea is validation should end without any warning.

what is about microsoftSQL2014 AlwaysON?

Quick notes:

MSSQL2014 alwayson is a database high availability solution.

Briefly:

• SQL2014 alwayson is a Database high availability technique that inherently uses functionality from Microsoft failover cluster function.
• MSSQL2014 operates on top of windows 2012 servers that are joined in a windows cluster. The windows cluster do not required a shared disk or storage pool rather a simple network share drive can be used as voting point or quorum. Even without quorom share/quorum disk it works fine!

• SQL2014 alwayson is a 2 node automatic failover technology having 3 node synchronous commit, in therory it can be maximum 8 node cluster. however  a 3 node cluster is the optimum cluster design one can have.
  • Automatic failover assures unplanned outage protections.( can be configured to 2 nodes only and rest of the node can be  defined as manual failover nodes, which allows planned activities or rolling patch upgrades)
  • Synchronous commits assures data availability between  max 3 nodes.( rest of the nodes will have async data commit and may have delay in real time data availability and hence manual failover to async nodes may cause data loss, async commit nodes can act as DR nodes.)
  • 1 node is primary for read and write operation. a maximum of 2 node can be made available for read only operations which can be used as real time reporting database and a maximum of 5 nodes can be configured as async read only database( which can be used as data warehouse type database where real time data is not a priority)

• SQL2014 has availability group(AG) which acts as the cluster service for selected database(s) that contains
  • contained database which allows users, roles and securites specific to database but not for the database server( which allows database alone with its users/roles/securities to be seamlessly migrated into different SQLservers.)
  • AG maintains the failover nodes and replicas which can manage the automatic or manual failover. technically it is the cluster manager for the database.
  • AG can be created on one or multiple databases with multiple cluster joined nodes.