Configure Availability Group Read-Only and Read-Write Access and Routing in Kubernetes

Introduction

This guide describes how to configure replica access and routing for SQL Server availability groups in Kubernetes.

Prerequisites

• A DxEMSSQL cluster created and configured in Kubernetes. Instructions setting up your cluster can be found in the SQL Server Availability Groups in Kubernetes guide.

  • Ensure you're connected to this cluster with the kubectl CLI utility.

• An availability group listener. More information can be found in the External Networking and AG Listeners guide.

• Each pod must be externally accessible via a Kubernetes load balancer or an external load balancing solution. More information can be found in the external accessibility section of the networking guide.

Configure Replicas for Read-Only Access

Availability Group secondaries can be configured to allow read-only access to their databases using T-SQL.

1. Verify the details of your service configuration before continuing.

   Load Balancer

   Obtain the IP addresses for each of the load balancers assigned to your pods.

   kubectl get service

   Node Port

   Refer to your external load balancer implementation for pod IP addresses.

2. Copy the T-SQL below into a file.

   USE [master] 
   GO 
   --Only allow read-write connections in the primary role
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
       PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE)
   )
   GO
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
       PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE)
   )
   GO 
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
       PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE)
   )
   GO 
   --Allow read-only connections in the secondary role
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
       SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
   )
   GO
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
       SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
   )
   GO 
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
       SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
   )
   GO  

3. Copy the T-SQL to the /var/opt/mssql folder on the primary pod.

   kubectl cp read-only-access.sql dxemssql-0:/var/opt/mssql -c sql
   Which pod is my primary?

   If you're unsure which pod is your primary, run the command dxcli get-ags-detail and search for <replica server name> after <role_desc>.

   kubectl exec -c dxe dxemssql-0 -- dxcli get-ags-detail vhost1 ags1 | grep PRIMARY

   If dxemssql-0 isn't the primary, you can fail your availability group back to dxemssql-0 using the command dxcli vhost-start-node.

   kubectl exec -c dxe dxemssql-0 -- dxcli vhost-start-node vhost1 dxemssql-0

4. Execute the T-SQL, replacing <sql_password> with the SQL Server password.

   kubectl exec -c sql dxemssql-0 -- /opt/mssql-tools/bin/sqlcmd \
       -S localhost -U SA -P <sql_password> -i /var/opt/mssql/read-only-access.sql

Verify Access

1. Copy the YAML configuration below to a file. This will create a mssql-tools pod within the Kubernetes cluster which you will use to access the AG. Alternatively, you can install sqlcmd on a remote machine outside the cluster.

   apiVersion: v1
   kind: Pod
   metadata:
     name: mssql-tools
   spec:
     containers:
     - name: sqlcmd
       image: mcr.microsoft.com/mssql-tools
       command: ["/bin/bash", "-c", "--"]
       args: ["while true; do sleep 30; done;"]
       imagePullPolicy: IfNotPresent
     restartPolicy: Always

2. Apply the configuration.

   kubectl apply -f mssql-tools.yaml

3. Create a database table on the primary using the mssql-tools pod. Replace <primary_lb_ip> with the IP address of your primary pod, and <sql_password> with the SQL Server password.

   kubectl exec mssql-tools -- /opt/mssql-tools/bin/sqlcmd \
       -S <primary_lb_ip> -U sa -P <sql_password> -Q "CREATE TABLE db1.dbo.table1 (column_1 int)"
   info

   If a database isn't available in the AG, create one using the instructions from the Kubernetes Availability Groups guide.

4. Now read the table contents from one of the secondaries, using the <secondary_lb_ip> instead.

   kubectl exec mssql-tools -- /opt/mssql-tools/bin/sqlcmd \
       -S <secondary_lb_ip> -U sa -P <sql_password> -K ReadOnly \
       -d db1 -Q "SELECT column_1 FROM db1.dbo.table1"
   sqlcmd parameters

   sqlcmd requires some additional parameters:

   • -K ReadOnly specifies read-only intent.
   • -d db1 issues a USE database_name statement for the connection.

5. Finally, attempt to read from the primary using ReadOnly intent. This command should fail.

   kubectl exec mssql-tools -- /opt/mssql-tools/bin/sqlcmd \
       -S <primary_lb_ip> -U sa -P <sql_password> -K ReadOnly \
       -d db1 -Q "SELECT column_1 FROM db1.dbo.table1"

Configure Read-Only Routing with Load Balancing

Now that your replicas are configured for read-only access, you can configure the availability group so listener connections with read-only intent are routed to the secondary replicas.

1. Verify your listener port using the command dxcli get-ags-detail.

   kubectl exec -c dxe dxemssql-0 -- dxcli get-ags-detail vhost1 ags1 | grep listener
   tip

   If your listener port isn't set, use the command dxcli add-ags-listener.

2. Copy the T-SQL below into a file, replacing <lb_ip> with the IP address of the load balancer associated with that pod.

   USE [master] 
   GO 
   --Update the read-only routing URLs
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
       SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://<lb_ip>:1433') 
   )
   GO 
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
       SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://<lb_ip>:1433') 
   )
   GO 
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
       SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://<lb_ip>:1433') 
   )
   GO
   --Update the read-only routing list with AG members in load-balanced configuration
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
       PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-1', 'dxemssql-2')))
   ) 
   GO 
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
       PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-0', 'dxemssql-2')))
   ) 
   GO 
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
       PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-0', 'dxemssql-1')))
   )
   GO 
   routing priority

   The normal behavior of SQL Server is to route incoming read-only connections to the first available replica on the list, with no regard to load balancing. Adding an extra set of parentheses around the two secondary replicas configures SQL Server to load balance the two secondaries. This feature is included in the T-SQL example above.

3. Copy the T-SQL to the /var/opt/mssql folder on the primary pod.

   kubectl cp read-routing.sql dxemssql-0:/var/opt/mssql -c sql

4. Execute the T-SQL.

   kubectl exec -c sql dxemssql-0 -- /opt/mssql-tools/bin/sqlcmd \
       -S localhost -U SA -P <sql_password> -i /var/opt/mssql/read-routing.sql

5. Test a read-intent connection to the AG listener on dxemssql-0. This command should output the name of one of the secondaries.

   kubectl exec mssql-tools -- /opt/mssql-tools/bin/sqlcmd \
       -S <primary_lb_ip>,<ag_listener> -U sa -P <sql_password> -M \
       -K ReadOnly -d db1 -Q "SELECT column_1 FROM db1.dbo.table1; SELECT @@SERVERNAME"

Enable Read-Write Routing to the Primary

In addition to routing read-only intent connections from the primary to the secondaries, you can also route read-write intent connections from the secondaries to the primary.

1. Copy the T-SQL below to a file, replacing <lb_ip> with the IP address of the load balancer associated with that pod.

   USE [master] 
   GO 
   --Allow all connections in the secondary role instead of READ_ONLY
   --Read-write intent connections will be routed to the primary
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
       SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
   )
   GO
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
       SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
   )
   GO 
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
       SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
   )
   GO
   --Update the read-write routing URLs
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
       PRIMARY_ROLE (READ_WRITE_ROUTING_URL = 'TCP://<lb_ip>:1433')
   )
   GO
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
       PRIMARY_ROLE (READ_WRITE_ROUTING_URL = 'TCP://<lb_ip>:1433')
   )
   GO
   ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
       PRIMARY_ROLE (READ_WRITE_ROUTING_URL = 'TCP://<lb_ip>:1433')
   )
   GO

2. Copy the T-SQL to the primary pod.

   kubectl cp write-routing.sql dxemssql-0:/var/opt/mssql -c sql

3. Execute the T-SQL.

   kubectl exec -c sql dxemssql-0 -- /opt/mssql-tools/bin/sqlcmd \
       -S localhost -U SA -P <sql_password> -i /var/opt/mssql/write-routing.sql

4. Test a read-write connection to the SQL listener on dxemssql-1 or dxemssql-2.

   kubectl exec mssql-tools -- /opt/mssql-tools/bin/sqlcmd \
       -S <secondary_lb_ip> -U sa -P <sql_password> -M -d db1 \
       -Q "CREATE TABLE db1.dbo.table2 (column_1 int); SELECT @@SERVERNAME"

Additional Information

• Microsoft - Primer on Offloading Read-Only Workloads to Secondary Replicas

• Microsoft - Configure Read-Only Access to a Secondary Replica

• Microsoft - Configure Read-Only Routing

• Microsoft - Configure Load-Balancing Across Read-Only Replicas

• Microsoft - Read-Write Connection Redirection