Tony Davis

28 September 2018

• SQL Source Control
• Database Source Control

Merging a Conflict with SQL Source Control and Beyond Compare

SQL Source Control (SoC) plugs directly into SQL Server Management Studio (SSMS) and is built with the singular purpose of providing an efficient interface between each developer’s local, working copy of the database, in SSMS, and the source control repository.

It allows each developer to work freely, on their own sandbox database, committing tested changes frequently to their local, and then to the shared team repository, directly from within SSMS, as well as updating their own work frequently with the changes of others.

What happens, however, if one database developer changes the name of a column, while another team member updates its data type? Or two developers accidentally work on the same stored procedure, making conflicting changes? The source control system will alert the team to the conflict and allow them to decide the correct outcome, via a merge operation.

When conflicting changes need to be resolved, during merge operations, a good strategy would be to switch to a tool whose dedicated purpose is to handle merging operations, such as Beyond Compare, or KDiff3, both of which integrate directly with SoC.

This article describes a simple example of what it’s like to merge a conflict in SQL Source Control. You will need to have installed Beyond Compare, one of the tools which integrates with SQL Source Control to resolve conflicts.

Handling Merge operations

When developers working on the same branch make conflicting changes to the same version of a file, in the shared repository, one will be obliged to perform a merge to resolve it. Similarly, developers working in a branch will need to merge their changes into mainline, regularly, and deal with any conflicts that arise. To minimize these conflicts, and any disruption to the work of others, they should regularly merge changes in the opposite direction, from trunk-to-branch, so that the two don’t drift too far apart.

A merge in either direction could be a complex process, especially if conflicts arise across many project files. Good team communication, and good database development practices, with sensible use of schemas, will help minimize the number and complexity of merge conflicts, but they won’t eradicate them completely.

Setting up SoC and Git

Imagine we have two developers, each working on a local copy of the same demo_db database. Each developer has used SoC to link his and her local copy of the database to a local Git repository. Each local repo has the same origin, i.e. the team’s shared repo, hosted in this example on Azure DevOps (previously Visual Studio Team Services). The setup is very similar to that described by Sjors Takes in his article, How to version control a database using GitHub and SQL Source Control, and I’ll refer there for further details.

Figure 1

Both developers have pulled from the shared repo, so all three repos are currently at the same revision (let’s say R5). Likewise, each has updated their local sandbox databases with any R5 changes.

Resolving merge conflicts outside SoC

In a distributed version control system (VCS), each user has a local repository and can commit locally and then “pull” the latest changes from a remote repo, and merge as necessary. The merge operation is safer in that each developer always has their local, committed changes to fall back on, in case the merge fails. The merge operation involves a local commit and a remote commit, and we need to create a third commit that combines the two. We commit the merged version locally and finally push the merged version to the remote repository. If the merge fails, we can simply try it again.

SoC does not support this model, unfortunately. Let’s imagine both developers start creating, or modifying, a stored procedure, dbo.Counting, locally, as if there was a misunderstanding as to who would be writing the new code.

Dev1 finishes work on the Counting stored procedure, in his local database, and is ready to share his changes. He commits the changes locally, then pulls from the remote, to update his local repo with any changes made in the meantime. Assuming there are no issues, he pushes his changes to the remote repo. Both his local and the remote repo are now at R6.

Dev2 has now also finished her work on the Counting procedure, in her local sandbox. Again, she commits, moving her local repo to R6, and then pulls from remote. At this point, in SoC, she will receive an error.

Figure 2

The problem is that there are now essentially two conflicting versions of R6; Dev2’s local R6, and R6 in the shared remote. SoC cannot handle this situation and invites Dev2 to resolve the conflict in an external Git tool.

Dev2 can open her tool of choice, such as Beyond Compare, KDiff3, or perhaps gmaster. She will need to pull the latest changes from the shared repo, which include the committed change that conflicts with a change she committed locally, and the pull operation will require her to merge her local commit and the remote commit, to create a third commit (R7) that combines the two. She can then commit the merged version locally, and finally push the merged version to the shared repository.

Resolving merge conflicts in SoC

It is worth reiterating that SoC is neither intended nor designed for handling branching and merging; there are plenty of dedicated tools for that. While it’s possible to resolve simple merge conflicts within SoC, using Beyond Compare, the integration is very basic, and only intended to allow you the convenience of handling very simple merges from directly within SSMS.

It tends to work better for centralized source control systems (such as SVN), rather than distributed systems, such as Git, and the merging process works more on the centralized model, even when using Git.

For it to work at all, developers must adopt the workflow of pulling from the shared remote before committing to their local repo any changes they have made, and tested, in their sandbox database.

Let’s return to our previous starting point, where both developers have pulled from the shared repo, so all three repos are at R5. Dev1 finishes work on the Counting stored procedure, in his local database, but before committing locally, he uses Pull from remote repository, within SoC, to update his local repo with any changes made by others, since he last pulled.

We’ll assume that no changes were committed to the remote in the meantime. This done, he commits the changes locally, and then uses Push to push them to the remote repo. Both his local and the remote repo are now at R6, and contain a Counting procedure that counts to 7(say).

Dev2 has now also finished her work on the Counting procedure, in her local sandbox. Her version counts to 8. Again, before committing the new code locally, she uses Pull from remote repository to get any changes. Using this process, SoC detects a conflict, as shown in Figure 3.

Figure 3

Behind the scenes, SoC has immediately pulled R6 from the remote and updated her local repo to this version (from R5), and so now contains a Counting stored procedure that counts to 7. She now needs to resolve the conflict between this version, and the version in her local sandbox database, which counts to 8.

The downside of this is, of course, that her local sandbox changes are not safely committed locally, so it won’t be so easy to revert to that state, should there be a problem with the merge operation.

If she decides to simply accept the revision in the remote repo, she can select Take theirs. The Apply changes button will activate and she can apply that version directly to her local database. Her local database and repo will now have a procedure that counts to 7, and her previous changes are lost.

If she decides to keep her version, she can select Keep mine, and then switch to the Commit tab to commit her new version to her local repo (creating R7), when she can then push to the remote.

However, in this example, let’s say that Dev2, after discussion with Dev1, decides on a new version of the code that counts to 9. She clicks the Merge… button, and SoC launches Beyond Compare to help her resolve the conflict and create the new merged file.

Figure 4 shows merge conflict in Beyond Compare.

Figure 4

The left panel shows the conflicting revision in her local database, and the right panel the revision now in her local repo (which matches what’s in the remote repo, i.e. they are both at R6). Dev2 uses the bottom panel to create a new version of the code that counts to 9 and hits the Save file icon to save the Merged file.

Figure 5

It’s important to know that when you click the Save, in the merge tool, the change is applied to the local database you are working in, right away – that’s why it appears in the left pane in the demo when we close out Beyond Compare and return to SSMS, as shown in Figure 6.

Figure 6

Notice that Keep mine is automatically selected, because the merged version is different from the version in the her local, and the remote, repo.

The stored procedure in her local sandbox will now count to 9, and she needs to commit that change to her local repo, so she switches to the Commit tab, and commits the merged file.

Figure 7

Dev2 can do a final review and decide if she wants to push the merged code to the shared repository.

Figure 8

This creates the new revision in the remote rep, which Dev2 can then pull to his local, repo and then update his sandbox database using Get latest, and then Apply Changes. Figure 9 shows the new revision in the remote repo (on Azure DevOps, in this example). Conflict resolved!

Figure 9

Conclusions

The ease with the team can coordinate source control operations, including the handling of branching and merging, will have a big influence on the speed and efficiency of the development process.

SoC provides a great interface in SSMS, to the local and remote repositories, allowing developers to work on their own local database copy, and commit changes frequently. With good practices, team members can work on a database in parallel, without fear of causing disruption to each other. However, when merge conflicts arise, it’s best to resolve them using a merge tool that it built for that task.

I like the flexibility that SoC’s integration with the Beyond Compare merge tool allows. It means I can to take components from both sides of the conflict, and even add additional code. However, given that we lose the safety net of being able to commit locally first, and then merge, I’d still recommend doing most merge operations outside of SoC, using a dedicated tool.

Want to see more demos of how to work with SQL Source Control? Check out the SQL Source Control course in Redgate University.