Azure Devops pipelines and databases

Azure devops pipelines are used today for continuous integration / deployment. Pipeline steps are specified to build application / service images and the images are used to deploy to test, staging and production environments. You can write a pipeline spec in Yaml and Azure devops will run it for you.

Example pipeline Yaml

The pipeline Yaml below specifies a set of 5 steps to run on an Ubuntu VM. The steps include restoring, building the service, testing it and then publishing it.

vmImage: 'Ubuntu 16.04'

buildConfiguration: 'Release'

- script: dotnet restore
displayName: 'dotnet restore'

- script: dotnet build --configuration $(buildConfiguration)
displayName: 'dotnet build $(buildConfiguration)'

- task: DotNetCoreCLI@2
displayName: 'dotnet test $(buildConfiguration)'
command: test
projects: '**/*Tests/*.csproj'
arguments: '--configuration $(buildConfiguration) --collect "Code coverage"'

- task: DotNetCoreCLI@2
displayName: 'dotnet publish $(buildConfiguration)'
command: publish
publishWebProjects: True
arguments: '--configuration $(BuildConfiguration) --output (Build.ArtifactStagingDirectory)'
zipAfterPublish: True

- task: PublishBuildArtifacts@1
displayName: 'publish artifacts'

Azure Devops pipelines and data

It is a best practice to test and stage with production data. You can add steps to the Azure Devops pipeline to deploy production databases customized for test and staging. Customizations needed typically are - Masking sensitive data, pulling release SQL scripts from a repo and applying them, and authentication controls. A good practice is to build an "image" of the customized data and then deliver from that image to the multiple test and staging environments. This is the practice used to deliver applications to test and staging.

Unlike applications, the image build for databases may not be feasible during a normal devops pipeline run because a multi terabyte database can take hours. Image builds can have a separate pipeline or automation that is triggered upon the arrival of fresh backup files. The creation of database clones from the image and deployment in containers to test and staging is instantaneous using database cloning technology. This step can be part of the normal pipeline run.

Windocks delivers databases for Azure devops pipelines

Windocks SQL Server containers, cloning and masking let you easily deploy databases to test and staging. Windocks orchestration allows you to combine all these into a single pipeline step without the need for you to write custom code. Simply provide a spec (in the form of a dockerfile) and Windocks will orchestrate the cloning, masking, containerization, production refresh services and abstract the deployment into a single pipeline step.

Steps to get started on Azure Devops pipelines and databases

1. Install Windocks Download the Windocks Community Edition or email 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), and authorization controls. The spec is provided in the form of a dockerfile. Build the image via a web application, command line or REST API.

Tutorial to build image from spec

3. Create the Yaml for the devops pipeline Use the sample above to create a Yaml file with steps for the devops pipeline. Substitute the content in the steps to make REST API calls to the Windocks server to create clones with containers. Get pipeline yaml for Windocks or see the REST API docs. Put in variable values in the Yaml for the IP / DNS for the server running Windocks, the sa user name and password you want for the containers, the credentials for the Windocks server, and the image name from step 1.
4. Create the pipeline in Azure Devops In Azure Devops UI, create a repository and push the pipeline Yaml to the repo. Then, create a pipeline pointing to the Yaml
5. Run the pipeline in Azure Devops Either manually run the pipeline in the UI or push to the repo to trigger a pipeline run. 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 Yaml

Explore topics