SQL Developer Bundle

Database development models

There are two common models for organizing database development:

• Dedicated
  Each developer has their own copy of the database
• Shared
  All developers work on a central copy of the database

This article briefly discusses each approach, their benefits and drawbacks, and makes recommendations for adopting the dedicated development model with SQL Source Control.

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.

Without source control, dedicated database development introduces a change management overhead, as changes from each database must be merged back into a central latest version.

The dedicated model, however, has several advantages. 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.

Shared development

A single copy of the database is created. All developers then make 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.

Shared development removes some of the change management overheads in environments without source control, but its inability to isolate individual developers' work is limiting.

Under a shared model, there is a greater risk of harmful changes occurring if developers modify the same object, or make changes that break referential integrity.

Comparing the development models

A dedicated database is fundamentally more desirable as it provides greater flexibility for each developer, letting them work without having an impact on the rest of the team.

Historically, however, implementing dedicated development databases has been challenging. Copies of the development database must be created for each developer. It must then be possible for each developer to access the "latest version" of the database, and the changes they make must be readily shared with other team members.

SQL Source Control seeks to address this issue by significantly simplifying the adoption of the dedicated development model.

Working with SQL Source Control

SQL Source Control supports both a dedicated and a shared database development model. However, SQL Source Control makes it simpler to set up the dedicated model, and will provide greater benefits to developers working in a dedicated development environment.

If you want to use SQL Source Control in a shared development environment, you may encounter some of the known issues discussed later in this article

Setting up SQL Source Control for a dedicated environment

The challenges of change sharing and ensuring a development team is working in sync, all on the latest version, are functionally eliminated by SQL Source Control. Changes can easily be committed and a database updated to the latest version from within SQL Server Management Studio.

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.

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.

Note that using a shared development model with SQL Source Control is not recommended.

You will also see better performance using SQL Source Control with a local dedicated database

Limitations of SQL Source Control in a shared development environment

There are a number of known issues that may arise if you use SQL Source Control under the shared model.

Committing another developer's changes

The Commit Changes tab in SQL Source Control shows a list of all objects with changes that have not yet been committed to source control.

Because all objects on the list are selected by default, and under a shared model the list shows changes from all developers, it is relatively easy to commit someone else's change. If they are in the middle of a complex set of changes, this could leave the source control version of the database in an inconsistent state, or cause errors.

If you are making changes to a single object, it is simple to avoid this issue.

To commit changes to only a single object, in the Object Explorer, right-click the object and select Commit changes to source control.

The To Commit tab is displayed with only that object selected.

Not seeing your changes

Just as it is possible to commit another developer's changes, so your changes could be committed by another developer.

If you were expecting to see a change listed on the Commit Changes tab, and it is not shown, it may have been committed by another developer.

If this occurs, you are recommended to check your source control logs or history

Spurious conflicts

Under a dedicated development model, legitimate conflicts occur when two developers each modify the same object but were not working on the latest version of the database. In this case, their changes can be incompatible, and the conflict must be resolved by choosing one version of the object.

This should not be able to occur when all developers are working on the same database. However, SQL Source Control maintains its own local working copy of the scripts folder that was committed to source control. This working copy is used in determining uncommitted changes. So if the working copy and the database get out of synch, it is possible to encounter spurious conflicts under a shared model.

The working copy updates when you visit or refresh the Commit Changes tab or the Get Latest tab. If you visit or refresh these tabs very infrequently, you may see spurious conflicts.

If you are working on a shared database, ignore any conflicts, and choose Keep mine

Undoing another developer's changes

SQL Source Control gives you the ability to undo database changes that have not been committed to source control. You can undo any uncommitted change, regardless of who made it.

When you undo a change to an object, that object returns to the last version you committed or got from source control. It is not undone to the previous source control version.

Therefore, if you undo another developer's change, the object could be left in a state that is out of date or significantly inconsistent with their expectations.

You are recommended not to undo changes you did not make.