Set up a SQL Server cluster on Linux with Always On availability groups and Pacemaker Stay organized with collections Save and categorize content based on your preferences.

Prepare the project and network

To prepare your Google Cloud project and VPC for the deployment of SQL Server Always On availability groups, do the following:

1. In the Google Cloud console, open Cloud Shell by clicking the Activate Cloud Shell [Activate Cloud Shell.] button.

   Go to the Google Cloud console

2. Set your default project ID:

   gcloud config set project PROJECT_ID
   

   Replace PROJECT_ID with the ID of your Google Cloud project.

3. Set your default region:

   gcloud config set compute/region REGION
   

   Replace REGION with the ID of the region you want to deploy in.

4. Set your default zone:

   gcloud config set compute/zone ZONE
   

   Replace ZONE with the ID of the zone you want to deploy in. It should be a valid zone in the region specified in the previous step.

Create Linux VMs

To achieve HA and quorum for the SQL Server cluster deploy three Linux virtual machines (VMs) to host the SQL Server cluster.

1. Initialize the following variables:

   PD_SIZE=30
   MACHINE_TYPE=n2-standard-8
   

2. Create the Linux VMs:

   gcloud compute instances create node-1 \
   --project=PROJECT_ID \
   --zone REGION-a \
   --machine-type $MACHINE_TYPE \
   --subnet SUBNET_NAME \
   --create-disk=auto-delete=yes,boot=yes,device-name=node-1,image=projects/ubuntu-os-cloud/global/images/ubuntu-2004-focal-v20240426,mode=rw,size=$PD_SIZE,type=projects/PROJECT_ID/zones/REGION-a/diskTypes/pd-balanced \
   --scopes=https://www.googleapis.com/auth/compute,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly,https://www.googleapis.com/auth/logging.write,https://www.googleapis.com/auth/monitoring.write,https://www.googleapis.com/auth/trace.append,https://www.googleapis.com/auth/devstorage.read_write
   
   gcloud compute instances create node-2 \
   --project=PROJECT_ID \
   --zone REGION-b \
   --machine-type $MACHINE_TYPE \
   --subnet SUBNET_NAME \
   --create-disk=auto-delete=yes,boot=yes,device-name=node-2,image=projects/ubuntu-os-cloud/global/images/ubuntu-2004-focal-v20240426,mode=rw,size=$PD_SIZE,type=projects/PROJECT_ID/zones/REGION-b/diskTypes/pd-balanced \
   --scopes=https://www.googleapis.com/auth/compute,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly,https://www.googleapis.com/auth/logging.write,https://www.googleapis.com/auth/monitoring.write,https://www.googleapis.com/auth/trace.append,https://www.googleapis.com/auth/devstorage.read_write
   
   gcloud compute instances create node-3 \
   --project=PROJECT_ID \
   --zone REGION-c \
   --machine-type $MACHINE_TYPE \
   --subnet SUBNET_NAME \
   --create-disk=auto-delete=yes,boot=yes,device-name=node-3,image=projects/ubuntu-os-cloud/global/images/ubuntu-2004-focal-v20240426,mode=rw,size=$PD_SIZE,type=projects/PROJECT_ID/zones/REGION-c/diskTypes/pd-balanced \
   --scopes=https://www.googleapis.com/auth/compute,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly,https://www.googleapis.com/auth/logging.write,https://www.googleapis.com/auth/monitoring.write,https://www.googleapis.com/auth/trace.append,https://www.googleapis.com/auth/devstorage.read_write
   

   Replace SUBNET_NAME with the name of your VPC subnet.

3. Update the hosts file on node-1, node-2, and node-3:

   Note: For production use, the domain resolution should be managed by a dedicated Domain Name System (DNS) server.
   1. Connect to each of your VMs using SSH. Refer to the Connect to Linux VMs documentation for more information.

   2. Open the hosts file for edit.

      sudo vi /etc/hosts
      

   3. Find the internal IP address for each Linux VM and append the host entries to the bottom of the file.

      Go to Compute Engine

      NODE1_INTERNAL_IP node-1
      NODE2_INTERNAL_IP node-2
      NODE3_INTERNAL_IP node-3
      

      Replace NODE1_INTERNAL_IP, NODE2_INTERNAL_IP and NODE3_INTERNAL_IP with the internal IP address of each Linux VM.

4. Check the communication between your VMs. All VMs that participate in the Always On availability group must be able to communicate with other VMs:

   1. Return to each Linux VM, run the commands from each VM, and verify that all VMs can communicate with each other.

      ping -c 4 node-1
      ping -c 4 node-2
      ping -c 4 node-3
      

Install and configure SQL Server

Download, install and configure the SQL Server engine on the three Linux VMs that will participate in the Always On availability group.

1. SSH to node-1, node-2, and node-3 and run the following steps:

   1. Import the public repository keys.

      wget -qO- https://packages.microsoft.com/keys/microsoft.asc \
      | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
      

   2. Register the SQL Server Ubuntu repository.

      sudo add-apt-repository \
      "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"
      

   3. Update the package index files and install SQL Server.

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

2. Configure SQL Server:

   1. Run the mssql-conf tool.

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

   2. Choose Developer edition for the SQL Server edition and accept the license agreement.

      The developer edition has all the enterprise features included, but you can use it only for non-production environments. More information is available regarding SQL Server editions and Microsoft licenses.

      Note: For each VM instance a SQL Server license is required. If you are running Microsoft application servers, License Mobility through Software Assurance helps you transition to Google Cloud. For more information, see Using License Mobility with Microsoft server applications. If you are not bringing your own license and require to purchase a license through Google (PAYG), follow the steps in Add a SQL Server license to a boot disk to ensure you purchase a license for each VM instance.

   3. Specify a password for the SA account.

   4. Verify that the mssql-server service is running.

      systemctl status mssql-server --no-pager
      

3. If you have a firewall enabled on your VMs, open the firewall for SQL Server:

   1. Check if Uncomplicated Firewall is installed and enabled by running the following command.

      sudo ufw status
      

   2. If the status is active, run the following commands to open the ports.

      sudo ufw allow 1433
      sudo ufw allow 5022
      sudo ufw reload
      

      Note: If you are using other ports for SQL Server or for your availability group endpoint, change the firewalls rules accordingly.

Connect to SQL Server

At this point, SQL Server is installed. To connect to it, create a windows machine in the same VPC, install SQL Server management Studio (SSMS) to connect to your newly created SQL Server instance on your VMs:

1. Create a Windows VM:

   1. Return to your Cloud Shell and run the following command.

      gcloud compute instances create node4 \
      --project=PROJECT_ID \
      --zone ZONE \
      --subnet SUBNET_NAME \
      --machine-type=n2-standard-4 \
      --create-disk=auto-delete=yes,boot=yes,device-name=node4,image=projects/windows-cloud/global/images/windows-server-2022-dc-v20240415,mode=rw,size=50,type=projects/p3rf-sqlserver/zones/ZONE/diskTypes/pd-balanced
      

2. Connect to the Windows VM on node-4 using Remote Desktop:

3. Update the hosts file on node-4:

   1. Open notepad in administrator mode.

   2. Click File > Open and open the hosts file.

      sudo vi /etc/haproxy/haproxy.cfg
      

   3. In the defaults section of the haproxy.cfg file, change the mode to tcp.

   4. Append the following section at the end of the haproxy.cfg file

      sudo systemctl start haproxy.service
      sudo systemctl enable haproxy.service
      sudo systemctl restart haproxy.service
      

   5. Go to the Load balancing page, click your load balancer. Observe your three unmanaged instance groups, they should now report as healthy.

      Go to Load balancing

      • Alternatively, you can run the following command in the Cloud Shell to see the status of the backend service.

        gcloud compute backend-services get-health aoag1-backend \
        --region REGION
        

        Replace REGION with the region where the Linux VMs are deployed.

   6. Once all three unmanaged instance groups are reporting healthy, continue to the next step.

      Note: If the unmanaged instance group does not report healthy, you may need to restart the haproxy.service from the node in question.

      sudo systemctl restart haproxy.service
      

4. Create the health check resource in Pacemaker:

   1. SSH to node-1 and create a health check resource for the HAProxy service in your pacemaker cluster:

      sudo crm status
      

   2. If the health check resource is not started on the primary node, move it with the following commands:

      sudo pcs resource move aoag1-healthcheck node-1
      sudo pcs resource clear aoag1-healthcheck
      

      You will see that the health check for the load balancer will be healthy only for node-1.

      Go to Load balancing

5. Create a virtual IP address resource in your Pacemaker cluster:

   1. Return to SSH on node-1 and find the name of the network interface of your node. You will need it in the next step.

      ip -c link
      

   2. Create the virtual IP address resource.

      sudo pcs resource create aoag1-vip ocf:heartbeat:IPaddr2 \
      ip="CLUSTER_ADDRESS" nic=NIC_NAME cidr_netmask=32 \
      op monitor interval=3600s timeout=60s
      

      Replace NIC_NAME with the network interface name from the previous step and CLUSTER_ADDRESS with the reserved IP address.

   3. Check that the virtual IP address resource is started on the primary host.

      sudo crm status
      

   4. If the virtual IP address resource is not started on the primary node, move it with the following commands.

      sudo pcs resource move aoag1-vip node-1
      

   5. Group the health check and virtual IP address resources together.

      sudo pcs resource group add aoag1-group \
      aoag1-healthcheck aoag1-vip
      

   6. Create a constraint that locates the new group on the same node as the primary.

      sudo pcs constraint colocation add master aoag1-group with master ms-ag1 score=INFINITY
      

Create a listener for your SQL Server availability group

Connections to SQL Server with availability groups should use an availability group listener name instead of the server name. If there is a failover, the listener will automatically redirect connections to the new primary node in the cluster.

1. Return to SSMS and connect to the node-1 database.

2. Run the following query:

   ALTER AVAILABILITY GROUP aoag1
   ADD LISTENER 'aoag1-listener' (
       WITH IP (('CLUSTER_ADDRESS','255.255.255.0')), PORT=1433
   );
   GO
   

   Replace CLUSTER_ADDRESS with the reserved IP address.

Set up a STONITH fence

STONITH is a fencing strategy for maintaining the integrity of nodes in a HA cluster. STONITH service works at the node level and protects the cluster from nodes that are either unresponsive or in an unknown state. We recommend the fence_gce fencing device specialized for Compute Engine on Google Cloud.

Set up fencing devices

1. Check if the fence_gce - Fence agent for Compute Engine is installed on node1:

   sudo pcs stonith list | grep fence_gce
   

   For more information, see:

   • Fence agent for Google Compute Engine.

   • To see the parameters associated with the agent run.

     sudo pcs stonith describe fence_gce
     

2. On node-1, create the fence_gce fencing type resources for each of the participating nodes:

   sudo pcs stonith create node-1-fence fence_gce \
   plug=node-1 \
   zone=REGION-a \
   project=PROJECT_ID \
   pcmk_reboot_timeout=300 pcmk_monitor_retries=4 pcmk_delay_max=30 \
   op monitor interval="300s" timeout="120s" \
   op start interval="0" timeout="60s"
   
   sudo pcs stonith create node-2-fence fence_gce \
   plug=node-2 \
   zone=REGION-b \
   project=PROJECT_ID \
   pcmk_reboot_timeout=300 pcmk_monitor_retries=4 pcmk_delay_max=30 \
   op monitor interval="300s" timeout="120s" \
   op start interval="0" timeout="60s"
   
   sudo pcs stonith create node-3-fence fence_gce \
   plug=node-3 \
   zone=REGION-c \
   project=PROJECT_ID \
   pcmk_reboot_timeout=300 pcmk_monitor_retries=4 pcmk_delay_max=30 \
   op monitor interval="300s" timeout="120s" \
   op start interval="0" timeout="60s"
   

   Replace REGION with the region where the Linux VMs are deployed and replace PROJECT_ID with your project ID.

3. You can test the status of the fencing agents by running the status command:

   sudo fence_gce -o status -n node-1 --zone=REGION-a
   sudo fence_gce -o status -n node-2 --zone=REGION-b
   sudo fence_gce -o status -n node-3 --zone=REGION-c
   

4. Create location constraints for your fencing devices to ensure that they are running only on the intended instances:

   sudo pcs constraint location node-1-fence avoids node-1
   sudo pcs constraint location node-2-fence avoids node-2
   sudo pcs constraint location node-3-fence avoids node-3
   

5. Enable fencing in your pacemaker cluster and set the cluster fencing timeout:

   sudo pcs -f stonith_cfg property set stonith-enabled=true
   sudo pcs property set stonith-timeout="300s"
   

6. Check the status of the cluster:

   sudo crm status
   

Test the fencing devices

After the setup of the fencing devices, we recommend you test them using the following steps.

1. Stop the fence on node-2:

   1. Connect to node-1 and run the following command to test the fence device associated with node-2 from your cluster.

      fence_gce -o off -n node-2 --zone=REGION-b
      

   2. Check the status of the cluster.

      sudo crm status
      

   3. You will also see that node-2 is turned off in Compute Engine.

      Go to Compute Engine

2. Restart the fence on node-2:

   1. Return to node-1 and restart the instance again by running the following command.

      fence_gce -o on -n node-2 --zone=REGION-b
      

   2. Check the status of the cluster in Pacemaker and Compute Engine. After a short time, you will see that node-2 is back online.

      sudo crm status
      

Configure Corosync for delayed restart

To avoid timing issues and ensure a proper order of operations performed in case of a fencing action, we recommend delaying the restart of Corosync service for 60 seconds.

For more information, see the Red Hat knowledgebase article.

1. Create a systemd drop-in file that sets a delay of the Corosync service start on node-1, node-2, and node-3:

   1. Open the corosync.service for edit.

      sudo systemctl edit corosync.service
      

   2. Append the following lines, save the file and exit the editor.

      [Service]
      ExecStartPre=/bin/sleep 60
      

   3. Reload the service manager and check if the configuration is taken into account.

      sudo systemctl daemon-reload
      systemctl status corosync.service --no-pager
      
      

      If you see the Drop-In section, then the settings in your drop-in file were successfully taken into account

Test failover

You are now ready to test if the failover works as expected.

1. Connect to the Windows VM on node-4 through Remote Desktop:
2. Open a PowerShell session:

3. Run the following script:

   while ($True){
     $Conn = New-Object System.Data.SqlClient.SqlConnection
     $Conn.ConnectionString = "Server=CLUSTER_ADDRESS;User ID=sa;Password=SA_PASSWORD;Initial Catalog=master"
     $Conn.Open()
   
     $Cmd = New-Object System.Data.SqlClient.SqlCommand
     $Cmd.Connection = $Conn
     $Cmd.CommandText = "SELECT @@SERVERNAME"
   
     $Adapter = New-Object System.Data.SqlClient.SqlDataAdapter $Cmd
     $Data = New-Object System.Data.DataSet
     $Adapter.Fill($Data) | Out-Null
     $Data.Tables[0] + (Get-Date -Format "MM/dd/yyyy HH:mm:ss")
   
     Start-Sleep -Seconds 2
   }
   

   Replace CLUSTER_ADDRESS with the listener IP address and SA_PASSWORD with the password of the SA account on SQL Server.

   Every 2 seconds, the script connects to SQL Server by using the availability group listener or DNN listener, and queries the server name.

   Leave the script running.

4. Return to SSH on node-1 and run the commands to trigger a failover to node-2:

   sudo pcs resource move ms-ag1 node-2 --master
   sudo pcs resource move aoag1-group  node-2
   sudo pcs resource move aoag1-vip node-2
   

5. Return to the PowerShell session on node-4:

   1. Observe the output of the running script and notice that the server name changes from node-1 to node-2 as a result of the failover.

6. Return to node-1 and initiate a failback to node-1:

   sudo pcs resource move ms-ag1 node-1 --master
   sudo pcs resource move aoag1-group  node-1
   sudo pcs resource move aoag1-vip node-1
   

7. Return to Powershell on node-4 and stop the script by pressing Ctrl+C.