The SQL Server DBA’s Guide to Docker Containers: Agile Deployment without Infrastructure Lock-in

By Edwin M Sarmiento

Get introduced to the world of Docker containers from a SQL Server DBA’s perspective. This book explains container technology and how it can improve the deployment of your SQL Server databases without infrastructure lock-in. You will be equipped with the right technical skills to guide stakeholders in your business as they adopt and adapt to new technologies to improve time-to-market and competitiveness. You will learn how to build a lab environment at home on which to build skills that transfer directly into your day job. 
This book teaches you how to install and configure Docker on both Windows Server and Linux operating systems. You will learn the most common Docker commands that you need to know as a DBA to deploy and manage SQL Server on containers. Support for SQL Server on Linux is new, and this book has your back with guidance on creating Docker images specifically for deployment to a Linux platform. Included is coverage of key Linux commands needed to manage SQL Server on that operating system. By the end of the book you will have learned how to create your own custom SQL Server container images with configuration settings that are specific to your organization, that are capable of being deployed to both Windows Server and Linux. 
What You Will Learn

• Create Docker containers for agile deployment of SQL Server
• Run multiple SQL Server instances on a single Linux machine
• Deploy custom images specific to your organization’s needs
• Know the benefits and architecture of container technology
• Install and configure Docker on Windows Server and Linux 
• Manage and persist SQL Server data in Docker containers

Who This Book Is For
Intermediate to senior SQL Server DBAs who are familiar with SQL Server on Windows and want to build their existing skills to deploy and manage SQL Server on Linux and through Docker containers. Readers should have a grasp of relational database concepts and be comfortable with the Transact-SQL language.

• Language: English
• Publisher: Apress
• Release date: May 29, 2020
• ISBN: 9781484258262

Book preview

© Edwin M Sarmiento 2020

E. M. SarmientoThe SQL Server DBA’s Guide to Docker Containers https://doi.org/10.1007/978-1-4842-5826-2_1

1. Introduction to Containers

Edwin M Sarmiento¹ 

(1)

Ottawa, ON, Canada

Those who cannot remember the past are condemned to repeat it.

—George Santayana

Container technologies are changing the way we develop, deploy, and run software. While it may seem like a technology that came out of nowhere and is now taking over IT organizations, nothing could be further from the truth.

In this chapter, we’ll travel back in time to look at a bit of the history around container technologies and how it evolved. You might be thinking: I hated history in high school. And I can totally relate. I only read history in high school and college for the sole purpose of answering test questions. I hated memorizing names and dates. So, why even bother with the history of container technologies?

History gives us the benefit of hindsight, something you don’t have when you are experiencing something for the very first time. When somebody else has already done what you are trying to accomplish, you have the benefit of the lessons they have learned, their mistakes that you can avoid, and possibly the patterns that seem to appear out of nowhere. These history lessons are full of insights that can help you better understand the technology or the world, in general. Over the years, I’ve learned to appreciate history and how it shaped our current reality – better yet, how I can change my present so I can shape the future.

We’ll also look at the company – Docker, Inc. – behind the development of the most widely used and recognized container technology today.

Conversations around container technologies wouldn’t be complete without comparing it with virtualization technologies. We’ll spend a little bit of time looking at the similarities and differences between the two.

Finally, we’ll look at how Microsoft adopted the container technology for both Windows and SQL Server.

The Latest 30-Year-Old Technology

I was surprised when I learned that container technologies date back to 1979. It was the Unix V7 that allowed processes to run in isolation – the ability to isolate and protect one process from another – at the operating system level. The idea came about due to the need to share very expensive computing resources among many users at the same time. Think back to the days when mainframes can only do one specific task that can take days or even weeks to run.

However, like any other technological innovation that didn’t get as much adoption, process isolation technologies became stagnant. It wasn’t until the year 2000 when the Unix-like operating system FreeBSD introduced Jails – a mechanism to partition a computer system into several independent mini-systems, each with its own files, processes, and security. You can think of this as the early days of the virtualization technologies that we know of today.

Several variations of implementing process isolation technologies evolved afterward, including the Linux VServer in 2001 (a patch to the Linux kernel that allowed operating system–level virtualization), Solaris Containers in 2004 (former Sun Microsystems’ implementation of operating system–level virtualization), and Open Virtuozzo in 2005. In 2006, Google came up with their own addition to the Linux kernel and named it Process Containers, later renaming it to Control Groups (cgroups) to avoid confusion with the word container in the context of the Linux containers.

The year 2008 became the pivotal moment for the evolution of container technologies when the Linux Containers (LXC) project was created, merging the work that Google engineers have done in cgroups into the Linux kernel. It allowed for running multiple isolated Linux systems on a host using a single Linux kernel – without the need for an additional patch.

The LXC became the building block of the modern container technologies that came after it. In 2011, VMWare with EMC and General Electric started an open source initiative named Cloud Foundry Warden for managing a collection of containers across multiple hosts. In 2013, Google created yet another open source version of their container stack called lmctfy (which stands for Let Me Contain That For You) and have since collaborated with Docker, Inc. for the development of the container runtime environment, libcontainer.

Container technologies may seem like the latest technology that is sweeping the IT world. The fact of the matter is that it isn’t new. It was just way ahead of its time. And now that its time has arrived, you just cannot afford to ignore it.

Docker – The Company and the Container Runtime

Docker is to container runtime engine much like how VMWare is to virtualization or Red Hat is to Linux – it has become a huge brand name in the IT world. The Docker runtime engine was developed by the company Docker, Inc. The company didn’t start out as a development company. In fact, they started as a platform-as-a-service company that allowed developers to build and run apps without worrying about the underlying infrastructure. Unfortunately, the company was struggling to generate revenue and pay their bills. The good thing about it is that they learned how to make the most out of the situation they were in.

While Docker, Inc. was having financial difficulties, the engineers decided to open source the underlying technology – Docker, the container runtime engine – behind their platform-as-a-service offering in 2013. They didn’t expect it to take off. And when it did, the company pivoted their business and focused on Docker. They even changed their name from dotCloud, the original name when the company was founded, to Docker, Inc. Thus, the modern container technology was born. Early versions of Docker leveraged LXC as the container runtime engine. Later versions use libcontainer.

It’s interesting to see how Docker pivoted from a struggling business to what can now be considered a profitable software company. We, IT professionals, need to learn from their example. We need to learn how to pivot when the need arise. Learn emerging technologies instead of settling for old ones so long as they meet business objectives. We need to learn how to pivot our roles from purely technical to more of leadership. And since technology is constantly evolving, we need to evolve with it, changing roles whenever necessary.

This is key to success in the IT industry.

The Case for Containers

We IT professionals like to geek out on technology because they are cool and fun. It’s easy to fall into the trap of focusing on technology for technology’s sake. But realize that every successful technology is meant to solve a business problem. To be successful as an IT professional, we need to focus on the business problem that a technology solution is meant to solve.

So what business problem does container technology solve? Reliably run software when moved from one computing environment to another. SQL Server administrators deal with this every time a database is migrated or upgraded from one platform to another – from physical machine to another physical machine, from physical machine to virtual machine, from older version to newer version, or from on-premises to the cloud. A more common scenario is when a developer writes T-SQL code from their development environment and promotes the code in staging environment and even production. Even with a proper change management process, this can still be an issue because there is no guarantee that the different computing environments will be the same. Configuration drift accounts for many high availability and disaster recovery system failures that I have to deal with as a consultant.

Container technology addresses this problem. It does so by packaging an application and all of its dependencies like binaries, kernel libraries, configuration files, system tools, and so on and creating an entire runtime environment. A container image is created and packaged with the application and all of its dependencies. The image is then deployed as a container at runtime.

Containers vs. Virtualization

At first glance, it may seem that containers look a lot like virtualization given their nature and functionality. Don’t be confused. Have a look at the diagram of the hypervisor architecture in Figure 1-1.

../images/484552_1_En_1_Chapter/484552_1_En_1_Fig1_HTML.jpg

Figure 1-1

Hypervisor architecture

In the world of virtualization, you run a hypervisor – VMWare, Hyper-V, Xen, and so on – on top of a hardware. You, then, create a virtual machine with its own operating system before you can install and run your application. Each virtual machine is configured to use a portion of the hardware’s CPU, memory, network, and storage resources. For years, virtualization solved a major business problem that the IT organization faced: overprovisioning physical hardware. In the past, we simply guessed and overprovisioned the specifications of the physical machine, letting it run while barely using even a quarter of the hardware resources. Virtualization allowed us to create multiple virtual machines on a single physical machine to maximize every bit of hardware resource available. It also allowed for process and application isolation – they run within the boundaries of the virtual machine. This made the finance people very happy. But not us.

We spend a huge amount of time managing operating systems – patching, securing, monitoring, updating, auditing, licensing, and even allocating resources for them. It’s like managing operating systems has become a business unit on its own. We’re not in the business of managing operating systems, but our day-to-day tasks sure seem like we are. Wouldn’t it be great if you only had one operating system that runs the different applications but in an isolated process, so they don’t interfere with one another, much like how one application is isolated from another through virtual machines? We don’t need to get rid of operating systems, we just need less of them.

This is where containers come in.

../images/484552_1_En_1_Chapter/484552_1_En_1_Fig2_HTML.jpg

Figure 1-2

Container architecture

In Figure 1-2, you only have one operating system. The containers share the same operating system kernel with other containers, each one running as isolated processes in user space. Containers abstract the operating system kernel instead of abstracting the hardware like what virtualization does. Imagine the impact this has on your resources. Instead of having multiple copies of the operating system running on guest virtual machines, you only have a single copy, thus reducing the amount of storage space requirement. You are also reducing the amount of administrative overhead necessary with managing operating systems. If you have ever had to work during the weekends and holidays just to install that critical security patch on all of your servers, you know how big of a deal this is. Plus, given the resource requirements for containers, you can run more of them in a single host compared to virtual machines.

Microsoft, Containers, and SQL Server on Linux

In the short period that I had working as an Oracle DBA, I made my own decision to pivot. Back then, Oracle was the name when it comes to relational databases. My peers were moving into Oracle and Java. But I decided to go the opposite way – I switched to Microsoft SQL Server and .NET. I did it because I saw how Microsoft did software, focusing more on the users rather than merely the technology. They created user interfaces, accessible documentation, tutorials, blog posts, online communities, and the like. The hard-core geeks made fun of it. But customers loved it. So, I pivoted.

The year was 2015. At the annual Microsoft MVP Summit, I was listening to Microsoft executives from the database systems group talk about a project that they were working on. They called it Project Helsinki. The mission is to bring SQL Server to Linux. Microsoft had customers asking whether they would make SQL Server available on Linux. The executives talked about how the next generation of developers didn’t really care about the underlying operating system – they just want a data platform. Microsoft was still focusing on the customers.

When SQL Server 2017 was released, it was made available for both Windows and Linux. It was no longer an April Fool’s joke (I wish they did not release it on April 1st). This was real. SQL Server 2017 was the first version that ran on Windows, Linux, and Docker containers. I immediately downloaded and installed SQL Server on my CentOS Linux virtual machine, and within a few minutes, I was able to connect to it remotely using my SQL Server Management Studio installation on my Windows 8 machine.

Tip

The Apress book Pro SQL Server on Linux by Bob Ward provides a back story on how Microsoft managed to make this seemingly impossible task a reality.

It was also during this time that Microsoft incorporated the Docker engine in the Windows Server 2016 operating system – Windows Server Containers. They partnered with Docker, Inc. to create a container runtime engine that is native inside the Windows Server operating system. Since Docker is open source, it allowed Microsoft to make modifications to it and make it work on Windows.

Open source, Microsoft, SQL Server on Linux , Containers. I’m sure the Steve Ballmer of 2001 didn’t see this coming.

Summary

As container technology is taking over the IT landscape, administrators need to be prepared to modernize their SQL Server databases and deploy them on newer platforms like Linux, specifically Docker containers. This is the goal of this book.

In the next chapter, we will look at how to leverage container technologies in a platform that SQL Server administrators are familiar with – the Windows Server operating system.

© Edwin M Sarmiento 2020

E. M. SarmientoThe SQL Server DBA’s Guide to Docker Containers https://doi.org/10.1007/978-1-4842-5826-2_2

2. Install and Configure Docker on Windows Server

Edwin M Sarmiento¹ 

(1)

Ottawa, ON, Canada

Learning is the creation of a relationship between the known and the unknown.

—Tony Robbins

The previous chapter introduced the container technology, its origins, and how SQL Server now runs on Linux and Docker containers. But for someone who has minimal to no experience with Linux, this can be a bit overwhelming. So, instead of jumping straight into Linux, this chapter will help you get started with Docker containers using a platform that you are already familiar with – the Windows Server operating system. Microsoft partnered with Docker, Inc. to bring Docker containers on the Windows Server operating system, making it easy for Windows and SQL Server administrators to learn and deploy Docker containers.

We will be using Windows Server 2016 Build 1607 for this chapter, but later versions such as 1709 and 1803 can be used as well. However, you certainly don’t want to be learning container technologies while having to deal with the issues of prerelease software. Trust me, I lost a lot of hair in the early 2000s dealing with prerelease software.

Note

This chapter does not go into the process of installing the Windows Server operating system. It is assumed that an existing clean installation of a Windows Server 2016 is available and has Internet connectivity. For a walk-through of installing Windows Server 2016, refer to Appendix A.

Also, the default Windows Server installation will opt for Server Core, unless you specifically choose Desktop Experience. You will be working with the command line when you start working with Docker and Linux so just stick with the graphical user interface on Windows for now.

Minimum System Requirements

Like any software installation, you need to meet the minimum system requirements to successfully use a Windows Server operating system as a Docker container host. A Docker container host is the operating system that runs the Docker daemon (a daemon is simply the Linux equivalent of a service in Windows).

RAM: At least 4GB. You don’t want to end up spending a lot of time troubleshooting a container deployment issue only to find out that you don’t have enough memory resources. Plus, a SQL Server instance will use up as much memory resource as it possibly can. It’s the same behavior when running it in a container.

Operating system: Just because we’re using Windows Server 2016 doesn’t mean that’s all you need to know. Remember, containers share the same operating system kernel. This means the operating system of the container host should support the operating system of the container image. Since we’re deploying a Windows Server 2016 container host, you can only deploy a Windows Server 2016 container image running Server Core or Nano Server. We’ll skip Nano Server since SQL Server is not supported to run on top of Nano Server.

Disk space: The bare minimum is 32GB. Much like virtual machines, each container image requires its own disk space. Allocate enough disk space so you can run multiple containers at the same time.

Enable Containers Feature in Windows Server 2016

Installing and configuring a Docker container host on a Windows Server 2016 machine is a three-step process. You need the machine to be connected to the Internet to download the binaries, the PowerShell packages, and the Windows-based container images.

The first step is to enable the Containers feature on Windows Server 2016:

1.

From the Start menu, select Server Manager.

2.

In Server Manager Dashboard, click the Add roles and features link. This will run the Add Roles and Features Wizard.

3.

Click Next until you get to the Select features dialog box. Select the Containers checkbox as shown in Figure 2-1 and click Next. Note that the .NET Framework 4.6 Features checkbox is selected by default on Windows Server 2016 Build 1607. If it isn’t, expand the .NET Framework 4.6 Features checkbox and make sure that .NET Framework 4.6 (Installed) is shown.

../images/484552_1_En_2_Chapter/484552_1_En_2_Fig1_HTML.jpg

Figure 2-1

Check the Containers checkbox in the Select features dialog box

4.

In the Confirm installation selections dialog box, click Install.

Since you will be working with Docker commands via the command line – both on Windows and Linux – this is a great opportunity to start working with the command line from within a Windows environment. Embrace the blinking cursor and get comfortable with it. An alternative to enabling the Containers feature is by using the Install-WindowsFeature PowerShell cmdlet. Open an elevated administrative PowerShell command shell and run the following command:

Install-WindowsFeature –Name Containers

You will be prompted to restart the machine after enabling the Containers feature to complete the installation process. Either you perform the restart using the method you’re familiar with or use the Restart-Computer PowerShell cmdlet.

Download and Install the Docker-Microsoft PackageManagement Provider

The second step is to download and install the Docker-Microsoft PackageManagement provider . The role of a package management system is to automate the process of consistently discovering, installing, updating, configuring, and removing a computer software. In this case, you will be installing a package management system specifically for use with Docker.

Run the following PowerShell command to download and install the Docker-Microsoft PackageManagement provider:

Install-Module -Name DockerMsftProvider -Force

When prompted to download and install the NuGet provider, type Y to respond Yes, as shown in Figure 2-2.

../images/484552_1_En_2_Chapter/484552_1_En_2_Fig2_HTML.jpg

Figure 2-2

Downloading the NuGet provider with the Docker-Microsoft PackageManagement provider

Note

The topic of package management systems can be a bit confusing for IT professionals who mainly work on the Windows platform. For years, software on Windows was distributed through EXE, MSI, and MSU files – just download the installation file, double-click, and install. This isn’t the case with Linux. Given the open source nature of Linux and the availability of software from different sources, package managers are used to install and manage software. PowerShell 5.0 introduced the capability to leverage a package manager to simplify the complexity of installing software and PowerShell modules.

If you want to geek out on the details of how the Docker-Microsoft PackageManagement provider is implemented , check out the GitHub repository at https://github.com/OneGet/MicrosoftDockerProvider.

Download and Install the Docker Package

The third step is to download and install the Docker software package . Run the following PowerShell command to download and install the Docker software package using the Docker-Microsoft PackageManagement provider that you downloaded in the previous step. Figure 2-3 shows a successful installation of Docker Enterprise Edition (EE).

Install-Package -Name docker -ProviderName DockerMsftProvider -Force

../images/484552_1_En_2_Chapter/484552_1_En_2_Fig3_HTML.jpg

Figure 2-3

Downloading and installing the Docker software package

This will create a Windows service named Docker Engine that calls the command C:\Program Files\Docker\dockerd.exe --run-service

The service is configured to automatically start when the server starts, but the installation process does not explicitly start the service. This is where you need to do the unpopular server reboot to update the environment variables so you can run Docker commands from either the command prompt or the PowerShell command shell. Don’t worry. This will be the last time you need to reboot your server .

Verifying the Docker Engine Installation

I did say you need to start being comfortable working with the command line. From here onward, you will be working with the command line – either the command prompt or PowerShell command shell. I prefer using PowerShell so I can leverage the available PowerShell cmdlets alongside the Docker commands. Just remember that the output of a PowerShell command or cmdlet is either an object or a collection of objects, while the output of a Docker command is text. Also, run every command in an elevated administrative PowerShell command shell.

Run the following command to check the version of Docker installed on the server:

docker version

Figure 2-4 shows the output of the command, displaying the version of the Docker client and the Docker engine running on the machine.

../images/484552_1_En_2_Chapter/484552_1_En_2_Fig4_HTML.jpg

Figure 2-4

Displaying the version of Docker client and Docker engine

The OS/Arch: properties for both client and server show windows/amd64 since the Docker engine is running on a Windows Server 2016 machine. Also, what was installed is the Docker Engine – Enterprise Edition. This means that you have full support from both Microsoft and Docker when you deploy containers on your Docker host. In fact, even though the Docker engine is the one responsible for running containers on a Windows Server machine, Microsoft is the first point of contact for support. Chapter 4 describes the difference between Docker Enterprise Edition and Docker Community Edition in more detail.

Run the following command to display system-wide information regarding the docker installation on the server. Figure 2-5 shows the output of the command.

docker info

../images/484552_1_En_2_Chapter/484552_1_En_2_Fig5_HTML.jpg

Figure 2-5

Displaying the system-wide information of the Docker installation

At this point, your Windows Server 2016 machine is a fully functional Docker container host.

Docker Desktop for Windows

The idea behind using a server operating system for deploying a Docker container host instead of a desktop operating system like Windows 10 is to get you to experience how it is like to deploy Docker in the real world, much like how data center engineers would deploy it in a production environment. Understanding the underlying infrastructure that runs an application helps you become a more well-rounded IT professional.

Docker Desktop for Windows is what most developers use to get started with learning Docker on a Windows operating system. It is a native Windows application that leverages Hyper-V virtualization. I like to think of it as some form of nested virtualization – it will create a MobyLinux virtual machine inside the Hyper-V host. The MobyLinux virtual machine will run as a Docker container host to run Linux-based containers. But because it’s a native Windows application, you can run Windows-based containers directly on the host. You can switch between running Linux-based and Windows-based containers with Docker Desktop for Windows. Figures 2-6 and 2-7 show how you can switch between Windows containers and Linux containers from within Docker Desktop for Windows.

../images/484552_1_En_2_Chapter/484552_1_En_2_Fig6_HTML.jpg

Figure 2-6

Switching to Windows containers

../images/484552_1_En_2_Chapter/484552_1_En_2_Fig7_HTML.jpg

Figure 2-7

Switching to Linux containers

Running the docker version command displays different results depending on whether you’re running Linux or Windows containers. Figure 2-8 shows the output of the command when running Linux containers. Notice that the OS/Arch: property of the client is windows/amd64 while that of the server is linux/amd64 (this is the MobyLinux virtual machine that was created to run on top of Hyper-V).

../images/484552_1_En_2_Chapter/484552_1_En_2_Fig8_HTML.jpg

Figure 2-8

Docker Desktop on Windows – client and server details (Linux containers)

The installation of Docker Desktop on Windows is straightforward. Just download and run the installation file from the Docker Hub .

Should any of your developer colleagues want to try out Docker, Docker Desktop on Windows is what you recommend. We big boys play with real toys – servers, that is.

Summary

This chapter walked you through the process of installing and configuring a Windows Server 2016 as a Docker container host. The server is now ready to run any Windows-based containers that have the same operating system kernel as Windows Server 2016. Note that this is the same setup as you would deploy in a production environment, minus all custom configuration that your organization requires for security, reliability, and standardization.

And while Docker was initially created for the Linux operating system, you didn’t have to learn anything Linux – yet. That’s what’s up in the next chapter. So, buckle up. It’s time to dive into the world of Linux.

© Edwin M Sarmiento 2020

E. M. SarmientoThe SQL Server DBA’s Guide to Docker Containers https://doi.org/10.1007/978-1-4842-5826-2_3

3. Install and Configure Docker on Linux

Edwin M Sarmiento¹ 

(1)

Ottawa, ON, Canada

The key to life is accepting challenges. Once someone stops doing this, he’s dead.

—Bette Davis

I still cannot believe I’m writing this chapter – about Linux, for SQL Server DBAs. In Chapter 1, I mentioned how Microsoft brought SQL Server to Linux in 2017. That came as a big surprise to a lot of SQL Server