Ding enables people living or working abroad to instantly top up mobile phones of friends and family back home. CEO Mark Roden established Ding in 2006 and since then the company has quickly become the world's largest provider of international mobile phone top-up. Ding provides an important service connecting people around the world, partnering with over 400 mobile operator partners, 600,000+ retail locations covering over 130 countries and an increasing online presence. You can learn more about Ding at https://www.ding.com/.

Over the past year, Ding has begun to incorporate use of SQL Server containers into its existing development cycle to run in parallel with the existing processes in place. The aim being to replace instances of SQL Server running on VMs with containers built from a custom image in our QA environment.

This document details the current environment, the existing processes and the new process implemented using SQL Server containers.

QA Environment

Our QA environment is completely virtualized in our corporate network on one physical host running Citrix XEN as a hypervisor. The host has 380GB of RAM, 3TB of storage and 24 E5-2620 v3 @ 2.4GHz CPUs.

New VMs are created from a custom Windows Server 2012 R2 ISO which then has the windows package manager (chocolatey) installed which is used to install a local instance of SQL Server.

Octopus deploy is used to install the applications which reference a local instance of SQL with databases restored from baselines kept in TFS. Baselines are database backups created from Production with a subset of data required by our applications

Each VM is essentially a stripped down, self-contained replica of the production environment. A typical VM would be running with 12GB of RAM, 4 vCPUs and 100GB storage.

Production Environment

Our production OLTP environment runs an Enterprise Edition of SQL Server 2012 in a two-node Windows Server 2012 R2 cluster hosted by a private cloud provider. This hosts 37 databases ranging from 30MB to 500GB in size. Each database has a custom process to create its baseline.

Applications referencing the databases are hosted on VMs replicated across four node stripes to provide redundancy. Each application references the database clustered instance via a DNS cname alias.

Storage in the production environment is provided by a Dell Compellent SC8000 SAN.

Baseline Creation Process

Production databases are backed up to a Dell DR4100 disk backup appliance; with a full backup taken daily, differential backups every 4 hours and transaction log backups every 10 minutes.

Baselines are created by restoring the full backup of a database into an isolated VM in the production environment and then running a series of powershell scripts to:

• Remove customer data and retain a subset of required reference data
• Rebuild all indexes and heap tables
• Shrink database files
• Backup database

The newly shrunk database is backed up, is copied to our Corporate environment and stored in TFS. 

Configuration of QA VMs

Each new VM has the package manager for windows (chocolately) installed in the initial VM setup. Chocolately is used to install SQL Server and is configured via a custom config file.

Once the instance of SQL Server is installed, applications can be deployed onto the VM using Octopus deploy. Octopus will perform the following steps within SQL Server when an application is deployed: -

• Restore the baselines from TFS
• Create any logins/users required and assign permissions
• Run any SQL scripts required to bring the databases into line with the TFS branch currently being worked on

Once this process is complete the VM has an instance of SQL Server hosting a full complement of databases. The DNS cname aliases in production referenced by the applications are overwritten on QA VMs via hostname entries which point to 127.0.0.1.

Challenges with the legacy process

The main problem with the existing process is the install of SQL Server from chocolately. The install could take up to 30 minutes and is prone to failure which requires the VM be rebuilt or parts of SQL that were installed to be removed and the process restarted.

Another issue was with the deployment of the databases via Octopus. Certain applications took a long time (up to 40 minutes) to restore the database and apply the required SQL scripts.

Container technology was suggested to remove these issues. The idea being that the installation and configuration of SQL Server, plus the database restores would be removed from the existing process and replaced by containers built from a custom image.

Implementation of containers for QA

WinDocks was chosen as the container technology because it allows for containers to run SQL Server 2012 on Windows Server 2012 R2 (matching our Production environment). Each new VM would reference a container running SQL Server on a separate physical machine (the WinDocks host).

The WinDocks software requires a default instance of SQL to be installed from which all subsequent containers are built from. The diagram below shows the new infrastructure: 

The container workflow begins with each database baseline created as normal on the baselining VM in Production. The VM hosting the SQL Server baseline databases is stopped and the respective database’s MDF & NDF files copied to the WinDocks host.

A container is then built with the databases added via a Dockerfile. The container is then committed (saved) as a custom image. The custom image is kept in line with Production by creating a container from it, applying any releases to that container, then stopping and committing the updated container to replace the old image. This allows the existing baseline process is to remain in place. Each SQL Server image provides an immutable copy that is used to instantiate a fully isolated and identical SQL Server container instance.  

The WinDocks host is a physical machine with 96GB of RAM, 460GB of storage and 8 E5520 @ 2.27GHz CPUs hyper-threaded.

Issues Raised

A pre-requisite of this project was that no alterations to the applications should need to be made, however this presented a challenge. The applications’ connection strings used DNS CNAME aliases which were over written by host name file entries to point to the local host. Containers function as a named instance, each with a port which cannot be specified in the host name file of a server. The solution was to create SQL client aliases replicating the DNS cname aliases on each VM referencing a container. This required the installation of the SQL native client on each VM but it is a simple install and allows port numbers to be specified with the WinDocks host name.

We also had multiple issues when using our existing Octopus application deployment process when the applications were referencing a container instead of a local instance of SQL Server. This required multiple updates to the base image for the application to be deployed successfully. This was a process of trial and error, uncovering different issues along the way which needed to be rectified. However, this also gave us an opportunity to delve into existing process and change any issues that we saw.

Benefits Found

Once a custom container image had been created and the issues above resolved, the time taken to create a new container with all the databases available has not exceeded 2 minutes, and up to 25 containers can be run on the WinDocks host. This is a dramatic improvement in setup time and is more reliable than the existing process.

Another benefit is simplified maintenance. WinDocks uses a default instance of SQL Server on the host server to support creation of containers. Updates applied to the default instance ensure that subsequent containers run at the higher patch level. This removes the need for individual SQL instances to be patched as containers can be dropped and new ones spun up in a short period.

Also, the applications on the QA VMs have more resources available now that they are no longer hosting an instance of SQL and are more responsive.

Moving Forward

The project has proved that containers can replace SQL Server on QA VMs with a significant reduction in setup time, maintenance, and with less resources. The QA Team have adapted their existing automated tests to the new infrastructure and have commented that they prefer working with containers as the VMs. At the time of writing all QA VMs that host a local instance of SQL Server have been shut down and the team are exclusively using VMs that reference SQL containers

Moving forward we will explore other benefits, including further reduction in resources needed for the QA VMs, implementing more custom images to fit different QA testing requirements and use of mounted databases to work with larger datasets.

On a personal note, this project has had its challenges but I have immensely enjoying implementing the new infrastructure/processes and seeing the benefits that it has brought. I look forward to the next few months when we can start exploring the other benefits that the technology can offer.

You can learn more about Ding at https://www.ding.com/.