Installing/Configuring SQL 2014 Always ON Cluster on Windows 2012 R2 – Recommended way

SQL Always ON Availability groups are just similar like Exchange Database availability groups.

It supports on only Enterprise editions.

Lets see how to build it –

• One domain Controller
• Two SQL Servers
• One Witness servers

(As its Even number of nodes – Having a Witness server so that it can run as – Node and file share majority)

Step 1 : (To be done on both the nodes)

Configure two network interfaces

• Primary.
• Replication. (database replication)

So that there is no Single point of failure on the  network interfaces.

If replication network fails ,It can failover to the production network automatically.

Replication Network – IP configuration

Default gateway and DNS should not be specified.

Note : Primary Network and Replication network cannot be in the same range.

Will do the same on both the nodes . Both Replication IP ,should ping each other

Step 2 : (To be done on both the nodes)

Installing Prerequisites

Start – Server manager – Manage – Add Roles and features – Role Based or Feature based installation

Install Failover Clustering and .Net Framework 3.5

Step 3 : (Do on the first node)

Creating Cluster

Start – Search – Failover clustering –

Right click on Failover cluster manager – Create Cluster

Add nodes

Give a Cluster name

Make sure you Uncheck “Add All Eligible Storages to the cluster

Step 4 :(To be done on Witness server)

Prepare File share

Login to Witness server – Create a Witness folder – Share it with

Give full control to –

Cluster name

Node1

Node2

Administrators

Step 4 : (can done from SQL node1)

Add File share witness to the cluster

Configure Cluster Quorum (File share witness)

Right Click on the cluster name – More Actions – Configure Cluster Quorum settings

Select Select the Quorum witness

Configure File share witness

Browse for the Witness server share

Click ok

Step 5 : (To be done on both the nodes)

Installing SQL Server 2014

Download binaries of SQL Enterprise 2014 Server

Run the setup – New SQL Server stand-alone installation or add features to an existing installation

Chosen evaluation just for my test – you can choose product key.

Choose SQL Serve feature installation

Choose –

• Database Engine Services
• Management Tools – Basic
• Management Tools – Complete

You can choose Default Instance or Named instance if the Cluster is going to be shared.

MAKE you use a Domain account with Domain admins permission.

For

• SQL Server Agent
• SQL Server Database Engine
• SQL Server Browser

Choose Mixed Authentication Mode –

Change the Data Directories to a different drive if its production.

Make sure same drive letters are named on both the nodes.

Step 6 : (To be done on both the nodes)

Enable Always ON to the SQL Server 2014

Open SQL Server Configuration Manager – SQL Server Services – SQL Server (MSSQL Server)

Always ON High Availability

Enable Always ON availability groups

Restart the SQL service . For Changes to take effect.

Step 7 : (To be done on SQL node1)

Create Test database and run a full backup on it.

Create a test database

and Right click on the database Tasks – Backup

Click ok

Step 8: (To be done on SQlnode1)

Create Availability Group

Login to SQL management studio –

right click on Always on High Availability – New availability group wizard

Choose Next

Enter availability group name

Choose the test database

• Add Replica (Add the secondary server)
• Configure Listeners (Do not forget to add listeners)

NOTE : Am choose readable secondary “NO” -As Skype for business doesn’t support it. so see the purpose of the application and build accordingly.

But SharePoint 2013 supports though.

Typically you can put the listener on  5024, or 5025. Enter the IP Address for the Listener Name.

Verify DNS record gets created for the listener name. If DNS doesn’t exist – Create A record.

Choose the file share we initially created.

Now Restarting servers database should failover automatically.