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.