November 27, 2022

How to Configure Always-On in MS SQL Server 2019

In this tutorial, we would go through the process of creating an Always-On Availability in SQL Server 2019.

 

Overview of AlwaysOn Availability

AlwaysOn is a High-Availability(HA) technique used as a disaster recovery(DR) system for a set of databases placed in a group called availability group. All the nodes participating in an AlwaysOn must be clustered in windows. That is they must be in the same windows failover cluster.

Take note of the following terms, that you’ll encounter in setting up Always On

  • Availability Group – logical container that is hosting the AlwaysOn availability replicas and databases
  • Availability Replica – the instances that are hosting the databases
  • Availability Databases – these are the databases protected by the AlwaysOn availability
  • Availability Group Listener – this the way the users connect to the database. It is a network name and IP address

 

Process of Configuring AlwaysOn Availability

These are the general procedure for setting up Always On

  • Create a Windows Failover Cluster
  • Instal SQL Server on all the Nodes
  • Enable AlwaysOn on All the Nodes
  • Create Your Databases
  • Configure AlwaysOn Availability Group
  • Check the Availability Group Dashboard

Let’s now follow the step by step procedure below

I already created windows failover cluster (see how to set up windows Failover Cluster). I’ve also installed SQL Server on the 2 nodes I want to use.

Step 1– Enable AlwaysOn Availability in all the nodes

To do this, Open configuration manager and go to the properties of the database. Click on the AlwaysOn availability and enable it as shown below

Enable AlwaysOn Availability from Configuration Manager
Enable AlwaysOn Availability from Configuration Manager

Step 2 – Start the AlwaysOn High Availability Wizar

To do this, open Managment Studio, expand Always On High Availability. Right Click Availability Group and choose New Availability Group Wizard.

Start New Availability Group Wizard
Start New Availability Group Wizard

 

Availability Group Wizard
Availability Group Wizard

 

Step 3 – Click next on the wizard and provide and name for the Availability group.

Name the Availability Group
Name the Availability Group

Step 4 – Select the Databases you want to use for the Availability Group

Select Databases that meets prerequisite
Select Databases that meets prerequisite

Step 5 – In the next screen, add the secondary replication using the button Add Replication. For this demo, I added one more

Add Replicas
Add Replicas

 

Step 6 – Add a Listener

Click on the Listener tab and add a new Listener. For this demo, I use the settings below

Add availability group listener
Add availability group listener

Step 7 – For the Initial Data Synchronisation, accept the default

Initial Data Synchronization
Initial Data Synchronisation

 

Ensure that the validation succeeds as shown below. Then you can proceed to  complete the installation.

 

At the end of all the steps, the final window would give a success message as shown below:

Always On Availability Group Wizard Completed Successfully
Always On Availability Group Wizard Completed Successfully

 

5 4 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments