How to Run HA SQL Server on IBM Cloud Private

Contact Sales

How To

This post is part of our ongoing series on running Microsoft SQL Server on Kubernetes. We’ve published a number of articles about running Microsoft SQL Server on Kubernetes for specific platforms and for specific use cases. If you are looking for a specific Kubernetes platform, check out these related articles.

Running HA SQL Server on Amazon Elastic Container Service for Kubernetes (EKS)

Running HA SQL Server on Google Kubernetes Engine (GKE)

Running HA SQL Server on Azure Kubernetes Service (AKS)

Running HA SQL Server on Red Hat OpenShift

Running HA SQL Server with Rancher Kubernetes Engine (RKE)

And now, onto the post…

IBM Cloud Private is an application platform for developing and managing on-premises, containerized applications. It is an integrated environment for managing containers that includes the container orchestrator Kubernetes, a private image registry, a management console, and monitoring frameworks.

Portworx is a cloud native storage platform to run persistent workloads deployed on a variety of orchestration engines including Kubernetes. With Portworx, customers can manage the database of their choice on any infrastructure using any container scheduler. It provides a single data management layer for all stateful services, no matter where they run.

This tutorial is a walk-through of the steps involved in deploying and managing a highly available Microsoft SQL Server database on Kubernetes. Portworx is a Microsoft SQL Server high availability and disaster recovery partner and this tutorial will show you how to reliably run this database for mission-critical applications.

In summary, to run HA SQL Server on IBM Cloud Private (ICP) you need to:

Setup and configure ICP environment
Install cloud native storage solution like Portworx as a DaemonSet on Kubernetes
Create a storage class defining your storage requirements like replication factor, snapshot policy, and performance profile
Deploy SQL Server on Kubernetes
Test failover by killing or cordoning node in your cluster
Expand the storage volume without downtime
Backup and restore SQL Server from a snapshot

How to install IBM Cloud Private

IBM Cloud Private facilitates the development of applications in a shared, multitenant environment and supports both Linux x86_64 on x86 and Linux on Power (ppc64le) architectures. This deployment is based on ICP running in x86 architecture. For a detailed walkthrough of setting up ICP, refer to the official IBM documentation.

By the end of this step, you should have a Kubernetes cluster with one master and three worker nodes.

px-sql-icp-0

Installing Portworx in Kubernetes

Installing Portworx on ICP is not different from installing it on a Kubernetes cluster setup through Kops. Portworx documentation has the steps involved in running the Portworx cluster in a Kubernetes environment.

Ensure that Portworx storage cluster is installed and available as a DaemonSet.

px-sql-icp-1

Creating a storage class for MS SQL Server

Once the Kubernetes cluster is up and running, and Portworx is installed and configured, we will deploy a highly available Microsoft SQL Server instance.

Through storage class objects, an admin can define different classes of Portworx volumes that are offered in a cluster. These classes will be used during the dynamic provisioning of volumes. The storage class defines the replication factor, I/O profile (e.g., for a database or a CMS), and priority (e.g., SSD or HDD). These parameters impact the availability and throughput of workloads and can be specified for each volume. This is important because a production database will have different requirements than a development Jenkins cluster.

$ cat > px-sql-sc.yaml << EOF
kind: StorageClass
apiVersion: storage.k8s.io/v1beta1
metadata:
    name: px-mssql-sc
provisioner: kubernetes.io/portworx-volume
parameters:
   repl: "3"
   io_profile: "db_remote"
   priority_io: "high"
allowVolumeExpansion: true
EOF

Create the storage class and verify it’s available in the default namespace.

$ kubectl create -f px-sql-sc.yaml
storageclass.storage.k8s.io "px-mssql-sc" created

$ kubectl get sc
NAME                PROVISIONER                     AGE
px-mssql-sc         kubernetes.io/portworx-volume   6s
stork-snapshot-sc   stork-snapshot                  12m

Creating a MS SQL Server PVC

We can now create a Persistent Volume Claim (PVC) based on the Storage Class. Thanks to dynamic provisioning, the claims will be created without explicitly provisioning Persistent Volume (PV).

$ cat > px-sql-pvc.yaml << EOF
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
   name: mssql-data
   annotations:
     volume.beta.kubernetes.io/storage-class: px-mssql-sc
spec:
   accessModes:
     - ReadWriteOnce
   resources:
     requests:
       storage: 5Gi
EOF

$ kubectl create -f px-sql-pvc.yaml
persistentvolumeclaim "mssql-data" created

Let’s verify the PVC with the following command:

$ kubectl get pvc
NAME          STATUS    VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS        AGE
mssql-data    Bound     pvc-7ec6beb0-737a-11e9-8bbc-000c29549a08   5Gi        RWO            px-mssql-sc         46s

Deploying MS SQL Server on Kubernetes

Finally, let’s create a Microsoft SQL Server instance as a Kubernetes deployment object. For simplicity sake, we will just be deploying a single SQL Server pod. Because Portworx provides synchronous replication for High Availability, a single SQL Server instance might be the best deployment option for your SQL database. Portworx can also provide backing volumes for multi-node SQL Server deployments. The choice is yours.

cat > px-sql-db.yaml << EOF
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql
spec:
  strategy:
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 1
    type: RollingUpdate
  replicas: 1
  selector:
    matchLabels:
      app: mssql
  template:
    metadata:
      labels:
        app: mssql
    spec:
      containers:
      - name: mssql
        image: microsoft/mssql-server-linux:2017-latest
        imagePullPolicy: "IfNotPresent"
        ports:
        - containerPort: 1433
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: SA_PASSWORD
          value:  "P@ssw0rd"
        volumeMounts:
        - mountPath: /var/opt/mssql
          name: mssqldb
      volumes:
      - name: mssqldb
        persistentVolumeClaim:
          claimName: mssql-data
EOF

$ kubectl create -f px-sql-db.yaml
deployment.extensions "mssql" created

Make sure that the SQL Server pods are in the Running state.

$ kubectl get pods -l app=mssql -o wide --watch

Wait till the SQL Server pod reaches the Running state.

$ $ kubectl get pods -l app=mssql -o wide --watch
NAME                    READY     STATUS    RESTARTS   AGE       IP            NODE
mssql-6649487f9-qgzfg   1/1       Running   0          1m        10.1.98.149   70.0.60.173

We can inspect the Portworx volume by accessing the pxctl tool running with the SQL Pod.

$ VOL=`kubectl get pvc | grep mssql-data | awk '{print $3}'`
$ PX_POD=$(kubectl get pods -l name=portworx -n kube-system -o jsonpath='{.items[0].metadata.name}')
$ kubectl exec -it $PX_POD -n kube-system -- /opt/pwx/bin/pxctl volume inspect ${VOL}

px-sql-icp-2

Failing over MS SQL Server on Kubernetes

Let’s populate the database with sample data.

Create a SQL file with the below statements:

CREATE DATABASE classicmodels;
go

USE classicmodels;
go

CREATE TABLE offices (
  officeCode varchar(10) NOT NULL,
  city varchar(50) NOT NULL,
  phone varchar(50) NOT NULL,
  addressLine1 varchar(50) NOT NULL,
  addressLine2 varchar(50) DEFAULT NULL,
  state varchar(50) DEFAULT NULL,
  country varchar(50) NOT NULL,
  postalCode varchar(15) NOT NULL,
  territory varchar(10) NOT NULL,
);
go

insert  into offices(officeCode,city,phone,addressLine1,addressLine2,state,country,postalCode,territory) values
('1','San Francisco','+1 650 219 4782','100 Market Street','Suite 300','CA','USA','94080','NA'),
('2','Boston','+1 215 837 0825','1550 Court Place','Suite 102','MA','USA','02107','NA'),
('3','NYC','+1 212 555 3000','523 East 53rd Street','apt. 5A','NY','USA','10022','NA'),
('4','Paris','+33 14 723 4404','43 Rue Jouffroy abbans',NULL,NULL,'France','75017','EMEA'),
('5','Tokyo','+81 33 224 5000','4-1 Kioicho',NULL,'Chiyoda-Ku','Japan','102-8578','Japan'),
('6','Sydney','+61 2 9264 2451','5-11 Wentworth Avenue','Floor #2',NULL,'Australia','NSW 2010','APAC'),
('7','London','+44 20 7877 2041','25 Old Broad Street','Level 7',NULL,'UK','EC2N 1HN','EMEA');
go

We will copy the sample data to the MS SQL pod before loading it through the SQLCMD utility.

$ SQL_POD=$(kubectl get pods -l app=mssql -o jsonpath='{.items[0].metadata.name}')
$ kubectl cp sample_data.sql $SQL_POD:/tmp

Let’s load the sample data into SQL Server.

$ kubectl exec $SQL_POD -- /opt/mssql-tools/bin/sqlcmd -U sa -P P@ssw0rd -i /tmp/sample_data.sql

We can query the database by running the below command:

$ kubectl exec $SQL_POD -- /opt/mssql-tools/bin/sqlcmd -U sa -P P@ssw0rd -d classicmodels -Q 'select * from offices'

The below query shows only the cities from the table.

$ kubectl exec $SQL_POD \
-- /opt/mssql-tools/bin/sqlcmd -U sa -P P@ssw0rd \
-d classicmodels -Q 'select city from offices'

px-sql-icp-3

Now, let’s simulate the node failure by cordoning off the node on which SQL Server is running.

$ NODE=`kubectl get pods -l app=mssql -o wide | grep -v NAME | awk '{print $7}'`
$ kubectl cordon ${NODE}
node "70.0.60.173" cordoned

We will now go ahead and delete the SQL Server pod.

$ POD=`kubectl get pods -l app=mssql -o wide | grep -v NAME | awk '{print $1}'`
$ kubectl delete pod ${POD}
pod "mssql-6649487f9-qgzfg" deleted

As soon as the pod is deleted, it is relocated to the node with the replicated data. STorage ORchestrator for Kubernetes (STORK), Portworx’s custom storage scheduler allows co-locating the pod on the exact node where the data is stored. It ensures that an appropriate node is selected for scheduling the pod.

Let’s verify this by running the below command. We will notice that a new pod has been created and scheduled in a different node.

$ kubectl get pods -l app=mssql
NAME                     READY   STATUS              RESTARTS   AGE
mssql-6649487f9-8pg6n   0/1       ContainerCreating   0          13s

Wait till the pod is ready and run the select query in it.

$ SQL_POD=$(kubectl get pods -l app=mssql -o jsonpath='{.items[0].metadata.name}')
$ kubectl exec $SQL_POD \
-- /opt/mssql-tools/bin/sqlcmd -U sa -P P@ssw0rd \
-d classicmodels -Q 'select city from offices'

Observe that the database table is still there and all the content intact!

Performing Storage Operations on Kubernetes

After testing end-to-end failover of the database, let’s perform StorageOps on our ICP cluster.

Expanding the Volume with no downtime

Let’s first get the Portworx volume name backing the SQL Server deployment and inspect it through the pxctl tool.

$ VOL=`kubectl get pvc | grep mssql-data | awk '{print $3}'`
$ PX_POD=$(kubectl get pods -l name=portworx -n kube-system -o jsonpath='{.items[0].metadata.name}')
$ kubectl exec -it $PX_POD -n kube-system -- /opt/pwx/bin/pxctl volume inspect ${VOL}
Volume	:  947192497390503735
	Name            	 :  pvc-7ec6beb0-737a-11e9-8bbc-000c29549a08
	Size            	 :  5.0 GiB
	Format          	 :  ext4
	HA              	 :  3
	IO Priority     	 :  LOW
	Creation time   	 :  May 10 23:22:48 UTC 2019
	Shared          	 :  no
	Status          	 :  up
	State           	 :  Attached: 20ad6a2e-6fe2-4304-b038-4f1768d1b982 (70.0.60.174)
	Device Path     	 :  /dev/pxd/pxd947192497390503735
	Labels          	 :  io_profile=db,namespace=default,pvc=mssql-data,repl=3
	Reads           	 :  349
	Reads MS        	 :  131
	Bytes Read      	 :  22888448
	Writes          	 :  438
	Writes MS       	 :  663
	Bytes Written   	 :  139821056
	IOs in progress 	 :  0
	Bytes used      	 :  61 MiB
	Replica sets on nodes:
		Set 0
		  Node 		 : 70.0.60.174 (Pool 0)
		  Node 		 : 70.0.60.173 (Pool 0)
		  Node 		 : 70.0.60.171 (Pool 0)
	Replication Status	 :  Up
	Volume consumers	 :
		- Name           : mssql-6649487f9-8pg6n (ac1096df-737b-11e9-8bbc-000c29549a08) (Pod)
		  Namespace      : default
		  Running on     : 70.0.60.174
		  Controlled by  : mssql-6649487f9 (ReplicaSet)

The current volume size is 5GiB as defined in the PVC specification. Let’s expand it to 7GiB using the following command.

$ kubectl exec -it $PX_POD -n kube-system --  /opt/pwx/bin/pxctl volume update $VOL --size=7
Update Volume: Volume update successful for volume pvc-7ec6beb0-737a-11e9-8bbc-000c29549a08

px-sql-icp

Backing up and restoring a SQL Server instance through snapshots

Portworx supports creating Snapshots for Kubernetes PVCs. Since there is only one SQL Server instance, we can use regular, local snapshots to backup and restore.

Let’s create a snapshot for the Kubernetes PVC we created for SQL Server.

cat >  px-sql-snap.yaml << EOF
apiVersion: volumesnapshot.external-storage.k8s.io/v1
kind: VolumeSnapshot
metadata:
  name: px-sql-snapshot
  namespace: default
spec:
  persistentVolumeClaimName: mssql-data
EOF

$ kubectl create -f px-sql-snap.yaml
volumesnapshot.volumesnapshot.external-storage.k8s.io/px-sql-snapshot created

Verify the creation of the volume snapshot.

$ kubectl get volumesnapshot
NAME                AGE
px-sql-snapshot   25s

$ kubectl get volumesnapshotdatas
NAME                                                       AGE
k8s-volume-snapshot-362739d0-6406-11e9-85ba-1b691b451216   45s

We can now create a new PVC from the snapshot.

$ cat > px-sql-snap-pvc.yaml << EOF
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: px-sql-snap-clone
  annotations:
    snapshot.alpha.kubernetes.io/snapshot: px-sql-snapshot
spec:
  accessModes:
     - ReadWriteOnce
  storageClassName: stork-snapshot-sc
  resources:
    requests:
      storage: 5Gi
EOF

$ kubectl create -f px-sql-snap-pvc.yaml
persistentvolumeclaim/px-sql-snap-clone created

A new SQL Server pod based on the new PVC restored from the snapshot will contain the data from the original volume.

$ cat > px-sql-db-clone.yaml << EOF
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-snap
spec:
  strategy:
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 1
    type: RollingUpdate
  replicas: 1
  selector:
    matchLabels:
      app: mssql-snap  
  template:
    metadata:
      labels:
        app: mssql-snap
    spec:
      containers:
      - name: mssql
        image: microsoft/mssql-server-linux:2017-latest
        imagePullPolicy: "IfNotPresent"
        ports:
        - containerPort: 1433
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: SA_PASSWORD
          value:  "P@ssw0rd"
        volumeMounts:
        - mountPath: /var/opt/mssql
          name: mssqldb
      volumes:
      - name: mssqldb
        persistentVolumeClaim:
          claimName: px-sql-snap-clone
EOF

 
$ kubectl create -f px-sql-db-clone.yaml
deployment.extensions/mssql-snap created

Querying the new SQL Server pod will show the same data as the original.

$ SQL_POD=$(kubectl get pods -l app=mssql-snap -o jsonpath='{.items[1].metadata.name}')
$ kubectl exec $SQL_POD \
> -- /opt/mssql-tools/bin/sqlcmd -U sa -P P@ssw0rd \
> -d classicmodels -Q 'select city from offices'
city
--------------------------------------------------
San Francisco
Boston
NYC
Paris
Tokyo
Sydney
London

(7 rows affected)

Summary

Portworx can be easily deployed on ICP to run stateful workloads in production on Kubernetes. Through the integration of STORK, DevOps and StorageOps teams can seamlessly run highly available database clusters in Kubernetes. They can perform traditional operations such as volume expansion, snapshots, backup and recovery for the cloud native applications.