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:
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
- 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.
-
In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.
- Enable the GKE API.
-
In the Cloud Console, 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.
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
Create a regional cluster named
sqlserver-cluster
that spans two zones in theus-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.
In Cloud Shell, deploy the manifest file:
kubectl apply -f sqlserver-volume.yaml
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:
In Cloud Shell, deploy the manifest file:
kubectl create -f sqlserver-deploy.yaml
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...
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.
In Cloud Shell, install the Python package called
mssql-cli
:sudo pip install mssql-cli
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 thesqlserver-secret
in Kubernetes.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>
To exit
mssql-cli
, pressControl+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.
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}"
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.
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...
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
- In the Cloud Console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.
Delete individual resources
In Cloud Shell, delete the cluster:
export CLOUDSDK_CONTAINER_USE_V1_API_CLIENT=false gcloud beta container clusters delete sqlserver-cluster --region us-central1
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.