High availability refers to a system or infrastructure’s ability to remain operational and accessible for an extended period, typically measured as a percentage of uptime. It ensures that critical services or applications are consistently available to users without significant interruptions due to hardware or software failures, scheduled maintenance, or other unforeseen events.
High availability is significant for businesses as it ensures uninterrupted operations, prevents revenue loss, and maintains customer satisfaction. It reduces financial risks, protects reputation, and helps businesses maintain their competitive edge. By incorporating redundancy and fault-tolerant mechanisms, high-availability systems improve overall reliability. Redundant components and load-balancing techniques distribute the workload across resources, ensuring scalability and optimal performance even during peak usage periods.
Databases are crucial components of modern applications and play a vital role in storing, managing, and retrieving data. They serve as the backbone for various business-critical systems, including customer relationship management (CRM), enterprise resource planning (ERP), e-commerce, financial applications, and more. The importance of databases lies in their ability to provide consistent, reliable, and secure access to data.
AlwaysOn Availability Groups is a high availability and disaster recovery feature introduced in Microsoft SQL Server. It provides a solution for database mirroring and failover clustering, allowing you to create a group of databases that can fail over as a single unit.
In the context of high availability, AlwaysOn Availability Groups offer the following benefits to SQL databases:
- Increased Uptime: By configuring multiple database replicas, AlwaysOn Availability Groups ensure that your databases remain available even in the event of a failure. If the primary replica becomes unavailable, one of the secondary replicas can be automatically promoted to become the new primary, minimizing downtime.
- Redundancy and Fault Tolerance: AlwaysOn Availability Groups provide database redundancy by maintaining synchronized copies of the databases across multiple replicas. These replicas can be hosted on different servers, improving fault tolerance and protecting against hardware or software failures.
- Load Balancing and Read-Only Routing: You can configure read-only replicas within an availability group, enabling you to offload read workloads from the primary replica. This load-balancing capability improves performance by distributing read operations across multiple replicas.
- Disaster Recovery: AlwaysOn Availability Groups can be used for disaster recovery purposes by configuring replicas in different geographic locations. This allows for automatic or manual failover to the secondary replicas in case of a disaster or planned maintenance, ensuring business continuity.
- Scalability and Performance: With the increasing volume of data and user demands, databases need to scale and handle larger workloads efficiently. AlwaysOn Availability Groups offer the ability to distribute read workloads across multiple read-only replicas, allowing for load balancing and improved performance. This scalability feature ensures that the database can handle growing demands without sacrificing responsiveness.
This section provides a comprehensive guide on deploying Always On availability groups in SQL Server 2022 instances. It covers the necessary steps and procedures to implement this high-availability feature.
Prerequisites
For this guide, you need to have Windows Server 2022 Failover Clustering (WSFC) configured. This can be done by following the aid provided here.
For my setup, I used 3 servers, configured as shown:
| TASK | IP ADDRESS | HOSTNAME |
| iSCSI Target/AD DS/DNS Server | 192.168.200.55 | win-server.computingforgeeks.com |
| Cluster Node1 | 192.168.200.70 | node1.computingforgeeks.com |
| Cluster Node2 | 192.168.200.71 | node2.computingforgeeks.com |
You also need to install the SQL Server 2022 with a stand-alone installation and remote authentication enabled(able to login with AD user). This can be done by following the below guide:
Once the prerequisites have been met, proceed to configure the AlwaysOn Availability Groups in SQL Server 2022 with the below steps.
#1. Add SQL 2022 Instances to the Failover Clustering
First, open the SQL Server 2022 Configuration Manager on all the nodes, select SQL Server Services->SQL Server (instance name), right-click and select properties.
Navigate to the Alwayson Availability Groups tab and check the box to enable the features. Then restart the SQL server
Next, we need to create a folder for the backup of the database of AlwaysOn Availability Groups and also add full access permission to the SQL Server login user.
Create the data directory with the required permissions.
Create a backup directory on the C Drive with the name AlwaysonDB-Backup
You also need to allow the database mirroring service port through the firewall. Launch Windows Defender, under the inbound ports, and add a new rule.
Add port 5022/TCP as shown.
Provide the rule name and create it.
#2. Create the AlwaysOn Database
Now on the selected Primary Node, you need to accomplish the below steps. First, access the SQL Server Management Studio (SSMS) and create a database for the AlwaysOn Availability Groups. Remember to specify the database path created in Step 1.
Once the database has been created, take a backup by right-clicking on it and proceeding as shown.
Proceed and remove the available default backup path to allow us to specify a desired path.
Set the created Path as the backup directory, and provide the name for the backup.
Once complete, you will see this:
#3. Create the Availability Groups in SQL Server 2022
Access the database on the Primary node using SQL Server Management Studio (SSMS) with the domain user/user set as the admin account for the SQL server.
Right-click on the Always On High Availability->Availability Groups, launch the New Availability Group Wizard
Click Next to proceed
Provide the name for the Availability group you want to create:
Select the database we created earlier for the Alwayson availability group.
Specify the replicas by clicking the Add Replica button.
Once all the Replicas have been added, you will see this.
Now proceed to the listeners tab, here provide a preferred name for the listener in the Listener DNS Name field, provide the listening port and set a static IP address for the listener as shown:
Set the preferred data synchronization method. The default selection is Automatic seeding, which is still okay.
Validate the made configs. If all is okay, proceed.
A summary of the made settings will be provided
Once finished, you will be able to view the status of the Availability group by selecting show dashboard as shown:
You can also verify access by connecting to the listener using sqlcmd as shown:
sqlcmd -S listener01 -Q "select @@servername"
##OR
sqlcmd -S 192.168.200.75 -Q "select @@servername"Sample output:
#4. Configure Failover Cluster on SQL Server 2022
Configuring the Failover Cluster on SQL Server 2022 involves running Server failover cluster installation on all the nodes. For this guide, we will have two phases.
a. Configure Failover Cluster on SQL Server 2022 on Primary Node
After following all the above steps of configuring WSFC, installing the SQL server 2022, and creating users for remote authentication, we come to yet another stage.
Here, we will run the SQL Server installation program on the selected Primary node and select the New SQL Server failover cluster installation option.
Ensure that you pass this validation. If you encounter issues with the Microsoft Cluster Service(MCS), all you need is to obtain a good report by validating your cluster(You can bypass the services causing errors during cluster validation). If all is well, you will see this:
Select the features to be installed as shown:
Configure the SQL Server Network Name.
Set Cluster Resource Group.
Choose the cluster disk for the SQL Server Databases. Here you can select the shared storage set for the cluster.
Set the cluster network and provide an available IP for the SQL Server Failover Cluster Instance
Configure the service accounts, and set the admin users for the SQL Server Database Engine and SQL Server Agent service
Proceed with the rest of the steps as we did for the stand-alone installation.
b. Configure Failover Cluster on SQL Server 2022 on Secondary Nodes
On the secondary nodes, we need to install the SQL Server Failover Cluster Instance as well. Run the SQL Server installer and select the Add node to a SQL Server failover cluster option
Perform system checks:
Provide the node settings for the cluster as shown. The cluster should be detected automatically as shown.
Configure the cluster network, the available cluster should be detected.
Next, configure the service accounts. The services set on the Primary node will be detected, so provide the passwords.
Now proceed with similar steps as for the stand-alone installation.
Once complete, the role will now be available on the WSFC management as shown:
From here, you can take note of the Cluster Network name. For this case it is MSSQL-CLUSTER1 and use it for connection as shown below.
#5. Test AlwaysOn Availability Groups in SQL Server 2022
Now to test if everything is working, we can try login to the SQL server using the SQL Server Network Name. This can be done using the SSMS as shown.
Once connected, you should see this.
You can also view the database and availability groups:
Verdict
That is it for now how to configure AlwaysOn Availability Groups in SQL Server 2022. I hope this worked for you too.
See more on this page:
- Run Microsoft SQL Server 2022 in Docker / Podman Container
- How To Install Microsoft SQL Server 2022 on Ubuntu 20.04
- Install Microsoft SQL Server 2022 on Rocky Linux 8 / AlmaLinux 8
