Deploying single instance SQL Server 2017 on GKE

Stay organized with collections Save and categorize content based on your preferences.
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.

Architecture of persistent disks deployed in 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:

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:

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 Clean 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.

    Go to project selector

  3. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

  4. Enable the GKE API.

    Enable the API

  5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  6. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

  7. Enable the GKE API.

    Enable the API

  8. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

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

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

Creating a GKE cluster

  1. In Cloud Shell, 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 container get-server-config \
        --region us-central1 \
        --format='value(validMasterVersions[0])')
    
    export CLOUDSDK_CONTAINER_USE_V1_API_CLIENT=false
    gcloud 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.

  1. In Cloud Shell, generate a secure random password to be used for the SQL Server system admin (SA) account:

    SA_PASSWORD=$(</dev/urandom tr -dc '_0-9a-zA-Z' | head -c16)
    echo "SQL Server system admin password is: ${SA_PASSWORD}"
    
  2. Make a note of this password and store it in a secure location.

  3. Create a secret named sqlserver-secret that contains the SA account password

    kubectl create secret generic sqlserver-secret \
        --from-literal=SA_PASSWORD="${SA_PASSWORD}"
    

Creating the StorageClass and PersistentVolumeClaim

In this section, you create a sqlserver-volume.yaml file defining the StorageClass and a PersistentVolumeClaim for a 200GB 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.

  1. In Cloud Shell, create the sqlserver-volume.yaml manifest file:

    cat > sqlserver-volume.yaml <<EOF
    # sqlserver storage class and volume claim: sqlserver-volume.yaml
    kind: StorageClass
    apiVersion: storage.k8s.io/v1
    metadata:
      name: repd-us-central1-b-c
    provisioner: pd.csi.storage.gke.io
    parameters:
      type: pd-standard
      replication-type: regional-pd
    volumeBindingMode: WaitForFirstConsumer
    allowedTopologies:
    - matchLabelExpressions:
      - key: topology.gke.io/zone
        values:
        - 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
    EOF
    
  2. Deploy the manifest file:

    kubectl apply -f sqlserver-volume.yaml
    

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.

In this section, you create and deploy the manifest file sqlserver-deploy.yaml that 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.
  1. In Cloud Shell, create the sqlserver-deploy.yaml manifest file:

    cat > sqlserver-deploy.yaml <<EOF
    # sqlserver deployment and service: sqlserver-deploy.yaml
    apiVersion: apps/v1
    kind: Deployment
    metadata:
      name: sqlserver-deployment
    spec:
      replicas: 1
      selector:
        matchLabels:
          app: ms-sqlserver
      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
    EOF
    
  2. Deploy the manifest file:

    kubectl create -f sqlserver-deploy.yaml
    
  3. 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...
    
  4. Verify that the services are running:

    kubectl get services
    

    Make a note of the EXTERNAL_IP listed in the output. You will need that 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  EXTERNAL_IP  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 mssql-cli:

    pip install mssql-cli
    
  2. Connect to SQL Server:

    ~/.local/bin/mssql-cli -S EXTERNAL_IP -U sa
    
    • Replace EXTERNAL_IP with the value of the external IP address that you noted in a previous step.
    • When prompted, enter the SQL Server system admin account password that you generated earlier.
  3. Validate your connection:

    SELECT GETDATE()
    

    The output looks similar to the following:

    Mail: sqlcli@microsoft.com
    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.

Simulating a zone failure

In this section, you simulate a zone failure by shutting down the node in the zone where the SQL Server is running. GKE moves your workload to the other zone and attaches the regional disk to the new node.

  1. In Cloud Shell, get the current node, instance group, and zone of the SQL Server pod and store them in the environmental variables NODE, IG, and ZONE respectively:

    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 "In instance group: ${IG}"
    echo "In zone: ${ZONE}"
    
  2. Simulate a zone failure by resizing the instance group where the SQL Server pod is running to zero nodes.

    gcloud compute instance-groups managed resize ${IG} --size=0 --zone ${ZONE}
    

    GKE migrates the pod to the node in the other zone–this might take several minutes to complete, as GKE needs to detect that the node has been shut down, remount the regional persistent disk, and then start the SQL Server container on the node in the other zone.

  3. To see the failover take place, monitor the state of the deployment and the pod:

    while true ; do
       echo "----"
       date
       kubectl get deployments
       kubectl get pods -l app=ms-sqlserver -o wide
       sleep 15
    done
    

    The output will show the deployment going from a READY state of 1/1, to 0/1 as GKE detects the loss of the zone. The STATUS of the pod goes from Running on the node which has been shut down, to ContainerCreating on the replacement node in the other zone.

    NAME                   READY   UP-TO-DATE   AVAILABLE      ...
    sqlserver-deployment   0/1     1            0              ...
    NAME                             READY   STATUS            ...
    sqlserver-deployment-xxxx-xxxx   0/1     ContainerCreating ...
    

    Once the migration is complete, the output shows that the deployment and the pod have a READY state of 1/1, and the pod has a STATUS of Running.

    NAME                   READY   UP-TO-DATE   AVAILABLE      ...
    sqlserver-deployment   1/1     1            1              ...
    NAME                             READY   STATUS            ...
    sqlserver-deployment-xxxx-xxxx   1/1     Running           ...
    
  4. Press Control+C to interrupt the repeating status query.

  5. Reconnect to SQL Server:

    mssql-cli -S [EXTERNAL_IP] -U sa
    

    Your service is now running on a node in a different zone, using the same regional persistent disk.

  6. To reverse the simulated zone failure, resize the instance group that you resized previously back to one node. This will not change anything in the GKE deployment.

    gcloud compute instance-groups managed resize ${IG} --size=1 --zone ${ZONE}
    

Clean 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

  1. In the Google 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 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