How is DB Version Control different from other tools?

It is not a secret that there are other tools on the market to version control SQL Server databases. But how is DB Version Control different?

Migration based vs State based tools

There are two approaches how to version databases - migration based uses change scripts (usually written manually) to change the database from one version to another.

The other approach is state based - schema objects and static data is scripted into files. During deployment, the target database is being compared with scripted files and the differences are applied.

Which approach is better?

Migration based approach offers the following advantages:

  • A developer has full control to specify what a migration script does
  • It is easy to create a fast, repeatable and automated deployment process

On the other hand:

  • Writing migration scripts manually may be time consuming and error-prone (in case of frequent/complex changes)
  • It is much harder to see the history of individual database object (e.g. a table or a view)

Flyway, Liquibase, DbUp, RoundhouseE and other similar tools use migration based approach.

State based approach is:

  • Easy to get started by choosing the schema objects/static data for change tracking through GUI
  • Does not require writing SQL migrations manually (most of the time)

However:

  • Deployment is a "black" box - difficult to see the automatically generated deployment script
  • Difficult to apply complex database changes (e.g. table rename or table split)
  • Usually requires expensive tools to be installed on the deployment server
  • The deployment takes longer (due to comparison being done during deployment)
  • Not possible to include both schema and static data changes into a single migration

Red Gate SQL Source Control, ApexSQL Source Control and couple other tools use state based approach.

How DB Version Control is different?

The tool combines both approaches to remove the limitations:

  • Allows schema objects/static data selection through GUI
  • Scripts the database objects into the files for change tracking
  • Automatically generates migration scripts (and allows to modify them or create new ones) including both schema and static data changes in a single migration
  • Only migration scripts are used for deployment - easy to set up and fast deployment, repeatable and automated process without expensive software needed

Interested? Let us know at hello@dbversioncontrol.com and we will set up a demo for you!

 

Leave a comment