Tony Davis

11 June 2021

• Flyway
• Database Versioning
• Automation and workflow

What is Flyway Enterprise?

Flyway Enterprise is a set of tools and services that combine to support the various tasks involved in database development and deployment, from version control to delivery. It incorporates the Flyway database migration engine, plus a set of Redgate’s existing tools, including schema comparison and cross-database version control, that are already widely used in the automation of build, test and deployment processes. It supports SQL Server, Oracle, PostgreSQL, MySQL and an expanding range of other RDBMSs.

Used together, these tools allow teams to introduce automation and control to their database processes, improve collaboration and overcome differences in their approaches to application and database development. Flyway Enterprise will make it easier for teams to adapt and deploy databases quickly and effectively in response to business change, while improving code quality and reducing deployment failures.

Why Flyway Enterprise?

Database developers are facing changes in what is expected of them. They must deliver new features quickly, in sync with applications, and without downtime. At the same time, they must deal with the challenges arising from increasing diversity of scale in the database systems, an extending range of platforms and the increasing complexity of corporate databases.

Flyway Enterprise is a response to the pressure points in the software delivery process that arise from these challenges. It is an integrated solution that will allow teams to standardize their approach to tasks such as versioning, testing and deployment, for any database system. It aims to provides tools for development and deployment that are as versatile as the development and DevOps teams that use them.

Flyway Enterprise will speed up development of smaller database systems, such as microservice systems, and scale smoothly to accommodate team-based development of corporate databases. It supports extended automation of the development, testing and deployment processes, while promoting visibility and inspection of the proposed changes, such as a check on a deployment by your security team, well in advance of any release.

Diversity of scale

Alongside the measured delivery of organization-wide databases, developers now face the task of creating and enhancing a whole range of special-purpose databases, often on embedded platforms or Linux hosts. Microservices, bought-in systems, and special-purpose databases have become essential parts of the organization, alongside the classic relational corporate databases, such as SQL Server, Oracle, PostgreSQL and MariaDB.

Where there is just a small tactical database and matching application, as is typical in many microservice architectures, the database can be developed and deployed without any extra software tools, aside from a way to deliver the required database changes, reliably, to the production system. These changes are usually described in one or more migration scripts, deployed with a tool such as Flyway, which supports a wide range of different database systems.

Enterprise databases, of course, tend to be bigger, more complex, often supporting many applications and usually developed and maintained by several, large teams of developers. Here, you’ll find that the simple migration-based approach can become slower and less reliable, without additional tools that will extend it to support large-scale team-based development practices. This includes automated builds and deployments, and all the pipeline checks, oversight and cross-team cooperation that is required to maintain the quality of what’s delivered, during frequent deployment cycles.

A range of platforms

What will help a team to develop and maintain a mixture of special-purpose and corporate databases, hosted on a range of platforms, both cloud and on-premises? For a start, it requires much better teamwork between developers and ops to make these databases easier to install, develop and maintain across all these difference platforms. This means more efficient development, easier deployment and monitoring.

Teams must be able to choose the best tools for the purpose, in each case, and the tools need to work together. With Flyway Enterprise, this involves more than just having a Command-line interface (CLI) but also being able to integrate with the development and release tools with which the team is already familiar, such as GitHub, Azure DevOps, Jenkins and TeamCity.

DevOps tools must fit in to the way that individual teams choose to work, rather than to impose a methodology on them. With database development, this means helping you automate various types of database scripting. This includes the migration scripts required for deployment, of course, but also the object-level DDL scripts that will make it easier for teams to track changes through source control, and the build scripts for reproducing and testing the latest development version. The tools must support versioning but also accommodate ‘variants’, such as those required for different regions or platforms. They must be versatile.

Flyway Enterprise allows teams to expand existing processes smoothly to the development and maintenance of corporate database systems running on a diverse range of platforms.

More complex corporate databases

As databases grow bigger and more complex, and teams larger, Flyway Enterprise will make it easy for you to incorporate into your process the team-based development practices required to support Enterprise databases. It allows for parallel database work streams through branching and merging and offers ways to cope with complex dependencies between different components of a corporate data system.

Above all, it will then allow teams to automate as much as possible of the build, test, and deployment processes, while accommodating all the required pipeline checks, tests, and reviews.

Coordinating releases with the applications that use the database

Flyway Enterprise will allow the team to maintain a strict versioning system for the database, in source control. This will make it easier to coordinate database upgrades with changes to each associated application.

The version of the database and application, in operation, must be kept in synch to avoid any compatibility problems, and there is a more general requirement to ensure that any change is ‘non-breaking’. For ‘close-coupled’ systems without a defined interface between the database and application, the application will access the entire database. In such cases, the release of the entire database must be under strict version control, and the application and database must be released together. All project resources will be organized side by side in the same source code repository, with a unified strategy for versioning.

For databases that support several applications, non-breaking changes are achieved only by having a definition of the database objects that are accessed by the application, and strict versioning of both the database/application interface and the application.

Extended automation

Flyway Enterprise will allow teams to script out parts of the development and deployment process that are currently manual, and error-prone, and automate them. Automation is about standardizing practices to help create a repeatable, reliable workflow. We automate the delivery of materials that need to be processed or approved. The goals of introducing automation are to make the whole delivery process resilient, secure, repeatable, quick and visible.

• Resilient – the process mustn’t break due to avoidable errors in the source code, or an error in the process due to an unforeseen condition.
• Secure – this of course means logging, and access control, but also includes the need maintain strict versioning system that prevents uncontrolled changes to the database. I t also requires adequate reporting of changes, so that only a limited amount of source code needs to be widely disseminated
• Repeatable – if a process is repeatable, it there is less opportunity mistakes. It requires a scripted approach that starts from a single ‘source of truth’, and avoids having poorly defined decision points in the process
• Fast – by automating as much as possible, developers can move quickly, spending as little time as possible on routine and administration.
• Visible – the development process must be visible. As teams get bigger and different skills are required, so reports, metrics and logging become more important, making it easier for team members to keep up to date with changes in the database.

Providing an integrated solution

Flyway Enterprise has been developed in discussion with existing users of Redgate’s database tools, and with a clear understanding of the problems they face in developing and deploying database changes.

To deal with the problem of supporting the growth of specialized databases to support microservices, it includes Flyway, which provides versatile system for building databases from version-controlled migration scripts and supports a diversity of database systems on a range of platforms. The migrations approach is ideal for keeping a database in sync with the application.

The increasing complexity of corporate databases is accommodated by Flyway Enterprise schema comparison tools that support a hybrid approach to source control and change automation tools, to manage the test and deployment workflow that underpins a database release.

Version Control

Flyway Enterprise promotes a controlled development and deployment process that always starts from a known version in source control. It ensures that every artifact of the database source, including schema, modules, data scripts is controlled and at a known version.

At each phase of development, Flyway Enterprise change control or schema comparison will then produce, in the version control system, the set of scripts that describe all development changes required for the new version. It can read scripts directly from any of the major source control systems. The purpose of a ‘build’ is then to ensure that the resulting database is at the version of the DDL code that produced it. With the discipline of knowing the exact version of any copy of the database, the team can then be confident that what is tested is exactly what is intended for release.

By automating database build and deployment tasks (see later) from a controlled set of traceable files, Flyway Enterprise change automation makes these processes reliable, repeatable, and quicker. The team will also find it much easier to coordinate database upgrades with changes to the associated application. They can automatically deploy the tested and verified database changes that will ensure that the database and application are always at compatible versions.

Optimizing the migration approach

When updating an existing production database, you will generally not be able to take it offline, tear down the existing version and build the new one. You’ll need a migration script that can do that upgrade with minimal disruption to the live service, updating the metadata from the previous version, while preserving all existing data.

Flyway Enterprise is optimized to assist with a “migration-first” approach to the development of a wide range of databases systems. It will build a database from scripts, to a specified version, and track, manage and apply all database changes.

With this approach, the developers save migrations scripts for each commit and then create a new version by running any migration scripts that were not previously applied to the target database, in the correct order. It’s an ideal approach for keeping a database in sync with the application, especially during development of specialist databases, and for databases that support microservices, which will typically involve frequent deployments. The team can carefully design migration scripts to cope with the preservation of data during this process.

However, without the right tools, it can also be a manual, and error-prone approach. Flyway Enterprise automates both the generation and deployment of these migration scripts. For developers, this will increase the speed and efficiency of the process and greatly reduce the risk of manual errors.

During the database development cycle a team can work collaboratively, using visual change control tools that allow you to import an existing database, and generate in source control the scripts to build a new version of the database. They then provide a direct interface between each developer’s local working copy of the database and what is in the source control system, such as Git. Developers can test changes locally using built-in unit testing frameworks. When they are ready to commit, sharing changes with the team, the change control tool will auto-generate the migration script to move an existing database, safely, from previous versions to the new one.

The team must, of course, carefully design and test any migration scripts that requires migrating existing data, to ensure that on deployment all data in the production system is preserved. The change automation tools in Flyway Enterprise will build the new database version by automatically running any migration scripts that were not previously applied to the current version of the database, in the correct order. It does not require a separate manifest to define the correct order, as do many build tools.

The Build-first approach

For every version of a database, Flyway Enterprise will save in source control the set of migration scripts to describe the sequence of changes required to move an existing database safely from its current version to the new version. However, the team also need a separate means of maintaining the current ‘state’ of each object, for every version. This makes it much easier for them to track, through the source control system, exactly what changed between two versions, as well as who changed which objects, when and why.

Flyway Enterprise supports this by also allowing you to script a compatible source directory of object-level scripts, as well as database build scripts, for every version. For example, you can generate the object-level source every time a migration to a new version successfully completes. It can then use schema comparison to compare any two versions of a database, list the differences, and auto-generate a migration script to synchronize a target database to the source.

This allow Flyway Enterprise to also support a conventional “build-first” approach to database development. If the source is the object source folder and the target an empty database, for example, it will generate a build script for the database, automatically creating all the objects in the correct dependency order.

This ‘hybrid’ capability will save teams a lot of time with routine development tasks and help them extend their existing migration process to support the team-based development practices required for complex enterprise databases. For example:

• Running ad-hoc development builds – by comparing the object source to an empty database to generate a build script for the metadata of any version. Flyway Enterprise accounts automatically for the relational dependencies between database objects that mean you must create them in the right order
• First cut migration script for merge operations – you can compare a branch version to the object source for the latest version in main to find the differences and generate a first-cut migration script to perform a merge.
• First cut rollback script – by comparing the object source reference for a version (say V1.1.4) to new version (V1.1.5).
• Detect and account for database drift – you can compare an existing database to its object source to it hasn’t drifted, i.e., that it really is at its stated version. Uncontrolled changes can cause security problems and deployment failures, if undetected.

Automated deployments and releases

Flyway Enterprise’s change automation tools provide a consistent and automated way to build any version of a database in source control, to prepare for a release. They aim for a design that allows as rapid as possible a delivery of database changes to a staging environment and permit as much as possible of the process to inspected, including the generated scripts, warnings, test results and changes.

They integrate directly with existing build and Continuous Integration services such as Azure DevOps or Jenkins, so the team can automate database builds throughout the development process. They will do this to check, continuously, that the source code and data files can be used to successfully create the database, with its development data, to the correct version. They also incorporate DevOps practices such as build verification, code analysis, and automated unit testing of the database. These practices will drive up the quality of the changes delivered, reduce the likelihood of a failed build, and drive down future maintenance costs.

Whereas the continuous integration process is concerned with testing, the release process must provide confidence that the deployment will occur predictably and safely. Flyway Enterprise supports a ‘continuous deployment’ model, where every new version is automatically released, but also recognizes that, for many teams prefer to select a version to be a release candidate at the right time, and that the release pipeline will require manual ‘checkpoints’.

Flyway Enterprise packages its outputs (NuGet or .zip) to communicate database artifacts between processes. These packages cannot be changed, but can be inspected, making as much as possible of the process visible to others, including any generated scripts, warnings, test results and a summary of the changes. The NuGet package contains a ‘model’ of the current release version, for which all changes were tested, and will automatically compare it to the target database to check for drift, before running the deployment. By packaging and controlling releases in this way, you can always be sure of exactly what was released and guarantee against unapproved changes entering the deployment pipeline.

Flyway Enterprise change automation also integrates directly with existing release tools such as Azure DevOps, Jenkins, Bamboo, Octopus Deploy to ensure that the tested release is fast and repeatable. By releasing frequently from controlled assets, in version control, with built in testing, verification, approval and sign off, you will also spot potential issues with a proposed change, as early as possible, before they reach production and become very expensive to fix.

Conclusions

Databases are used for purposes, and on platforms that, twenty years ago were difficult to imagine. This makes more demands on development to ensure that databases and applications can grow and develop in step and make the actual delivery process resilient, secure, repeatable, quick and visible. Hopefully, in this article, I’ve suggested some of the many ways that the tools in Flyway Enterprise can be used to achieve this in an integrated way that is easy to automate.