Deploy MSSQL in the Kyma Runtime
- How to configure and build a MSSQL database Docker image
- How to create a Namespace in the Kyma runtime
- How to deploy the MSSQL database Docker image to the Kyma runtime which includes:
- A Kubernetes Secret to store the database user/password
- A Kubernetes
PersistentVolumeClaim
for the storage of the database data - A Kubernetes Service used to expose the database to other Kubernetes resources
Prerequisites
- Docker installed with a valid public account
kubectl
configured to KUBECONFIG downloaded from the Kyma runtime- GIT installed
In this tutorial, you configure a database named DemoDB
which contains one Orders
table populated with two rows of sample data.
- Step 1
-
Go to the kyma-runtime-extension-samples repository. This repository contains a collection of Kyma sample applications which will be used during the tutorial.
-
Download the code by choosing the green Code button, and then choosing one of the options to download the code locally.
You can instead run the following command using your CLI at your desired folder location:
Shell/BashCopygit clone https://github.com/SAP-samples/kyma-runtime-extension-samples
-
- Step 2
Open the
database-mssql
directory in your desired editor.Explore the content of the sample.
Within the
app
folder you can find the configuration files for setting up the sampleDemoDB
database within the Docker image.The process includes defining the database password as well as the creation of the
Orders
table with sample data.Within the
docker
folder you can find the Dockerfile, which is a text-based set of instructions that is used to create a container image. Notice how the last command references theentrypoint.sh
script defined within the app directory which is used to call the commands to configure the sample database.Within the
k8s
folder you can find the resource definitions that will be used to deploy the sample to the Kyma runtime. This includes thedeployment.yaml
which specifies the microservice definition of the MSSQL database and also a service definition which exposes the microservice to other resources within the cluster. Thepvc.yaml
specifies a persistent volume claim which is used to request a storage location for the data of the database. Thesecret.yaml
contains the database user and password.
- Step 3
A Docker container image is a lightweight, standalone, executable package of software that includes everything needed to run an application: code, runtime, system tools, system libraries and settings. In this step, you will build a
mssql
image according to the Dockerfile definition contained in the docker folder. Make sure to run the following commands from thedatabase-mssql
directory using your CLI, and also replace the value of<your-docker-id>
with your Docker account ID.To build the Docker image, run this command:
Shell/BashCopydocker build -t <your-docker-id>/mssql -f docker/Dockerfile .
To push the Docker image to your Docker repository, run this command:
Shell/BashCopydocker push <your-docker-id>/mssql
- Step 4
Make sure to replace the value of
<your-docker-id>
with your Docker account ID.Start the image locally by running the following command. The start up will take about two minutes due to the scripts that run to initialize the database.
Shell/BashCopydocker run -e ACCEPT_EULA=Y -e SA_PASSWORD=Yukon900 -p 1433:1433 --name sql1 -d <your-docker-id>/mssql
Open a bash shell within the image by running this command:
Shell/BashCopydocker exec -it sql1 "bash"
Start the
sqlcmd
tool, which allows you to run queries against the database, by running this command:Shell/BashCopy/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P Yukon900
Enter a query by entering this command:
Shell/BashCopyUSE DemoDB SELECT * FROM ORDERS
The query can now be executed by entering this command:
Shell/BashCopyGO
End the
sqlcmd
session by running:Shell/BashCopyexit
End the bash session by running:
Shell/BashCopyexit
Shutdown the Docker container by running this command:
Shell/BashCopydocker stop sql1
Here are some additional commands that can be used:
To start the container again, run:
Shell/BashCopydocker start sql1
The container can be removed by running:
Shell/BashCopydocker rm sql1
To list all local containers existing:
Shell/BashCopydocker container ls -a
To list out all local images existing
Shell/BashCopydocker images
- Step 5
You can find the resource definitions in the
k8s
folder. If you performed any changes in the database configuration, these files may also need to be updated. The folder contains the following files:secret.yaml
: defines the database password and user which is base64-encoded.pvc.yaml
: defines aPersistentVolume
used to store the data of the database.deployment.yaml
: defines the Deployment definition for the MSSQL database as well as a Service used for communication. This definition references both thesecret.yaml
andpvc.yaml
by name.
Run the following commands from the
database-mssql
directory using your CLI.Start by creating the
dev
Namespace and enablingIstio
:Shell/BashCopykubectl create namespace dev kubectl label namespaces dev istio-injection=enabled
Namespaces separate objects inside a Kubernetes cluster. Choosing a different namespace will require adjustments to the provided samples.
Adding the label
istio-injection=enabled
to the namespace enablesIstio
.Istio
is the service mesh implementation used by the Kyma runtime.Apply the
PersistentVolumeClaim
:Shell/BashCopykubectl -n dev apply -f ./k8s/pvc.yaml
Apply the Secret:
Shell/BashCopykubectl -n dev apply -f ./k8s/secret.yaml
Within the
deployment.yaml
, adjust the value ofspec.template.spec.containers.image
, commented with #change it to your image, to use your Docker image. Apply the Deployment:Shell/BashCopykubectl -n dev apply -f ./k8s/deployment.yaml
Verify the Pod is up and running:
Shell/BashCopykubectl -n dev get po
This command results in a table similar to the one below, showing a Pod with the name
mssql-
ending with a random hash. The STATUS will displayRunning
when the Pod is up and running.Shell/BashCopyNAME READY STATUS RESTARTS AGE mssql-6df65c689d-qdj4r 2/2 Running 0 93s
What is the name of the file that contains the database user and password?
- Step 6
Kubernetes provides a port-forward functionality that allows you to connect to resources running in the Kyma runtime locally. This can be useful for development and debugging tasks. Make sure to adjust the name of the Pod in the following commands to match your own.
Confirm the port on which the Pod is listening:
Shell/BashCopykubectl get pod mssql-6df65c689d-qdj4r -n dev -o jsonpath="{.spec.containers[*].ports}"
This command should return the ports of two containers existing within the pod:
Shell/BashCopy[{"containerPort":15090,"name":"http-envoy-prom","protocol":"TCP"}] [{"containerPort":1433,"protocol":"TCP"}]
Apply the port-forward to the Pod using the port 1433 used by the MSQL Deployment:
Shell/BashCopykubectl port-forward mssql-6df65c689d-qdj4r -n dev 1433:1433
This command should return:
Shell/BashCopyForwarding from 127.0.0.1:1433 -> 1433 Forwarding from [::1]:1433 -> 1433
At this point, a tool such as
sqlcmd
or a development project running on your computer can access the database running in the Kyma runtime usinglocalhost:1433
.To end the process, use
CTRL+C
.
What kubectl command is used to allow localhost access to resources on the Kyma Runtime?
- Step 7
Similarly to how the Docker image can be accessed locally, you can perform the same on the Deployment running in the Kyma runtime. Make sure to adjust the name of the Pod in the following commands to match your own.
By default, a Pod includes the denoted Deployments defined in the
yaml
definition as well as theistio-proxy
. For themssql
Deployment, this means there will be two containers in the Pod.The
describe
command can be used to view this information.Shell/BashCopykubectl describe pod mssql-6df65c689d-qdj4r -n dev
Run the following command to obtain a bash shell.
If any adjustments where made to the name of the MSSQL deployment, adjust the name of the container denoted by the
-c
option.Shell/BashCopykubectl exec -it mssql-6df65c689d-qdj4r -n dev -c mssql -- bash
This may output the following message which can be ignored:
groups: cannot find name for group ID 1337
To run the
sqlcmd
tool, which allows you to run queries against the database, run this command:Shell/BashCopy/opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U SA -P Yukon900
The commands performed in [Step 4: ](Use the Docker image locally) to query the database can now be used in the same fashion.