Tutorial : Deploy Microsoft SQL Server on OpenShift

Disclaimer: This tutorial is not an official guide and intended for home lab only. For production use, please find official guide on Microsoft website or your local reseller.

One of my friends ask me whether we can deploy Microsoft SQL Server on OpenShift. After take a look at MSSQL documentation [1], there is a section to deploy MSSQL on AKS but not on OpenShift. This tutorial will guide you step-by-step how to deploy MSSQL (Bonus: deploy .NetCore application as MSSQL client) on OpenShift as easy as 1–2–3.

Step 1: Create new project on OpenShift

Lets start with creating new project on OpenShift.

$ oc new-project mssql

Step 2: Create MSSQL Secret for MSSQL password

$ oc create secret generic mssql --from-literal=SA_PASSWORD="Sql2017isfast"

Step 3: Create Persistent Volume Claim for DB Storage

kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: mssql-data
spec:
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 8Gi

$ oc apply -f https://raw.githubusercontent.com/erfinfeluzy/ocp4-notes/master/assets/mssql-storage.yaml

Step 4: Deploy MS SQL Server on OpenShift

$ oc apply -f https://raw.githubusercontent.com/erfinfeluzy/ocp4-notes/master/assets/mssql-deployment.yaml

note:

- deployment using image from Microsoft: mcr.microsoft.com/mssql/server:2017-CU8-ubuntu

- expose service as Load Balancer or Port Forward

Step 6: Query on Pods

Remote shell to mssql pods

$ oc get pods

$ oc rsh mssql-deployment-XXXXXXX

Try query inside pods

$ cd /opt/mssql-tools/bin

$ ./sqlcmd -S localhost -U sa -P Sql2017isfast
1> create database dbtest
2> select name from sys.databases
3> go
name                                                                                                                    
------------------------------
master                                                                                                                  
tempdb                                                                                                                  
model                                                                                                                   
msdb                                                                                                                    
dbtest
(5 rows affected)
1> use dbtest
2> create table tphone(id int, name nvarchar(50))
3> insert into tphone values(100,'Aina')
4> go
Changed database context to 'dbtest'.
(1 rows affected)
1> select * from tphone
2> go
id          name
----------- --------------------------------------------------
        100 Aina
(1 rows affected)
1> quit

Bonus: Query from External OCP

1. Step Service Type:LoadBalancer*
2. check load balancer url

$ oc get svc

============
NAME            TYPE           CLUSTER-IP       EXTERNAL-IP                                                                    PORT(S)           AGE
mssql           LoadBalancer   172.30.229.233   xxx.ap-southeast-1.elb.amazonaws.com   1433:31107/TCP    4h23m
mssql-service   LoadBalancer   172.30.145.178   xxx.ap-southeast-1.elb.amazonaws.com   31433:31749/TCP   4h23m

use mssql-service to access from outside cluster. for example: xxx.ap-southeast-1.elb.amazonaws.com:1433.

*currently our OpenShift lab is deployed on AWS, thus we can leverage AWS ELB as Load Balancer.

Or,

you can simply use OpenShift pod port forwarding to your local machine

$ oc port-forward mssql-deployment-XXXXXXX 1433:1433

Bonus: Deploy .NetCore Sample Application

$ oc apply -f https://raw.githubusercontent.com/erfinfeluzy/ocp4-notes/master/assets/dotnet-template-erfin.json

Select Template

Instantiate Template

Check database

$ oc get pods

$ oc rsh mssql-deployment-XXXXXXX

Query inside mssql pods

$ cd /opt/mssql-tools/bin
$ ./sqlcmd -S localhost -U sa -P Sql2019isfast
1> use myContacts
2> go
Changed database context to 'myContacts'.
1> select * from Customers
2> go
Id          Name
----------- ---------------------------
          1 erfin2
          2 aryo

(2 rows affected)

Additional: Change icon dotnet and mssql

Add label to mssql deployment

app.kubernetes.io/name=mssql

Add label to dotnet deployment

app.kubernetes.io/name=dotnet

Conclusion

Microsoft SQL Server can be deployed on OpenShift with traditional kubernetes components such as: Deployment, Service, etc. At time this post published, currently there is no Kubernetes Operator available for MSSQL server.

References:

[1] https://learn.microsoft.com/en-us/sql/linux/quickstart-sql-server-containers-kubernetes?view=sql-server-ver16