Dev, test, and staging environments with release migration SQL scripts using Git, containers and production database clones
The workflow
Build the database image - An administrator builds the Windocks image specifying the database backups needed to deliver database clones, the path to the git repo containing the migration SQL scripts. This image is used to deliver containers and database clones with specified scripts applied. You may also add data masking scripts that automatically mask sensitive production data.
Develop SQL scripts using production data clones in containers - Developers write SQL scripts and code using production database clones in containers (development environment). A Windocks image is built first that specifies the details of the databases and then containers with clones are created from this image. They get these environments on demand using a web app, a command line or a REST API. Using production data clones and containers makes collaboration easy since developers can easily reproduce each other's environments in seconds. Using production data improves release quality since the entire release cycle uses production data so the final upgrade works smoothly
Use Git for SQL script source control Developers push scripts/code to a Git repo feature branch incrementally as and when the scripts are ready. The Git repo is the source control system for database scripts. Use one branch for each feature and/or each developer. Also use one or two Git branches for each release (such as release301ReadyForTest, release301Final). Code conflicts are flagged by Git and are resolved either automatically or by the developers
Unit test the feature branch on production clones with SQL scripts automatically applied When the developer has added unit tests to the feature Git branch, they create a manifest file containing the feature's freshly developed SQL script file paths. Now, when the developer or devops pipeline gets the production data clone, it has the scripts from the manifest automatically applied. If using EF migrations, the developer specifies which migrations to apply before delivering the database and then unit testing is done
Merge SQL scripts to release branch When the developer is ready to release, they merge the SQL scripts or EF migration code into the Git release branch. They also update the manifest file with the SQL script paths for the release branch. All future production clones and containers have the release SQL scripts automatically applied.
Test and stage with production clones, containers and release SQL scripts automatically applied Devops pipelines or test teams get the production database clones on demand with the release SQL scripts applied automatically. The on demand production clones with SQL scripts applied are used as test and staging environments. The SQL scripts can also update the database version to ensure no errors in versioning. Because the testing and staging is done with production data, the final production deployment is smooth and less error-prone.
Do both release upgrade and rollback testing with containers As a best practice, the release SQL scripts should be both for release upgrade and release rollback. Containers make it easy for many developers, testers, and pipelines to work with various combinations of SQL scripts applied (for features, release upgrade, and release rollback) because containers are created and torn down in seconds. Upgrade testing is done on one container with upgrade scripts applied and rollback testing is done on another container with upgrade and rollback scripts applied. Multiple developers, testers, pipeline runs can each develop, test and stage with various combinations of SQL scripts using containers
Detailed Steps
Step 1. Setup the git repo
Instructions to set up a git repo are here
The sample shows an example of a public repo. If the repo is private, use git over SSH and follow these steps (Email support@windocks.com if you need help)
- cd C:\Windows\System32\config\systemprofile
- Copy the private key privatekey.pem to C:\Windows\System32\config\systemprofile
- Create a file named config (no extensions) and enter the following with your ip address and user name for the git repo machine:
Hostname 142.4.299.87
IdentityFile C:\Windows\System32\config\systemprofile\privatekey.pem
User yourSshUserName
Create a git branch for each feature and release. For each feature create a file in your git repo with a naming convention like myfeature.txt that contains the paths (relative to the home of the git repo) to the database scripts files for that feature. As scripts are ready to be applied for unit testing, add the script to the manifest file for that feature. Here is an example of the script manifest file:
manifest.txt
addAnotherColumn.sql
subdir\addColumnAtCreate.sql
Also create a branch for the current release and add a manifest file for that. For example, if you are developing for release 301, create a file in your git repo called release301.txt and put the paths (relative to the home of the git repo) of all the database scripts for that release. When you are finished with a feature branch, merge the feature branch to the release Git branch so all the SQL scripts from the feature branch are available in the release branch
Different developers can use different manifest files (and consequently SQL scripts) applied to the production clone by specifying the path to their manifest file when they create their container with clone (Described in more detail below)
Step 3. Create the dockerfile for the image
The dockerfile used to build the image is available in windocks\samples\DbScriptsSourceControlGitForSqlServerImages and is also below. You may add data masking scripts to mask sensitive production data (see the sample windocks\samples\cloneFromFullBackup for this)
dockerfile
FROM mssql-2017
SETUPCLONING FULL customers C:\windocks\dbbackups\customerdatafull.bak
ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1
# When container is created, the RUN command below clones the git repo to a directory in the container named homedirforrepo
RUN "C:\Program Files\Git\cmd\git.exe" clone https://github.com/WinDocks/git-db-scripts-runtime homedirforrepo
# This is the format for SSH secure git repo (commented)
# RUN "C:\Program Files\Git\cmd\git.exe" clone securedvmhostname:/path/to/git/repo.git homedirforrepo
# STARTENV_GITBRANCH_ENDENV is a variable name for the git branch you want - the variable value is specified at container create time
# The convention of STARTENV_something_ENDENV is used for these variables (Functionality available in enterprise editions, for other editions, you may just use a hardcoded git branch name)
# When container is created, changes the container's git repo code to the branch specified at container create time
RUN git -C homedirforrepo checkout STARTENV_GIT_BRANCH_ENDENV
# STARTENV_MANIFEST_ENDENV is a variable name for the path (including name) to the scripts manifest file relative to the repo home - the variable value is set at container create time
# This RUN command reads the script manifest file and combines the content from all those files specified in the manifest into one script file named all.sql in the container root directory
RUN powershell.exe -command "(Get-Content -path homedirforrepo\STARTENV_MANIFEST_ENDENV) | foreach { Add-Content -Value $(Get-Content homedirforrepo\$_) -Path all.sql}"
# This RUN command runs the combined scripts file when the container starts
RUN all.sql
Step 4. Build the image
In a command line on the Windocks server:
docker build -t yourimagename path\to\directory\containing\dockerfile
This builds the image from which clones and containers are delivered. Alternatively, use the web application described here and click on Build in the top menu.
For SQL Server, once the image is built, in the web application, select Refresh and put in the schedule for refreshing the image with transaction log backups (SQL Server) and the directory in which the log backups are available periodically. Now Windocks automatically refreshes the image to keep it current with production. Upon a release, you may also manually refresh the image with production log backups with
docker exec imagename path\to\log\backups
or in the UI under Refresh.
Step 5. Create the database environment
See the samples in the directory windocks\samples\DbScriptsSourceControlGitForSqlServerImages to avoid copy/paste from this web page. When the developer or the devops pipeline needs a refreshed environment, they can use the web application (works from any machine) (click on Images in the top menu) or use the command line below (Command line only works on the Windocks machine) or use the REST API (works from any machine):
Command line is as below:
docker create -e STARTENV_MANIFEST_ENDENV=manifestdir\manifest.txt -e STARTENV_GIT_BRANCH_ENDENV=master --name yourcontainername yourimagename
Then,
docker start yourcontainerName
Or
docker start firstThreeLettersOfContainerIdReturnedByCreate
Web app is the easiest way to create these containers. In enterprise editions, you may specify the variable values for git branch and manifest in the UI.
REST API with curl is the best option for devops pipelines and it works as below. (Download curl from here:
First, get your access token - create a file named getauthtoken.json and put the following inside it:
{ "Data": "action=bizexec&bizexectype=authlogin&bizparameters=administrator~youradministratorpassword" }
Then run the curl command below:
curl -H "Content-Type: application/json" -d@getauthtoken.json -X POST http://localhost:3000/windocks
This will return your access token to be used for the REST API call below. The token is the string after the last ~ (don't copy the ~)
Create a file named create.json and put the following inside it (Note the \\ to escape the directory \ in manifest path):
{ "AttachStdout": false, "AttachStderr": false, "ExposedPorts": {"10032/tcp":{}}, "Env": ["STARTENV_MANIFEST_ENDENV=manifestdir\\manifest.txt", "STARTENV_GIT_BRANCH_ENDENV=master","SA_PASSWORD=secret123@"],"Image": "yourimagename", "HostConfig": {"NetworkMode": "bridge", "RestartPolicy": {"Name": "no"}}}
Then run the curl command below using a container name you want and the access token from above:
curl -H "Content-Type: application/json" -d@create.json -X POST "http://localhost:3000/containers/create?name=yourcontainername&token=youraccesstoken"
This creates a container from the image named yourimagename (must already exist) at the port 10032 with admin password of secret123@. If you want an auto generated port, set ExposedPorts to {}. The scripts in the specified git repo branch in the manifest will be applied
Parse the return string from the curl command to get the container Id (or use yourcontainername to avoid parsing) and then start the container with the following curl command:
curl -H "Content-Type: application/json" -X POST http://localhost:3000/containers/yourcontainername/start?token=youraccesstoken
Step 6. Logs
View logs of the scripts in the web application by clicking log next to your container / clone in the web application. You may also get image build time logs in Images
You may also use the REST API to get the logs as below:
Create a file named getcontainerlog.json and put the following inside it:
{ "Data": "{\"action\": \"containerlog\", \"containerlog\": {\"containerid\": \"yourFullContainerId\"}} " }
Then run the curl command below and the access token from above:
curl -H "Content-Type: application/json" -d@getcontainerlog.json -X POST http://localhost:3000/windockswrapper?token=youraccesstoken
For image logs, create a file named getimagelog.json and put the following inside it:
{ "Data": "{\"action\": \"imagelog\", \"imagelog\": {\"imagename\": \"yourImageName\"}} " }
Then run the curl command below and the access token from above:
curl -H "Content-Type: application/json" -d@getimagelog.json -X POST http://localhost:3000/windockswrapper?token=youraccesstoken