Applies to
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 development 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 development 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 them 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. Additional information can be found in the Object status icons and visualization article.
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 change made by any of the developers, no matter if such change is committed or not. More about this can be found in the How to use the Change log feature in ApexSQL Source Control article.
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 source control conflict article for more information.
In the Shared model, the entire team is informed about any changes in a database and more importantly, about the intention of any developer to make changes. This way, developers can avoid overriding each other’s changes. This is covered through an option for each developer to check out an object before making changes (for this feature check the Checking out and checking in objects article, and more important these operations can be enforced by using the database policies explained in the Database development policies article.
Conflicts can happen very rarely 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. The conflict can happen when one repository is shared between databases, which is not the best practice.
Implementation differences
When using ApexSQL Source Control, the development model can be selected in the second step of the Source control setup window, right after the source control system is selected:
In the 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, scalar-valued function, 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, the 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 their 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.
More about these features can be found in the following articles:
- Labels
- Working with static data
- Object filtering
- Historical view
- Working folder
- Source controlling custom scripts
- How to utilize branching and merging with Git
FAQs
Q: Is it necessary to have a separate machine with SQL Server installed when using the dedicated development 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. Also, either model 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 development model?
A: This database is created with a default configuration. Any security settings are completely left to end-users.
Affected versions
The SQL inventory system explained in this article is implemented in ApexSQL Source Control 2021 version and higher. In the previous versions of ApexSQL Source Control, the scalar-valued function FrameworkVersion will not be created and corresponding permission is not needed.
From the ApexSQL Source Control 2021 version, the Alter any database DDL trigger and Alter any schema permissions are replaced with the Alter permission for databases linked in the shared development model for Azure SQL Database and Amazon RDS.
