Let’s install SQL Server in a free Kubernetes cluster on IBM Cloud.

mssql-statefulset

Kubernetes installation

Installing Kubernetes on IBM Cloud is free. Simply search for Kubernetes, select the Kubernetes service or use this link), and create a free cluster in the desired resource group.

After 5-10 minutes, the cluster should be created.

1_kubernetes_overview

To test the connection to the cluster, select the Kubernetes cluster and then click Actions > Connect via CLI. The command we are looking for is the second one.

2_connect_via_cli

Now, if you run this command in the IBM Cloud console, you should see a success message, indicating that you can now interact with the cluster.

To retrieve cluster nodes, run the following command:

kubectl get nodes

The response should resemble the following:

NAME             STATUS   ROLES    AGE   VERSION
10.144.214.202   Ready    <none>   17m   v1.25.10+IKS

Permanent storage

Most databases deployed in Kubernetes require persistent storage. In the case of IBM Cloud, this is represented by IBM Cloud Object Storage bucket(s).

To create a free IBM Cloud Object Storage, search for Object Storage, select the Object Storage option (or use this link), and choose the Lite (free) plan.

To attach the storage to our Kubernetes cluster, we first need to generate IBM Cloud Object Storage service credentials.

To do this:

  1. Navigate to the object storage instance.
  2. Click on Service Credentials and then New credential.
  3. Enter a name for the service credential.
  4. Select the Writer role, which is required for the dynamic bucket provisioning feature.
  5. click Add.

The service credential is now created. Make a note of the apikey as we will need it later to communicate with Cloud Object Storage.

Generate the kubernetes secret

Before mapping the storage, we need to generate a Kubernetes secret using the apikey obtained earlier and the resource ID.

To obtain the resource ID, run the following command in the IBM Cloud console, replacing <service_name> with the correct object storage name.

ibmcloud resource service-instance <service_name> | grep GUID

Now let’s generate the Kubernetes secret required to connect to our object storage instance. Run the following command, replacing <apikey> and <GUID> with the correct values

kubectl create secret generic cos-write-access --type=ibm/ibmc-s3fs --from-literal=api-key=<apikey> --from-literal=service-instance-id=<GUID>

Install the storage plug-in

First, add the IBM Cloud Helm repo by running the following command:

helm repo add ibm-helm https://raw.githubusercontent.com/IBM/charts/master/repo/ibm-helm

Next, update the Helm repos:

helm repo update

If you have previously installed the IBM Cloud Object Storage Helm plug-in, remove the ibmc plug-in using the following command:

helm plugin uninstall ibmc

Next, download and unpack the chart to the current directory for configuration:

helm fetch --untar ibm-helm/ibm-object-storage-plugin

Edit ibm-object-storage-plugin/templates/provisioner-sa.yaml and change line 69 as follows:

From:

rules:
- apiGroups: [""]
  resources: ["secrets"]
  #resourceNames: [""]
  verbs: ["get"]

to:

rules:
- apiGroups: [""]
  resources: ["secrets"]
  resourceNames: ["cos-write-access"]
  verbs: ["get"]

where cos-write-access is the above secret we created.

Now, let’s proceed with installing the Helm plug-in:

helm plugin install ./ibm-object-storage-plugin/helm-ibmc

Once the plug-in is installed, we can proceed to install the storage plug-in in our cluster using the following command:

helm ibmc install ibm-object-storage-plugin ibm-helm/ibm-object-storage-plugin --set license=true

If you encounter an error such as Error: fork/exec /path/.local/share/helm/plugins/helm-ibmc/ibmc.sh: permission denied, grant the necessary permissions to the file:

chmod 777 /path/.local/share/helm/plugins/helm-ibmc/ibmc.sh

Installing SQL Server

To install Microsoft SQL Server on Kubernetes, we first need to create a secret for the sa password. Run the following command:

kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD='<strong password here>'

Next, we will use a modified version of the YAML file provided by Microsoft in order to utilize our persistent storage.

apiVersion: apps/v1
kind: StatefulSet
metadata:
 name: mssql
spec:
 serviceName: "mssql"
 replicas: 1
 selector:
  matchLabels:
   app: mssql
 template:
  metadata:
   labels:
    app: mssql
  spec:
   securityContext:
     fsGroup: 10001
   containers:
   - name: mssql
     image: mcr.microsoft.com/mssql/server:2022-latest
     ports:
     - containerPort: 1433
       name: tcpsql
     env:
     - name: ACCEPT_EULA
       value: "Y"
     - name: MSSQL_ENABLE_HADR
       value: "1"
     - name: MSSQL_AGENT_ENABLED
       value: "1"
     - name: MSSQL_SA_PASSWORD
       valueFrom:
         secretKeyRef:
          name: mssql
          key: MSSQL_SA_PASSWORD
     volumeMounts:
     - name: mssql
       mountPath: "/var/opt/mssql"
 volumeClaimTemplates:
   - metadata:
      name: mssql
      annotations:
        ibm.io/auto-create-bucket: "true"
        ibm.io/auto-delete-bucket: "true"
        ibm.io/tls-cipher-suite: "default"
        ibm.io/secret-name: "cos-write-access"
     spec:
      storageClassName: "ibmc-s3fs-standard-cross-region"
      accessModes:
      - ReadWriteOnce
      resources:
       requests:
        storage: 8Gi

If everything is set up correctly, apply the YAML file using the command:

kubectl apply -f mssql.yml

The installation process should now commence.

Testing

Let’s perform some tests to ensure everything is working as expected.

First, check if the persistent volume claim is ready by running:

kubectl get pvc

The expected result should be:

NAME            STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS                      AGE
mssql-mssql-0   Bound    pvc-6131e4c7-c1e8-444b-b612-7a0229272986   8Gi        RWO            ibmc-s3fs-standard-cross-region   71s

Next, check the actual persistent volume by running:

kubectl get pv

If everything is functioning correctly, the result should be:

NAME                                       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   CLAIM                   STORAGECLASS                      REASON   AGE
pvc-6131e4c7-c1e8-444b-b612-7a0229272986   8Gi        RWO            Delete           Bound    default/mssql-mssql-0   ibmc-s3fs-standard-cross-region            3m38s

Finally, check the status of the pod by running:

kubectl get pods

You should see a result similar to:

NAME      READY   STATUS    RESTARTS   AGE
mssql-0   1/1     Running   0          4m50s

To log in to the SQL Server pod, execute the following command:

kubectl exec -it mssql-0 -- /bin/bash

Then, connect to the SQL Server using sqlcmd:

./opt/mssql-tools/bin/sqlcmd -U sa

If everything is set up correctly, you should be able to run any SQL command.

3_sql_commands