Membuat dan Mekonfigurasi SQL 2014 Always ON Cluster pada Windows 2012 R2 – Recommended way

SQL Always ON Availability groups hanya mirip seperti Exchange Database availability groups.
ini hanya supports untuk SQL Server Enterprise editions.
mari kita lihat bagaimana cara membuat itu–

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

(bahkan seperti jumlah nodes – memiliki sebuah Witness server sehingga dapat dijalankan sebagai – Node dan file share majority)
Step 1 : (jangan dilakukan Pada Kedua node)
Configure two network interfaces

• Primary.
• Replication. (database replication)

Agar tidak ada Single point pada kegagalan network interfaces.
jika replication network gagal ,maka itu dapat otomatis failover pada production network.

Replication Network – IP configuration
Default gateway dan DNS tidak harus di tentukan.
Catatan : Primary Network dan Replication network tiddak bisa pada 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.