How do you deal with multiple developers and database changes?

Question

I would like to know how you guys deal with development database changes in groups of 2 or more devs? Do you have a global db everyone access, maybe a local copy and manually apply script changes? It would be nice to see pros and cons that you've noticed for each approach and the number of devs in your team.

In one environment where I worked, we scripted all DB changes and used SVN to track script changes. But it was a pain, and it isn't foolproof since it can be bypassed (inadvertenly). There has to be a better way, so I will be interested in the answer to this. — Joel Lee, Mar 29 '11 at 18:24
@andesoj, i'm not sure it belongs there. This is more about development than DB Administration. — Jonas Stawski, Mar 29 '11 at 19:29

score 3 · Accepted Answer · edited May 23 '17 at 12:04

3

Start with "Evolutionary Database Design" by Martin Fowler. This sums it up nicely

There are have been other questions about DB development that may be useful too, for example Is RedGate SQL Source Control for me?

edited May 23 '17 at 12:04

Community

1
1

answered Mar 29 '11 at 18:24

gbn

422,506
82
585
676

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Our approach is that everyone has their own DB, the complete DB can be created from create scripts with base data if required. All the scripts required for this are in source control.

All scripts are CREATE scripts and they reflect the current state of the database schema. Upgrades are in separate SQL files which can upgrade existing DBs from a specific version to a newer one (run sequentially). After all the updates have been applied, the schema must be identical to what you would get from running the setup scripts.

We have some tools to do this (we use SQL Server and .NET):

Scripting is done with a tool which also applies a standard formatting so that the changes are well traceable with text diff tools (and by the SCM)
A runtime module takes care of comparing the existing DB objects, run updates if required, automatically apply "non-destructive" changes, then check the DB objects again to ensure a correct migration before committing the changes

The toolset is available as open-source project (licensed under LGPL), it's called the bsn ModuleStore (note that it is limited to SQL Server 2005/2008/Azure and to .NET for the runtime part).

score 0 · Answer 3 · answered Apr 05 '11 at 19:58

We use what was code named "Data Dude" - the database features in TFS and Visual Studio - to deal with this. When you "get latest" and bring in code that relies on a schema change, you also bring in the revised schemas, stored procedures etc. You rigght-click the database project and Deploy; that gets your local schema and sp in sync but doesn't overwrite your data. The job of working out the script to get you from your old schema to the new one falls to Visual Studio, not to you or your DBA. We also have "populate" scripts for things like lists of provinces and a deploy runs them for you.

So much better than the old way which always fell apart at high stress times, with people checking in code then going home and nobody knowing what columns to add to make the code work etc.

How do you deal with multiple developers and database changes?

3 Answers3