This might seem like a stupid question, but I've been looking into open source solutions for schema migration, namely Liquibase and Flyway.
However, my boss told me that SQL Server Data Tools (SSDT) achieves the same job. I'm not sure if agree, but I can find very little on the Internet that directly compares it to Liquibase and/or Flyway.
My view is that SSDT is a development, data modelling and design tool for SQL Server and also supports schema comparison (and generating scripts thereof) and source control. It tackles a different problem although there may be some overlap with Liquibase/Flyway in some aspects of schema migration. But as an overall schema migration tool, Liquibase and Flyway are fully dedicated tools whereas SSDT is more for the design and development of a database.
Any opinions would be much appreciated even if it's just to say there's no comparison and SSDT is not a schema migration tool per se at all.
Best Answer
SSDT is comparable to Liquibase/Flyway as it does what they do but by taking a different approach. With SSDT you have the development environment so you get things like go to definition, find references and intelli-sense as well as the ability to compile a project into a dacpac and then deploy that dacpac to a database.
The SSDT way (and redgate sql compare way) to do a deloyment is to declare what you want so if you want to change a table that looks like:
to a table that looks like:
with SSDT you just change your table definition to the second one and let SSDT worry about how to upgrade it (can it do an alter table, add column or does the column order change so you will need to rebuild the table etc).
With Liquibase (DbUp, ReadyRoll, manual methods etc) what you do is in this case have to write the alter table yourself and make sure that you run the scripts in the correct order, consider this scenario:
If any of the releases are missed, none of the next ones can continue.
Benefits of upgrade scripts (Liquibase, DbUp, etc):
Benefits of compare / merge (SSDT, Redgate SQL Compare):
Drawbacks of upgrade scripts:
Drawbacks of using compare / merge:
Personally I really think SSDT is a professional development environment and it means that I can concentrate on writing useful code and tests rather than writing upgrade scripts which are in themselves just a means to an end.
You asked for opinions so there you go :)
ed