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
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.
Step 3 – Click next on the wizard and provide and name for the Availability group.
Step 4 – Select the Databases you want to use for the Availability Group
Step 5 – In the next screen, add the secondary replication using the button Add Replication. For this demo, I added one more
Step 6 – Add a Listener
Click on the Listener tab and add a new Listener. For this demo, I use the settings below
Step 7 – For the Initial Data Synchronisation, accept the default
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: