Thursday, March 17, 2022

Configure MSDTC to run Distributed transactions for SQL Server Linux Containers on Azure Kubernetes

It's been a while since I've had the opportunity to write and share a blog post about SQL Server containers and Linux. Today, I'd like to show you how to set up and use MSDTC (Microsoft Distributed Transaction Coordinator) to execute distributed transactions for SQL Server containers running on a Kubernetes platform.

 

Please see the following documentation for more information on DTC and SQL Server on Linux. How to configure MSDTC on Linux - SQL Server | Microsoft Docs.

 

The first step is to set up a Kubernetes cluster and connect to it. As usual, I'll be using Azure Kubernetes Service (AKS) as my Kubernetes Platform. Please see  Quickstart: Deploy an AKS cluster by using Azure CLI - Azure Kubernetes Service | Microsoft Docs This documentation describes how to deploy an Azure Kubernetes Service cluster with Azure CLI. After the cluster has been deployed, we will proceed to connect to the Azure Kubernetes Cluster using the steps outlined here.

 

I'm going to use the cluster's default namespace to deploy my SQL Server containers and other supporting objects. Before I deploy my SQL Server containers, I need to save the 'sa' password as a secret in Kubernetes, so I'd use the command: 

 

kubectl create secret generic mssql --from-literal=SA_PASSWORD="MyC0m9l&xP@ssw0rd"

 

Please note, I will use the same ‘sa’ password to connect to both the SQL Server instances that I deploy.

 

Now that we've created the secret, we're ready to deploy SQL Server containers. I prefer and recommend deploying SQL Server containers as'statefulset' deployments, which ensure that the container name and hostname remain unchanged even after the pods are deleted and recreated. For more information on statefulset deployments, see StatefulSets | Kubernetes.

 

The following yaml script is used to deploy the following objects: one storage class, two SQL Server containers, and two load balancer services to connect to the respective SQL Servers.

 

kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
     name: azure-disk
provisioner: kubernetes.io/azure-disk
parameters:
  storageaccounttype: Standard_LRS
  kind: Managed
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
 name: mssql
 labels:
  app: mssql
spec:
 serviceName: "mssql"
 replicas: 2
 selector:
  matchLabels:
   app: mssql
 template:
  metadata:
   labels:
    app: mssql
  spec:
   securityContext:
     fsGroup: 10001
   containers:
   - name: mssql
     image: mcr.microsoft.com/mssql/server:2019-latest
     ports:
     - containerPort: 1433
       name: tcpsql
     - containerPort: 13500
       name: dtcport
     - containerPort: 51000
       name: dtctcpport
     env:
     - name: ACCEPT_EULA
       value: "Y"
     - name: MSSQL_ENABLE_HADR
       value: "1"
     - name: MSSQL_AGENT_ENABLED
       value: "1"
     - name: MSSQL_RPC_PORT
       value: "13500"
     - name: MSSQL_DTC_TCP_PORT
       value: "51000"
     - name: SA_PASSWORD
       valueFrom:
         secretKeyRef:
          name: mssql
          key: SA_PASSWORD
     volumeMounts:
     - name: mssql
       mountPath: "/var/opt/mssql"
 volumeClaimTemplates:
   - metadata:
      name: mssql
     spec:
      accessModes:
      - ReadWriteOnce
      resources:
       requests:
        storage: 8Gi
---
apiVersion: v1
kind: Service
metadata:
  name: mssql-0
spec:
  type: LoadBalancer
  loadBalancerIP: 40.88.213.209
  selector:
    statefulset.kubernetes.io/pod-name: mssql-0
  ports:
  - protocol: TCP
    port: 1433
    targetPort: 1433
    name: tcpsql
  - protocol: TCP
    port: 51000
    targetPort: 51000
    name: dtctcpport
  - protocol: TCP
    port: 135 
    targetPort: 13500
    name: nonrootport
---
apiVersion: v1
kind: Service
metadata:
  name: mssql-1
spec:
  type: LoadBalancer
  loadBalancerIP: 20.72.137.129
  selector:
    statefulset.kubernetes.io/pod-name: mssql-1
  ports:
  - protocol: TCP
    port: 1433
    targetPort: 1433
    name: tcpsql
  - protocol: TCP
    port: 51000
    targetPort: 51000
    name: dtctcpport
  - protocol: TCP
    port: 135
    targetPort: 13500
    name: nonrootport

 

Let me explain each section of the script above. The first section of the script below creates the Azure disk-based storageClass, which is used as persistent storage for SQL Server containers.

 

kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
     name: azure-disk
provisioner: kubernetes.io/azure-disk
parameters:
  storageaccounttype: Standard_LRS
  kind: Managed

 

The script's second section deploys multiple SQL Server instances as part of a single deployment. The number of SQL Server containers deployed is determined by the value you enter in the replicas field. In this case, we're only deploying two SQL Server containers. We are also configuring two environment variables that are required for MSDTC configuration.

  • MSSQL RPC PORT-> 13500: This is the TCP port to which the 'RPC endpoint mapper' process is bound.
  • MSSQL DTC TCP PORT -> 51000: This is the port on which the MSDTC server listens. If this option is not set, the MSDTC service will use a random ephemeral port on service restarts, and firewall exceptions will need to be reconfigured to ensure that the MSDTC service can communicate. We are also exposing these ports 1433, 13500, and 51000 at the container level.

For the above two environment variables, you can use any port of your choice.

 

apiVersion: apps/v1
kind: StatefulSet
metadata:
 name: mssql
 labels:
  app: mssql
spec:
 serviceName: "mssql"
 replicas: 2
 selector:
  matchLabels:
   app: mssql
 template:
  metadata:
   labels:
    app: mssql
  spec:
   securityContext:
     fsGroup: 10001
   containers:
   - name: mssql
     image: mcr.microsoft.com/mssql/server:2019-latest
     ports:
     - containerPort: 1433
       name: tcpsql
     - containerPort: 13500
       name: dtcport
     - containerPort: 51000
       name: dtctcpport
     env:
     - name: ACCEPT_EULA
       value: "Y"
     - name: MSSQL_ENABLE_HADR
       value: "1"
     - name: MSSQL_AGENT_ENABLED
       value: "1"
     - name: MSSQL_RPC_PORT
       value: "13500"
     - name: MSSQL_DTC_TCP_PORT
       value: "51000"
     - name: SA_PASSWORD
       valueFrom:
         secretKeyRef:
          name: mssql
          key: SA_PASSWORD
     volumeMounts:
     - name: mssql
       mountPath: "/var/opt/mssql"
..
..

 

The script concludes with the creation of the load balancer service, which allows us to connect to the SQL Server instances externally. You'll notice in the script that we expose three ports: 1433 for connecting to SQL Server, and 13500 and 51000 for DTC communication. We also configure port routing to occur between port 135 (MSDTC RPC port) and port 13500 using the 'port' and 'targetPort' options in the script. I also am configuring the load balancers to use static IPs, which ensures that the external IP address for the service does not change when the service is deleted and recreated. To know more and to create static IPs in AKS please refer: Use static IP with load balancer - Azure Kubernetes Service | Microsoft Docs.

 

..
    port: 1433
    targetPort: 1433
    name: tcpsql
  - protocol: TCP
    port: 51000
    targetPort: 51000
    name: dtctcpport
  - protocol: TCP
    port: 135
    targetPort: 13500
    name: nonrootport

 

After you've used yaml to create the necessary objects. When you use the command kubectl get all to list all the objects, you should see something like this.

 

C:\>kubectl get all
NAME          READY   STATUS    RESTARTS   AGE
pod/mssql-0   1/1     Running   0          61m
pod/mssql-1   1/1     Running   0          61m

NAME                 TYPE           CLUSTER-IP    EXTERNAL-IP     PORT(S)                                        AGE
service/kubernetes   ClusterIP      10.0.0.1      <none>          443/TCP                                        7d1h
service/mssql-0      LoadBalancer   10.0.18.186   40.88.213.209   1433:31875/TCP,51000:31219/TCP,135:30044/TCP   3d1h
service/mssql-1      LoadBalancer   10.0.16.180   20.72.137.129   1433:30353/TCP,51000:32734/TCP,135:31239/TCP   3d1h

NAME                     READY   AGE
statefulset.apps/mssql   2/2     5d20h

 

You can now connect to the SQL Servers, add linked servers, and run the distributed transactions shown below.

I connect to mssql-1, then add mssql-0 as a linked server and run the distributed transaction to list mssql-0's sysprocesses.

 

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver  = N'40.88.213.209', @srvproduct=N'SQL Server' ;
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'40.88.213.209', @rmtuser = 'sa', @rmtpassword = 'xxxx', @useself = N'False';
GO

set xact_abort on
begin distributed transaction
select * from [40.88.213.209].master.dbo.sysprocesses
commit
Go

 

Posted at https://sl.advdat.com/3u8em5shttps://sl.advdat.com/3u8em5s