This article covers the main features of ApexSQL tools that are involved with SQL source control. For the purpose of the article, we will use ApexSQL Source Control, ApexSQL Diff and ApexSQL Build.
The latest version for any of the mentioned tools can be downloaded from ApexSQL download section, and you can play along while reading the article.
Apex SQL Source Control
ApexSQL Source Control is a SQL Server Management Studio add-in that allows version controlling SQL database objects. It natively supports all major source control systems such as Git, Mercurial, Perforce, Subversion and Team Foundation Server (including Visual Studio Online) requiring no additional client tools to be installed
Source control development “models”
ApexSQL Source Control supports two database development models: Shared and Dedicated.
Dedicated model means that each developer is working on a local instance of a database. It allows developers to work independently and test changes without affecting the rest of the team. However, it requires a separate license of SQL Server for each developer. Developers have to be aware of conflicts that may occur in case more than one developer is working on the same object and want to commit it to the repository. We will explain conflicts later in this article.
Shared model means that all developers are working on the same database and that all changes are applied directly against a database, and after that committed to the repository. Conflicts cannot occur in shared model. However, a developer can override other’s change, so it is essential that the entire team is aware of all changes and all objects that are currently in use by someone else.
Initial database linking
To link a database, right-click on it in the Object Explorer pane and from the ApexSQL Source Control menu click the Link database to source control option:
Choose the source control system and database development model:
In case the shared model is selected, an additional option allows you to choose a host database for the framework objects that ApexSQL Source Control is using.
Select objects to be included in version control. By default all supported objects are included (Learn more):
Specify credentials for the repository, along with the repository path and particular project inside the repository if needed (Learn more):
After the linking is done, all objects included in the linking process will appear in the Action center tab. This is the main window used by ApexSQL Source Control in communication with the repository.
It shows the comparison results for each object, comparing its current state in the database with the latest version on the repository.
After the initial commit is performed, the Action center tab will show that the local database and the repository are synced. Each change made after the initial commit will appear upon refreshing the Action center tab, no matter which user made a change.
Check out an object
To check out an object, right click on it from the Object Explorer, and choose the Check out option from the ApexSQL Source Control menu:
Check out serves for information purposes, meaning that it can be overridden by other developer.
Lock an object
To prevent other developers from working on an object, it is necessary to use the Checkout and lock option. It is located in the ApexSQL Source Control menu:
By locking an object, the rest of the team will be prevented from making any changes, until an object is unlocked, or after the change made against an object is committed to the repository.
Each object status is followed by the appropriate icon in the Object Explorer pane, so the entire team is aware of objects that are checked out, locked, or edited:
Besides following the object status in the Object Explorer pane, the same information is available through the Object status form, where all object are listed along with the information about the status of an object and the user who performed an action:
Resolving conflicts in dedicated development model
Conflicts occur within the dedicated database development model in cases when developer A modified an object and committed changes to the repository while developer B was working on the same object version prior to the change. In this situation, when developer B tries to commit the change, a conflict occurs, since the version that developer B used was not the latest one from the repository. ApexSQL Source Control does not allow any action (push to the repository or pull to a database) until the conflict is resolved. For resolution, all changes from the database can be committed to the repository (keep local), the latest changes from the repository can be applied to a database (take remote), or each conflict can be resolved manually by using either the built-in line by line merging tool or one of the 3rd party merging tool (Learn more):
Using Labels/Tags
A Label or Tag represents the current state of the repository (snapshot of the repository or the check point) and it is often used when a stage of database development is finished/milestone is achieved, or a testing is finished. In this case, a label is created, so the database can be easily reverted to the previous state if needed.
To create a label using ApexSQL Source Control, right click the database in the Object Explorer and from the ApexSQL Source Control menu click the Create label option. For reviewing and applying specific labels, use the Show labels option from the same menu:
Applying a label with ApexSQL Source Control will initiate the dependency aware mechanism that will take the created label and create a script that will be applied against the database. The entire process is wrapped as a transaction which means that either all is applied or nothing. Before applying a label, a developer can explore the script, modify it or save it for later usage:
History
Through the history form, a developer will be able to explore each commit (change set) and to compare each version of an object committed to the repository with the current version from the database or any other version as well. Even more, the selected version can be retrieved from the repository and applied to the database with a single click (Learn more):
Compare/sync the repository with database
Using ApexSQL Diff, a SQL Server database schema comparison, and synchronization tool, a database can be compared with the repository.
ApexSQL Diff will compare specified sources, by comparing the latest version from the repository with the database, or it can be set to compare the database with the specific label.
Source control from the Destination section can be set in a way that the latest version on the repository is used for comparison, or any label specified:
Source control, as the destination, will by default be the latest version in the repository, unless a label is specified
Build a database from the repository
ApexSQL Build can be used to take the latest changes from the repository (or a specific label) and create a deployment and/or immediately create/update the target database.
To build a database directly from the source control repository, select the Build new database option:
Specify the mechanism of deployment:
Specify the repository that holds the database to be deployed:
Reference: Pinal Dave (https://blog.sqlauthority.com)
