Having a SQL Server database under source control is rapidly becoming the norm vs the exception in many software development teams. Using any development model (dedicated or shared), requires the team to establish a workflow and a set of rules. The dedicated model, though, allows a developer to act as an independent part of the process mainly in case the central server/repository is down. In this case, the team can continue to work unhindered. This article will focus on using the dedicated development model for SQL Server source control
Challenge
Implementing a model of database development in a way that each developer will have a separate copy of the database (working copy), and that all changes will be applied to the working copy (tested if needed), before committing changes to the central repository, is a key concern.
An issue that cannot be avoided, in this model, is when a conflict occurs when committing a change to the repository. Therefore, having an appropriate mechanism for detecting and resolving conflicts is crucial. Conflicts should be detected in case any team member does not work with the latest version of an object from the repository.
In case the central repository is down or inaccessible, the rest of the team can continue working locally. With the ability to synchronize local changes, once the repository is back online, continuity in the development process can be maintained
Requirements
Each developer must have SQL Server Management Studio installed on a local machine. Having a separate instance of SQL Server on each developer’s machine is recommended, as well.
The solution must include a mechanism for conflict resolution, ensuring that all developers are aware of changes made by the rest of the team.
In addition to this, the solution must ensure that the each team member can work independently, and cannot be affected by the rest of the team.
Solution
Implementing a dedicated model approach
ApexSQL Source Control is a SQL Server Management Studio add-in that offers a possibility to link a database to source control using the dedicated development model. This will allow developers to work independently on separate machines.
To link a database, right click on it from the Object Explorer and from the ApexSQL Source Control menu choose the Link database to source control option:
In the first step of the wizard, select source control system that will be used and make sure the Dedicated database option is selected:
In the Object filtering step, include objects that will be version controlled. By default, all supported datatypes and object instances are included in version control:
The last step is to specify the repository credentials and a path to the project where the database will be linked:
By clicking the Finish button, ApexSQL Source Control will initialize the linking process. All objects, previously included in the version controlling process will be scripted and prepared for the initial commit. In the same process (linking process), ApexSQL Source Control creates additional objects in the database linked to the repository, in order to track later changes.
Once the linking process is finished, the Action center tab will appear, showing the difference between the working copy and the current state of the repository. The first developer who links the database will need to include all objects and initially commit them to the repository:
The rest of the team can have an empty database on a local machine that will be linked to the same repository, and the latest version of objects will be applied against an empty database. In other words, the rest of the team will build the same database locally (create a working copy) using the latest version of objects committed to the repository by using ApexSQL Source Control:
From this point, the dedicated database development model is implemented as the whole team will have the same database synced with the repository and any developer can start working independently on the working copy from a local machine.
Conflicts
If a developer starts working on an object, of a different version than the latest version from the repository, a conflict will be detected as soon as a developer tries to commit changes. To prevent conflicts, it is necessary to check if the version of an object is the same in a working copy of a database as the latest version on the repository. Pulling the latest version of an object from the repository will avoid conflicts. In case of such a conflict, ApexSQL Source Control will detect it, and the Conflict resolution pane will be shown directly in the Action center tab:
ApexSQL Source Control does not allow any actions to be performed (in any direction) until the conflict is resolved. As a conflict resolution, a developer can choose to keep local changes, take remote, or to merge changes.
Keeping the local changes will overwrite the latest version on the repository with changes made against a working copy of a database.
Taking remote changes discards the local ones and applies the latest version from the repository against a working copy of a database.
By merging changes, all changes will be combined in a single section (below the Differences section), and a developer can resolve conflicts line by line, choosing either local or remote ones to be applied.
By clicking the Confirm button, a developer confirms that all conflicts are resolved and that the final change can be applied.
Independent work and working offline
If each developer is working on a separate instance of SQL Server (which is recommended), then if any SQL Server goes offline, no other developer is impeded.
If the central repository is not accessible for any reason, the Working offline button appears in the Action center tab:
Developers can continue to work on the working copy, since all changes will be saved locally on each machine, and as soon as the central repository becomes accessible, ApexSQL Source Control will compare and sync locally saved changes with the latest state on the repository. This applies only to Git and Mercurial source control systems. On the other hand, a developer can manually check if the repository is accessible by initiating the Go online option:
September 17, 2015
