In a multi-user database-development environment, avoiding conflicts and overwrites with edits, and ensuring all changes are audited and recorded is important. Until recently however, effective tools for SQL development management have lagged well behind their client developer equivalents, like Visual Studio. In this article, we will look on specific database source control challenges and a way to address them use new SQL developer tools that make meeting these straightforward and easy
Challenge
A developer begins working on making changes to an object, but nobody else in the team is made aware. Another developer then also begins to change this object and conflicts arise. This is the fundamental challenge of any source control enabled environment
Requirement
Avoid conflicts by providing visual cues for an object’s editable status to allow developers to opt into a non-enforced source control enabled environment
Solution
ApexSQL Source Control provides a range of visual representations for the current object status in SQL Server Management Studio’s Object Explorer pane, to facilitate this level of developer interaction:
For example, if an object is checked out, your teammates will see this symbol superimposed on the object in SSMS 
When an object is checked out the object icon in the Object Explorer pane changes
You see:
The rest of the team will be informed, as the icon of the checked out object will be automatically changed in Object Explorer pane
Others see:
When a developer executes the script that modifies an object, the icon will change to another state
You see:
The rest of the team will be notified that the modifications are applied to the checked out object
Others see:
Just by showing these visual cues, team members can, in most cases, work harmoniously without causing conflicts in objects or between each other
Challenge
Sometimes a gentleman’s agreement or unenforced rules are not enough to prevent conflicts, overwrites etc.
Even with the visual clues, developers can still attempt to check out previously checked out objects. A developer will be informed that the object is checked out already, by someone else, but it could be checked out again by clicking the Yes button in the following dialog:
Source control is only as good as the people using it. However, if people forget or just ignore requirements to check out objects first, or do not respect the previously checked out status of other object, then overwrites and conflicts can occur. In this situation, locking down the environment, a bit more, to mandate explicit check out may help
Requirement
Requiring users always to always check out an object before editing
Solution
ApexSQL Source Control provides a Permissive policy, to set the database development for the source control shared database in a way that each object must be checked out before edit. When the object is checked out, the rest of the team will also be aware that someone is working on an object, and additionally be prevented from checking out the object at all:
This will ensure that each object needs to be checked out first, so if a developer tries to edit any object, a SQL Server Management Studio warning appears, with an informative message that an object needs to be checked out before editing:
Challenge
In some cases, there is a need to prevent developers totally, to modify a specific object, or a group of dependent objects, when a single developer works on them.
Requirement
Require the objected be locked before allowing edits
Solution
ApexSQL Source Control offers a Restrictive policy option, to set the source control shared database development in a way to require a lock on an object before editing. When the object is locked, the rest of the team will not be able to work on the same object until either an object is unlocked by a developer who locked it:
With this policy enabled, if a developer tries to modify an object that is not locked, a popup message appears:
To lock the object, right click on it in the Object Explorer pane, and select the Check out and lock option from the ApexSQL Source Control context menu:
The icon in the Object Explorer pane changes
You see:
The rest of the team will be notified about the object locking, as the corresponding icon will change
Others see:
When a developer executes the script that modifies an object, the icon will change to another state
You see:
Unlike the Check out option, where each developer can check out an object, no matter if it is already checked out by someone else, the Check out and Lock option will not allow that. The developer who locks an object is the only one who can “unlock” it, by applying changes to source control repository (if it is modified), or by applying the Undo Check out/lock option from the ApexSQL Source Control menu in Object Explorer pane:
Challenge
Once source control has been implemented it is important to have oversight of all changes, in a way to know who modified an object, when, and which is the exact object that is modified
Requirement
Seeing a full history of changes
Solution
ApexSQL Source Control provides a Logging feature to enable logging for changes made in the source control shared database development:
With this policy enabled, each modification performed will be logged, so the rest of the team can easily check who, when, and which is the exact object that is modified. Learn more
April 22, 2015
