Skip to main content
Version: v23.0

Scaling SQL Server AG in Kubernetes

Introduction​

Kubernetes allows deployments, StatefulSets, and other configurations to be scaled up and down to meet demands. To successfully scale your SQL Server AG, some consideration must be given to your current configuration.

This guide describes how to scale a Kubernetes AG cluster in Kubernetes. Also included in this guide are steps to modifying service configurations and updating read-only/read-write replica access and routing using T-SQL.

Prerequisites​

  • A DxEnterprise + SQL Server AG running in Kubernetes. More information about creating this cluster can be found in the Kubernetes guide.

Scale the StatefulSet​

Add Replicas to the Cluster​

  1. To scale a StatefulSet up, run the command kubectl scale along with the total quantity of replicas for the StatefulSet.

    kubectl scale --replicas=4 statefulset/dxemssql

  2. Wait for the pod status to change to ready.

    kubectl get pods

  3. Activate the DxEnterprise license for the fourth pod using the command dxcli activate-server.

    kubectl exec dxemssql-3 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD

  4. Join the fourth pod to the DxEnterprise cluster using the command dxcli join-cluster-ex.

    kubectl exec dxemssql-3 -- dxcli join-cluster-ex dxemssql-0 p@ssw0rd

  5. Add the fourth pod to the existing availability group using the command dxcli add-ags-node.

    kubectl exec dxemssql-3 -- dxcli add-ags-node vhost1 ags1 "dxemssql-3|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|50224"

Remove Replicas From the Cluster​

If you want to save the volumes for the AG member fort later use, you only need to run the kubectl scale command. However, if you choose to scale down your replica and delete the volumes for that pod, you should also deactivate the DxEnterprise license.

  1. Run the command dxcli deactivate-server to deactivate the license for that pod.

    kubectl exec dxemssql-0 -- dxcli deactivate-server dxemssql-3

  2. Scale the StatefulSet down using kubectl scale.

    kubectl scale --replicas=3 statefulset/dxemssql

  3. Delete the persistent volume claims for the pod.

    kubectl delete pvc dxe-dxemssql-3
    kubectl delete pvc mssql-dxemssql-3
    danger

    Deleting volumes can result in unintended data loss. Double-check what is being deleted!

Update Network Configuration​

After you add the pod to the cluster, you might need to update one or more service configurations.

Add to Headless Service File​

Headless services allow DNS resolution in situations where NAT matchmaking may be unavailable. An initial configuration is provided in the Kubernetes guide, which can be expanded upon with the following YAML:

#Headless services for local connections/resolution
apiVersion: v1
kind: Service
metadata:
  name: dxemssql-3
spec:
  clusterIP: None
  selector:
    statefulset.kubernetes.io/pod-name: dxemssql-3
  ports:
  - name: dxl
    protocol: TCP
    port: 7979
  - name: dxc-tcp
    protocol: TCP
    port: 7980
  - name: dxc-udp
    protocol: UDP
    port: 7981
  - name: sql
    protocol: TCP
    port: 1433
  - name: listener
    protocol: TCP
    port: 14033
tip

Use 3 dashes (---) to separate service definitions.

Paste that YAML into the headless-services.yaml file. Then run kubectl apply to update the service configuration.

kubectl apply -f headless-services.yaml

Add a New Load Balancer​

If each pod in the cluster has their own load balancer for external access, you'll need to add new load balancer for the pod to give it a public IP too. Follow the instructions in the networking guide for making the new pod externally accessible with its own IP address.

Update Read-Only & Read-Write Replicas​

Please review the External Networking and AG Listeners guide before continuing with this section. Also, verify the details of your service configuration.

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

kubectl get service

Update Read-Only Access​

Adding read-only/read-write access to the replica follows the same steps in the Configure Replicas for Read-Only Access section of the External Networking and AG Listeners guide. The only modification required is to use the T-SQL below instead of the T-SQL supplied in that section.

Copy the T-SQL below into a file. Use the steps from the remainder of the section to apply the configuration.

USE [master] 
GO 
--Only allow read-write connections in the primary role
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' 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-3' WITH (
    SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
)
GO

Update Read-Only Routing​

In addition to adding a read-only routing URL, the read-only routing list must be updated for each AG member to reflect the new membership.

Copy the T-SQL below into a file, replacing <lb_ip> with the IP address of the load balancer associated with that pod. Use the steps from the remainder of the Configure Read-Only Routing with Load Balancing section to apply the configuration.

USE [master] 
GO 
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' 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', 'dxemssql-3')))
) 
GO 
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
    PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-0', 'dxemssql-2', 'dxemssql-3')))
) 
GO 
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
    PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-0', 'dxemssql-1', 'dxemssql-3')))
)
GO 
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' WITH (
    PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-0', 'dxemssql-1', 'dxemssql-2')))
)
GO

Update Read-Write Routing​

Adding read-write routing to the replica follows the same steps in the read-write routing section of the External Networking and AG Listeners guide. The only modification required is to use the T-SQL below instead of the T-SQL supplied in that section.

Copy the T-SQL below to a file, replacing <lb_ip> with the IP address of the load balancer associated with that pod. Use the steps from the remainder of the section to apply the new read-write routing configuration.

USE [master] 
GO 
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' WITH (
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
)
GO
--Update the read-write routing URLs
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' WITH (
    PRIMARY_ROLE (READ_WRITE_ROUTING_URL = 'TCP://<lb_ip>:1433')
)
GO

Additional Information​