Flyway

Working with Microsoft SQL Server Database (SSDT) Projects

We work with some teams that are using SSDT for their database development.  This is a state-based approach.  If these teams want to reliably automate their deployments, they need a migrations-based approach.  You can add migrations deployments without changing the way your developers work.

The developers can continue to use SSDT.  The release manager can use Flyway Desktop and a database built from the SSDT project as it's development database.  To prepare for a deployment:

  1. Make sure the development database for the Flyway Desktop project is up to date with all the SSDT changes.
  2. Use Flyway Desktop to save all the schema model changes to disk.
  3. Use Flyway Desktop to generate the migration script.  The next version number is generated and you can specify a more descriptive filename.  You can also edit the migration script (e.g., add some data updates), if needed.
    1. Projects can also be configured to generate a corresponding Undo migration script as well.
  4. Verify the migration script in Flyway Desktop to catch any issues as soon as possible.
  5. Use Flyway Desktop to commit and push these changes to Git.

At this point, your pipeline is setup to pick up these migration scripts and deploy to your downstream environments.  Learn more about Deploying database changes using Flyway.


Why should I use Flyway with SSDT Projects?

If your team is used to using SSDT Projects, then they may not want to change.  Adding Flyway into this existing process provides the following benefits:

  1. Flyway manages the execution of migration scripts on downstream environments.  This provides the ability to track who and when migration scripts were deployed to each of your downstream environments and track how long each script took to execute.
  2. With Flyway, you can track and deploy non-state based scripts as part of your project and these will follow the same deployment process.  These could be scripts that have some initial setup for the database, or scripts that have a one-off data change, to even more complicated scripts that handle complex database refactorings like table or column splits.
  3. With Flyway Enterprise, you can track which tables contain static data like configuration data or lookup tables.  These static data will be versioned controlled alongside the schema objects so you can see how it changes overtime. 
  4. With Flyway Enterprise, you can generate versioned and undo migration scripts that capture schema and static data changes in with the correct dependency ordering.  (SSDT recommends handling static data with a post deploy script, which would then have to be written idempotently since it is executed on every deployment.) 
  5. Flyway also provides static code analysis for your SQL changes.  Flyway Enterprise also allows you to write your own customized rules that will check your SQL changes and can be setup as part of a CI/CD process.  These can enforce naming conventions or raise issues before scripts are deployed to Production.
  6. If your organization uses other databases in addition to SQL Server, then Flyway supports over 50 different databases and therefore you can standardize your database deployment across multiple teams/organizations all with Flyway.  



Didn't find what you were looking for?