SQL Server and Postgres source control, release migrations and testing, with Git, containers, and DB cloning

Updating databases with release migration scripts for multiple environments and team members can be challenging.  This article describes how to solve the challenge using Git, containers, and secure production database clones, for delivery of dev, test, and staging environments in seconds.  While the article focuses on SQL Server, Postgres and MySQL are supported using the same methods.   

The system simplifies release migrations, and delivers several benefits:

1.     Team collaboration and work-from-home benefit with instant bug and test results reproduction. 

2.     Release quality and timelines improve with secure production databases delivered on demand for testing.

3.      Speed and efficiency of database development and testing is similar to that of application development, by using Git repos, Git branching, secure production database clones, and automated script application.

A Git repo and Git branching provides source control of database scripts, and delivery of dev, test, staging, and production environments with scripts automatically applied. For example, a developer works on a feature branch with a production database clone with feature scripts applied automatically.   Testing works on a release branch with an identical production database clone with release scripts applied automatically.   A pipeline stage tests Release branch rollback with a third identical production database clone with upgrade and rollback scripts applied automatically.

System components

Any public or private Git repo including GitHub, GitLab, or Git on a private VM.  

A database image is a set of production databases, restored from backups, or database files, with data masking scripts applied. Users can specify scripts from a Git branch and repo, to support dev, test, and staging environments.  When transaction log backups are available the image can be updated incrementally, forever.

A script manifest file is a text file listing scripts to be run.

A database container is a database instance providing database services.  

Database clones are writable databases delivered from an image that are delivered in seconds and require only 40 MB of storage on delivery. 

Step 1: Build the database image 

Images are specified by a dockerfile. The dockerfile below includes a path to a production database backup and a data masking script. A command clones a Git repo into the container file system, with a specified branch checkout.  Users or a pipeline provides input for two environment variables, highlighted in red, specifying the Git branch and a script manifest file (more on this below).  On delivery a PowerShell command creates a concatenated “all.sql” script, reflecting the order of scripts in the manifest file, which is then run.  

The image is built by selecting the dockerfile and cleansedata script using the Windocks web application, entering a new image name, and clicking on “build.”  

Step 2: Manifest files

A manifest is a text file listing the paths for scripts relative to the root of the Git repo, in the order they are to be run.    Multiple manifest files can be used. One manifest can specify upgrade scripts, and a second combines upgrade scripts followed by rollback scripts.  A manifest.txt file could include:

FeatureA\script1.sql

FeatureA\script2.sql

Step 3:  Development and test environments

Development begins with delivery of a SQL Server environment, with a production database clone with no Git based scripts applied.    As work progresses, scripts are committed to the repo, and a manifest file is created and committed, listing scripts in the order they are to be run.  

Users and pipelines provision updated environments using the Windocks web application, or docker commands, or restful API.  Log output for scripts is output to the web application, and available via the Rest API (see www.windocks.com/lps/restapi). ).  Environments are used to unit test the SQL scripts and applications, with user specified manifest files and different Git branches.  The command for delivery of the environment follows, with the user inputs highlighted in red. On delivery the container is started with the docker start command. 

Step 4: Release branch testing

As work progresses, developer branches are merged into a release branch, with updated manifest files. Testers and DevOps pipelines deliver release testing environments using the same database image, referencing the release branch and updated manifest files. 

This approach to database migrations with Git supports a complete dev/test life cycle with a consistent database environment. Following release to production a new images can be built, or existing images are updated with transaction log backups (more on this below).  

Tutorials and step-by-step instructions

For more detailed instructions, and information on the restful API, and secure Git credentials for private repos, visit windocks.com/lps/docdev.

Updatable images

When they are available transaction log backups can be used to update the image to provide an updated or even near real-time clone of production databases. All that is needed are transaction log backups in a folder accessible to the Windocks server.  Images can be updated manually, or with an automated schedule, and the system handles the ordering and application of the transaction logs in LSN sequence. 

The system reports on the status of scheduled image updates, and image updates do not affect previously delivered environments.  Cloned near real-time production databases are used for reporting, production database debug and support, and other non-production database needs.  

An open platform

Windocks is open to use of third party tools for data masking, database backups, and synthetic data. SQL Server containers support Active Directory and Windows Authentication.  

Data governance and security

This system creates a secure centralized data repository for enterprise use. Where organizations struggle with VM and instance sprawl, containers allow for consolidation on the container host.  A single database image easily supports 20 to 50 simultaneous environments, for a potential 95% reduction in storage.  Windocks also includes a security event audit log for compliance reporting and audit support.

Explore database containers with Git

Windocks supports all releases and editions of SQL Server 2008 to 2019, and Postgres and MySQL too. Visit www.windocks.com and ask about a free supported pilot.