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
- 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)
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)
Start – Search – Failover clustering –
Right click on Failover cluster manager – Create Cluster
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 –
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
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
- 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.
- 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
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
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.
Great post. It was easy to follow and worked on the first try.
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?
if you assign the Permission over the cluster object. It should work on both.
I think the above might be a little incorrect. Since it is AlwaysON, the disk resources are not shared. Therefore if you create a login on the primary, you also have to create it on the secondary nodes. The same applies for SQL Server Agent Jobs.
The simplest way to copy the logins to secondary node is described in the link below:
I hope this helps.
Otherwise this is a great post with good detail.
I agree with Rikesh.
Right. It should be created on Both nodes.
So great! Complete and clear.