Enhancing DevOps with SQL Server on Linux Container

SQL Server 2017 has been released GA since October 2017 and since then, and as we speack, got 7 Cumulative Updates.
Among a lot of features with this 2017 edition, I will just highlight one of the key feature which is the fact that now SQL Server runs on Linux. Yes, you heard/read correctly, SQL Server runs on Linux! ;)
Through this blog article I will centralize resources and some scenario to demonstrate why and how SQL Server on Linux Container is very helpful for your DevOps journey.

Linux is leading in some areas like big data, devops and containers. Based on customers demand, SQL Server on Linux is resolving business and technical challenges such as:
  • Migrations from other DBs running on Linux
  • DevOps with containers and container platforms
  • When customers are happy with SQL Server, but want Linux
  • Developers on mac and Linux that need a local db for dev
  • Expanding market opportunity for SI and ISV partners
  • Hosters that specialize in Linux-based offerings

This MVA training course: Installing, Configuring, and Managing Microsoft SQL Server on Linux allows to know more about the features and capabilities of SQL Server 2017 on Linux where you will find detailed and advanced scenarios like:
  • Introduction to Microsoft SQL Server on Linux
  • Installation on Linux, Running Containers, and DevOps
  • High Availability, Disaster Recovery, Backup/Restore
  • Performance and Architecture
  • Linux-Based Tools
  • Security, Agent, Monitoring, and Troubleshooting
  • Microsoft SQL Server on Linux in Azure, AWS, GCP, and Private Cloud
  • Future Features of Microsoft SQL Server on Linux
And don't forget to get the free white paper about Why switch to SQL Server 2017 on Linux?

Let's see how to use SQL Server with Docker on Linux

Based on the official microsoft/mssql-server-linux Docker image page we could have more details about how to use it, the requirements, etc. The associated GitHub repository could be found here as well. In our case let's run these 2 basic commands we need to create a SQL Server on a Docker container:
docker pull microsoft/mssql-server-linux:2017-latest
docker run \
  -e 'ACCEPT_EULA=Y' \
  -e 'SA_PASSWORD=<password>' \
  -p 1433:1433 \
  --name <container-name> \
  -d microsoft/mssql-server-linux:2017-latest

Congratulations! From here, you have now a SQL Server you could use.

You could now for example connect inside your container to run SQL queries like CREATE DATABASE, CREATE TABLE, INSERT INTO, etc. by running this command:
docker exec \
  -it <container-name> \
  /opt/mssql-tools/bin/sqlcmd \
  -S localhost \
  -U sa \
  -P <password>

With the following command, if you have the sqlcmd tool installed and have a public IP address on your Docker host, you could connect from outside the container:
sqlcmd \
  -S <ip-address>,1433 \
  -U SA \
  -P <password>

Furthermore, you could follow this example to make a custom Dockerfile (without the NodeJS app parts) to be able to run SQL scripts to create your database/tables, import data from a csv file, etc.

Make sure you check out these 2 resources below to see what you could do more with your SQL Server Linux running on Docker:
One important remark you will notice with the second resource is the "data persistence" part. Indeed, if your container is updated/deleted you will loose your data. For that you need to persist them on a Volume, you need to map a folder of your Docker host while executing the docker run command: -v <host directory>:/var/opt/mssql

Let's now see how to host SQL Server on Azure Kubernetes Service (AKS)

On an existing AKS cluster we could simply run these two commands:
kubectl run <deployment-name> \
  --image microsoft/mssql-server-linux:2017-latest \
  --port 1433 \
  --env 'ACCEPT_EULA=Y' \
  --env 'SA_PASSWORD=<password>'

You could now connect into your container/pod to run from there SQL queries like we did with docker exec earlier but instead with kubectl exec.

We could expose this deployment via an internal IP address (only accessible from within the cluster, for example a web/api app):
kubectl expose deployment <deployment-name> \
  --port=1433 \
  --target-port=1433 \
  --type ClusterIP

For your information, we could also expose this deployment via a public IP address:
kubectl expose deployment <deployment-name> \
  --port=1433 \
  --target-port=1433 \
  --type LoadBalancer

And then connect via the sqlcmd tool like we did earlier.

You could find more details about configuring this with more advanced feature like Secrets, PersistentVolumes, etc. via Kubernetes deployment YAML file: Configure a SQL Server container in Kubernetes for high availability.

Like described in my previous blog article, Helm could help you to repeat and automate your Kubernetes deployments. Fortunately there is an official Helm Chart for Microsoft SQL Server 2017 on Linux (associated GitHub repository could be found here). Feel free to leverage it! ;)

Important note: to implement properly Always On availability groups for SQL Server containers, SQL Server 2019 now supports availability groups on containers in a Kubernetes cluster.

What about SQL Server on OpenShift?

On an OpenShift cluster on Azure for example, either in its free edition OpenShift Origin or if you have a RedHat license you could provision OpenShift Container Plaform, you will be able to deploy the same Docker image discussed on this blog article. If you look closely, it's an Ubuntu based image and it will work on RedHat. But for compliance, security, performance and support the recommendation is to have a RHEL based image. You could follow this tutorial to do so, where you will manipulate this specific image, with Secrets, PersistentVolume, etc. on OpenShift.
For your information, Microsoft is currently working on the Preview version of the official MSSQL Server RHEL Docker image. Stay tuned!

For more technical details, I co-presented during a RedHat and Microsoft event on June, 14 2018 where I demonstrated a deployment of a .NET Core app and a SQL Database on an OpenShift Container Platform cluster, check out the story in my GitHub repository.

Make sure also to check out these 2 resources below to see what you could do more with your SQL Server Linux running on RHEL and OpenShift:

In summary, what did we do and learn?

  • See the value proposition of SQL Server on Linux
  • Demonstrate how to host your SQL Server on: 
    • Linux Container (Ubuntu)
    • Kubernetes cluster with AKS (Ubuntu)
    • OpenShift cluster (Ubuntu and REHL)
Complementary tools and resources to leverage:
Hope you enjoyed this blog article, you appreciated the resources and the walk-through process to understand different concepts and options to host SQL Server on Linux Container. And hopefully you could leverage it for your own needs and context!


Post a Comment