Database build process management

Question

What options exists to manage database scripts and do a new development for database:

For example, the database used by a number of applications and there are a number of developers working with database, what will be the best options to maintain database up to date with the last changes and what should be the process of deployment changes to production

I see two options:

1. Microsoft visual studio has a database project, so all database scripts should be add in the project and database can be rebuild from visual studio
2. Restore database from backup and apply only new scripts to database

What another options exists? How can I manage database development, what is the best practices? what will be advantages and disadvantages of options I write above? How to maintain new sql scripts?

I understand then source control system should be used, but with DB scripts it's not so easy as with application.

I believe it will be no universal solution, but at least I am interesting in DB developers opinion how it's implemented in your company.

Answer 1

Liquibase is IMHO the best tool. It's brutally simple in its approach, which is one of the reasons it works so well.

You can read up on the site how it works, but basically it creates and manages a simple table that stores a hash of each script to determine if it has run a script yet or not. There's pre- and post- sql too, and you can bypass on conditions... it does pretty much everything you'd want or need. It also has maven integration, so it can seamlessly become part of your build.

I used it very successfully on a large (8 developers) project and now I wouldn't use anything else.

And it's free!

Answer 2

Currently we use SVN and have an "UpgradeScripts" folder where all developers commit their scripts to.

Each script has a generated prefix in the format upg_yyyymmddhhmmss_ScriptName.sql - So when they are deployed they run in a pre-defined order; keeping the database consistent.

This is generated through the below SQL and enforced through a pre commit hook:

select 'upg_' + convert(varchar, SYSUTCDATETIME(), 112) 
    + replace(convert(varchar, SYSUTCDATETIME(), 8), ':', '') 
    + '-'  
    + 'MeaningfulScriptName'

Another handy technique we use is making sure the difference between static and non-static data is clear; so in our database there is the standard "dbo" schema - which indicates non-static data which may change between environments, and a "static" schema. All tables in this schema have static id's, so developers know they can use them in enums and reference the id's in scripts.

If you are looking for something more formal, Red Gate have a utility called SQL Source Control.

Or you could look into using the Data Tier Application framework.