SQL Server 2017 containers for DevOps scenarios

This post was authored by Tony Petrossian, Partner Group Program Manager, Database Systems Group

SQL Server 2017 will bring with it support for the Linux OS and containers running on Windows, Linux, and macOS. Our goal is to enable SQL Server to run in modern IT infrastructure in any public or private cloud.

With support for containers, SQL Server can now be used in many popular DevOps scenarios.  Developers working with Continuous Integration/Continuous Deployment (CI/CD) pipelines can now include SQL Server 2017 containers as a component of their applications for an integrated build, test, and deploy experience.

CI/CD automation with containers – Using containers greatly simplifies the development, testing, and deployment of applications. This is achieved by the packaging of all dependencies, including SQL Server, into a portable, executable environment that reduces variability and increases the speed of every iteration in the CI/CD pipeline. This also enforces a consistent experience for all participants since they can share the same state of an application in their containers. Developers can improve applications in their local environments during the first part of the Continuous Integration process.

The development process starts by taking a container that represents the current state of a production application, including a subset of the sanitized data. Developers can then add their features and fixes to it, while having the ability to verify the functionality of the application at any moment. Then, the container can be sent to a testing/quality assurance environment where it can be tested with a larger, more representative dataset.

Continuous Deployment is a critical part of DevOps pipelines. With a successful CD pipeline, a validated and self-contained version of the application is available. Developers can publish and share fully configured containers with all application dependencies, including SQL Server, with their peers. This can significantly improve developers’ ability to collaborate as they can all work on the same exact configurations simultaneously without having to build the complicated environment necessary for developing and testing applications with many components.

Parallel testing made fast and easy – Developers can automate the large-scale testing of containerized applications that include SQL Server. Thousands of tests can execute in parallel using high-density container deployments with managed container services. Kubernetes, Docker Swarm, or other orchestration systems can be used to easily manage a large number of test executions. Long-running test cycles can be optimized by load balancing the executions across multiple pods that spin up on demand and spin down when finished.

The Microsoft SQL Server development team is now taking advantage of these capabilities in building, testing, and publishing the new versions of SQL Server 2017. The team uses Azure Container Services to deploy hundreds of containers managed by a large Kubernetes cluster to execute all daily tests of SQL Server. Hundreds of thousands of tests are executed within hours of the availability of a new build! This methodology has enabled the team to run more tests in less time with fewer resources.

Multi-OS development, test, and production environments – With the containerization of the app, developers no longer need to be concerned about aligning stages of the development and production pipeline with the same exact distribution and version of Linux. Developers can containerize their application environment, including SQL Server, to abstract it from the operating system of the underlying host. Whether part of the pipeline is operated on Ubuntu and other parts in Red Hat Enterprise Linux (RHEL), the ability to containerize the entire application environment eliminates the need to overcome challenges of a cross-platform environment. Developers are also free to choose their preferred development environment without worrying about compatibility issues in later parts of the pipeline. With SQL Server 2017, developers can run SQL Server Linux Docker images on macOS, Windows, and Linux.

Deploying SQL Server into production – As new versions of the application are tested and verified, builds of the containers are published for use in staging and production. The exact version of the SQL Server that was used throughout the development and testing pipeline is now in the production image, and the team can be confident that the entire stack, including SQL Server, has been tested as one unit and is ready for use.

Learn more about how to better integrate your SQL Server data/database on the DevOps cycle.