Recently, I’ve been working with a customer who wants to provide databases on their Kubernetes cluster. Ever since Microsoft’s SQL Server was released on Linux some years ago, I’ve been fascinated with it. I decided to give it a go recently on Kubernetes, and get it all working.
This is part one, where I deploy SQL server without persistent storage. In part two, I will discuss using persistent storage.
There is a lot of debate about whether or not you should run databases on kubernetes or not. If you’re operating in a public cloud environment, this is much more clear cut to my mind than if you’re not. If you are, then it may be better to use a service from a cloud provider where infrastructure is taken care of for you. It’s just easier.
If you are not operating in a public cloud environment, then running on kubernetes gives you the resilience and abstraction from infrastructure that is as close as you can get to running in a public cloud. This is very useful in disconnected environments and environments where you cannot access public cloud (yes they do exist).
Suffice to say, there are reasons that you may want to do this.
Why SQL server?
SQL server is ubiquitous. It is the database that a lot of applications use. As applications get either refactored or shifted to kubernetes, it is reasonable to assume that there will be instances where running a SQL server database on kubernetes is needed.
In order to get the database up and running, you will need to have a secret. This is the initial SA password that is used for the database. The easiest way to do this is to create an opaque secret.
The command below creates an opaque secret with a password that is complex enough to start the database.
kubectl create secret generic mssql --from-literal=SA_PASSWORD="MyC0m9l&xP@ssw0rd" --namespace=mssql
The manifests for deploying SQL server are relatively simple. The pages here give a really good overview of the general installation and command line options available for SQL Server on linux. These can be converted to manifest files.
First we create a namespace. Technically, you don’t need to do this and can run everything in the default namespace, but for neatness sake, I always think it’s worth creating a separate namespace.
kind: Namespace apiVersion: v1 metadata: name: mssql labels: name: mssql
Create a deployment for SQL server. I am creating a deployment rather than a statefulset for demonstration purposes.
apiVersion: apps/v1 kind: Deployment metadata: name: mssql-a namespace: mssql spec: replicas: 1 selector: matchLabels: app: mssql-a strategy: type: Recreate template: metadata: labels: app: mssql-a spec: terminationGracePeriodSeconds: 10 securityContext: fsGroup: 1000 containers: - name: mssql image: mcr.microsoft.com/mssql/rhel/server:2019-latest ports: - containerPort: 1433 name: mssql-port protocol: TCP env: - name: MSSQL_PID value: "Developer" - name: ACCEPT_EULA value: "Y" - name: MSSQL_SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: SA_PASSWORD
The environment variables that can be used to configure MSSQL server are listed here.
In the manifest above, I am using three variables.
- MSSQL_PID: The SQL Server edition or product key. In my case, “developer edition”
- ACCEPT_EULA: Accept the End User License Agreement
- MSSQL_SA_PASSWORD: The SA password for the database. In my case, this refers to the secret that I created earlier
Create a service that can be used to expose the pods that we created above. The service is named mssql-a purely because I may have more than one database that i want to expose.
This service exposes the database pods on port 1433, the default SQL server port.
apiVersion: v1 kind: Service metadata: name: mssql-a namespace: mssql spec: selector: app: mssql-a ports: - protocol: TCP port: 1433 targetPort: 1433
I’ll cover this piece in a second blog post, because it deserves its own topic entirely.
The database manifest works but will store data locally only. This means that it is only useful for development purposes. If the pod is restarted for any reason, data will be lost.
Client side tools
Install client side tools to connect to the database.
There is a really good document here that describes how to install the client side utilities in order to connect to your database.
I use fedora, so am using the instructions for RHEL8 (close enough)
Use curl to install the microsoft repository on your system.
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo
Install the client side tooling and the unix ODBC client
sudo yum install -y mssql-tools unixODBC-devel
Add the SQL tools to your default path and load the path into the current environment.
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc
Test your database.
sqlcmd -S localhost -U SA -P '<YourPassword>'
Port forward from local machine to database
As I have not created any ingress for my database, the easiest way for me to get connectivity is to port forward directly to it. I can use the command below to port forward from my local workstation to my database.
First I need to get the pod name of my database in order to port forward to it.
kubectl get pods -n mssql NAME READY STATUS RESTARTS AGE mssql-a-8469f884f7-rrbx9 1/1 Running 0 18m
I can then use the port-forward command to forward a local port to the pod port so that I can perform some testing and check that my database actually works.
kubectl port-forward mssql-a-<pod> 1433:1433 -n mssql --address 0.0.0.0
Database connect and test
Once everything has been created on the kubernetes side of the house, we can connect to the database and see that it is available.
I can connect to my database using the password I set originally. As I have port forwarded to my cluster, no ingress is needed. This is useful for testing.
I create a database named foo
[root@fedora]# sqlcmd -S localhost -U SA -P 'MyC0m9l&xP@ssw0rd' 1> create database foo 2> go
If I select the names of all databases from the sys.Database table, I can see that the last entry is my database foo.
1> select name from sys.Databases 2> go name -------------------------------------------------------------------------------------------------------------------------------- master tempdb model msdb foo (5 rows affected)
I can switch to the foo database and being to use it. I create a table and insert a single line of data into my newly created database.
1> use foo 2> go Changed database context to 'foo'. 1> create table bar (id INT, name VARCHAR(50)) 2> go 1> insert into bar values (1, 'test') 2> go (1 rows affected)
If I select all of the data from my table bar I can see the single line of data that I inserted above.
1> select * from bar 2> go id name ----------- -------------------------------------------------- 1 test
I have a functional database that is running on kubernetes!
Running databases on kubernetes isn’t that difficult. There are reasons that you want to do this. The difficult part about this is the ephemeral nature of pods on kubernetes and how to handle persistent storage with databases. This is the topic of my next post, where I will show how to use persistent storage to make your databases on kubernetes more robust.