Skip to main content
Version: v23.0

External Networking and AG Listeners for SQL Server in Kubernetes

Introduction​

This guide describes how to deploy load balancer services for your DxEMSSQL Kubernetes cluster to enable access to DxEnterprise, SQL Server, and the availability group listener. There are many different network configurations that are possible for a Kubernetes cluster, so only a few examples are given below.

Prerequisites​

  • The Kubernetes CLI installed installed on the local machine.

  • A DxEMSSQL StatefulSet deployed in Kubernetes on a cloud provider (AWS, Azure, or Google Cloud) or locally. See the SQL Server Availability Groups in Kubernetes guide for details.

  • If you are going to provision a static IP using a cloud provider, you will need to have your preferred cloud CLI installed and logged in.

    For information about installing the Azure CLI and logging in, view Microsoft documentation.

Make Your Cluster Externally Accessible​

Load balancers and node ports can be used to access pods in a Kubernetes cluster. Kubernetes load balancers provide access to cluster resources via an externally-accessible IP address: you define the load balancer configuration in the YAML, and Kubernetes takes care of load balancing incoming connections for you.

Node ports are a good option in environments where Kubernetes load balancers are not available, or you want to implement your own load balancing solution. Node ports expose a port on the Kubernetes host that outside connections can use to reach your pods, but it is up to you to implement a service that load balances the incoming connections.

Some basic example configurations are given below.

  1. Set an AG listener port using the command dxcli add-ags-listener.
kubectl exec -c dxe dxemssql-0 -- dxcli add-ags-listener vhost1 ags1 14033

  1. Copy the load balancer or node port configuration to your local machine.

    warning

    Ports mapped to a load balancer within Kubernetes will be accessible to the external network, which in some configurations may be a public network.

    #Example load balancer service
    #Access for SQL server, AG listener, and DxE management
    apiVersion: v1
    kind: Service
    metadata:
      name: dxemssql-cluster-lb
    spec:
      type: LoadBalancer
      selector:
        app: dxemssql
      ports:
      - name: sql
        protocol: TCP
        port: 1433
        targetPort: 1433
      - name: listener
        protocol: TCP
        port: 14033
        targetPort: 14033
      - name: dxe
        protocol: TCP
        port: 7979
        targetPort: 7979

  2. Apply the configuration.

    kubectl apply -f service.yaml

  3. For load balancers, list the service and note its public IP address. For node ports, list any one of your nodes and note its IP address.

    kubectl get service

  4. To verify connectivity, you can connect to the SQL instance (port 1433) or AG listener (port 14033) via SSMS/sqlcmd and the load balancer IP address.

    sqlcmd -S <ip_address> -U sa -P <sql_password>

  5. OPTIONAL: Set a cluster passkey using the command `dxcli cluster-set-secret-ex. A passkey is required to remotely manage your DxEnterprise cluster.

    kubectl exec dxemssql-0 -- dxcli cluster-set-secret-ex p@ssw0rd
    info

    DxEnterprise’s administration port of 7979 is used for DxCLI and DxAdmin, DxEnterprise’s client UI. Use the IP address from step 4 and the passkey to connect to the cluster from a remote machine. For more information about installing DxAdmin, view the DxAdmin Client UI Quick Start Guide.

Using a Static IP​

When creating a Kubernetes load balancer service using a cloud provider, the load balancer is automatically assigned a public IP address that lasts for the lifespan of the resource. This IP address is ephemeral, and will likely change if the service is deleted and recreated. The provider-specific resources below contain instructions on how to create a static IP for your load balancer service. You can use the YAML example from the previous section as the basis for your new load balancer.

Reference Microsoft documentation for further information about static IPs in AKS clusters.

Make Each Pod Externally Accessible​

Services can also be assigned to a single pod. For load balancers, this is a unique public IP address. For node ports, this is the node IP(s) and a unique port.

This requires a slight modification of the YAML example from the previous section, but the instructions are otherwise the same. Each pod participating in the AG will require a unique service configuration like the one below.

warning

Ports mapped to a load balancer within Kubernetes will be accessible to the external network, which in some configurations may be a public network.

#Example load balancer service
#Access for SQL server, AG listener, and DxE management
apiVersion: v1
kind: Service
metadata:
  #Unique name
  name: dxemssql-0-lb
spec:
  type: LoadBalancer
  selector:
    #Assign load balancer to a specific pod
    statefulset.kubernetes.io/pod-name: dxemssql-0
  ports:
  - name: sql
    protocol: TCP
    port: 1433
    targetPort: 1433
  - name: listener
    protocol: TCP
    port: 14033
    targetPort: 14033
  - name: dxe
    protocol: TCP
    port: 7979
    targetPort: 7979
tip

Multiple YAML configurations can be saved in one file using a line of three dashes (---) between each configuration.

Use Tunnels for Faster Connections to the Listener​

Connections to the AG listener via the Kubernetes load balancer may be slow due to how Kubernetes handles traffic to pods that do not have a TCP listener active. To alleviate this issue, a DxEnterprise tunnel can used to redirect listener traffic from the secondaries to the primary to avoid timeouts. Run the dxcli add-tunnel command to add the tunnel to the Vhost.

kubectl exec dxemssql-0 -- dxcli add-tunnel listener true ".ACTIVE" "127.0.0.1:14033" ".INACTIVE,0.0.0.0:14033" vhost1
info

The add-tunnel command given above uses .ACTIVE and .INACTIVE designations to determine where to route traffic. In DxEnterprise, the .ACTIVE node is always the primary replica, and .INACTIVE will be every secondary replica in the Vhost. The command will create tunnel origins (listeners) on the secondaries on port 14033 that route traffic to the primary pod.

Next Steps​

Additional Information​