Deploying single instance SQL Server 2017 on GKE

By: Stéphane Fréchette, Google Cloud Solution Architect, Pythian

This tutorial shows you how to set up and deploy a single instance SQL Server 2017 on Linux instance by using regional persistent disks on Google Kubernetes Engine (GKE). This deployment isn't SQL Server always on and might not be suitable for mission-critical or large workloads. Instead, Pythian recommends this deployment for smaller workloads in production, development, and staging environments.

The following diagram illustrates how regional persistent disks provide synchronous replication between two zones.

This deployment architecture provides resiliency against zone failure. In this configuration, GKE is the cluster orchestrator. When a zone fails, the orchestrator moves your workload to the other zone and attaches the regional disk to the new node.

This tutorial assumes you are familiar with the following:

• GKE
• Cloud Shell
• SQL Server 2017 on Linux

Objectives

• Create a regional GKE cluster.
• Create a Kubernetes StorageClass that is configured for replicated zones.
• Deploy SQL Server with a regional disk that uses the StorageClass.
• Simulate a zone failure by deleting a node.
• Verify that the SQL Server instance and data migrate successfully to another replicated zone.

Costs

This tutorial uses the following billable components of Google Cloud:

• GKE
• Compute Engine disks

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

When you finish this tutorial, you can avoid continued billing by deleting the resources you created. For more information, see Cleaning up.

Before you begin

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

4. Enable the GKE API.

   Enable the API

5. In the Cloud Console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Cloud Console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Cloud SDK already installed, including the gcloud command-line tool, and with values already set for your current project. It can take a few seconds for the session to initialize.

You run all of the commands for this tutorial in Cloud Shell.

Creating a GKE cluster

You create Kubernetes objects by using manifest files in YAML format. These files are provided for you in a GitHub repository.

1. In Cloud Shell, download the manifest files from the GitHub repository:

   git clone https://github.com/sfrechette/sqlserver-gke-cluster.git
   cd sqlserver-gke-cluster
   

2. Create a regional cluster named sqlserver-cluster that spans two zones in the us-central1 region. This command might take a while to complete.

   CLUSTER_VERSION=$(gcloud beta container get-server-config \
       --region us-central1 \
       --format='value(validMasterVersions[0])')
   
   export CLOUDSDK_CONTAINER_USE_V1_API_CLIENT=false
   gcloud beta container clusters create sqlserver-cluster \
       --cluster-version=${CLUSTER_VERSION} \
       --machine-type=n1-standard-2 \
       --region=us-central1 \
       --num-nodes=1 \
       --node-locations=us-central1-b,us-central1-c
   

You now have a regional cluster with one node in each zone, us-central1-b andus-central1-c. The gcloud command also automatically configures the kubectl command to connect to the cluster.

Creating a Kubernetes secret

Kubernetes can manage sensitive configuration information, such as passwords, as secrets.

• In Cloud Shell, create a secret named sqlserver-secret that contains the password that is used for the SQL Server system admin (SA) account:

  kubectl create secret generic sqlserver-secret \
      --from-literal=SA_PASSWORD="[YOUR_COMPLEX_P@SSWORD]"
  

  Where [YOUR_COMPLEX_P@SSWORD] represents a value you enter as the password for the SQL Server system admin.

Creating the StorageClass and PersistentVolumeClaim

In this section, you use the sqlserver-volume.yaml file to create the StorageClass and PersistentVolumeClaim to define the zones of the regional disk. The zones listed in theStorageClass match the zones of the GKE cluster. It's a best practice to store database data files (mdf) and log files (ldf) on separate volumes. Placing both data and log files on the same device can cause contention for that device, resulting in poor performance. Placing the files on separate drives allows the I/O activity to occur at the same time for both the data and log files. However, for the purposes of this tutorial, the database data and log files are created on the same volume.

The sqlserver-volume.yaml file looks like the following:

# sqlserver storage class and volume claim: sqlserver-volume.yaml
kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
  name: repd-us-central1-b-c
provisioner: kubernetes.io/gce-pd
parameters:
  type: pd-standard
  replication-type: regional-pd
  zones: us-central1-b, us-central1-c
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: sqlserver-volume
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 200Gi
  storageClassName: repd-us-central1-b-c

This manifest also describes the PersistentVolumeClaim that request 200 Gibit of storage.

1. In Cloud Shell, deploy the manifest file:

   kubectl apply -f sqlserver-volume.yaml
   

2. Validate if the claim was bound successfully:

   kubectl get pvc sqlserver-volume
   

   It might take a couple of seconds to provision. The output looks similar to the following:

   NAME              STATUS  VOLUME        CAPACITY  ACCESSMODES  STORAGECLASS
   sqlserver-volume  Bound   pvc-865594fc  200Gi     RWO          repd-us-centr
   

Setting up and deploying SQL Server

The container hosting the SQL Server instance is described as a Kubernetes deployment object. The deployment creates a replica set. The replica set creates the pod.

The next manifest file describes the following:

• The container based on the SQL Server mssql-server-linux Docker image.
• The sqlserver-volume persistent volume claim.
• The sqlserver-secret that you already applied to the Kubernetes cluster.
• The manifest also describes a load-balancer service. The load balancer guarantees that the IP address persists after the SQL Server instance is recovered.

# sqlserver deployment and service: sqlserver-deploy.yaml
apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: sqlserver-deployment
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: ms-sqlserver
    spec:
      terminationGracePeriodSeconds: 10
      containers:
      - name: ms-sqlserver
        image: mcr.microsoft.com/mssql/server:2017-latest
        ports:
        - containerPort: 1433
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: sqlserver-secret
              key: SA_PASSWORD
        volumeMounts:
        - name: sqlserver-volume
          mountPath: /var/opt/mssql
      volumes:
      - name: sqlserver-volume
        persistentVolumeClaim:
          claimName: sqlserver-volume
---
apiVersion: v1
kind: Service
metadata:
  name: sqlserver-deployment
spec:
  selector:
    app: ms-sqlserver
  ports:
    - protocol: TCP
      port: 1433
      targetPort: 1433
  type: LoadBalancer

To deploy the SQL Server, complete the following steps:

1. In Cloud Shell, deploy the manifest file:

   kubectl create -f sqlserver-deploy.yaml
   

2. Verify that the pod is running. It might take a few seconds for the pod to show a status of Running while the persistent disk is attached to the compute node. You might have to issue the following command several times until the deployment is ready.

   kubectl get pods -l app=ms-sqlserver -o wide
   

   The output is similar to the following:

   NAME           READY  STATUS    RESTARTS  ...  NODE
   sqlserver-dep  1/1    Running   0         ...  gke-sqlserver-...-pool59c596...
   

3. Verify that the services are running:

   kubectl get services
   

   In the output, make a note of the External-IP because you need the external IP address to connect to the SQL Server instance:

   NAME          TYPE         CLUSTER-IP    EXTERNAL-IP     PORT(S)
   kubernetes    ClusterIP    10.55.240.1   <none>          443/TCP
   sqlserver-dep LoadBalancer 10.55.243.48  35.243.149.107  1433:30809/TCP
   

Connecting to SQL Server

Now that you have SQL Server running on your cluster, you can connect to it.

1. In Cloud Shell, install the Python package called mssql-cli:

   sudo pip install mssql-cli
   

2. Connect to SQL Server:

   mssql-cli -S [EXTERNAL_IP] -U sa
   

   Replace [EXTERNAL_IP] with the value of the external IP address that you copied in a previous step. When prompted, enter the password [YOUR_COMPLEX_P@SSWORD] as the sqlserver-secret in Kubernetes.

3. Validate your connection:

   SELECT GETDATE()
   

   The output looks similar to the following:

   Mail: [e-mail]
   Home: http://github.com/dbcli/msql-cli
   master>
   master> SELECT GETDATE()
   +----------------------------+
   |  (No column name)          |
   |----------------------------|
   | 2019-06-06 16:38:40.600    |
   +----------------------------+
   (1 row affected)
   Time:2.266s (2 seconds)
   master>
   

4. To exit mssql-cli, press Control+D.

   The output is:

   Goodbye!
   

Simulating a zone failure

In this section, you simulate a zone failure and watch Kubernetes move your workload to the other zone and attach the regional disk to the new node.

1. In Cloud Shell, get the current node of the SQL Server pod:

   NODE=$(kubectl get pods -l app=ms-sqlserver -o jsonpath='{.items..spec.nodeName}')
   
   ZONE=$(kubectl get node $NODE -o \
   jsonpath="{.metadata.labels['failure-domain\.beta\.kubernetes\.io/zone']}")
   
   IG=$(gcloud compute instance-groups list \
       --filter="name~gke-sqlserver-cluster-default-pool zone:(${ZONE})" --format='value(name)')
   
   echo "Pod is currently on node ${NODE}"
   echo "Instance group to delete: ${IG} for zone: ${ZONE}"
   

2. Simulate a zone failure by deleting the instance group for the node where the SQL Server pod is running. This might take a while to complete.

   gcloud compute instance-groups managed delete ${IG} --zone ${ZONE}
   

   Kubernetes detects the failure and migrates the pod to a node in another zone.

3. Verify that both the SQL Server pod and the persistent volume migrated to the node that is in the other zone:

   kubectl get pods -l app=ms-sqlserver -o wide
   

   The output displays a node that is different from the previous step:

   NAME           READY  STATUS   RESTARTS  ...  NODE
   sqlserver-dep  1/1    Running  0         ...  gke-sqlserver-...-pool22305c...
   

4. Reconnect to SQL Server:

   mssql-cli -S [EXTERNAL_IP] -U sa
   

   You have attached a regional persistent disk to a node that is in a different zone.

Cleaning up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.

Delete the project

Caution: Deleting a project has the following effects:
• Everything in the project is deleted. If you used an existing project for this tutorial, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com URL, delete selected resources inside the project instead of deleting the whole project.
1. In the Cloud Console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

Delete individual resources

1. In Cloud Shell, delete the cluster:

   export CLOUDSDK_CONTAINER_USE_V1_API_CLIENT=false
   gcloud beta container clusters delete sqlserver-cluster --region us-central1
   

2. In Cloud Shell, delete the disk:

   PD=$(gcloud compute disks list  \
       --filter="name~'gke-sqlserver-cluster'" \
       --format='value(name)')
   gcloud compute disks delete ${PD} --region us-central1
   

What's next

• Explore other GKE tutorials.
• Read more about SQL Server on Google Cloud.
• Try out other Google Cloud features for yourself. Have a look at our tutorials.