Differences between the dedicated and shared development models

Applies to
ApexSQL Source Control

Summary
This article provides a general explanation about dedicated and shared development models including implementation differences with ApexSQL Source Control.

Description

Development environment/topography

The Dedicated model means that each developer has a separate, local copy of a database(s) on a local machine. Find out more about this in the article: How to implement the dedicated model

The Shared model means that all developers are working on the same database(s). Find out more about this in How to implement the Shared model.

The Dedicated model allows developers to work independently on local sandboxes, and to push changes to a central repository that the entire team can access. This leaves an option for each developer to make changes on a local copy of a database as much as needed and test those changes locally, before pushing to a remote repository.

When working in the Dedicated model, changes are applied against a local copy of a database first (affecting single developer only) and after that, pushed to source control to be available for the rest of the team.

Making changes

The Shared model allows developers to make changes on a shared database and have the entire team be informed about which changes are made. This is covered using Object status icons in the Object Explorer pane. When working in the Shared model, changes are applied against a database first (affecting all developers) and after that, pushed to source control.

In the Dedicated model, developers can choose which changes will be applied from source control against a local copy of a database.

In the Shared model, all developers will be affected by changes made by anyone from the team. For this purpose, besides the Object status feature, the Logging feature is used to log each and every change made by any of the developers, no matter if such change is committed or not.

Conflict management

It is essential that before making any changes on an object in the Dedicated model, a developer ensures that a version of an object that will be changed is the most recent one (the latest version) from source control. Otherwise, a conflict may appear. Check the How to resolve conflicts article for more information.

In the Shared model, the entire team is informed about any changes in a database and more important, about the intention of any developer to make changes. This way, developers can avoid overriding each other’s changes. This is covered though an option for each developer to check out an object before making changes, and more important these operations can be enforced by using the Database policies feature

Conflicts cannot happen in the Shared model as the entire team has access to the same database and all objects as well as the same repository with the latest version of each versioned object.

Unlike conflicts in the Dedicated model, changes in the Shared model made by one developer can be overridden with changes made by other developer.

Implementation differences

When using ApexSQL Source Control, development model can be selected in the second step of the Source control wizard, right after the source control system is selected:

In case of choosing the Dedicated model, additional objects will be created in each database that is linked to source control. Once a database is linked, one system table and a database DDL trigger will be created:

When linking a database using the Shared model on a SQL Server instance where there are no databases already linked, ApexSQL Source Control will need an additional database to act as the repository. By default, a name of the repository database is ApexSQL, but this can be changed by giving any name you like, or by picking any of the existing databases from the drop-down list. The first user will need the control server permission against a SQL Server instance in order to link the first database from the selected SQL Server instance:

When linking another database from a separate machine while connecting to the same SQL Server instance where at least one database is already linked, the existing ApexSQL Source Control database e.g. ApexSQL will be used as well as for any other database from the same SQL Server instance that is linked using the Shared model. This means that each SQL Server will have one database for this purpose. Also, other developers will need a lower level of privileges for the linking process compared to the first developer who linked a database:

When a database is linked using the Shared model, an additional database is created contains five tables and two stored procedures. In addition to this, one DDL trigger will be created on a SQL Server level:

For more details about these objects and its purpose, check the ApexSQL Source Control framework objects inventory article.

ApexSQL Source Control features that do not depend on the selected development model are, labels, static data, object filtering, revision history, working folder functionality, custom scripts, branching. These features are implemented in the same way for both development models.

Q: Is it necessary to have a separate machine with SQL Server installed when using the dedicated model?

A: No, a separate machine is not required but only a SQL Server instance. However, it is recommended to have a separate machine in order to work independently and unaffected in case any machine is down for some reason.

Q: Is it possible to use both models with each of the supported source control systems?

A: Yes, both development models are available regardless of the selected source control system. In addition, either models can be used with the Working folder functionality.

Q: Is it possible to have the same database linked to source control by multiple users using both development models at the same time?

A: No, if a database is linked using the dedicated model it cannot be linked using the shared model and vice-versa.

Q: Is there any security mechanism for the additional database created by ApexSQL Source Control in the Shared model?

A: This database is created with default configuration. Any security settings are completely left to end users.