It is recommended to deploy to production environments using a CI/CD tool, for consistency, reliability, and faster delivery.

In order to get this set up, you need to work out what Flyway commands you want to run for your workflow and build them into a small script which is configured to run using your chosen CI/CD tool's job/task configuration. So for example, you might just choose to run the flyway migrate command and execute that as bash within Github actions yaml configuration.

Flyway Desktop can help you build the bash script component by generating default scripts which match your project type on the automated deployment page.

For more general context and help with setting up workflows see the onboarding checklist and Redgate University end-to-end demo.

Recommended build/deployment steps

1. Check out scripts from version control
2. CI - Build, analyze, and test database changes
   1. Build - The build validates your scripts and makes sure they can run successfully.
   2. Analyze - Code analysis checks - Make sure you're not using deprecated syntax, changing security settings, or dropping data.  You can also enforce naming conventions are being followed.
   3. Test - Testing can be unit tests, integration tests, regression tests, performance tests, etc.  Having a few smoke tests that run on every build and give quick feedback to ensure the most critical parts of your system are not broken is a good idea.
3. Deploy to a disposable test environment
   1. Execute your scripts on a build environment
4. Deploy to additional environments (optional)
   1. You may have additional test environments or Quality Assurance (QA)/User Acceptance Testing (UAT) environments that need to be deployed to.  Using an automated process helps ensure the consistency of your environments.
5. Deploy to a Staging/Pre-Production environment (optional)
   
   1. A Staging/Pre-Production environment is as similar to your Production environment as possible.  It may even have a copy of Production data, so that the deployment can be tested on a realistic environment as possible before going to Production.
   2. Staging/Pre-Production environments can also be used to check how long a deployment will take to run.  This information can be used to schedule deployments for off hours or down time as needed.
6. Check production for drift
   1. Check for Drift to make sure no objects have changed in Production since your last deployment.  This ensures that the Staging/Pre-Production deployment you did really reflects the deployment to Production and that no Production hot fixes will cause issues with the current deployment.
7. Manual intervention before going to Production (optional)
   1. Review the Change Report to quickly see how many objects will be changed.  Drill into a side-by-side difference of each object to understand the changes easily and determine if there's any impact. 
   2. Review the SQL script - This is the exact script that will be executed on the Production Environment.
8. Deploy to Production
   1. Optionally execute custom scripts before or after the main deployment via callbacks
9. Log changes
   1. Log changes (in SQL Monitor) and/or in Flyway Pipelines.

EDITION: ENTERPRISE

State-based deployments

State based deployments involve:

1. Generate a deployment script by running a comparison between your schema model and target environment. This is done using the prepare command.
2. Execute the deployment script against your target environment. This is done using the deploy command.

Resources

• Common state-based deployment scripts
• Example state-based CI/CD pipelines
• Autopilot CI/CD walkthrough

Migrations-based deployments

Migrations based deployments involve running the migrate command to apply pending migrations to a target environment.

Note: If you did not use Flyway Desktop and your downstream databases already have objects in them, then you will need to baseline your downstream environments first.  In Flyway Desktop, we set baselineOnMigrate=true and baselineVersion=<your_baseline_version_number> in the project settings so this will automatically be done the first time your pipeline runs.  Once your downstream environments are baselined, use these Example CI/CD Pipelines to setup your own pipeline for deploying database changes.

Resources

• Common migrations-based deployment scripts
• Example migrations-based CI/CD pipelines
• Autopilot CI/CD walkthrough

Ensuring that Flyway is installed on your build agent and available on your path

In order to use Flyway to migrate your databases as part of an automated CI/CD pipeline, you need to be able to access Flyway on your CI/CD agents.  There are a few ways to do this:

1. Use Docker (recommended). This way, you can just pull the latest version or a tagged version into your pipeline when it runs.  There's nothing to install/maintain.
2. Install the Flyway command line by downloading it and unzipping it to a location on the agent (e.g., C:\Flyway). See this tutorial for scripts to do this.
3. Commit the unzipped flyway command line files into your version control system.  Your CI/CD agents can checkout the repository with the necessary flyway files to execute the flyway commands from there.  When upgrading, just commit the latest flyway into your repository and it will be used in future pipelines.
4. Use Chocolatey or another package management system.

Ensuring that Flyway is authenticated to use enterprise features

Authentication in a pipeline requires non-interactive authentication.

Online non-interactive authentication

Access Token (PAT)

You can create a token in the Redgate portal and use that with a registered email address to authenticate Flyway. You might want to setup a service account associated with the flyway commands being called from CI/CD so that it's not tied to a particular end user.

This can be achieved using the email parameter and the token parameter. These can be set via environment variables.

Using a personal access token will store a license permit in the Redgate app data folder, the same location as when running the Auth command in interactive authentication.

Offline non-interactive authentication

These work without contacting Redgate (e.g. air-gapped pipelines) and so will expire at renewal time.

This will require that you update your pipeline to refresh the key/permit to continue working.

Non-interactive authentication is achieved using license permits or license keys (deprecated).

Handling authentication when connecting to databases in CI/CD pipelines

For authenticating against production in databases in a CI/CD pipeline, secrets may need to be passed around.

The exact mechanism will vary depending on the CI/CD tool, but Flyway supports the sue of environment variables for setting most parameters, including auth parameters such as username and password.

Flyway also comes with native support for some secret management stores.

Implementing a rollback strategy

You may want to consider implementing a rollback strategy so there is a known process for handling any failed deployments.

Switching from state-based deployment to migration-based deployment

There are a few edge cases that state-based deployments do not handle well.  A few are examples:

• Updating transactional data
• Adding a new NOT NULL column without a default value
• Renaming a table
• Splitting a table
• Renaming a column
• Splitting a column

These can be worked around with pre/post deployment scripts, but if you find yourself needing to workaround these a lot, or if you just want to guarantee that this exact script will be executed on your downstream environments, then moving to a migrations-based approach is an option.

It is usually possible to easily convert from a state-based pipeline to a migrations-based pipeline. While the recommended approach for deploying with migrations is to use the migrate command, it is also possible to deploy migrations using the prepare and deploy commands, so it may be possible to upgrade pipelines with minimal or potentially no change. See this tutorial.