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.

  • 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 - 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
  • Step 4 - 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
  • Step 5 - 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. 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 - Add a step to the devops pipeline to test the database scripts, application with current database, etc.

Azure devops video

Azure devops: Use the Windocks REST API 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'


Jenkins : Use the Windocks REST API from the pipeline code.