10

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.

16

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:

create table a(id int)

to a table that looks like:

create table a(id int, another_column varchar(12))

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:

  1. Release 1 - create column hello on table
  2. Release 2 - rename column hello to joe_blogs
  3. Release 3 - rename column joe_blogs to hello
  4. Release 4 - create column joe_blogs

If any of the releases are missed, none of the next ones can continue.

Benefits of upgrade scripts (Liquibase, DbUp, etc):

  • You have complete control over the scripts
  • DBA's / Developers are used to this

Benefits of compare / merge (SSDT, Redgate SQL Compare):

  • Do not have to write upgrade scripts
  • It is easy to get to any specific version just compare and merge that version

Drawbacks of upgrade scripts:

  • Must be run in order
  • Rely on humans no making mistakes
  • Can be slow especially if you have a lot of changes
  • Unless your team is very disciplined databases in different environments (dev, test, staging, prod etc) often become out of sync making any testing invalid
  • Downgrading a release means writing the reverse of all the scripts you have already written

Drawbacks of using compare / merge:

  • Tools are not 100% trusted, perhaps unfairly
  • SSDT requires a working project, many many databases have code that doesn't actually compile or run (think dropped tables but not procedures etc), I have seen this in about 8/10 databases I have inherited :)
  • Many DBA's / developers are hesitant to give up developing in SSMS / notepad

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

  • 1
    Does SSDT work with anything else than SQL Server? e.g. Postgres? – a_horse_with_no_name Nov 20 '15 at 14:12
  • 3
    No "SQL server data tools" :) – Ed Elliott Nov 20 '15 at 14:29
  • 1
    Why not? SSIS package can transfer mostly all ODBC sources – a_vlad Nov 20 '15 at 15:29
  • Unless I misunderstood I think we're talking about managing the database schema rather than creating ssis packages? Happy to be corrected :) – Ed Elliott Nov 20 '15 at 16:13
  • 1
    SSIS is intended to move data and as such supports connections to a large variety of systems. SSDT is intended for the development and maintenance of SQL Server database projects. It has a build process that checks your targeted SQL Server version for compatibility of scripts and checks all references, procs, etc. for T-SQL syntax. – Dave Nov 20 '15 at 16:34
2

I just top-up prevision answer.

The biggest difference described on Flyway web-site on central place:

Solves only one problem and solves it well. Flyway migrates your database, so you don't have to worry about it anymore.

Visual Studio + SSDT + SSIS = full power ETL Tool, with only one real drawback - it only work under windows It need Windows + SQL Server for run packages, but work with mostly all sources.

For transfer/migrate data - a lot of products on the market. Commercial, Open Sources, Community/Express and etc

For migrate code - all not so good. Even if software promise "convert triggers, procedures and functions without problems", in fact - only simple, most code migration - manual.

2

I have worked with both Sql server data tools and flyway. Using SSDT, I get the following advantages:

  1. I can compile the database project.. meaning, there is no fear of dropping a column that is being referenced by a view, function or stored procs. This is a great feature, because in the past, we found about such misses only after the release
  2. After successful build, SSDT generates what is known as a "DACPAC". Think of this a msi with a version.

  3. A given dacpac, with say version 5, can be applied to a database which is on Dacpac version 1,2,3,4 or 6,7,8 etc. If its applied to 1-4, the database will be upgraded. If applied to 6,7 etc., the DB will be downgraded/rolled back. There will be warnings, if there will be data loss, which can be suppressed. So, we get a great rollback feature, which is not available with other tools like flyway etc. With flyway, one has to provide a new set of scripts for rolling back.

  4. DACPAC applies all the changes in one transaction; meaning if there 5 table changes in the upgrade and one of them fails, the entire transaction is rolled back. Flyway supports this also, but for every file.

However, SSDT and DACPACs are Microsoft SQL Server specific; flyway can be used for a variety of databases.

So, bottom line, if you are using only SQL Server, it should be a fairly an easy decision to go with SSDT and DACPACs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.