Before starting, ensure the following requirements are met:
sudo timedatectl set-ntp true) to ensure all nodes have synchronized time.Import Microsoft’s GPG Key:
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
Register the SQL Server Repository:
Replace <version> with your Ubuntu version (e.g., 22.04):
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/<version>/prod.list)"
Install SQL Server:
sudo apt-get update
sudo apt-get install -y mssql-server
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
Enable TCP/IP Protocol:
Connect to SQL Server via sqlcmd and enable TCP/IP:
sqlcmd -S localhost -U SA -P '<YourStrongPassword>'
Execute in the SQL prompt:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'remote access', 1;
RECONFIGURE;
GO
Create a Pacemaker Login (for cluster management):
CREATE LOGIN [pacemakerLogin] WITH PASSWORD = N'<StrongPassword>';
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 '<StrongPassword>' >> ~/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;
Pacemaker is the cluster manager for SQL Server on Ubuntu. Follow these steps on all nodes:
Install Pacemaker and Dependencies:
sudo apt-get install -y pacemaker pcs fence-agents resource-agents
Set the Pacemaker Password:
All nodes must have the same password for the hacluster user:
sudo passwd hacluster
Authorize Nodes to Join the Cluster: On the primary node, run:
sudo pcs cluster auth <node1> <node2> ... <nodeN> -u hacluster
Replace <node1>, <node2>, etc., with your node hostnames/IPs.
Create and Start the Cluster: On the primary node, execute:
sudo pcs cluster setup --name <ClusterName> <node1> <node2> ... <nodeN> --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
Verify Cluster Status: Check if all nodes are online:
sudo pcs status
You should see all nodes in a “Online” state.
Install the SQL Server HA Resource Package: This package provides Pacemaker integration for SQL Server AGs:
sudo apt-get install -y mssql-server-ha
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
YourDatabase with your database name.SYNCHRONOUS_COMMIT ensures data consistency; use ASYNCHRONOUS_COMMIT for better performance (but higher risk of data loss).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
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
Add the Database to the Listener:
ALTER AVAILABILITY GROUP [MyAG]
ADD DATABASE [YourDatabase];
GO
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;
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
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
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
Verify Cluster Resources: Check the status of all resources:
sudo pcs status
You should see:
ag_cluster) in a “Master” state on the primary node.virtualip) bound to the AG node.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.
Update DNS:
Point your AG listener name (e.g., myaglistener.example.com) to the virtual IP (e.g., 10.0.0.100).
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 '<YourStrongPassword>'
Run queries to confirm access to the database. If the primary node fails, the connection will automatically redirect to the new primary.
hacluster password matches across all nodes.sudo pcs status to check for errors; common issues include network timeouts or storage unavailability./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.
