I got recently the 4.08 update from the Windocks team and I was very excited to evaluate some of new features. The first cool one I want to present in this blog concerns the Kubernetes support for deploying Windocks containers that will make my application deployment definitely easier. Let’s say you want to deploy your application that is tied to a Windocks container for SQL Server. In a previous blog post I explained why we are using Windocks in our context. So, with previous versions of Windocks, we had to write custom scripts to deploy applications on K8s that are tied to a Windocks. With the new version 4.08, this process may be simplified because both of applications and their related Windocks containers are directly deployable on K8s by using a YAML deployment file.
In fact, the new way consists in deploying a Windocks SQL Server proxy on K8s that works in conjunction with a Windocks Server. Once the SQL Server proxy deployed a corresponding Windocks container is spinning up with their specific parameters as shown in the picture below:
First of all, in order to make access secure between K8s and the Windocks Server authentication is required and we need to provide credential information that will be stored in the sql-proxy secret in K8s. SA password is also included in this secret and will be used to setup the SA account when the Windocks container will spin up.
$ kubectl create secret generic proxy-secrets --from-literal=WINDOCKS_REQUIRED_USERNAME='clustadmin' --from-literal=WINDOCKS_REQUIRED_PASSWORD='StrongPassword' --from-literal=WINDOCKS_REQUIRED_CONTAINER_SAPASSWORD=’sa_password'
The next step consists in deploying the Windocks SQL proxy by with the specific environment variables including WINDOCKS_REQUIRED_HOSTNAME (Windocks server name or IP Address), WINDOCKS_REQUIRED_IMAGE_NAME (Windocks based image used for container) and WINDOCKS_SQL_PROXY_OPTIONAL_LISTENING_PORT (optional).
- The Windocks SQL Proxy YAML file
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
name: windocks-sql-proxy-secure
labels:
app: sqlproxy-secure
spec:
replicas: 1
template:
metadata:
labels:
app: sqlproxy-secure
tier: frontend
spec:
containers:
- name: sqlproxy-secure-app
image: windocks/windocks-sql-server-proxy
imagePullPolicy: Always
ports:
- name: tcp-proxy
containerPort: 3087
- name: tls-proxy
containerPort: 3088
envFrom:
- secretRef:
name: proxy-secrets
env:
- name: PROJECT_ID
value: project_id_for_GKE_deployment_optional
- name: WINDOCKS_REQUIRED_HOSTNAME
value: xx.xxx.xxx.xxx
- name: WINDOCKS_REQUIRED_IMAGE_NAME
value: 2012_ci
- name: WINDOCKS_SQL_PROXY_OPTIONAL_LISTENING_PORT
value: "3087"
If we want to make the SQL Proxy pod accessible from outside a service is needed but this is not mandatory according to the context. Note that you may also use TLS connection to secure the network between K8s and the Windocks server.
- The Windocks service YAML file
apiVersion: v1
kind: Service
metadata:
name: windocks-sql-proxy-secure
labels:
app: sqlproxy-secure
tier: frontend
spec:
sessionAffinity: ClientIP
type: LoadBalancer
ports:
- port: 3087
name: tcp-proxy-secure-service
targetPort: 3087
- port: 3088
name: tls-proxy-secure-service
targetPort: 3088
selector:
app: sqlproxy-secure
tier: frontend
Let’s give a try on my Azure infrastructure including an AKS cluster and a Windocks Server installed in an Azure VM. I also took the opportunity to create my own helm chart from the YAML files provided by the Windocks team. It will make my deployment easier for sure. Here the command I used to deploy my Windocks helm chart on my AKS cluster.
$ helm install --name windocks2012 --namespace dmk --set Windocks.Image=2012_ci --set Windocks.Port=3089 --set Windocks.PortSSL=3090 .
Deployment will be performed in a specific namespace named dmk and the 2012_ci image will be used as based image for my Windocks container. I will be able to connect to my Windocks container by using the 3089 port through the SQL Proxy deployed on K8s. After few seconds the following resources were deployed within my dmk namespace including a Windocks SQL Proxy pod and the Windocks SQL Proxy service.
$ kubectl get all -n dmk
NAME READY STATUS RESTARTS AGE
pod/windocks2012-sqlproxy-securewindocks-sql-proxy-secure-56fb8694m 1/1 Running 0 13m
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S)
AGE
service/backend ClusterIP 10.0.126.154 <none> 80/TCP
8d
service/windocks2012-sqlproxy-securewindocks-sql-proxy-secure LoadBalancer 10.0.252.235 xx.xx.xxx.xxx 3089:30382/TCP,3090:30677/TCP 44m
NAME DESIRED CURRENT UP-TO-DATE AVAILABLE AGE
deployment.apps/windocks2012-sqlproxy-securewindocks-sql-proxy-secure 1 1 1 1 44m
NAME DESIRED CURRENT READY AGE
replicaset.apps/windocks2012-sqlproxy-securewindocks-sql-proxy-secure-56fbdb5c96 1 1 1 44m
Once deployed, the SQL proxy will redirect all connections from 3089 port to the container port after spinning up the corresponding Windocks container on the Windocks server. We may get some details by taking a look at the SQL Proxy logs on K8s. As a reminder the container port is allocated dynamically by default by the Windocks server and the SQL proxy get it automatically for connection redirection.
… Valid response for creating Windocks container Container id is b1201aaaba3b4cd047953b624e541e26500024e42e6381936fc7b526b5596a99 Container port is 10001 Setting up tcp server redirecting connections from 127.0.0.1:3089 to xx.xxx.xxx.xxx:10001 …
Let’s try to connect by using mssql-cli and the external IP of the SQL Proxy service and the 3089 port. The connection redirect is effective and I can interact with my Windocks container on local port 10001:
master> SELECT top 1 c.local_net_address, c.local_tcp_port ....... FROM sys.dm_exec_connections as c; +---------------------+------------------+ | local_net_address | local_tcp_port | |---------------------+------------------| | 172.18.0.5 | 10001 | +---------------------+------------------+
The Windocks container for SQL Server was spinning up my 3 testing databases as expected:
master> \ld+ +-------------------+-------------------------+-----------------------+------------------------------+ | name | create_date | compatibility_level | collation_name | |-------------------+-------------------------+-----------------------+------------------------------| | master | 2003-04-08 09:13:36.390 | 110 | SQL_Latin1_General_CP1_CI_AS | | tempdb | 2019-06-27 20:04:04.273 | 110 | SQL_Latin1_General_CP1_CI_AS | | model | 2003-04-08 09:13:36.390 | 110 | SQL_Latin1_General_CP1_CI_AS | | msdb | 2012-02-10 21:02:17.770 | 110 | SQL_Latin1_General_CP1_CI_AS | | AdventureWorksDbi | 2019-06-27 20:04:03.537 | 100 | Latin1_General_100_CS_AS | | ApplixEnterprise | 2019-06-27 20:04:04.477 | 90 | SQL_Latin1_General_CP1_CI_AS | | dbi_tools | 2019-06-27 20:04:05.153 | 100 | French_CS_AS | +-------------------+-------------------------+-----------------------+------------------------------+
From the Windocks server, I may get a picture of provisioned containers. The interesting one in our case is referenced by the name k8s-windocks2012/xxxx:
PS F:\WINDOCKS\SQL2012> docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES e9dbe5556b2f 2012_ci "" 29 minutes ago Stopped 10002/ dab/Windocks-id:31432367-c744-4ae3-8248-cb3fb3d2792e b1201aaaba3b 2012_ci "" 13 minutes ago Started 10001/ k8s-windocks2012/Windocks-id:cfa58c38-d168-4c04-b4c8-12b0552b93ad
Well, in a nutshell a feature we will consider to integrate in our DevOps Azure pipeline for sure. Stay tuned, other blog posts will come later.
See you!
By David Barbarin
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/microsoft-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/10/JPC_wev-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2024/03/AHI_web.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2023/03/KKE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2024/01/HME_web.jpg)