Challenge
Database development in a multiuser environment has its challenges on a daily level even without including the database source control. But when the database source control is included, based on the chosen source control system, those challenges can multiply by the number of database developers working on the same database.
Every database developer has its style of writing objects scripts, adding comments for part of the created object scripts, even object script formatting.
One of the many concerns that can be raised in the source controlled environment is how to define database changes properly. Many of the database changes can be recognized as a false positive change, e.g. case changes in the database object names. This type of change, chosen to be committed to source control, will cause the changeset to be larger than it is and will require more time for searching the right version of the object through the history of the committed changesets.
Native solutions for source control systems often provide some type of freedom for define database changes, but in the end, all changes made against objects no matter how small and insignificant are (like additional spaces, case differences in names, etc) will be recognized as a difference when the database and source control scripts of an object are compared.
More advanced source control users know how to exclude specific objects or even a part of the object script from the comparison process, but relative new source control users will find this hard and time-consuming. Even when source control users learned how to do that, for every newly created object the whole process needs to be repeated.
Using any of the third-party tools for define database changes will speed up the process, but still what will be defined as a database change will be done against an object, not the whole database. One more problem is how to set what will be defined as a database change across the multiuser environment.
The main problem that needs to be solved is how to create the usability for define database changes, to all users, new and advanced, to do the same quality work that will easy database development so only ‘’real’’ database changes are committed to source control.
Using ApexSQL Source Control database developers regardless of the source control knowledge will have the ability to specify what will be defined as a database change and what needs to be shared with other database developers by committing it to source control. Here will be explained the solution for define database changes using the dedicated development model and Git repository hosted on Azure DevOps Services.
Solution
ApexSQL Source Control provides a solution to define database changes using the Script options feature for the following natively supported source control systems:
- Note: No matter what source control system is chosen, the defined database changes process is the same
Environment settings/Multiuser environment
To start working with the Script options feature in ApexSQL Source Control, the link process for a database to source control must be started. Click on the Link a database to source control right-click Object Explorer context menu command to open the Source control setup window:
When the Source control setup window is opened, choose the Native option and from the Source control system drop-down list specify the wanted source control system:
- Note: Be sure to choose the correct source control repository, no matter where is hosted. To use the Git repository hosted on Azure DevOps, from the Source control system drop-down list Git must be chosen
In the Development model tab are present two options, the Dedicated and Shared options:
- Note: There are no differences in the workflow of the Script options, regardless of the chosen development model
In this solution, the Git source control system will be chosen, although is hosted on AzureDevOps Services and the dedicated development model.
More about the Object filtering tab can be found in the Object filtering article.
The next tab in the linking process is the Script options tab:
Under the Script options tab, all available options for define database changes are divided into two parts, the Ignore comparison options, and Synchronization options. This solution will include only Ignore comparison script options.
More about each listed option can be found in the Script comparison and synchronization options in ApexSQL Source Control article.
Checking/unchecking any of the listed script options will help to define database changes. By default, some of the listed script options are already checked (Case, Column order, etc). The checked Ignore comparison options will be applied to the comparison process between the linked database and source control after the initial commit is done.
- Note: The Synchronization options will be applied only in applying changes from source control against a linked database process
When linking a database to an empty source control repository, the best practice is to leave the default options set. But if a database is being linked to a source control repository that has previously committed changesets, the best practice is to set right away the Ignore script options set. This way, when the Action center tab is opened only the ’’real’’ differences between the linked database and source control repository will be shown.
Since in this solution a database will be linked to an empty source control repository, everything in the Script options tab will be left by default.
More information about the linking process is available in the How to link a SQL Server database to a source control system article.
After a database is linked in the dedicated development model to the source control repository and the initial commit is performed, every pre-requires is completed and the focus can be on how to define database changes.
Define database changes
A simple change in one of the database stored procedures, like adding an inline comment, will be recognized as a difference between the linked database and source control repository, and in the Action center tab the message will be changed, notifying the user who made the change about the changed status:
Click on the message will refresh the Action center tab and show the changed procedure with its script where the made change will be highlighted:
Every change, no matter how insignificant, made against a database will be recognized in the Action center tab as a difference between the linked database and source control repository. For this solution changes made against the objects (either in the database or on source control repository) are:
-
Adding new empty lines after every written SQL line
-
Changing column order in a table
-
Adding a new inline comment
-
Changing cases in the name of the database object
Every of above-mentioned change will be followed by the object status change in the Object Explorer panel, from ’’link’’ to ’’edited’’ status:
Based on the formatting style of a database developer, every of the above-listed type of change will be recognized as a schema change and shown in the Action center tab as a difference between the linked database and source control repository, but is not the type of change that can affect the execution of the object’s script and committing those changes to source control will only create one more changeset nothing more.
To avoid that, go to ApexSQL main menu in SQL Server Management Studio – ApexSQL Source Control menu and choose the Options command:
In the Script options tab, choose the SQL Server connected in the Object Explorer panel where the linked database is stored, and choose that database:
On the list of available Ignore comparison script options will be options for the above-mentioned changes which allows those types of changes to be ignored during the comparison process between the linked database and source control repository. To define database changes, and set those types of changes not to be recognized as a difference, check the following options:
-
From the Attributes for script-based objects section – the Comments and White space options:
-
From the General attributes section – the Case in names option:
-
From the Table attributes section – the Column order option:
Saving these options set by click on the OK button in the Options window, and refreshing the Action center tab will lead to no differences between the linked database and source control repository at all since all made changes will now be ignored. In the Action center tab will be shown the following message:
But all changed database objects will still have the edited status in the Object Explorer panel. This way, every database developer can define database changes and choose when they will be recognized as a difference between the linked database and source control repository, and choose when those changes should be committed to the source control repository for the other database developers to be used.
- Note: If any other change that is not set to be ignored is made against already changed objects, those changes will be listed in the Action center tab
The process is the same for every of the Ignore comparison option and can be applied against any linked database, regardless of the chosen development model, source control system, or the number of database developers.
Sharing Ignore comparison script options set
After the script options are set and the Save as my defaults button is clicked, the Ignore comparison options set will be saved in the MyDefaults file on this location by default C:\Users\<user_name>\AppData\Local\ApexSQL\ApexSQL Source Control
This options set can be applied to any linked database, by click on the My defaults button in the same tab after the SQL Server and any other linked database is chosen. To share this saved options set among all users on the same linked database, copy and send the MyDefaults file to every user to paste it (overwrite the existing one) on the same location on its machines. Every user should open the Options window, and under the Script options tab chooses the desired linked database, click the My defaults button, and the OK button to apply the saved script options set.
More about the saving options set in ApexSQL Source Control can be found in the Default option settings in ApexSQL products article.
Define database changes like this, all database developers will have the same script options ignored and there will not be ’’false’’ changes or changesets committed to the source control repository.
February 19, 2021
