When the Microsoft SQL Server team started working on supporting Linux for SQL Server 2017, their entire test infrastructure was, naturally enough, on Windows Server (using virtual machines deployed on Azure). Instead of simply replicating that environment for Linux, they used Azure Container Service to produce a fully automated test system that packs seven times as many instances into the same number of VMs and runs at least twice as fast.
“We have hundreds of thousands of tests that go along with SQL Server, and we decided the way we would test SQL Server on Linux was to adopt our own story,” SQL program manager Tony Petrossian told InApps. “We automated the entire build process and the publishing of the various containers with different versions and flavors. Our entire test infrastructure became containerized and is deployed in ACS. The tests and the build of SQL Server [we’re testing] get containerized and we deploy hundreds of thousands of containers a Kubernetes managed cluster. That gives us faster testing, more parallelization, more efficiency, more automaton and a simpler process.”
Kubernetes is an open source container orchestration engine, which was originally developed by Google and is now managed by the Cloud Native Computing Foundation.
For the daily build, testing uses 700-800 containers. That used to be the nightly build test, because it took so long to run, but it’s faster now. “Each container runs through a set of permutations for a set of tasks then goes away,” Petrossian explained. “We can now make change to the code, build, publish the containers, deploy them and run through our daily test in a few hours. It used to take usually more than a day to go through. Now we’re doing more testing because the automation is a little easier for managing Kubernetes clusters than for deploying VMs, and we run through it faster so we have more time. If we really need to, we can do two or three daily builds, not just one.”
The improved density from using containers is even more useful when it comes to the weekly test suite, which uses 1,500 to 1,600 containers. “We’re doing more tests, faster and it’s costing us less because we’re deploying into ACS with a much higher density of containers per VM. On the Windows side, we used to deploy a VM with a SQL Server instance and it runs through the tests,” Petrossian said.
“We have a seven to one improvement on the Linux side because we deploy seven containers on a VM instead of having seven VMs each running a version of SQL Server and its associated tests. Each container running in ACS is self-contained and we can have many of them running on a single VM.”
Because so much of the code of SQL Server itself is the same on both platforms, the tests aren’t necessarily specific to SQL Server on Linux. A very large percentage of the SQL tests are completely OS-agnostic; they don’t care if they’re running on Windows or Linux. These are tests that exercise some internal component of SQL Server that has nothing to do with the OS, which is most of SQL Server.
“SQL Server interacts with the OS around the edges, for things like authentication, I/O and networking. We have tens of thousands of optimizer tests that are just pure SQL code with no system calls being made and those can run anywhere,” Petrossian said.
The Cloud Native Computing Foundation is a sponsor of The New Stack.
Photo by Jan Erik Waider on Unsplash.
