This article is intended to help you get up and running with a pipeline in Azure DevOps for building and deploying your databases using Flyway.

If you would prefer to watch a video, please see my colleague Andrew Guards content here which inspired this article: https://www.red-gate.com/hub/university/courses/flyway/flyway-desktop-enterprise-implementation/preparing-for-the-poc/proof-of-concept

This article will include changes to how Check is run in a pipeline using the new Environments configuration.

Contents:

Creating an Azure DevOps project

Creating an Azure Self-hosted agent

Creating a Flyway Desktop project

Linking our Project to our Azure DevOps Repository

Create our First Migration

Download and Configure YAML Files

Configure and run Build Pipeline

Configure and run Production Pipeline

Creating and reviewing Drift

Configuring Deploy Cherry Pick

Configuring Undo Definition

Flyway Version in Pipeline

Creating an Azure DevOps project

To begin, go to https://dev.azure.com/

If necessary, create an Organization or use an existing one. Once you've done that, you will see this page where you can easily create a new project:

In the top right-hand corner, click New Project and you will be presented with this page, simply name your project (description is optional) and for Flyway Desktop integration, you'll need it to be the default of Version Control Git under Advanced Options.

Once created, you will be presented with this landing page:

Creating an Azure Self-hosted agent

First, you'll need to set up your Self-hosted Agent, so click on Project Settings in the bottom left-hand corner, then click on Agent Pools > Default:

Next, click on New agent and then Download:

Navigate to your downloads and unzip the following file:

Inside the folder, run the following as an administrator config.cmd and then enter your server URL which will be dev.azure then the name of your organization, e.g. "https://dev.azure.com/DanCalver".

Press enter for PAT and generate a PAT token; go back to Azure DevOps and in the top right-hand corner, click on user-settings and at the bottom of the list Personal authentication token.

Click New Token, and you'll be presented with this pop-up configuration page for the token.

Depending on your organizations' security policy, instead of granting unrestricted access, it may be advisable to exercise greater precision in defining your permission parameters, however, this can also cause disruptions in your pipeline with needing to manually grant access at various stages.

You can set full access and give the token a 90-day expiration (this means in 90 days you'll need to configure a new PAT and update the agents token).

Once done, you'll be presented with a token. You'll need to copy this because you can't access this again.

Once you've copied the token, you can paste that into the command line for the agent configuration.

Here, you can choose the default pool, or if you've defined your own you can enter it here. Later in the document there will be a stage where, if you've specified a custom pool, you will need to enter that value so it can find your agent.

Next, you can give the agent a meaningful name to appear in Azure and then specify a folder location for the agent's files or default of _work (this is where your reports and other Flyway resources get stored in the process).

Here, you can choose to have the agent run as a service, which means your agent will be run as a service and will start automatically with the start-up of the operating system on which it is installed.

Say Yes (Y) to the next configuration options, and then you can either give it a specific account to use or default NT/AUTHORITY\NETWORK SERVICE.

Now it will be run as a service.

Creating a Flyway Desktop project

First let's ensure we have all the required databases needed to take advance of Flyways advanced features such as change and drift reports

This example is using the Northwind database here: https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs.

Copy the code from "instnwnd.sql" and execute against our production/target database, e.g. "DansFlywayPipeline_Prod".

Now create the project in Flyway Desktop. Open the tool and click New Project:

This will create the project which contains the following objects:

migrations - where it stores your generated migrations by default, unless overridden

flyway.toml - project configuration settings, such as comparison options

flyway.user.toml - local developer environment settings

schema-model - stated-based model of your database objects

Once created, you'll see this page; click Link development database:

Now enter the credentials of the development database you created further up, then Test and save.

Now you'll come to this schema model page, currently you don't have any objects to track:

Next step will be to click on Generate migrations on the left side panel and proceed to click on Set up shadow database:

Same as development, now enter your details for the shadow database you pre-created.

You'll then come to this landing page; proceed to click Create baseline:

When creating a baseline, you're essentially taking a snapshot of your production/target database so that you can bring fresh databases up to the same point, which is what you're going to do by applying the baseline you generate to your development database.

From here, you can modify the existing comparison options that will be respected during the script creation, but you're going to stick with the defaults.

From here, click on Add target database:

Now you're going to link the production/target database you created earlier which you populated with the Northwind database.

Once tested and saved, click Create baseline:

This will give you this script; you can review and modify the description if you desire. Once reviewed you can save.

Next Verify generated migrations.

Because you currently don't have these objects in the schema-model, the tool is asking if you want to generate a migration for dropping these objects, which you don't want to do!

This is because you're going to simulate bringing your development database up to speed by applying the baseline to it, so now click on the Migrations scripts tab on the left:

On the far right side, you're going to use the drop-down and select "development database" as your target, hit Migrate:

You should then get this message that your baseline migration was applied:

By default, Only show pending migrations is enabled, but while still using development database, if you untick, you can see that your baseline was migrated successfully:

Now you can go back to your Schema Model tab, and you should see all the objects created from your baseline that you now need to save to your project.

Once you've clicked Save to project, you'll get this successful output of all your objects:

You can ignore the Generate migration button for now because you don't have any new changes, so simply press close in the top-right.

If you look at the project in file explorer, you can see the structure that's been created in your schema model:

Linking your project to your Azure DevOps Repository

In the right hand corner of the tool, you can click on VCS tab to bring the panel out, now click Initialize repository.

Next you can select the path, which will be your Flyway Desktop project folder:

Next, go back to your Azure DevOps project and click on repo:

Now copy the commands from Clone to your computer.

Go back to Flyway Desktop to the VCS tab and click Add remote repository:

Now paste the url into the UI below:

Once added, all of your changes within the project will show as uncommitted changes:

You can then add a commit message and tick the files you want to commit.

Once you hit Commit, you can then see the UI light up for objects that are ready to push:

Once you've hit Push, you can go back to your repository.

Now if you refresh your repo in Azure DevOps you'll see your project:

Create your first migration

Go back to SSMS and make a modification to your development database, e.g. 'DansFlywayPipeline_Dev'.

Add a COLUMN to your table Customers by running the following code:

ALTER TABLE dbo.Customers
ADD TwitterID VARCHAR(20);

Once done, you can go back to Flyway Desktop and check the Schema model tab and you'll see a modification for your table is available:

Once you're happy with the changes, we can hit Save to project in the top right:

Now click Generate migration. You'll be presented with this screen to review all the changes to the object and then Generate scripts:

You'll be asked if you want to auto-generate undo scripts for your migrations. You would typically say yes (if you happen to say no, and you want to change this you can change it under the cogwheel).

Now you can review the generated migration and undo script to see what the code looks like that will be run as well as modify the migration script name. When you're happy you can hit Save.

If you now go back to Migrations scripts tab you can see the newly generated migration script:

You can now commit these changes to your source control repository. If you click on VCS in the far right corner you're presented with the Version Control tab:

Here you can provide a specific branch or keep to master as the default. Provide a meaningful commit message and Commit:

Once committed, you'll see the up arrow icon glow to indicate there's changes to push up; hit Push changes:

Now you can see your files are in the repository:

Download and Configure YAML Files

You're going to be using the YAML scripts in this example: https://github.com/red-gate/Flyway-Sample-Pipelines/tree/main/Azure/Azure-Templatized-YML-Pipeline

First, you're going to download the repository, by stepping out into the main of Flyway-Sample-Pipeline, clicking on code then Download Zip:

Once downloaded, proceed to extract and then navigate through the folders to the following folder:

Flyway-Sample-Pipelines-main\Flyway-Sample-Pipelines-main\Azure\Azure-Templatized-YML-Pipeline\flyway-10.4\

Copy the following YAML files into your Flyway Desktop project folder.

• deploy-build.yml
• deploy-cherrypick.yml
• deploy-prod.yml
• undo-definition.yml

Your project should now look like this: (note .git folder will only show if you have show hidden folders ticked)

Next, you need to go back to the folder and copy out the templates folder into a location, e.g. documents.

Now you'll need this folder to be placed into a separate repository within your pipeline. First initialize a repository from this location:

Now go back to the Azure DevOps Project > Repos, then click on your repo name in the navigation bar:

Click New repository, then name it templates and don't add a readme, then click create at the bottom right-hand corner:

Copy the commands from Push an existing repo and paste into your command line.

Perform a 'git status' to see the new files we need to add and commit, now do ' git add *'

Next, you need to commit and push:

Now you have your dedicated templates repository inside our project:

Note in vars.yml schemas parameter is not required for SQL Server -schemas="$(schemas)".

You're going to make a modification to the 'build.yml' by adding the explicit locations path.

If you click on the build,yml file you'll see the below screen, click edit:

By default you'll have the following code for migrate and undo:

- script: '$(FLYWAY) migrate info -url="$(target_database_JDBC)"'
continueOnError: false
displayName: 'Validate Migrate Scripts'

- script: '$(FLYWAY) undo info -url="$(target_database_JDBC)" -target="$(FIRST_UNDO_SCRIPT)"?'
continueOnError: true
displayName: 'Validate Undo Scripts'

You're going to modify the yaml to be configured like this:

        - script: '$(FLYWAY) migrate info -url="$(target_database_JDBC)" -locations="filesystem:$(WORKING_DIRECTORY)\$(Build.Repository.Name)\migrations"'

          continueOnError: false

          displayName: 'Validate Migrate Scripts'

        - script: '$(FLYWAY) undo info -url="$(target_database_JDBC)" -target="$(FIRST_UNDO_SCRIPT)"? -locations="filesystem:$(WORKING_DIRECTORY)\$(Build.Repository.Name)\migrations"'

          continueOnError: true

          displayName: 'Validate Undo Scripts'

Go back to your Flyway Desktop project folder, as you need to configure these yml files to work with your pipeline. First open deploy-build.yml (you can use any text editor, e.g. Notepad++ or Visual Studio Code). 

At the top of the file, you have these variable groups needed to create inside your Azure DevOps Library:

Next you have the different stages where the variable groups are being referenced.

You will notice there's this repository of templates mentioned, which we created above.

Across the different yml files for deploying, you want the following variable groups and their variables (feel free to modify their names to be meaningful to your project, but they must match the ones in the yml).

To help configure these jdbc connection strings use Flyway Desktop.

If you click on the cogwheel inside Flyway Desktop, you can modify the development database and configure the jdbc:

You may notice, you have (userName and password) variables for using SQL Server Auth for connecting to your databases in the pipelines, if you prefer you can use Windows auth.

This example uses SQL Server auth, creating a user called "DanFWDUser" who has sysadmin permissions: 

• dans_pipeline_yaml_build_credentials
  • databaseName
  • password
  • target_database_JDBC
  • userName

Make sure to click on Pipeline permissions and give Open access for all pipelines in this project to use this resource:

•  dans_pipeline_yaml_prod_credentials
  • check_JDBC
  • check_password
  • check_userName
  • databaseName
  • password
  • target_database_JDBC
  • userName

• dans_pipeline_yaml_test_credentials
  • check_JDBC
  • check_password
  • check_userName
  • databaseName
  • password
  • target_database_JDBC
  • userName

• dans_pipeline_yaml_vars
  • BASELINE_VERSION
  • FIRST_UNDO_SCRIPT
  • schemas

If you have multiple schemas, you can comma separate them in the variable value.

Earlier on you created a migration and an undo script; you're going to grab this value to put as your first undo:

• redgate_global_vars
  
  • AGENT_POOL
  • FLYWAY_LICENSE_KEY

Now you have your library viarables ready, you can begin to modify the yaml files in your Flyway Desktop project.

First, go back to 'deploy-build.yml' and change the values for:

• targetCredentials
• pipelineParameters

Next you have 'deploy-prod.yml'.

Once again you're going to change the (targetCredentials & pipelineParameters).

Remove the third block with the second production step as you only have the one production database you want to deploy to:

The first block that's not commented out is test, so you're going to provide your test credential variable.

On the second it's your production credentials. The modified version should look like this

Once saved, head back to your Flyway Desktop project and the VCS tab.

You should see these changes for the yaml files to be committed and pushed:

For an initial pipeline to run to against build, test and prod you're all set with the yaml files'

There's a section for configuring the last two yaml files further down in the article.

• deploy-cherrypick.yml
• undo-definition.yml

Now if you go back to Azure DevOps, on the left hand panel click on Pipelines:

Configure and run Build Pipeline

Click on Create Pipeline > Azure Repos Git (YAML):

Select your repository for your Flyway Desktop project and yaml files e.g. DansFlywayPipeline:

Use an Existing Azure Pipeline YAML file:

You will then get a side bar pop out for selecting which brand you want to run and the path to the yaml file e.g. deploy-build

Next you'll get a screen to review the yaml being run. You can also add additional variables and then run:

Once you trigger the run, you'll be met with this display:

If you then click on Build under Jobs you might need to give permissions such as me:

We can fix this by going into the templates repository.

First navigate back to repos and then Manage Repositories:

Click on templates:

Click on Security and you'll see at the bottom you have pipeline permissions. By default there will be none set because you've only just created the pipeline above.

You can specify which pipelines you give permission for e.g. Open Access.

Once you've done this, the build will then continue, you may then get this issue about Agents not having permissions:

You need to navigate back to Project settings here at the bottom:

Next Agent Pools > Default > Security:

You will see you haven't given permissions for any pipelines to use the default agents pool:

Once again for this example, give Open Access.

You will need to run the pipeline build again, but it should now run without further access requests!

You have success with your first build of the Flyway project in your pipeline:

If you now click on Validate Migrate Scripts you should see your migrations being validated:

Also for the Validate Undo Script, your first undo script was successfully run:

You can also go back and rename the pipeline to be more meaningful for it's task.

Configure and run production pipeline

Once again click on Pipelines:

Click on Create Pipeline > Azure Repos Git (YAML):

Select Azure Repos Git > Your pipeline:

You now have a manual review step where you can view the various reports generated.

If you go to the publish task, you can go to the agent location and check the reports:

You can Inspect Change Report and see what changes have been made:

Once you've reviewed, you can go back to the pipeline andReject or Resume with a comment:

Creating and reviewing drift

Go back to SSMS and create a change directly on the production/target database to simulate drift occurring. 

You can make the following change:

ALTER TABLE dbo.Customers
ADD FacebookID VARCHAR(20);

Once you've made this change, you can simply re-run the production pipeline.

This pipeline is set to fail on drift detection which you can see below:

You can now review the drift report and see the following:

With regards to correcting drift, that's down to you the individual, and how you would prefer to go about handling this.

Configuring Deploy Cherry Pick

Cherry Pick is used for selecting specific migrations to be run against the target database.

To configure, if you go into your Flyway Desktop project and modify "deploy-cherrypick.yml".

Once again, you need to configure the parameters with your library variables:

Once you've committed and pushed within Flyway Desktop, you can go back to Azure DevOps.

Now use the following steps for creating the Cherry Pick pipeline:

Azure Repos Git > DansFlywayPipeline > Existing Azure Pipelines YAML file: 

Hit continue and then once the YAML file is loaded, rather than Run, you need to hit Save:

This is because if you run straight away it won't prompt you for the parameter to populate with migration ids.

Once done, you will come to a landing page where you will then execute Run pipeline:

Once you've hit run, you will get a side bar pop up on the right hand side which will ask for a comma separated list of migration version numbers.

When you're entering the values, please ensure you're leaving no spaces or you will get an invalid flag error upon running the pipeline:

Once you've put in all the desired migrations, hit Run in the bottom right hand corner

Upon inspecting the deployment to your test environment, you can see it validated all of your migrations successfully, but it only applied the migrations you specified via pipeline parameter.

In the first table it shows the two migrations you provided are set to status Pending and once successfully applied, they get Success.

The other migrations are rightfully Ignored:

Configuring undo definition

The undo definition is great for reverting applied migrations that you want to undo.

To configure out of the box you need to, once again, update the pipelineParameters & targetCredentials in the 'undo-definition.yml' in your project:

Save and commit/push to your repository and then create the new pipeline from the YAML file:

Azure Repos Git > DansFlywayPipeline > Existing Azure Pipelines YAML file: 

Once again, save the new pipeline:

Once done, you will come to a landing page where you will then execute Run pipeline:

When you've run, you'll get the side bar asking for the migration ids for the migrations you want to undo:

You will notice the other migrations are ignored and the one you specified has successfully been undone:

Flyway version in pipeline

How does the Azure DevOps pipeline get the Flyway CLI or the version of the Flyway CLI to use?

When you installed Flyway Desktop earlier in the document, this creates an environment variable for the tool in your path:

The self-hosted agent will then use the version of the Flyway CLI embedded within Flyway Desktop.

However, you can also specify the exact version of the Flyway CLI you wish to use, or if you don't have Flyway Desktop on the machine you're running the agent on.

This can be done by specifying an environment variable for the Flyway CLI:

C:\Users\Dan.Calver\Documents\Flyway\flyway-10.9.1