SQL Source Control technical overview

This article gives a simple architectural and conceptual overview of SQL Source Control. It isn't necessary to understand this in order to use SQL Source Control, but it may assist with troubleshooting, or even be interesting.

SQL Source Control uses the technology that powers SQL Compare to create and maintain folders of object creation scripts representing your database schema. It plugs into SQL Server Management Studio and compares the current state of the database to the representation in source control, detecting changes and letting you commit them.

However, SQL Source Control does not perform repeated direct SQL Compare comparisons between the database and source control. Using only those comparisons would not capture all the information needed to track database changes. In a situation where there is more than one developer, for example, the state of the database may change while you are working. So the difference script produced by a simple comparison may not always represent the actual changes you made. SQL Source Control therefore maintains local, temporary copies of the database schema.

The architecture is:

There are two sets of temporary files:

The working base
This is a copy of the state of the database when you started working.

It is the baseline against which the changes you make are measured. Differences between the working base and the current state of the database are the changes to commit.

The working base enables SQL Source Control's Undo Changes feature.
The transient
This is a copy of the state of source control when you started working.

It is periodically updated. Differences between the transient and the current version in source control are the changes to get.

Conflicts occur when the differences between the database and the working base, and between the working base and the transient, are not consistent. This indicates that potentially incompatible changes have been made to both the database and source control.

Periodically, SQL Source Control may automatically update the working base. This occurs when the working base is different to both the database and the transient, but the database and the transient are the same as each other. This is done to prevent spurious change notifications. For example, if you created an object and subsequently dropped it, without committing either change, SQL Source Control updates the working base silently, rather than notify you of two changes.

Was this article helpful?

Search support