• Flyway
• Integrating with Flyway
• Team-based Development

Using SSDT and DACPACs with Flyway: a Demonstration

For the demonstration, all you will need are:

1. Two DACPAC files, one representing the ‘before’ version of the database (target) and one the ‘after’ version (source)
2. SQLPackager.exe from SSDT – to compare the source and target DACPACs and generate a ‘synchronization’ script
3. A couple of script files – which I provide, to automatically convert the script into a Flyway migration file. Note that this will migrate just the metadata, which is generally what you will want.
4. Flyway Teams CLI – to run the migration

Before we turn to the practicalities, a quick explanation of why we might want to do this, and what Flyway brings to SSDT/DACPAC development, and vice versa.

SSDT in database development

SQL Server Data Tools (SSDT), provided by Microsoft as part of Visual Studio, is a tool for developing, managing, and deploying SQL Server databases. It provides a set of utilities that assists the developer in designing and maintaining the database schema, such as the tables, constraints, indexes, procedures and routines, to write and debug SQL scripts, and to deploy changes to the database.

SSDT supports all the work of creating and managing the database, whether you are using a visual designer or writing SQL scripts directly within Visual Studio. It includes a collection of visual tools for tasks such as schema comparison, refactoring, and version control, helping development to maintain the structure of a database accurately and efficiently.

Once development changes are complete, the team that uses SSDT will generally deliver the new version of the database as a DACPAC (Data-tier Application Package). DACPACs serve as a portable container that encapsulates the design of the database, including both the metadata and the data. This DACPAC is then used to deploy the database schema.

Deploying database changes made in SSDT

SSDT uses DACPACs to deploy database changes to different database environments (e.g., development, testing, production). The developer can generate a DACPAC file that represents the current state of the database schema and associated objects. This file can be easily shared with the team or deployed to different SQL Server instances.

SSDT can perform a comparison between the desired schema state, defined in the DACPAC, and the target database. It then generates a deployment script that contains the necessary SQL statements to update the target database schema accordingly. This ensures that the database schema is consistent across different database environments and simplifies the database deployment process.

How can Flyway improve the SSDT process?

The most immediate change that Flyway can make is to promote the synchronization script into a migration script that can then be used to change a database from one version to another. By chaining them together into a migration run, a database can be easily migrated from one version to another.

SSDT, by itself, has no concept of a database version, though a particular database installation can have a version. Updates are done via synchronization scripts that need to be specific to a source and target database installation. This means that the synchronization script can only be generated and tested for a specific source and target.

The problem here is that it generally pushes testing back to staging, during deployment, when the synch script is generated, to ensure that it works with different data sets. In contrast, a Flyway migration script will always change a database from one version to the next version (or previous version with an UNDO script). This makes it much easier to set up team-based development work with branching and merging. It makes testing much lass fraught because there is no doubt about the version being tested. It renders the provisioning and updating of copies of the database much easier because the version they start at is known and there will be migration scripts that will, if executed in turn by Flyway, result in target database being at the required version. By the time that this same chain of migration files is used to update production systems, the migration scripts are well-used and tested.

DACPACs provide a simple model of SQL Server database development and deployment and can still be used for branch development work. However, they are best used just for recording the schema and data of a database at a point in time. A DACPAC has the drawback that it can’t be inspected or searched and is therefore less useful in source control. A DACPAC is a clever package system, but it is proprietary, so you cannot get a record of changes between DACPACs from your source control system.

Adding Flyway to the Process

Flyway helps to manage and apply incremental changes to database schemas in databases. It is particularly useful in CI and DevOps environments for defining exactly what is in a deployment, for testing that migrations work as intended, and for maintaining copies of a database at the same version. Because it can detect the version of any Flyway-maintained database it can apply the right migration scripts in the right order to bring it up to the version you need.

In an integrated system, a developer will be able to continue to use SSDT for branch development, but in conjunction with Flyway to integrate the work. For example, in a branch-based database development, each feature branch database can be managed by SSDT, but the work will be merged into the shared Develop branch using Flyway migration scripts (meaning that the Develop branch database is managed by Flyway).

Rather than applying the SSDT-generated deployment script directly to the database, we modify the script to remove any code that isn’t necessary or useful as part of a Flyway migration and assign it a version number, in accordance with Flyway’s naming conventions.

From that point, Flyway will manage all migrations to ensure that all changes are controlled, and it will automatically keep a record of each new version of the database, and when it was made. This makes it much easier to create copies of the database for testing and makes it more certain of what is in a deployment.

Integrating SSDT and Flyway development: a demonstration

To integrate SSDT with Flyway, each developer can follow a six-step process:

1. Create a ‘before’ DACPAC from the database, as it was before you made any development changes. Normally, you can just keep the DACPAC you used to create your copy of the database.
2. Make your changes in Visual Studio on your copy of the database.
3. When you have completed and tested your work, and are ready to merge it in, create a DACPAC of your copy of the database.
4. Use the SQLPackage utility to compare the two DACPACs, with the post-change DACPAC (from step 3) as the source, and with the one created at the start of the work as the target. This will script out all the changes made, as SQL Statements.
5. Remove all SQLCMD placeholders, or change them into Flyway custom placeholders, and remove any transactions in the script. Alternatively, extract the relevant portions of the synchronization script. The generated synchronization script may include statements for various types of changes, such as creating tables, altering columns, or modifying stored procedures. Identify the specific changes that you want to include in your migration file.
6. Use Flyway to apply the extracted and modified SQL scripts. Once you have the SQL scripts extracted from the DACPAC, you can use Flyway’s standard workflow to apply these scripts as part of your database migration process. You can execute the extracted SQL scripts using Flyway’s command-line tool, Maven plugin, or other integrations.

By combining these steps, you can leverage the advantages of both DACPACs and Flyway. The DACPACs provide a convenient way to package and distribute the database schema, while Flyway enables you to manage and apply incremental changes to the schema in a controlled and versioned manner.

Adding changes into Develop to make a new version

Imagine that we have a developer working on an accountancy module for Pubs, the old Sample Sybase database. The Dev delivers a DACPAC called Pubs1.1.13.DACPAC. This developer started work using a DACPAC of version 1.1.12. Now we have to create a Flyway migration file.

All we need for this is SQLPackage.exe (part of SSDT), the three script files I’ve written, and the two DACPAC files of different versions of the Pubs database. The Pubs project that I used is available on GitHub.

Fortunately, we already know that the developer made no changes to existing tables. We now create a first-cut migration file called AddAccountingFeature.sql. if it checks out, we can name it V1.1.13__Accounting.sql, and add it to the migrations for this branch to incorporate the work.

This batch script will do step 4 and 5 of the steps itemized in the paragraph above.

You can, of course, execute this in a DOS window once you’ve changed to the correct working directory

<MyPathTo>\Pubs\Branches\develop>call GenerateMigrationScript.bat "Versions\1.1.13\Pubs1.1.13.DACPAC" "Versions\1.1.12\Pubs1.1.12.DACPAC" "AddAccountingFeature.sql"
Generating publish script for database 'My_Database '.
Successfully generated script to file S:\work\Github\FlywayTeamwork\Pubs\Branches\develop\AddAccountingFeature.sql.
Changes to connection setting default values were incorporated in a recent release.  More information is available at https://aka.ms/dacfx-connection
Time elapsed 0:00:18.12
Migration script AddAccountingFeature.sql generated successfully.
Migration script AddAccountingFeature.sql cleaned of SQLCMD code.
<MyPathTo>\Pubs\Branches\develop>

I’ll explain about the magic within this batch file after the next example.

Updating the ‘Main’ branch to create a release candidate from the ‘Develop’ branch

With the same development of Pubs, the old Sample Sybase database. We can now do something more complicated, with branch based development. For this example, I am using my Flyway Teamwork framework.

The database is at version 1.2 in our main branch, and we want to bring it up to 1.3, merging in all the current cycle of Develop branch’s improvements. We will want a single migration that will do this so as to reduce the clutter in the main branch. In terms of the development branch, this is equivalent to a database at 1.1.15. When we’ve done it, we can check that the main branch’s database at 1.3 is the same as the development branch database at 1.1.15

We run a batch file (CreateMigrationFile.bat) to do this:

Here, I just need to specify the locations of my two DACPACs. The first line just moves us to our working directory where the files and code are placed. I’ve already generated DACPACS for every version, so I’ll use them. As you see, I’ve chosen to generate a migration file from the database represented by Versions\1.1.2\pubs1.1.11.DACPAC to the version represented by Versions\1.1.15\pubs1.1.15.DACPAC. This will then allow me to migrate the main branch from V1.2 (V1.2__SecondRelease1-1-3to1-1-11) to V1.3 (V1.3__ThirdRelease1-1-11to1-1-15).

Generating the sync script

As you will have noticed, the real work is done in a second batch file called ‘GenerateMigrationScript.bat’, shown below, which uses just those three parameters. You may need to adjust the value for sqlPackagePath to your SSDT installation.

Converting to a Flyway-compatible migration

Rather sneakily, because DOS doesn’t allow us to do regex operations on entire files, there is a short bit of PowerShell which is called by the batch, so the file TidyUpCode.ps1 needs to be there:

So, when we run this, we now have a Flyway-compatible migration file.

Running the migration

Let’s try it out. We will use this migration file to move the main branch of the database from v1.2 to v1.3. When we’ve finished, it should be identical to the copy of the development database V1.1.15.

We put the migration file in place, alter Flyway.conf to add the latest accounting schema to the list of schemas, and run:

Screeds of information messages then scroll up over the screen, and when all is quiet, we get this. The Teamwork framework provides the extra reports and data, but you don’t need that for the Demo. You can of course, get the Teamwork Framework to create the DACPACs for you automatically after every migration, as described in Flyway and SSDT: Extracting a DACPAC from a Flyway-managed Database.

Checking that it worked

SQL Compare can be used to test whether the script we’ve used has worked successfully, just to give us confidence. It shows that the two databases (development branch 1.1.15) and the main branch (1,3) are different only where constraints have been assigned different automatic names, which isn’t a difference and can be taken from the comparison with a SQL Compare option.

You can also do the comparison by comparing the DACPACs if you’ve set a Flyway callback that automatically generates DACPACs.

To Sum up

Flyway does not insist on a special methodology for development. It just needs a migration file with a version number in the filename as the deliverable. If you are doing SQL Server development there are a wide range of tools to assist from the free tools to the eye-wateringly expensive. Your only task is to integrate the work of the development team into a series of migrations. Flyway can work magic in deployments but merging the work of several developers can sometimes require patience and skill. However, short of the beautiful hand-crafted migration file, which is rare, DACPACS are as good a way of delivering individual work to the team as any.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Teams

Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.

Find out more