SQL Server Always On availability group

In this guide, we will walk you through setting up your first database cluster running on SQL Server Always On availability group using ClusterControl. By the end of this tutorial, you will have a fully operational database cluster that you can monitor, manage, and scale using ClusterControl.

SQL Server is a relational database management system (RDBMS). Applications and tools connect to a SQL Server instance or database, and communicate using Transact-SQL (T-SQL).

The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Always On availability groups maximize the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

Prerequisites

Ensure the following requirements are met before proceeding:

ClusterControl should be installed and running. If not, follow the installation instructions in Quickstart or use the Installer Script.
You need at least three hosts (bare-metal or virtual machines) installed with the supported operating system. See Operating System.
SSH access to all servers involved.
All nodes must have internet access during the deployment stage.
NTP is configured and running on all hosts to ensure that all nodes in the cluster maintain synchronized time.

Architecture

Below is a simplified diagram of the final architecture:

flowchart TD
    A{{**Users/<br>clients/<br>apps**}} --> |RW| B[**192.168.99.100**<br>SQL Server Primary]
    A --> |RO| C[**192.168.99.101**<br>SQL Server Replica] & D[**192.168.99.102**<br>SQL Server Replica]
    B -.-> |replicates| C & D
    E[/**ClusterControl<br>192.168.99.5**/] -.- |manages| B & C & D
    subgraph SQL Server Always On AG
        B
        C
        D
    end

Step 1: Set up SSH key-based authentication

On the ClusterControl server, generate a new SSH key:
```
ssh-keygen -t rsa
```

Copy the SSH key to all SQL Server nodes:

ssh-copy-id -i ~/.ssh/id_rsa [email protected]  # Primary
ssh-copy-id -i ~/.ssh/id_rsa [email protected]  # Replica 1
ssh-copy-id -i ~/.ssh/id_rsa [email protected]  # Replica 2

Test the connection:

ssh [email protected] "ls /root"
ssh [email protected] "ls /root"
ssh [email protected] "ls /root"

Host mapping - The first prerequisite is updating the hosts file in all the nodes, including the ClusterControl node, with the hostnames and IP addresses of the database nodes that will participate in the availability group. The following is the expected content of /etc/hosts on every host:

127.0.1.1 ClusterControl
127.0.0.1 localhost

# The following lines are desirable for IPv6 capable hosts
::1 localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
192.168.99.100 SQL-primary
192.168.99.101 SQL-replica1
192.168.99.102 SQL-replica2

Step 2: Deploy a SQL Server Always On availability group

Open your browser and navigate to your ClusterControl server’s IP or domain name.
From the ClusterControl dashboard, click on Deploy a cluster in the top-right corner and choose Create a database cluster.
Choose SQL Server from the Database dropdown and the Vendor and Version (e.g., "Microsoft" and "2022") from the wizard. Click Continue.
In the Deploy SQL Server cluster, configure the cluster as follows:
(1) Cluster details(2) SSH configuration(3) Node configuration(4) Add nodes(5) Preview
Name: My SQL Server 2022 Cluster

Tags: sql, ag,
SSH user: root

SSH user key path: /root/.ssh/id_rsa

SSH port: 22

Install software: Enabled

Disable firewall: Checked

Disable SELinux/AppArmor: Checked
Admin username: SQLServerAdmin (default)

Admin username: Set or copy the generated password for your reference.

Server Port: 1433 (default)

Repository: Use vendor repositories (default)

Enable SSL encryption: On (default)
Primary node: (hostname is required as IP is not supported)

Specify "SQL-primary" as the primary node and press Enter.

Replica node:

Specify "SQL-replica1" as replica and press Enter.

Specify "SQL-replica2" as another replica and press Enter.

Wait until everything is green. If a red warning appears, inspect the error and fix it.

Proceed only if all nodes are reachable (shown in green).
Review the summary of your deployment. You can always go back to any previous section to modify your configurations if you wish. The deployment settings will be kept until you exit the deployment wizard.
Click Finish to begin the deployment.
ClusterControl will start to provision the hosts, install SQL Server, and configure the Always On availability group. Monitor the progress from Activity Center → Jobs.

Step 3: Monitor your cluster

After the cluster has been deployed, you will be redirected to the Home page. This page provides an at-a-glance view of your cluster's status and important aspects of a database, including:

Cluster health: The Home page provides the cluster state
Node health: Rollover on the honeycomb diagram and get a more detailed view of a particular node. You can also see more detailed histograms under ClusterControl GUI → Clusters → choose the cluster → Dashboards.
Recent alarms: View the most recent alarms of your cluster.
Automatic recovery status: Green means ClusterControl will raise an alarm and perform recovery procedures if it detects the database service is down. Red means ClusterControl will raise an alarm, but the recovery procedures will be skipped.
Topology viewer: Roll over each cluster entry to see the summary of the cluster visualized in topology view to better understand your overall database state. Also available under ClusterControl GUI → Clusters → choose the cluster → Nodes → Topology.

Step 4: Connecting to the database cluster

At this point, your application should be able to access the database cluster via the following endpoints:

The primary SQL Server is 192.168.99.100 on port 1433. We can use SQL Server Management Studio (SSMS), Azure Data Studio, or the SQLCMD tool to connect to the SQL Server. For the SSMS connection, authentication, choose SQL Server Authentication, and use the SQL credentials specified during deployment.
For read-only workloads, you may send the database connections to the replica nodes, 192.168.99.101 or 192.168.99.102, on port 1433.

Note

Most of the SQL Server drivers and application connectors come with a built-in support for Always On availability group. Check your application connector's documentation for details.

Tip

You can use popular SQL Server Management Studio (SSMS), Azure Data Studio, or SQLCMD tool to perform database administration tasks.

Step 5: Enable automatic backups

To ensure your cluster data is protected, you should schedule an automatic backup. For SQL Server Always On availability group, ClusterControl supports full, differential, and transaction log backups. To schedule a backup:

Go to ClusterControl GUI → choose the cluster → Backups to open the backup overview page of the cluster.
Go to Create Backup → Schedule a Backup to schedule an automated backup. It will open a backup wizard to follow.
In the Create a backup schedule wizard, configure the backup as below:
(1) Configuration(2) Advanced settings(3) Storage(4) Schedule(5) Preview
Schedule name: Daily full backup - SQL Server

Cluster: (leave as it is)

Backup method: (choose between full, differential, and transaction log)

Upload backup to cloud: Off
Compression: On (default)

Retention: On (default)

Include system databases: On (default)
Storage location: Store on node (default)

Storage directory: /var/lib/backups (default)

Backup subdirectory: BACKUP-%I (default)
Set backup schedule: Simple

Every: day at 2:00

Select timezone: Use your preferred timezone
Review the summary of the backup configuration. You can always go back to any previous section to modify your configurations if you wish. The backup configuration settings will be kept until you exit the wizard.
Click Create to schedule the backup.

Once configured, ClusterControl will take care of the automated backups, ensuring your data is safe. All successful backups will be listed in the Backups → All Backups page, where you can see the backup logs, size, and perform restoration if necessary.

Step 6: Configure alerts

To keep track of any issues or incidents in your cluster, it's important to set up alerting. ClusterControl supports sending alarms and alerts to email, web hooks, and third-party notification services like Slack or Telegram. In this example, we are going to use email.

Firstly, configure the mail server. Go to ClusterControl GUI → Settings → You don't have a mail server configured. Configure now → SMTP Server. Fill up all necessary information about the SMTP server. You can also opt for Sendmail; however, a mail transfer agent (sendmail, postfix, or exim) must be installed on the ClusterControl server.

Once configured, we can configure the alert and recipients as below:

Go to ClusterControl GUI → choose the cluster → Settings → Email Notifications.
Choose a user group that you belong to from the User group dropdown.
Choose your email address from the Users in the selected group dropdown.
Click Enable.
Set the Digest delivery time when you want a digested (summarized events) to be sent to you every day.
Set all Critical events to "Deliver" (default), all Warning events to "Digest" and you may ignore the Info events.

This ensures you are always notified when critical issues arise and can act quickly to resolve them. You will also get an email with a summary of warning events every day at the configured delivery time.

Tip

You can also configure alarms to be sent to third-party notification systems (Slack, Telegram), incident management systems (PagerDuty, ServiceNow, OpsGenie), or web hooks. See Integration → Notification Services.

Step 7: Manage your cluster

ClusterControl provides many other features for managing your cluster, including:

Database node management: Perform start/stop/restart node, reboot host, promote replica, rebuild replica. These features are available at ClusterControl GUI → choose the cluster → Nodes → Actions.
Configuration management: Perform database configuration changes globally or on individual database node. This feature is available at ClusterControl GUI → choose the cluster → Manage 🡒 Configuration.
Backup management: Create, schedule, restore, upload backup to the cloud, and set a retention period. These features are available at ClusterControl GUI → choose the cluster → Backups → Actions and ClusterControl GUI → choose the cluster → Backups → More.
Maintenance management: Activate, deactivate, remark, and schedule maintenance mode for all nodes. This feature is available at ClusterControl GUI → choose the cluster → Nodes → Actions → Schedule maintenance.
SSH console: Access your nodes directly from the ClusterControl GUI via web SSH console. This feature is available at ClusterControl GUI → choose the cluster → Nodes → Actions → SSH Console.

Step 8: Scale your cluster

One of the key benefits of using ClusterControl is the ease of scaling your cluster. For SQL Server Always On availability group, you can scale out the cluster with another replica. Common use cases are to use the replica node for to scale out the read-only workloads, backups, reporting, or analytics purposes.

Adding a new replica node is an online and non-blocking operation. It shall not cause downtime to the running cluster however, it will increase load on the source node due to initial RDB syncing from the primary.

ClusterControl assumes that the new node that you want to add meets the requirements as decribed under Prerequisites and configured with a proper SSH key-based authentication as shown under Step 1: Set up SSH key-based authentication.

To add a new replica, go to ClusterControl GUI → choose the cluster → Actions → Add new → Replica node.
In the Create a replica node wizard, configure the following:
(1) Node Configuration(2) Add node(3) Preview
Port: 1433

Install software: On (default)

Disable firewall: Checked (default)

Disable SELinux/AppArmor: Checked (default)
Primary node: Choose the primary node from the dropdown.

Node: Specify the node's hostname that you want to add and press Enter.
Review the summary of the deploymnent. You can always go back to any previous section to modify your configurations if you wish. The deployment configuration settings will be kept until you exit the wizard.
Click Finish to trigger the deployment job.
ClusterControl will start provisioning the new replica node. You can monitor the progress from Activity center, where you will see detailed logs of the deployment. This process can take a few minutes. Once the deployment is complete, the node will appear in the Nodes page.

Conclusion

Congratulations! You’ve successfully deployed, monitored, managed, and scaled your first SQL Server Always On availability group using ClusterControl. From here, you can explore additional features such as failover management, access control list (ACL) rules, advanced monitoring, advanced backup management, database restore, and many more. As you grow, ClusterControl will help ensure that your database infrastructure remains highly available and performant.