DxOperator with Non-default Ports for SQL Server

This guide shows how to configure a Microsoft SQL Server Availability Group cluster with non-default ports using DxOperator. Many security best practices require the use of non-default ports with SQL Server, and this guide shows how to select a port other than 1433. This guide is intended to supplement the DxOperator Quick Start Guide.

Create a SQL Server ConfigMap

We recommend configuring the listening port in Microsoft SQL Server using the mssql.conf configuration file, which we will store in a Kubernetes ConfigMap. If you don't already have a ConfigMap for this, create one as shown below. If you do already have one, add the tcpport entry to the network section as shown on the highlighted lines below.

Download the file below, and change the tcpport value as desired using a text editor.

configmap.yaml

kind: ConfigMap 
apiVersion: v1 
metadata:
  name: mssql-config
data:
  mssql.conf: |
    [network]
    tcpport = 51433

    [sqlagent]
    enabled = true

kubectl apply -f configmap.yaml

Update the DxEnterpriseSqlAg configuration

There are three changes that may need to be made to the DxEnterpriseSqlAg configuration to support changing the SQL Server listening port.

1. The ConfigMap must be linked, if not already.
2. The DxEnterprise container needs to be configured to use the non-default port for administration. This is done using the MSSQL_TCP_PORT environment variable.
3. If the createLoadBalancers option is in use, it must be disabled, and the load balancers need to be created in a custom way.

Potential changes to the default configuration are highlighted below:

DxEnterpriseSqlAg.yaml

apiVersion: dh2i.com/v1
kind:  DxEnterpriseSqlAg
metadata:
  name: dxesqlag
spec:
  synchronousReplicas: 3
  asynchronousReplicas: 0
  # ConfigurationOnlyReplicas are only allowed with availabilityGroupClusterType set to EXTERNAL
  configurationOnlyReplicas: 0            
  availabilityGroupName: AG1
  # Listener port for the availability group (uncomment to apply)
  #availabilityGroupListenerPort: 14033
  # For a contained availability group, add the option CONTAINED
  availabilityGroupOptions: null
  # Valid options are EXTERNAL (automatic failover) and NONE (no automatic failover)
  availabilityGroupClusterType: EXTERNAL
  createLoadBalancers: false
  template:
    metadata:
      labels:
        label: example
      annotations:
        annotation: example
    spec:
      dxEnterpriseContainer:
        image: "docker.io/dh2i/dxe:latest"
        imagePullPolicy: Always
        acceptEula: true
        clusterSecret: dxe
        vhostName: VHOST1
        joinExistingCluster: false
        env:
        - name: MSSQL_TCP_PORT
          value: "51433"
        # QoS – guaranteed (uncomment to apply)
        #resources:
          #limits:
            #memory: 1Gi
            #cpu: '1'
        # Configuration options for the required persistent volume claim for DxEnterprise
        volumeClaimConfiguration:
          storageClassName: null
          resources:
            requests:
              storage: 1Gi
      mssqlServerContainer:
        image: "mcr.microsoft.com/mssql/server:latest"
        imagePullPolicy: Always
        mssqlSecret: mssql
        acceptEula: true
        mssqlPID: Developer
        # Only set this to a value if you created a ConfigMap
        mssqlConfigMap: mssql-config
        # QoS – guaranteed (uncomment to apply)
        #resources:
          #limits:
            #memory: 2Gi
            #cpu: '2'
        # Configuration options for the required persistent volume claim for SQL Server
        volumeClaimConfiguration:
          storageClassName: null
          resources:
            requests:
              storage: 2Gi
      # Additional side-car containers, such as mssql-tools (uncomment to apply)
      #containers:
      #- name: mssql-tools
          #image: "mcr.microsoft.com/mssql-tools"
          #command: [ "/bin/sh" ]
          #args: [ "-c", "tail -f /dev/null" ]

caution

These changes cannot be applied to a running deployment. To update an already-deployed DxEnterpriseSqlAg, the DxEnterpriseSqlAg must be deleted and re-created with the updated manifest. Data volumes will be preserved, and this process may incur downtime, but will not result in any data loss.

Configure External Load Balancers

This step is only required if per-pod external access is needed. If you previously did not enable the createLoadBalancers option, this step can be skipped.

If per-pod external access is required, custom LoadBalancer services must be defined. Specifically, one service must be defined per pod created by the DxEnterpriseSqlAg. For the deployment above, with three pods, the services would be defined as below.

Additional ports, e.g. Availability Group listeners, can also be defined in these services.

Download the file below, and update the values, including port and targetPort, as desired using a text editor.

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.

ExternalLoadBalancers.yaml

apiVersion: v1
kind: Service
metadata:
  name: dxesqlag-0-lb
spec:
  type: LoadBalancer
  selector:
    dh2i.com/name: dxesqlag-0
  ports:
    - name: sql
      protocol: TCP
      port: 51433
      targetPort: 51433
    #- name: aglistener
    #  protocol: TCP
    #  port: 14033
    #  targetPort: 14033
    - name: dxe
      protocol: TCP
      port: 7979
      targetPort: 7979
---
apiVersion: v1
kind: Service
metadata:
  name: dxesqlag-1-lb
spec:
  type: LoadBalancer
  selector:
    dh2i.com/name: dxesqlag-1
  ports:
    - name: sql
      protocol: TCP
      port: 51433
      targetPort: 51433
    #- name: aglistener
    #  protocol: TCP
    #  port: 14033
    #  targetPort: 14033
    - name: dxe
      protocol: TCP
      port: 7979
      targetPort: 7979
---
apiVersion: v1
kind: Service
metadata:
  name: dxesqlag-2-lb
spec:
  type: LoadBalancer
  selector:
    dh2i.com/name: dxesqlag-2
  ports:
    - name: sql
      protocol: TCP
      port: 51433
      targetPort: 51433
    #- name: aglistener
    #  protocol: TCP
    #  port: 14033
    #  targetPort: 14033
    - name: dxe
      protocol: TCP
      port: 7979
      targetPort: 7979

kubectl apply -f ExternalLoadBalancers.yaml