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

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. Table of Contents 5 About the Author 12 About the Technical Reviewer 13 Acknowledgments 14 Introduction 15 Chapter 1: Introduction to Containers 19 The Latest 30-Year-Old Technology 20 Docker – The Company and the Container Runtime 21 The Case for Containers 22 Containers vs. Virtualization 22 Microsoft, Containers, and SQL Server on Linux 25 Summary 26 Chapter 2: Install and Configure Docker on Windows Server 27 Minimum System Requirements 28 Enable Containers Feature in Windows Server 2016 29 Download and Install the Docker-Microsoft PackageManagement Provider 30 Download and Install the Docker Package 31 Verifying the Docker Engine Installation 32 Docker Desktop for Windows 33 Summary 36 Chapter 3: Install and Configure Docker on Linux 37 Minimum System Requirements 38 Basic Linux Commands You Need to Get Started 39 Installing Docker on CentOS Linux 41 Update the CentOS System Packages 41 Install the Required Dependencies 42 Add the Docker Stable Repository to Your System 43 Install Docker CE 45 Starting the Docker Daemon and Enabling on Startup 46 Installing Docker on Ubuntu Linux 47 Update the Ubuntu System Packages 47 Install the Required Dependencies for Connecting to a Repository over HTTPS 48 Add the Official GPG Key from Docker 49 Add the Docker Stable Repository to Your System 50 Install Docker CE 51 Verifying the Docker Engine Installation 52 Getting Rid of sudo? 54 Summary 56 Chapter 4: The Docker Ecosystem 57 Hello World: Docker Edition 58 How Docker Runs Containers 60 The Docker Ecosystem 62 Docker CLI 62 Docker Daemon 63 Docker Commands 63 Docker Images and Containers 64 Docker Hub 65 Exploring Docker Hub 67 Docker Image Naming Conventions 69 Running SQL Server on Windows Containers 74 Running SQL Server on Linux Containers 76 Beginning to Explore Containers and Images 77 Things to Consider 78 Connecting to the SQL Server Instance Remotely 81 Lifecycle of a Container 84 Summary 85 Chapter 5: Docker Images and Containers 87 A Quick Review (And Then Some...) 88 Behind the Scenes of Pulling Images 91 Fat Manifest? 93 Image Manifest 97 Content Digest 99 Distribution Hash 100 Your Docker Host’s Local Filesystem 103 Union Filesystem 103 Files, Directory Names, and Symbolic Links 103 Stitching Layers: Assembling the Image 108 How a Union FileSystem Works 109 Traversing the Layers 110 The Container Layer 115 Summary 116 Chapter 6: Managing and Administering Containers 117 Configuring the Docker Daemon 118 Working with dockerd 119 Working with daemon.json 119 Changing the Default Image and Container Directory 121 Stopping and Restarting the Docker Daemon 124 Configure the Docker Daemon for Remote Access on CentOS Linux 124 Configure Remote Access to the Docker Daemon with the systemd Unit File 125 Protect the Docker Daemon Socket by Configuring Mutual TLS Encryption 128 Update the daemon.json to Include the Settings for Mutual TLS Encryption 132 Enable Firewall Port to Allow Traffic to Docker Daemon 132 Testing Remote Client Connectivity 133 Cleanup 134 Configure Docker CLI Bash Completion 135 Starting and Stopping Containers 136 Deleting Containers and Images 138 Interacting with Running Containers 139 Sharing Files Between Host and Container 140 Configuring Container Resources 142 Exploring the Container Logs 144 Reviewing the Status of the Docker Daemon 146 Summary 148 Chapter 7: Persisting Data 150 Stateful vs. Stateless Applications 151 Containers Are Ephemeral and Immutable 152 How Cloud Providers Implement Infrastructure- as-a-Service 153 Docker Volumes 156 Attach Volumes to Containers 160 To -v or to --mount 161 Sharing Volumes Between Containers? 162 Leveraging Volumes with SQL Server on Containers 163 Smart SQL Server In-Place Upgrade 169 Summary 173 Chapter 8: Working with SQL Server on Linux 174 SQL Server on Linux Architecture 175 Project Drawbridge 176 SQLOS and SQLPAL 177 Difference Between SQL Server on Windows vs. Linux 179 Installing SQL Server on Linux 180 Manual Installation 181 Unattended Installation 183 Configuring the Firewall 186 A Note on the Linux Firewalls and Docker 190 Configuring SQL Server on Linux 191 Enabling the SQL Server Agent 193 Configuring Default Database Data and Log Directory 194 Configuring Default Database Backup Directory 194 Enabling Trace Flags 195 Viewing All Instance-Level Configuration Settings 196 Working with the Filesystem 197 Important Directories in the Linux Filesystem 198 File Permissions 200 Assigning Permissions to Files and Directories 202 Writing a Simple Linux Bash Script 205 Running the Script 209 Passing Parameters to the Script 210 Adding Conditional Logic in Bash Scripts 211 Summary 215 Chapter 9: Creating Custom SQL Server on Windows Container Images 216 Creating Custom Docker Images 217 Exploring the Dockerfile 219 The File 220 The FROM Instruction 221 The LABEL Instruction 221 The RUN Instruction 222 The COPY Instruction 223 The ADD Instruction 225 The SHELL Instruction 226 The CMD Instruction 227 The ENTRYPOINT Instruction 228 CMD vs. RUN vs. SHELL vs. ENTRYPOINT 229 The WORKDIR Instruction 230 The ENV Instruction 230 Using Escape Characters 231 Putting the Pieces Together with a Dockerfile 232 Build the Custom SQL Server on Windows Image 235 Optimizing the Dockerfile 239 Summary 244 Chapter 10: Creating Custom SQL Server on Linux Container Images 245 Additional Dockerfile Instructions 245 The EXPOSE Instruction 246 The VOLUME Instruction 248 The USER Instruction 248 Build the Custom SQL Server on Linux Image 249 Installing a Specific SQL Server Version 252 Creating a Custom Image That Runs As Non-root 254 Running a Container As Non-root 258 Non-root User and Docker Volumes 263 Optimizing the Dockerfile 266 Running Scripts Inside the Dockerfile 268 Install the SQL Server Command-Line Utilities 268 Write the Bash Script to Check for Backups 269 Run the Bash Script in the Last Instruction 273 Multi-stage Builds 275 Docker Compose and YAML Files 279 Installing Docker Compose 279 Creating a YAML File for Multi-container Apps 280 Summary 284 Chapter 11: Guide to Docker Networking for SQL Server DBAs 285 The docker0 Bridge 286 Virtual Network Adapters 289 Working with and Exposing Ports: Running Multiple SQL Server Instances on Linux via Containers 291 Using the Same Port on Multiple Containers 292 Intra-container Communications 296 Putting It All Together 299 Summary 301 Appendix A: Building a Personal Lab Environment 303 The Need for a Personal Lab 304 Software for the Lab Environment 306 Operating System 306 Virtualization Platform 307 Installing CentOS Linux Server 307 Installing Ubuntu Linux Server 318 Utilizing Public Cloud Resources 341 Provisioning a Linux Docker Host on Microsoft Azure 342 Provisioning a Linux Docker Host on AWS 342 Provisioning a Linux Docker Host on Google Cloud Platform 343 Linux Client Tools 344 Using SSH to Connect to Linux 344 Using WinSCP for Copying Files to Linux 346 SQL Server Client Tools 349 Azure Data Studio 350 mssql-cli 352 Summary 353 Index 354