SQL Server standalone

In this guide, we will walk you through setting up your first database cluster running on SQL Server standalone 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).

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 one host (bare-metal or virtual machines) installed with the supported operating system. See Operating System.
SSH access to the server involved.
The node must have internet access during the deployment stage.

Architecture

Below is a simplified diagram of the final architecture:

flowchart TD
    A{{**Users/<br>clients/<br>apps**}} --> |RW/RO| B[**192.168.99.100**<br>SQL Server] 
    E[/**ClusterControl<br>192.168.99.5**/] -.- |manages| B
    subgraph SQL Server Standalone
        B
    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 the SQL Server node:

ssh-copy-id -i ~/.ssh/id_rsa [email protected]

Test the connection:
```
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-Server
```

Step 2: Deploy SQL Server standalone

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-Server" as the primary node and press Enter.

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

Proceed only if the node is 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 host and install SQL Server. 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 the 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 SQL Server 192.168.99.100 is 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.

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 standalone, 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, postfi,x 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 to 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. These features are available at ClusterControl GUI → choose the cluster → Nodes → Actions.
Configuration management: Perform database configuration changes. 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.

Conclusion

Congratulations! You’ve successfully deployed, monitored, managed, and scaled your first SQL Server Standalone 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.