Source control is an essential aspect of database development, allowing developers to track and manage changes to their SQL Server databases. In this article, we will explore the main features of ApexSQL tools that are involved with SQL source control.
ApexSQL Source Control
ApexSQL Source Control is a SQL Server Management Studio add-in that enables version control for SQL database objects. It supports all major source control systems such as Git, Mercurial, Perforce, Subversion, and Team Foundation Server.
ApexSQL Source Control offers two database development models: Shared and Dedicated. In the Dedicated model, each developer works on a local instance of the database, allowing for independent work and testing. However, it requires a separate license of SQL Server for each developer. Conflicts may occur if multiple developers are working on the same object and want to commit it to the repository.
In the Shared model, all developers work on the same database, and changes are applied directly to the database before being committed to the repository. Conflicts cannot occur in this model, but developers need to be aware of changes made by others to avoid overriding them.
Linking a Database
To link a database to source control, simply right-click on it in the Object Explorer pane and select the “Link database to source control” option. Choose the source control system and the database development model. You can also select specific objects to be included in version control.
After the linking process is complete, all objects included will appear in the Action center tab. This tab serves as the main window for communication with the repository, showing comparison results for each object.
Checking Out and Locking Objects
To work on an object, you can check it out by right-clicking on it in the Object Explorer and selecting the “Check out” option. This serves for informational purposes and can be overridden by other developers.
If you want to prevent other developers from working on an object, you can use the “Checkout and lock” option. This locks the object and prevents any changes until it is unlocked or changes made against it are committed to the repository.
The status of each object is indicated by icons in the Object Explorer pane, allowing the entire team to be aware of checked out, locked, or edited objects.
Resolving Conflicts
In the Dedicated development model, conflicts may occur when one developer modifies an object and commits changes to the repository while another developer is working on the same object version. ApexSQL Source Control does not allow any action until the conflict is resolved.
Conflicts can be resolved by committing all changes from the database to the repository, applying the latest changes from the repository to the database, or manually resolving conflicts using the built-in line by line merging tool or a third-party merging tool.
Using Labels/Tags
Labels or tags represent snapshots of the repository and are often used to mark milestones or stages of database development. ApexSQL Source Control allows you to create labels and easily revert the database to a previous state if needed.
To create a label, right-click on the database in the Object Explorer and select the “Create label” option. Labels can be reviewed and applied using the “Show labels” option.
History and Comparison
The history form in ApexSQL Source Control allows developers to explore each commit and compare versions of objects committed to the repository with the current version in the database. Selected versions can be retrieved from the repository and applied to the database.
Additionally, ApexSQL Diff, a SQL Server database schema comparison and synchronization tool, can be used to compare the database with the repository. ApexSQL Build can then be used to deploy the latest changes from the repository and create/update the target database.
By utilizing ApexSQL tools for SQL Server source control, developers can effectively manage and track changes to their databases, ensuring collaboration and version control throughout the development process.