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.
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:
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.
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.
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.
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 22.214.171.124 1433:31875/TCP,51000:31219/TCP,135:30044/TCP 3d1h
service/mssql-1 LoadBalancer 10.0.16.180 126.96.36.199 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.
EXEC master.dbo.sp_addlinkedserver = N'188.8.131.52', @srvproduct=N'SQL Server' ;
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'184.108.40.206', @rmtuser = 'sa', @rmtpassword = 'xxxx', @useself = N'False';
set xact_abort on
begin distributed transaction
select * from [220.127.116.11].master.dbo.sysprocesses