Speaking at SQLSaturday Cambridge!
I’m proud to announce that I will be speaking at SQL Saturday Cambridge on September 8th 2018! And wow, 748 SQL Saturdays! This one won’t let you down. Check out the amazing schedule of International Experts and Microsoft MVPs!
If you don’t know what SQLSaturday is, it’s a whole day of free SQL Server training available to you at no cost!
If you haven’t been to a SQLSaturday, what are you waiting for! Sign up now!
This year I have TWO sessions!
1. Monitoring Linux Performance for the SQL Server Admin
So you’re a SQL Server administrator and you just installed SQL Server on Linux. It’s a whole new world. Don’t fear, it’s just an operating system. It has all the same components Windows has and in this session we’ll show you that. We will look at the Linux operating system architecture and show you where to look for the performance data you’re used to! Further we’ll dive into SQLPAL and how it architecture and internals enables high performance for your SQL Server. By the end of this session you’ll be ready to go back to the office and have a solid understanding of performance monitoring Linux systems and SQL on Linux. We’ll look at the core system components of CPU, Disk, and Memory and monitoring techniques for each.
2. Containers – You Better Get on Board
Containers are taking over, changing the way systems are developed and deployed…and that’s NOT hyperbole. Just imagine if you could deploy SQL Server or even your whole application stack in just minutes. You can do that, leveraging containers! In this session, we’ll get your started on your container journey learning container fundamentals in Docker, then look at some common container scenarios and introduce deployment automation with Kubernetes. In this session we’ll look at Container Fundamentals with Docker Common Container Scenarios Automation with Kubernetes.
Thanks to everyone who attended the PASS Marathon Containers edition and to PASS for the opportunity to present. I received the Questions from the session and wanted to provide answers to the attendees and the community.
Here’s the list of questions from the session and my answers.
- What do you mean it is not for production environment in Windows?
- It’s my understanding that only Linux based SQL Server containers are supported and that Windows based containers are not. I’m looking to find an official statement, like a web site link) from Microsoft on this but I am having troubles doing so. Here is the official statement on running SQL Server on Linux in a Container – https://bit.ly/2LYPeKh
- When you say App1 on a container, is it just 1 executable/service or can be multiple of those on the same container?
- Generally speaking you’ll want only one process in a container. A primary reason for using containers is agility and a core way of achieving that is breaking dependencies by reducing what’s included inside the container.. Technically speaking, you can have more than one process inside a container. If fact SQL Server on Linux does. There’s the Watchdog process, then the actual SQL Server process. The output below is a process listing from inside a running SQL Server on Linux container. You can see PID 1 and 7 are processes inside the container.
root 1 /opt/mssql/bin/sqlservr
root 7 /opt/mssql/bin/sqlservr
For the internals geeks out there, let’s look a a process listing on the host OS that’s running our container. From there we can see that the sqlservr process is a child process of containerd which is managed by dockerd. This is the same SQL Server process inside the container. But in the first example you here can see the impact of namespaces…the process IDs are rebased and start at 1 and the second SQL Server PID is 9. In the output below you can see the PIDs are 2172 and 2213.
root 1034 /usr/bin/dockerd
root 1245 \_ docker-containerd
root 2154 \_ docker-containerd-shim -namespace moby -workdir
root 2172 \_ /opt/mssql/bin/sqlservr
root 2213 \_ /opt/mssql/bin/sqlservr
- Maybe I missed this part, how do I know what kind of image I could pull down?
- In the demos I show how to use docker search to find images that are available from the Docker Hub. If you prefer a web browser experience, check out the Docker Hub to see what containers are available to you. Here’s the code to find the mysql-server images available in Docker Hub.
- docker search mssql-server | sort
- Does SQL Container fit into production environment?
- Here is a link to the official word from Microsoft on running containers in production – https://bit.ly/2LYPeKh
- What I want you to leave this session with is an introduction to containers, starting your journey on what’s next when using containers. To that end here are some of the things you’ll need to consider before using containers in production
- Is your organization ready – Do the operational skills and technologies exist to support using containers in production.
- Backup and recovery – Does the organization have a strong backup and recovery environment. How are you going to protect the data running in a SQL Server container. Luckily, it’s just SQL Server on Linux so you can use the traditional technologies and techniques to backup your data.
- Data persistency – Understanding the underlying physical infrastructure and how to persistent data in ways that it’s protected and well performing.
- Orchestration – Is there technologies in place to manage the state of your containers, things like workload placement, starting, stopping and also data persistency.
- How do SQL Containers work with High Availability and Disaster Recovery?
- Backups and data persistency are primary concerns here. You still need to care and feed for your SQL Server databases just as if they were platformed on a full operating system. For HA, Microsoft has some guidance on how to use Kubernetes to provide HA services to your SQL Server containers here. What I want you to think about when using containers for SQL Server is deploying a new container is VERY fast. We want to be able to persist the data and be able to stand up a new container and mount our data inside that container. Using this technique we can restore SQL Services very quickly with low RTO. That itself is an interesting way to provide HA services without any additional technologies.
- Is there a way to have persistent storage for the system databases (e.g. master database for logins and what not)?
- In the demos during the session I defined a Docker Data Volume when we started the container where we mounted that as /var/opt/mssql/ inside the container. When SQL Server on Linux starts for the first time it will copy the system databases from its package directories into /var/opt/mssql/data. Since this data is stored in the persistent data volume if we stop and delete this container and start a new container pointing at that same docker data volume when SQL Server starts up it will use those system databases.
Starting a SQL Server Container with a Docker Data Volume. The -v parameter names the volume sqldata1 and /var/opt/mssql is where it will be mounted inside the container.
docker run \
-e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD \
--name 'sql1' \
-p 1433:1433 \
-v sqldata1:/var/opt/mssql \
- How about the backup of a container? can it be like VM’s snapshot?
- You can snapshot the state of a container with docker commit. This will create a new image from the container and that image can be used to create additional containers. But recall, containers are intended to be ephemeral, we really want to define the state of the container OUTSIDE of the container in code. The things inside the container that require data persistency, like databases should be taken care of using techniques like Docker Data Volume, backups and other high availability scenarios.