38 C
Dubai
Tuesday, October 15, 2024

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.

image

Replication Network – IP configuration

Default gateway and DNS should not be specified.

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

image

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

image

image

 

image

Step 3 : (Do on the first node)

Creating Cluster

Start – Search – Failover clustering –

Right click on Failover cluster manager – Create Cluster

image

Add nodes

image

Give a Cluster name

image

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

image

image

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

image

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

image

Select Select the Quorum witness

image

Configure File share witness

image

Browse for the Witness server share

image

Click ok

image

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

image

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

image

Choose SQL Serve feature installation

image

Choose –

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

image

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

image

MAKE you use a Domain account with Domain admins permission.

For

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

image

Choose Mixed Authentication Mode –

image

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

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

image

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)

image

Always ON High Availability

Enable Always ON availability groups

image

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

image

Click ok

image

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

image

Choose Next

image

Enter availability group name

image

Choose the test database

image

  • 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.

image

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.

image

Choose the file share we initially created.

image

image

Now Restarting servers database should failover automatically.

image

Satheshwaran Manoharan
Satheshwaran Manoharanhttps://www.azure365pro.com
Award-winning Technology Leader with a wealth of experience running large teams and diversified industry exposure in cloud computing. From shipping lines to rolling stocks.In-depth expertise in driving cloud adoption strategies and modernizing systems to cloud native. Specialized in Microsoft Cloud, DevOps, and Microsoft 365 Stack and conducted numerous successful projects worldwide. Also, Acting as a Technical Advisor for various start-ups.

Related Articles

8 COMMENTS

  1. How does SQL user replication work in Always On? If you create a SQL user with dbo rights for a database on the primary node, does it get replicated to the secondary node? If not, what is the proper way? Do you just create the user on both nodes?

LEAVE A REPLY

Please enter your comment!
Please enter your name here

× How can I help you?