How do you manage databases during development?

Question

My development team of four people has been facing this issue for some time now:

Sometimes we need to be working off the same set of data. So while we develop on our local computers, the dev database is connected to remotely.

However, sometimes we need to run operations on the db that will step on other developers' data, ie we break associations. For this a local db would be nice.

Is there a best practice for getting around this dilemma? Is there something like an "SCM for data" tool?

In a weird way, keeping a text file of SQL insert/delete/update queries in the git repo would be useful, but I think this could get very slow very quickly.

How do you guys deal with this?

Answer 1

You may find my question How Do You Build Your Database From Source Control useful.

Fundamentally, effective management of shared resources (like a database) is hard. It's hard because it requires balancing the needs of multiple people, including other developers, testers, project managers, etc.

Often, it's more effective to give individual developers their own sandboxed environment in which they can perform development and unit testing without affecting other developers or testers. This isn't a panacea though, because you now have to provide a mechanism to keep these multiple separate environments in sync with one another over time. You need to make sure that developers have a reasonable way of picking up each other changes (both data, schema, and code). This isn't necesarily easier. A good SCM practice can help, but it still requires a considerable level of cooperation and coordination to pull it off. Not only that, but providing each developer with their own copy of an entire environment can introduce costs for storage, and additional DBA resource to assist in the management and oversight of those environments.

Here are some ideas for you to consider:

1. Create a shared, public "environment whiteboard" (it could be electronic) where developers can easily see which environments are available and who is using them.
2. Identify an individual or group to own database resources. They are responsible for keeping track of environments, and helping resolve the conflicting needs of different groups (developers, testers, etc).
3. If time and budgets allow, consider creating sandbox environments for all of your developers.
4. If you don't already do so, consider separating developer "play areas", from your integration, testing, and acceptance testing environments.
5. Make sure you version control critical database objects - particularly those that change often like triggers, stored procedures, and views. You don't want to lose work if someone overwrites someone else's changes.

Answer 2

We use local developer databases and a single, master database for integration testing. We store creation scripts in SCM. One developer is responsible for updating the SQL scripts based on the "golden master" schema. A developer can make changes as necessary to their local database, populating as necessary from the data in the integration DB, using an import process, or generating data using a tool (Red Gate Data Generator, in our case). If necessary, developers wipe out their local copy and can refresh from the creation script and integration data as needed. Typically databases are only used for integration testing and we mock them out for unit tests so the amount of work keeping things synchronized is minimized.