There are two common ways for teams to develop databases:
Each developer works on their own copy of the database
Developers work on a single database
Some development teams use variations or combinations of these models. For example, a large team might use more than one shared development database.
Each developer works on their own copy of the database. The copy can be local or on a central server, but local copies provide better performance.
This approach mirrors application development. Each developer makes changes independently, tests them, and commits them to source control once they're confident in the changes. After the changes are committed, other developers can apply them to their copy of the database.
Because each developer is working in an isolated environment, there's no risk of accidentally overwriting someone else's changes. You're free to develop complex changes that may cause other parts of the database or the application to break. If something does break, there's no impact on other developers.
Like shared databases, dedicated databases don't contain any table data (unless you've source-controlled your static data). This means, for example, that external consultants with their own dedicated database copy won't have access to sensitive table data.
Each developer works on a single shared copy of the database. This is usually created from a backup of the production database. When a milestone is reached, a deployment script is created to migrate the final state of the database to a test database.
Because the changes are made directly to the database itself, you don't need to use the Get latest tab. There are no changes to share or get, and the database always has the most recent changes.
However, there's no safe way to test changes in isolation, and developers may overwrite each others' changes by mistake. Additionally, unless you create a new database to log changes, information about who made changes is eventually lost.
Working with SQL Source Control
When you link a database to source control, you specify whether the database is shared or dedicated. This lets SQL Source Control better support your development model.
SQL Source Control works best with the dedicated development model, because it gives you the most control over which changes to get. However, SQL Source Control is compatible with both models.
Setting up SQL Source Control for a dedicated development
- One developer links their copy of the database to a location in source control for the first time.
- The developer commits the objects.
- The other developers link 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.
- Each developer gets the latest version of the database.
Setting up SQL Source Control for shared development
- One developer links the shared database to a location in source control for the first time.
- The developer commits the objects.
- In SQL Server Management Studio, the other developers connect to the database and link to source control.
For more detailed instructions, see Linking a database to source control.
Switching to dedicated development
If you're using the shared development model, it's simple to switch to the dedicated model. To do this, each developer on your team needs to:
- Unlink the database from source control.
Create a new empty database. This will be each developer's dedicated copy.
- Link the new database to the source control repository that the shared database was linked to.
- Go to the Get latest tab and get the changes.
The new database is updated with the changes from the original database, creating a dedicated copy.