SQL Server on Ubuntu: Cluster Configuration Guide (Using Pacemaker)

Prerequisites

Before starting, ensure the following requirements are met:

• Ubuntu Version: Use Ubuntu 18.04 LTS or later (20.04/22.04 recommended for long-term support).
• SQL Server Version: Install SQL Server 2017 or later (Enterprise Edition is required for Always On Availability Groups).
• Network Configuration: All cluster nodes must be on the same subnet, with firewall rules allowing ports:
  • 1433 (SQL Server default instance)
  • 5022 (Availability Group endpoint)
  • 2224/tcp, 3121/tcp, 21064/tcp, 5405/udp (Pacemaker/Corosync communication)
• Time Synchronization: Use NTP (e.g., sudo timedatectl set-ntp true) to ensure all nodes have synchronized time.
• Storage: Shared storage (e.g., SAN, NAS) is not required for Basic Availability Groups, but recommended for data redundancy.

Step 1: Install SQL Server on All Cluster Nodes

1. Import Microsoft’s GPG Key:

   curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
   

2. Register the SQL Server Repository: Replace with your Ubuntu version (e.g., 22.04):

   sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu//prod.list)"
   

3. Install SQL Server:

   sudo apt-get update
   sudo apt-get install -y mssql-server
   

4. Configure SQL Server: Run the setup wizard to set the SA password and enable auto-start:

   sudo /opt/mssql/bin/mssql-conf setup
   

   Verify the service is running:

   sudo systemctl status mssql-server
   

5. Enable TCP/IP Protocol: Connect to SQL Server via sqlcmd and enable TCP/IP:

   sqlcmd -S localhost -U SA -P ''
   

   Execute in the SQL prompt:

   EXEC sp_configure 'show advanced options', 1;
   RECONFIGURE;
   EXEC sp_configure 'remote access', 1;
   RECONFIGURE;
   GO
   

6. Create a Pacemaker Login (for cluster management):

   CREATE LOGIN [pacemakerLogin] WITH PASSWORD = N'';
   ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin];
   GO
   

   Save credentials to /var/opt/mssql/secrets/passwd (readable only by root):

   echo 'pacemakerLogin' >> ~/pacemaker-passwd
   echo '' >> ~/pacemaker-passwd
   sudo mv ~/pacemaker-passwd /var/opt/mssql/secrets/
   sudo chown root:root /var/opt/mssql/secrets/passwd
   sudo chmod 400 /var/opt/mssql/secrets/passwd
   

   Exit sqlcmd:

   EXIT;
   

Step 2: Set Up Pacemaker Cluster

Pacemaker is the cluster manager for SQL Server on Ubuntu. Follow these steps on all nodes:

1. Install Pacemaker and Dependencies:

   sudo apt-get install -y pacemaker pcs fence-agents resource-agents
   

2. Set the Pacemaker Password: All nodes must have the same password for the hacluster user:

   sudo passwd hacluster
   

3. Authorize Nodes to Join the Cluster: On the primary node, run:

   sudo pcs cluster auth   ...  -u hacluster
   

   Replace , , etc., with your node hostnames/IPs.

4. Create and Start the Cluster: On the primary node, execute:

   sudo pcs cluster setup --name    ...  --start --all
   

   Example:

   sudo pcs cluster setup --name SQLCluster node1 node2 --start --all
   

   Enable the cluster to start on boot:

   sudo pcs cluster enable --all
   

5. Verify Cluster Status: Check if all nodes are online:

   sudo pcs status
   

   You should see all nodes in a “Online” state.

Step 3: Configure SQL Server Always On Availability Group (AG)

1. Install the SQL Server HA Resource Package: This package provides Pacemaker integration for SQL Server AGs:

   sudo apt-get install -y mssql-server-ha
   

2. Create the Availability Group: Connect to the primary replica via sqlcmd and run:

   CREATE AVAILABILITY GROUP [MyAG]
   FOR DATABASE [YourDatabase]
   REPLICA ON
       N'node1' WITH (
           ENDPOINT_URL = N'TCP://node1:5022',
           AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
           FAILOVER_MODE = MANUAL,
           SEEDING_MODE = AUTOMATIC
       ),
       N'node2' WITH (
           ENDPOINT_URL = N'TCP://node2:5022',
           AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
           FAILOVER_MODE = MANUAL,
           SEEDING_MODE = AUTOMATIC
       );
   GO
   

   • Replace YourDatabase with your database name.
   • SYNCHRONOUS_COMMIT ensures data consistency; use ASYNCHRONOUS_COMMIT for better performance (but higher risk of data loss).

3. Join Secondary Replicas: On each secondary node, connect to SQL Server and join the AG:

   ALTER AVAILABILITY GROUP [MyAG]
   JOIN WITH (
       ENDPOINT_URL = N'TCP://node1:5022',
       AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
       FAILOVER_MODE = MANUAL
   );
   GO
   

4. Create an AG Listener: A listener provides a single endpoint for client connections. Run on the primary node:

   CREATE AVAILABILITY GROUP LISTENER [MyAGListener]
   WITH IP ((N'10.0.0.100', N'255.255.255.0')), -- Replace with your virtual IP
   PORT = 1433;
   GO
   

5. Add the Database to the Listener:

   ALTER AVAILABILITY GROUP [MyAG]
   ADD DATABASE [YourDatabase];
   GO
   

6. Verify AG Status: Check the AG and replica states:

   SELECT * FROM sys.dm_hadr_availability_group_states;
   SELECT * FROM sys.dm_hadr_replica_states;
   

   Exit sqlcmd:

   EXIT;
   

Step 4: Integrate AG with Pacemaker

1. Create the AG Resource in Pacemaker: On the primary node, run:

   sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=MyAG --master meta notify=true
   

2. Create a Virtual IP Resource: This IP will be used by clients to connect to the AG:

   sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=10.0.0.100 cidr_netmask=24
   

3. Configure Resource Dependencies: Ensure the virtual IP starts after the AG and fails over with it:

   sudo pcs constraint colocation add virtualip ag_cluster-master INFINITY with-rsc-role=Master
   sudo pcs constraint order promote ag_cluster-master then start virtualip
   

4. Verify Cluster Resources: Check the status of all resources:

   sudo pcs status
   

   You should see:

   • The AG resource (ag_cluster) in a “Master” state on the primary node.
   • The virtual IP resource (virtualip) bound to the AG node.

5. Test Failover: Simulate a node failure by stopping the cluster on the primary node:

   sudo pcs cluster stop node1
   

   Check the status again—Pacemaker should automatically promote the secondary node to primary and move the virtual IP. Restart the stopped node to verify it rejoins the cluster as secondary.

Step 5: Validate Client Connectivity

1. Update DNS: Point your AG listener name (e.g., myaglistener.example.com) to the virtual IP (e.g., 10.0.0.100).

2. Connect Using the Listener: Use SQL Server Management Studio (SSMS) or sqlcmd to connect to the listener:

   sqlcmd -S myaglistener.example.com -U SA -P ''
   

   Run queries to confirm access to the database. If the primary node fails, the connection will automatically redirect to the new primary.

Troubleshooting Tips

• Firewall Issues: Ensure all required ports are open on all nodes.
• Credential Errors: Verify the hacluster password matches across all nodes.
• Resource Failures: Use sudo pcs status to check for errors; common issues include network timeouts or storage unavailability.
• SQL Server Logs: Check /var/opt/mssql/log/errorlog for SQL Server-specific errors.

By following these steps, you’ll have a highly available SQL Server cluster on Ubuntu using Pacemaker and Always On Availability Groups. This setup ensures minimal downtime for your databases and automatic failover in case of node failures.