I’m only half joking when I say containers are boring and dull. They’re not. The technology is amazing. However, that technology doesn’t fundamentally change what you’re dealing with. It’s SQL Server. How do you capture detailed performance metrics in a container? Extended Events. How do you capture aggregated performance metrics and query plans in a container? Query Store. What’s the backup syntax for a database in a container? BACKUP DATABASE. We can keep going on this, but I won’t.
To a great extent, this is the same as SQL Server on Linux: once you have it installed, it works just like the Windows version (well, save for the things which aren’t there yet). All of the magical parts are in getting there.
Where it gets interesting is that the SQL Server container is also where the database files are stored by default. I raised a point (which Grant and others have already noted in the past) that persisted storage volumes allow us to throw away a SQL Server container without throwing away the database files, provided that the container is set up to use that persisted storage.
For example, I can map the SQL Server container to a local directory on my Ubuntu or Windows Server, or — as is the case with Kubernetes — a second container can serve as the storage volume. SQL Server is then just a compute engine, or a “service” as Anthony points out in the Twitter thread.
Because every rule has a counter-example (even this one), there are cases when you do want the data to live with the container. For example, a test database for a unit test runner should probably live inside the container rather than being a persisted volume. The reason is that you can blow away the database after a test run and start over for the next run. Putting together a database for a hackathon or user group can be another exception for the same reason. But for pretty much every other purpose, I’d rather have a persisted volume.
I haven’t written much about them yet (key emphasis there …) but AGs now being supported for containers in SQL Server 2019 is a big deal. Recently, SQL Server 2019 CTP 3.0 was released, but there’s a slight problem: if you try to deploy an AG with Kubernetes, you may see the following errors when trying to deploy the pods with the YAML that contains their definition. The services (i.e. instances of SQL Server) get created, but the pods do not.
Read on for the root cause and the solution.
In October 2018, Microsoft announced a change to the source of their Docker containers. You should be using the new Microsoft Container Registry (MCR) as the source for official Docker container images for Microsoft products.
While existing container images in the Docker Hub are not affected, you may not get updated images unless you switch.
On the Docker Hub website, there are instructions on hitting the MCR:
Start a mssql-server instance using the latest update for SQL Server 2017
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest
There are much more sophisticated ways to get this done using Docker Files. However, this illustrates the point quite simply. You can customize your servers and then use those customizations. You don’t have to re-customize every time. Again, this is just a small slice of why containers are so powerful.
This method is great when you want to build out a sample data set, like when you’re running through automated testing and want to start from the same known point each time.
Grant Fritchey continues a dive into containers. First up is running a Docker container:
Let’s break this down a bit so you know what you just did. The two ‘-e’ statements are setting environment variables. The first is accepting Microsoft’s end user license agreement, EULA. The second is setting the SA password. By default, we’re running a Developer Edition of SQL Server here. If you want to, you can change to a version that you have a specific license for using the MSSQL_PID environment variable. Documentation for that is located here.
Now, let’s create a new container, but, let’s use the same volume:
docker run -e 'ACCEPT_EULA=Y' ` -e 'SA_PASSWORD=$cthulhu1988' ` -p 1450:1433 ` --name DockerDemo19 ` -v sqlvol:/var/opt/mssql ` -d mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu
What happens next is marvelous.
It’s an exciting time to get into containers and if you’re feeling a little trepidatious, they’re containers—the worst thing you can do is mess one up and then you just blow it away and start over.
The first command you have to learn is ‘docker pull’. You then have to supply something for it to pull, an image that will be used to create your containers. I’m using Powershell for the commands I’m posting this week. Here’s how you get an image with SQL Server 2017:
docker pull mcr.microsoft.com/mssql/server:2017-latest
Click through to learn more.
In a previous post I went through how to deploy SQL Server running in an Azure Container Instance using Terraform.
In that post, I used hardcoded variables in the various .tf files. This isn’t great to be honest as in order to change those values, we’d need to update each .tf file. It would be better to replace the hardcoded values with variables whose values can be set in one separate file.
So let’s update each value with a variable in the .tf files.
Click through for a demo.
One of the key principals of Kubernetes is the ephemerality of Pods. No Pod is every redeployed, a completely new Pod is created. If a Pod dies, for whatever reason, a new Pod is created in its place there is no continuity in the state of that Pod. The newly created Pod will go back to the initial state of the container image defined in the Pod’s spec. This is very valuable for stateless workloads, not so much for stateful workloads like SQL Server.
This means that for a stateful workload like SQL Server we need to store both configuration and data externally from the Pod to maintain state through the recreation of a Pod. Kubernetes give us constructs two constructs to do that, environment variables and Persistent Volumes.
Read on for a good bit of background and a few scripts to help you get started.
What this is going to do is create an Azure Container Instance Group with one container it in, running SQL Server 2019 CTP 2.5. It’ll be publicly exposed to the internet on port 1433 (I’ll cover fixing that in a future post) so we’ll get a public IP that we can use to connect to.
Notice that the location and resource_group_name are set using variables that retrieve the values of the resource group are going to create.
Cool! We are ready to go!
Fun stuff, and Andrew promises more.