How to setup Microsoft SQL Server High Availability with AlwaysOn in Azure

Overview

Microsoft SQL Server AlwaysOn is a collection of high availability and disaster recovery features introduced from SQL Server 2012. AlwaysOn is used to minimize the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) and maximized availability databases. SQL Server AlwaysOn branding encompasses both FCIs (Failover Cluster) and AGs (Availability Group) an enterprise-level alternative to database mirroring. Windows Server Failover Clustering is part of the backbone of SQL AlwaysOn features. An availability group supports a set of reading and write the primary databases and its supports one to four corresponding secondary databases.

Pre-requisites:

1. Domain Controller
2. Microsoft SQL Server (Primary)
3. Microsoft SQL Server (Secondary)
4. Windows File Server Witness

Before we are going to start the SETUP make sure all the servers are connected the Domain controller and communicate with each other properly.

Building your Windows Fail Over Cluster:

Fail Over Cluster is set of liberated computers that work together to improves the availability of services and applications. The cluster servers are known as “NODE” are linked by physical cables by software. If one of the node is fails, another node begins to provide services.

Note: To achieve this, all the node is having .NET Framework 3.5 and the Failover Clustering features enabled.

1. Click “Add Roles and Features” from Server Manager
2. Click “Next”.
3. Choose “Role-based or Feature-based Installation”
4. Choose the Server from the list of server pool
5. Skip to choose the Server Roles and click “Next” button
6. From the list of features, you can choose “Failover Clustering”
7. Click “Add Features” and “Next”
8. Installation process is started by clicking the “Install”
9. Once it is done you can see the features in under the Tools from “Server Manager”

Make sure you need to complete the same setup on another SQL Servers.

How to Add the NODES in clusters:

1. Open the Failover Cluster Manager console from Server Manager
2. Click to “Create Cluster” from right side of panel
3. Choose the server list which are the servers you want to add the Cluster Failover
4. Validation Warning windows you can choose to Run all tests.
5. Provide the unique name to access the Cluster and IP Address
6. You are ready to Create a cluster

How to Configure Cluster Quorum:

• Right click of the Cluster, click More Actions à Configure Cluster Quorum Settings
• Click Next
• Choose to “Select the quorum witness”
• Click to Finish the quorum configuration for File Share Witness

How to Enable AlwaysOn High Availability in SQL Server:

• Open SQL Server Configuration Manager
• Choose SQL Server Services à SQL Server (MSSQLSERVER) à Right click “Properties”
• In that properties, you can enable AlwaysOn Availability Groups
• Once it is enabled you need to restart the SQL services and Make sure the services are running in your server.

How to Allow SQL port in both High Availability Servers:

1. Open windows Firewall and choose “Inbound Rules”
2. Add “New Rules”
3. Choose the PORT rule creation
4. Mention the PORT Details in next window
5. SQL Port: 1443 and Cluster service PORT: 5022
6. Allow the connection from the PORT you mention in previous window
7. Choose the PROFILE to apply the rules
8. Give the name of New Firewall Inbound rules
9. Click “Finish” and check the open port in Properties

How to Create AlwaysOn High Availability Groups:

Before following this Setup, you must have a database on your local server and you did to take a full backup of this database.

1. Open SQL Server Management Studio à Connect à Database Engine
2. Right-click on the “AlwaysOn High Availability”
3. Create new Availability Group wizard click “Next”
4. Mention the group name of Availability Group
5. Select the database for the availability group
6. You need to add secondary replica server in here
7. Click Add Replica and connect another secondary SQL Server
8. Once it is connected server will appear the instance window
9. In next window, you need to specify the Shared file server location
10. Cross check the result of Validation cluster. You need to fix to proceed the next level if you get any error in validation result.
11. Ignore the warning of listener configuration. We can configure this part in later. But Listener is more important to configure the High Availability of AlwaysOn for SQL.
12. List of all your configuration report in summary window. You can able to download all the settings as a script for future reference.
13. Once Finished you can able to see the Databases and Replica servers in AlwaysOn High Availability

How to Add Listener for SQL AlwaysOn High Availability:

• AlwaysOn High Availability à Availability Group Listeners à Right click “Add Listeners
• In this window mention the name of Listener DNS name and PORT. Make sure to give the Static IP of Listeners to connect the clustering Databases.
• Once details are given click “ADD” button.
• For testing, this listener go to SQL Management Studio à Connect à Database Engine
• You can access the database which is added in AlwaysOn High Availability