Databases and DevOps pipelines
A DevOps pipeline is a sequence of code and configuration that builds applications, deploys them to test, staging environments, runs tests and deploys to production.
Typically a pipeline will build images for applications and then deploy the same image to different test and staging environments in containers.
Continuous integration / deployment (CI/CD) orchestration solutions such as Jenkins, Bamboo, or Azure Devops provide a syntax and convention for specifying pipelines. Once a pipeline is specified, the CICD orchestrator runs the steps in the pipeline.
Windocks enables databases to be an integral part of a DevOps pipeline.
Requirements for deploying databases in DevOps pipelines
Test and staging environments typically need production data copies for their test or staging environments. However making copies of large databases is not practical during a DevOps pipeline run. Once a production data copy is available, a fresh database service has to be deployed (typically a container) and the databases have to be attached to that container.
To deploy databases to test and staging environments, the following are needed:
- Using database container technology, a database container has to be spun up
- Using database cloning technology, the databases have to be cloned instantaneously no matter how big the database
- Using database masking technology, sensitive information (PIIA) has to be obfuscated so that devops pipeline data is in compliance with data security policies
- Various data related services have to be orchestrated in the correct sequence. The following services need to be orchestrated: cloning, container, masking, Git repo service to pull database scripts and apply them to the database clones, and authentication / authorization
Challenges and technologies for deploying databases in DevOps pipelines
- Container service - To spin up database containers, you need container technology that supports that particular database version and environment. Docker containers are available for Oracle, PostGre, and MySQL. However neither Docker Inc or Microsoft have a solution for SQL Server Windows containers (they only support Linux). Windocks offers the only available container technology for SQL Server Windows. It is based on Docker Inc's container technology.
- Cloning service - Cloning technologies are available in operating systems but require complex code to create, manage, and delete clones. This code has to be written and maintained for complex matrices of database versions and operating systems. Windocks provides database cloning technology based on standard Linux and Windows file systems and abstracts the complexity from you.
- Masking service - You can write masking scripts yourself to obfuscate PIIA data or use a built-in masking solution from Windocks or use other masking solutions based on your requirements. Masking solutions should include discovery of all the types of sensitive fields relevant to your databases, creation of masks, support foreign key constraints, and maintain formats of the data being obfuscated. The Windocks offering includes data masking for Oracle and SQL Server.
- Orchestration - Database orchestration is the control plane service that uses the above services to deploy databases. With Windocks orchestration, you simply write a spec (in the form of a dockerfile) with details on which databases to clone, their formats, cloning service, container service, authorization, git repo branches and scripts, etc. Windocks will deliver databases using the spec. Windocks is an open system that allows you to use either built-in Windocks cloning, container, masking services or solutions from others.
Steps to get started on DevOps pipelines and databases
1. Install Windocks
Download the Windocks Community Edition or email support@windocks.com for a full featured evaluation edition.
Provision a Windows Server VM (Server 2016, 2019, or 2022), install SQL Server (for SQL database delivery) and then install Windocks as described here.
For Oracle database delivery, also install the Windocks service for Linux as described here.
2. Provide the spec to the Windocks orchestrator to build the image
Specify:
- the path to one or more SQL backup or database files or Azure SQL BACPAC files, which database cloning to use Windocks database cloning or volume cloning from other companies)
- where to deliver the database clones (Windocks SQL Server Windows containers or SQL Server instances)
- how often to refresh from production, customizations such as database scripts to be applied
- which masking software to use (Windocks masking, other masking solutions or scripts)
- authorization controls
The spec is provided in the form of a dockerfile. Build the image via a web application, command line or REST API.
3. Create the pipeline
Use the documentation for your devops orchestrator (Jenkins, Bamboo, AzureDevops, etc) to create the pipeline. Use the REST API to add the Windocks steps to create the container and clone from the image.
4. Run the pipeline in your DevOps orchestrator
Either manually run the pipeline in the UI or push to the repo to trigger a pipeline run and see the pipeline logs. The database clones are available in containers created by the pipeline running on the Windocks machine on different ports as specified in the pipeline.