Configuring Azure DevOps build-release pipelines with ApexSQL DevOps toolkit plugin (4247737)

The ApexSQL DevOps toolkit plugin complies to the build-release management concept and in respect to that, the build and release pipelines will have to be configured in the following manner:

Build pipeline:

• ApexSQL DevOps toolkit Build – Package tasks – where a database NuGet package artifact will be created

Release pipeline:

• ApexSQL DevOps toolkit Sync – Sync Data (optional) – Deploy tasks – where an artifact from the build pipeline will be imported and deployed to a target database

Build pipeline

The basic database build pipeline consists of at least two tasks, the ApexSQL DevOps toolkit – Build task at the beginning and ApexSQL DevOps toolkit – Package task at the end of the pipeline. Naturally, it is always possible to insert other tasks for testing purposes between them:

In the configuration page for these ApexSQL DevOps toolkit related build pipeline tasks, it is necessary to leave the Include output in package option checked. In the designated ID field enter the package name in the standard format for the NuGet packages – {Name}.{Major}.{Minor}.{Build}, where the {Build} value can be replaced with the standard Azure DevOps environmental variable $(Build.BuildNumber) which will assign the pipeline execution order to the build part of the version:

The same ID should be repeated for all ApexSQL DevOps toolkit extension tasks used in the pipeline and that includes the ApexSQL DevOps toolkit – Package task, which will finalize the process by including database project in the NuGet:

It should be mentioned that the ID field for the package name can also include an absolute path for the output storage, but to link the build pipeline with the release pipeline this should be omitted so the working folder of the Azure DevOps agent that executes the pipeline will be used for output.

Check out the article on how to setup continuous integration and continuous delivery tasks using the ApexSQL DevOps toolkit – Azure DevOps Server/Services plugin to see more information about ApexSQL DevOps toolkit tasks configuration.

The build pipeline should be terminated with the Azure DevOps integrated Publish build artifact task. This task should be configured in the following manner:

• Display name – type in a recognizable name for this task
• Path to publish – should be populated with the $(Build.ArtifactStagingDirectory), the Azure DevOps system variable. When used, this variable targets the default artifact staging folder, dedicated for the executed build pipeline, within the agent’s working folder. In other words, this is the location where ApexSQL DevOps toolkit tasks are going to store the NuGet package if absolute path is omitted and the location will be now used as a source for publishing artifact
• Artifact publish location – this option can be set either way, both Azure pipelines or a local file share with defined path can be set without consequence to further workflow whatever was chosen; this will store output artifact, i.e. the NuGet database package, to the Azure DevOps hosted storage or on local storage respectively, to be consumed in the release pipeline

Release pipeline

The database package created with the build pipeline will contain database scripts which the ApexSQL DevOps toolkit – Sync task will use to compare with a target database and create a schema synchronization script to update it. After that, the ApexSQL DevOps toolkit – Deploy task will execute that script and update a target database.

Creating the release pipeline is standard as for any other type of projects. New release pipeline should be created and the artifact source selected from the build pipeline that creates the artifact, i.e. the database package:

Triggering the release deployment can be set to be manual or automatic:

For the pipeline tasks first add the ApexSQL DevOps toolkit – Sync task. The task should store output in another NuGet package which will be used as source for the ApexSQL DevOps toolkit – Deploy task. The absolute path for a created package should be left out so the Azure DevOps agent’s working folder can be used to store it and subsequent tasks after this one can use it directly from there:

The source for the ApexSQL DevOps toolkit – Sync task should be the Local NuGet type, and to directly use the build artifact the ID field should remain empty:

After that, the target database connection parameters should be entered. This is a database that the database scripts in the source NuGet will be compared with to generate the schema synchronization script:

The script will be stored in the defined NuGet which can be called the deployment package.

In case there is some static data included in the build, the ApexSQL DevOps toolkit – Sync data task can be added and the same configuration instructions apply to it if used. The only difference is that the generated output will be the data synchronization script, in contrast to the ApexSQL DevOps toolkit – Sync task:

Final task in the release pipeline would be the ApexSQL DevOps toolkit – Deploy task. As previously explained, this task will execute generated schema and/or data synchronization scripts. This task’s configuration does not necessary require that the task’s outputs are stored in a NuGet package, so the option to Include output in package can be omitted. If it is important to store deployment summary, that basically includes the SQL Server execution messages, this option can be used but it is recommended that a different package name is used here. This way the input NuGet package content will be separated from the output NuGet package:

For the deployment source the NuGet package generated with the ApexSQL DevOps toolkit – Sync/Sync data tasks should be used. To define that, in the Source section for the task, tick the From a package option, choose the Local storage option from the Stored on select box, and in the ID field type in the path to the deployment package. Since no absolute path was used in previous tasks, to create reference to the path for the created NuGet deployment package (i.e. the agent’s working folder) use the system variable $(System.ArtifactsDirectory) for the path and add the NuGet package name to it:

To finish the configuration of the ApexSQL DevOps toolkit – Deploy task, the target database connection parameters should be entered and this is the database where the synchronization scripts will be deployed:

If Azure DevOps build and release pipelines are configured with ApexSQL DevOps toolkit tasks, like explained in this article, it will be possible to perform standard build-release procedure. Execute the build pipeline, and create a release using the chosen version of the build artifact: