Notes & articles

Database development models

There are two common models for organizing database development:

• Shared

  All developers work on a central copy of the database

• Dedicated

  Each developer has their own copy of the database

Both models are supported by SQL Source Control.

Choosing the right development model depends very much on the needs of your project and team, and some development teams may employ variations or combinations of these models. For example, a large team may use more than one shared development database.

This article briefly discusses each basic approach, and how to use either development model with SQL Source Control.

Shared development

A copy of the database is created. Typically, this is done by restoring a backup of the production database. All developers then make their changes to this database. When a milestone is reached, a deployment script is created to migrate the final state of the database to a testing environment.

Under the shared model, a developer's changes are shared with all other users automatically, through the shared database itself. This simplifies change sharing, meaning that developers are always working on the latest version.

Shared development does not provide the sandbox offered by a dedicated environment. But it does remove some change management overheads. It is also simpler to set up than a dedicated environment, particularly for large teams, or teams working with large databases where creating multiple copies is impractical. Used with SQL Source Control, shared development provides increased visibility if multiple users are working on the same objects. Combined with the fact that all developers are always working on the latest version, this reduces the risk of conflicts.

Dedicated development

Each developer works on their own copy of the database. This copy can be local, or on a central server, although you will see better performance with local copies.

This approach mirrors application development. Each team member gets their own working copy of the database, makes changes independently, tests these changes, and then commits them back to source control once they are confident in their changes.

Committing back to source control enables changes to be shared as each developer gets the latest version. However, where developers do not frequently get the latest version, there is a heightened risk of conflicts.

Because each developer is working in an isolated environment, you have a safe sandbox. There is no risk of overwriting someone else's changes or anyone else overwriting your changes. You are also free to develop complex changes that may cause other parts of the database or the application to break. If something does break, there is no impact on other developers.

Working with SQL Source Control

SQL Source Control supports both a dedicated and a shared database development model.

When you link a database to source control, you specify whether the database will be shared or dedicated. This allows SQL Source Control to better support your development model. For example, under a shared model, SQL Source Control displays information about which SQL Server user made which change. This means you can avoid committing another user's changes, and more easily see who is working on what.

Setting up SQL Source Control for a shared environment

To set up SQL Source Control for use in a shared development environment:

1. One team member links the shared database to a location in source control for the first time.
2. They commit the objects.
3. In SQL Server Management Studio each other developer now connects to the database and links to source control.

For more detailed setup instructions, see Linking a database to source control

Setting up SQL Source Control for a dedicated environment

To set up SQL Source Control for use in a dedicated development environment:

1. One team member links their copy of the database to a location in source control for the first time.
2. They commit the objects.
3. Each other team member now links their own copy of the database to that source control location.

   Alternatively, they can create a new blank database and link it to the database in source control, or restore a backup of the development database. Restoring a backup has the advantage of including any necessary data.

4. They each get the latest version of the database.

For more detailed setup instructions, see Worked example: setting up SQL Source Control for development teams