How SQL Source Control works behind the scenes
Published 16 July 2015
SQL Source Control uses the SQL Compare engine to create and maintain folders of object creation scripts, called scripts folders, that represent your database schema. When you make changes to your database, you can commit them to source control. Likewise, when there are new changes to get from source control, you can apply them to your database.
However, SQL Source Control doesn't just run comparisons between the database and the scripts folder in your source control system. This wouldn't capture all the information needed to track database changes. For example:
- Amanda links a blank database to a blank repository in her source control system. As part of the process, SQL Source Control creates a scripts folder in her repository, representing the (blank) database schema.
- Amanda creates a table in her database and goes to the Commit tab.
If we compare Amanda's database and the scripts folder in source control, we can see a table in Amanda's database that isn't in source control. We don't have any information about where the difference came from, so we can't tell whether we should commit that table to source control or drop it from Amanda's database.
To solve this problem, SQL Source Control keeps track of three separate versions of the database:
- The database itself, the current state of the database in SQL Server.
- The transient, a local copy of the latest revision in source control. SQL Source Control periodically checks your source control system and updates the transient so it always reflects the latest version available (the head revision). The transient is stored in your local application data.
- The working base, a local copy of the database at the last time you ran a commit or a get latest. Like the transient, it's stored in your local application data.
The three-way comparison
When you visit the Commit tab or the Get latest tab, or click Refresh, SQL Source Control runs three comparisons using the SQL Compare engine. It uses the results of these three comparisons to create the list of changes.
1: Identifying changes to commit
SQL Source Control compares the database and the working base. Changes you've made to the database since the last time SQL Source Control communicated with source control are listed as changes to commit.
2: Identifying changes to get
SQL Source Control compares the working base and the transient. Changes that someone else committed since SQL Source Control last communicated with source control are listed as changes to get.
3: Identifying conflicts and no-ops
When you visit the Commit or Get latest tab, SQL Source Control runs comparisons 1 and 2 and checks the lists of changes against each other. If the same object appears in both lists, it means another user has committed a change to source control on an object you've changed locally.
At this point, one of two things may have happened: a conflict, or a no-op. To determine which, SQL Source Control runs a comparison between the transient and the database.
Possibility 1: you and someone else have changed the same object in different ways (conflict)
If the object isn't the same in the transient and the database, this change is a conflict. You can choose to resolve the conflict by applying the source control version to your database ("Take theirs"), or overwrite the version in source control with your own version ("Keep mine").
Possibility 2: you and someone else have made the same change to the object (no-op)
If the object is identical in the transient and the database, it means you and another user have independently made the same change to an object. SQL Source Control doesn't notify you, because the two conflict resolution options ("Take theirs" and "Keep mine") are functionally identical. Instead, SQL Source Control silently updates the working base with the change. This is a no operation, or no-op. It's the only situation in which SQL Source Control doesn't list a change in the Commit or Get latest tabs.
Example: Committing a new table to source control
- Beth creates a blank database and links it to a blank repository in her source control system.
- Beth creates a table in her database.
- Beth goes to the Commit tab. SQL Source Control runs the three-way comparison. Because there's a table in Beth's database that isn't in the working base, SQL Source Control knows this is a change to commit, and displays it in the Commit tab.
- Beth clicks Commit. SQL Source Control updates the scripts folder in Beth's repository with a script for the new table, and updates the transient and working base.
- SQL Source Control runs the three-way comparison again. There are no longer any differences between the working base and the database, so SQL Source Control reports that there are no changes to commit.