Main Options for Database DevOps

I’ve recently started a new contract that have goal to get their database structure into Git. This is laudable!👏 (you have your code in Git, right?!?)

At Omnitech we have a long running Teams chat going on the difference between State and Migration based versioning of Databases (we are MS SQL centric thinkers). I’ve compiled the feedback and created 2 small demos for my own learning to share with you.

State Based

1. Visual Studio SQL Server Data Tools (SSDT) Database Projects
   1. Make all changes in Visual Studio and check in the changes
   2. Provides refactoring, validation at development time and Schema Management
   3. Multiple profiles for different options
   4. Can target specific SQL versions
   5. Works with Azure Databases
   6. Referenced databased can be worked around
2. Works with Git and Pipelines for CI/CD
   1. .dacpac files are created via MSBuild that can be applied to databases
3. May run into interesting deployment and development issues
   1. Sometimes it has required adhoc SQL for breaking schema changes to get the project back to valid for small teams. This could likely be avoided with more work where Audit compliance is more stringent
   2. One experienced enough “pain” that he wouldn’t recommend this approach again (for larger products he would use migrations)
4. A more familiar approach for Developers
5. Good for simple single database maintained by Developers
6. You can see the full picture of what the database will be after changes are applied
7. Free!
8. https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects?view=sql-server-ver16

Migration Based

1. Use DBUp
2. Transitions, not States
3. More control than SQL Database Projects, you dictate and can see how the scripts are applied in order
4. May need to look through migrations to determine what the final state will be
5. A more familiar approach for DBAs of creating migration scripts
6. Works with Git and Pipelines for CI/CD
   1. Create and Update scripts with a comparison tool or manually
   2. Add the script to Git with the correct name and folder
   3. In the pipeline, execute the DBUp runner applies each script in succession
7. Free!
8. https://dbup.readthedocs.io/en/latest/philosophy-behind-dbup/

Another option is to use Entity Framework Core with Migrations . Here is a good video explaining the process .

RedGate Flyway

1. You choose migration or state approach
   1. You can see the schema model and get migration scripts at the same time
2. Created to help DBAs do DevOps
3. Has a yearly per user cost
4. Works with Git and pipelines
5. A lot of helpful features are included
6. https://www.red-gate.com/products/flyway/

Which will you chose for your project?