---
title: DevOps Pipelines with Database Containers and Clones
description: Add database containers to devops pipelines with production data clones by using the Windocks REST API.
---

 

 

 TECHNICAL DOCS

# DevOps pipelines with database containers and clones

Add database containers to devops pipelines with production data clones by using the Windocks REST API. Test database scripts and the applications with production data in the devops pipelines..

[Back to Technical Docs Gallery ](https://23302346.hs-sites.com/resources)

# Steps for the DevOps Pipeline

## Step 1 - Build the image

From backups (SQL Server) or dumps (PostgreSQL, MySQL). See the sample windocks\samples\incrementalRefreshWithTransactionLogBackups\dockerfile for SQL Server in Windocks enterprise, windocks\samples\cloneFromFullBackup for SQL Server in Windocks Standard edition, windocks\samples\postgres\dockerfile for PostgreSQL or windocks\samples\mysql\dockerfile for MySQL. Build the image with **docker build -t yourimagename path\to\directory\containing\dockerfile**. The image build is typically not part of the devops pipeline since it could take a long time depending on the database size.

## Step 2 - Refresh on an ongoing basis

SQL Server images in Windocks enterprise edition are kept updated automatically with latest production data using transaction log backup files. To set up automatic updates of the image from production transaction log backups, use the web application and click on Refresh in the top menu, find your image and fill in the fields under Scheduled refresh. After that, put production transaction log backups in the specified directory on an ongoing basis. If you want to use your own automation to periodically update the image with transaction log backups, use the command line **docker exec yourimagename path\to\directory\containing\transaction\log\backups**. For Windocks Enterprise, PostgreSQL, MySQL images, a fresh image has to be built again from a full backup or a dump. You can automate this by using Windows Task Scheduler to build an image daily from the latest full backup using **docker build -t yourimagename path\to\directory\containing\dockerfile**

## Step 3 - Get authentication token

Add step to the devops pipeline to get your Windocks authentication token.  Get the authentication token using your username and password with the [REST API for get authentication token](https://windocks.com/lps/restapi#getauthtoken)

## Step 4 - Cleanup

Delete the old container and clone for the dev / test / staging environment before creating a fresh database container / clone. Use a container naming convention based on devops build number or date so that you know the container name to delete. Delete with the [REST API for container and clone creation](https://windocks.com/lps/restapi#rmcontainer)

## Step 5 - Create and clone

Add step to the DevOps pipeline to create the container and clone from the image. Create with the [REST API for container and clone creation](https://windocks.com/lps/restapi#create). Use a naming convention for the container based on date or devops build number so that it is easy to manage the container / clone lifecycle.

## Step 6 - Test

Add a step to the devops pipeline to test the database scripts, application with current database, etc.

[Back to Top ](https://23302346.hs-sites.com/doc-devops#) [Back to Technical Docs Gallery ](https://23302346.hs-sites.com/resources)

 

 

# **Azure DevOps**

Use the [Windocks REST API](https://windocks.com/lps/restapi) to set up database containers and clones in the pipelines. You will need variables for the Windocks server IP, user name, password. Here is the sample pipeline yaml.

pool:  
name: Hosted

variables:  
WindocksIP: '35.163.236.33'  
WindocksPort: '3000'  
WindocksUser: 'administrator'  
WindocksPassword: 'youradminpassword'  
RunScripts: 'scripts\\\\addColumnAtCreate.sql,scripts\\\\addAnotherColumn.sql'  
ImageName: 'clone2'  
SaUserName: 'sa'  
SaPassword: 'passwordyouwant'

# Set by pipeline  
Port: '0'  
WindocksToken: '0'  
TestContainerName: 'Test-$(Build.BuildNumber)'  
StaggingContainerName: 'Staging-$(Build.BuildNumber)'

steps:  
- bash: |  
echo {\"Data\": \"action=bizexec\&bizexectype=authlogin\&bizparameters=$(WindocksUser)~$(WindocksPassword)\"} > login.json  
echo "Get token with curl"  
curl -X POST -H "Content-Type: application/json"  
"http://$(WindocksIP):$(WindocksPort)/windocks" -d@login.json > output  
read -a loginOutput < output  
echo $loginOutput  
# Save current field separator  
saveIFS=$IFS  
# Set field separator  
IFS='~}"'  
# Split using field separtor  
parm=($loginOutput)  
# Restore saved field separator  
IFS=$saveIFS

token=${parm[6]}  
echo $token

#Save token so other steps can access it  
echo "##vso[task.setvariable variable=WindocksToken]$token"

displayName: 'Setup: Get Windocks token'

- bash: |  
# Create a json file for the Sql container creation. Note the Env value  
echo {\"AttachStdout\": false, \"AttachStderr\": false, \"ExposedPorts\": {}, \"Env\": [\"RUN=$(RunScripts)\",\"SA_PASSWORD=$(SaPassword)\"], \"Image\": \"$(ImageName)\", \"HostConfig\": {\"NetworkMode\": \"bridge\", \"RestartPolicy\": {\"Name\": \"no\"} } } > create.json  
cat create.json  
echo "Created a json file for the Sql container creation"

displayName: 'Environment SQL: Container Settings'

- bash: |  
# Create the SQL container with a production database clone and run the upgrade scripts specified in the variable $(RunScripts)  
echo "Creating container with curl"  
createOutput=$(curl -X POST -H "Content-Type: application/json" "http://$(WindocksIP):$(WindocksPort)/containers/create?name=$(TestContainerName)&token=$(WindocksToken)" -d@create.json)  
echo "Container created and update scripts have been run"  
echo $createOutput

# Get port on which the container was delivered. The port can be assigned with the API by setting "ExposedPorts": {"10031/tcp":{}} for example with port number 10031  
# Save current field separator  
saveIFS=$IFS  
# Set field separator  
IFS=' =&'  
# Split using field separtor  
parm=($createOutput)  
# Restore saved field separator  
IFS=$saveIFS  
#Save port so other steps can access the port  
containerPort=${parm[4]}  
echo $containerPort  
echo "##vso[task.setvariable variable=Port]$containerPort"

displayName: 'Test env SQL: Create container and upgrade'

- bash: |  
# Start the container  
echo "Start container with curl"  
output=$(curl -X POST -H "Content-Type: application/json"  
"http://$(WindocksIP):$(WindocksPort)/containers/$(TestContainerName)/start?token=$(WindocksToken)")  
echo $output  
echo "Contianer started"

displayName: 'Test env SQL: Start container'

- powershell: |  
Write-Host "SQL test environment verify"  
Install-PackageProvider nuget -force  
Install-Module -Name SqlServer -Force

$op = Invoke-Sqlcmd -ServerInstance "$(WindocksIP),$(Port)" -Username $(SaUserName) -Password $(SaPassword) -Query "SELECT @@VERSION;"  
if($op)  
{  
Write-Host "***** PASS *****"  
}  
else  
{  
Write-Host "***** Fail *****"  
}

displayName: 'Test env SQL: Deployment verification'

- powershell: |  
Install-PackageProvider nuget -force  
Install-Module -Name SqlServer -Force  
$table = Invoke-Sqlcmd -ServerInstance "$(WindocksIP),$(Port)" -Username $(SaUserName) -Password $(SaPassword) -Query "use customers; select column_name from INFORMATION_SCHEMA.columns where table_name = 'customers' and column_name = 'excessinfo'"  
$c = $table | Select-Object -Property column_name

if($c)  
{  
Write-Host "***** Pass *****"  
}  
else  
{  
Write-Host "***** Fail *****"  
}  
displayName: 'Test env SQL: Upgrade verification'

- bash: |  
# Create the SQL container with a production database clone and run the upgrade scripts specified in the variable $(RunScripts)  
echo "Creating container with curl"  
createOutput=$(curl -X POST -H "Content-Type: application/json" "http://$(WindocksIP):$(WindocksPort)/containers/create?name=$(StaggingContainerName)&token=$(WindocksToken)" -d@create.json)  
echo "Container created and update scripts have been run"  
# Get port on which the container was delivered. The port can be assigned with -p port  
echo $createOutput

# Save current field separator  
saveIFS=$IFS  
# Set field separator  
IFS=' =&'  
# Split using field separtor  
parm=($createOutput)  
# Restore saved field separator  
IFS=$saveIFS

#Save port so other steps can access the port  
containerPort=${parm[4]}  
echo $containerPort  
echo "##vso[task.setvariable variable=Port]$containerPort"

displayName: 'Staging env SQL: Create container and upgrade'

- bash: |  
# Start the container  
echo "Start container with curl"  
output=$(curl -X POST -H "Content-Type: application/json"  
"http://$(WindocksIP):$(WindocksPort)/containers/$(StaggingContainerName)/start?token=$(WindocksToken)")  
echo $output  
echo "Contianer started"

displayName: 'Staging env SQL: Start container'

 

 

[Back to Top ](https://23302346.hs-sites.com/doc-devops#) [Back to Technical Docs Gallery ](https://23302346.hs-sites.com/resources)

# **Jenkins**

Use the [Windocks REST API ](https://23302346.hs-sites.com/rest-api)from the pipeline code.

[Back to Top ](https://23302346.hs-sites.com/doc-devops#) [Back to Technical Docs Gallery ](https://23302346.hs-sites.com/resources)

## Extend CI/CD to your data layer

[GET A DEMO ](https://23302346.hs-sites.com/schedule-demo) [TRY FREE ](https://23302346.hs-sites.com/try-free)