GCP — SQL Server AO-AG with Single Subnet

Introduction

SQL Server Always On Availability Groups (AOAG) allows users to deploy highly available and automated failover with SQL Server databases. It is commonly deployed using multi-subnets in Google Cloud.

However, sometimes it might be desirable to deploy it in a single subnet configuration. Which could be because network design was planned only to have one subnet per region and adding new subnets might be difficult.

Note: The multi-subnet deployment configuration is simpler (less components) than single subnet configuration. Hence, prefer to use multi-subnet if you can.

Understanding issue with single subnet

Typically, availability groups within a single subnet needs to have floating ip. In GCP true floating IPs is not available. Therefore below solution is a workaround to mimic floating ip by using internal load balancer (ILB) component of GCP.

Architecture

The SQL Server nodes will be part of Windows Server Failover Cluster (WSFC) group, with always on enabled. AG-Listener’s IP coinciding with ILB (internal load balancer’s) IP. This means, any traffic sent to ILB will get routed to AG-Listener. This creates an illusion (for SQL Server) that Listener IP is floating between nodes. In reality that IP (eg. 10.128.0.20 in diagram below) is only attached to ILB and NOT to the SQL Server nodes. A health check is created on WSFC layer using which ILB is able to route traffic to the current primary.

SQL Server Availability Group in single subnet

Sequence of resource deployment is important here, AG-Listener should be created first and ILB should be created after that only. When AG-Listener is created windows/sql server automatically checks if that IP is already in use or not. Hence, if ILB is created first AG-Listener creation will fail.

Health check on WSFC is created via below powershell configuration on WSFC Cluster. Example health check code as below. It enables port 59997 to accept connections on whichever sql server node is primary.

$cluster_network_name = 'Cluster Network 1'
$ip_resource_name = 'sql-ag_10.128.0.20'
$load_balancer_ip = '10.128.0.20'
[int]$health_check_port = 59997
Get-ClusterResource $ip_resource_name |
  Set-ClusterParameter -Multiple @{ 'Address'=$load_balancer_ip;
                                    'ProbePort'=$health_check_port;
                                    'SubnetMask'='255.255.240.0';
                                    'Network'=$cluster_network_name;
                                    'EnableDhcp'=0; }

Corresponding to above below is health check configuration need on ILB layer to probe 59997 port and determine the primary node.

gcloud compute health-checks create tcp sql-healthcheck \
    --check-interval="2s" \
    --healthy-threshold=1 \
    --unhealthy-threshold=2 \
    --port=59997 \
    --request=10.128.0.20 \
    --timeout="1s"

Detailed implementation steps

https://cloud.google.com/community/tutorials/sql-server-ao-single-subnet
I have published detailed step by step instructions as above.