Wibke Carstensen

1 August 2020

An introduction to SQL Source Control

Updated August 2020

In our series of blog posts about Version Control and the Database, we’ve explored the history of version control, why databases have some unique requirements, and why it’s important to include them in your VCS.

In this concluding post, I focus on Redgate SQL Source Control and answer some fundamental questions about what it is, how it works, and which added, database-specific benefits it brings to version control. You can explore everything I talk about by downloading a 28-day fully-functional free trial of the tool.

What does SQL Source Control do?

SQL Source Control plugs into SQL Server Management Studio (SSMS) and links your databases to an existing version control system, such as Git, TFS or Subversion.

This allows you to manage changes to database schema and static data alongside application code. You can see who made what change, when, and why, and have a simple way of sharing changes across a team of developers.

Most importantly, you work from a single source of truth, greatly reducing the risk of downtime at deployment.

How does SQL Source Control work?

Version-controlling only really works well for text files, because it’s easy to tell what’s changed. It’s consequently hard to version control database schemas because they’re not text files and the data within the database has to be maintained.

For this reason, SQL Source Control generates a version of your database schemas as text files, and links these files to your version control system. This means that, when you make a schema change and commit it to version control, it’s easy to see what the change was.

Behind the scenes, SQL Source Control uses the SQL Compare engine to create and maintain folders of object creation scripts, called scripts folders, which represent your database schema.

However, SQL Source Control doesn’t just run comparisons between the database and the scripts folder in your source control system, it actually keeps track of three separate versions of the database to capture all the information needed to track database changes:

• The database itself – the current state of the database in SQL Server
• The transient – a local copy of the latest revision in version control. SQL Source Control periodically checks your version control system and updates the transient so it always reflects the latest version available. 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 command. Like the transient version, it’s stored in your local application data.

More detail on how the three-way comparison works can be found on our documentation pages.

What additional benefits does SQL Source Control offer?

SQL Source Control offers all the standard benefits of version control, such as a single source of truth, ease of sharing changes across a team for collaboration, a full change history, and more. This is explained in more detail in Part II of the Version Control and the Database series.

In addition, it includes some features which address some database-specific problems in the version control context:

Connected development model

Changes made to an application during development result, directly, in a change to the underlying code file. These source files are kept in version control, so that each revision to a file is retained. This process doesn’t interrupt or modify the developer’s connected development model, whereby a developer works directly with his or her code, within Visual Studio.

SQL Source Control supports the connected development model for databases by allowing developers to work in SQL Server Management Studio (SSMS), the preferred IDE for database development, to make changes to their database online while maintaining a full history of changes in version control.

Other approaches, whether home-grown or using SQL Server Data Tools (SSDT) in Visual Studio, have often by necessity adopted an offline model.

Following a home-grown approach, for example, developers can periodically script out the database objects they’ve changed. In the absence of a tool like SQL Source Control, it is, however, very hard to maintain a proper incremental history of changes. Packaging up a specific database version becomes difficult, and discrepancies are common between the ‘working’ database and what’s in version control. Because of these difficulties, home-grown approaches tend to be offline with the scripts in version control, which are then applied to a database.

This is the reason why most home-grown solutions for version controlling databases have, by necessity, adopted an offline development model whereby, rather than working directly with the database, developers work with the scripts, in version control, which are then applied to a database.

There are many drawbacks to this model. It’s disruptive and, when working with scripts rather than a database, you lose the benefits of immediate syntax checking. If you’re just working with a script, completely independent of the database, it’s possible – even likely – that you’ll introduce syntax errors.

To check the syntax of an offline script, it must be run against a database to make sure the latest version of the scripts, in version control, will build a functional database. Additional processes must also be in place to ensure objects are scripted in dependency order and referential integrity is maintained, and that changes made by teams of developers can successfully be merged.

Static data

With SQL Source Control, you can version data that doesn’t change very often. Version controlling data that changes frequently (for example contacts) isn’t practical and this kind of data is generally not under a developer’s control.

Other data such as a country list doesn’t get updated frequently and typically gets described as static or reference data. It tends to have dependencies associated with it as it gets referenced in a number of places.

To build a specific version of the database, you need to know which countries have to be included, so it’s important this data is also stored in version control, so dependencies won’t be broken and the database builds successfully.

By default, Redgate’s SQL Source Control doesn’t include any data. You can, however, choose to mark particular tables as static data and start version controlling them alongside schema changes. For more details on how to set this up, see our documentation.

Object locking

Object locking is a common version control feature, but often not available for team-based database development. It solves an issue that those affected initially don’t even know they have. Problems arise when two developers work on the same object at the same time. Unless they’re great communicators, they can easily end up overwriting each other’s changes.

SQL Source Control offers a quick and easy fix to this by letting you lock specific features when you start work on them. All you need to do is right-click the object you want to work on in the Object Explorer and select ‘Lock this object’. That’s it. If someone else works on the same object and tries to save changes to it, they’ll see a message saying that it’s locked by you.

Migrations

There are two methods of managing database changes, often referred to as the state- or migrations-based approaches. Both, at their root, are about what your favored source of truth is: the definition of how you want the database to look (its state), or the scripts you produce to handle the upgrade (the migrations).

SQL Source Control supports what is described as a state-first approach. It’s primarily state-based, but can also mix and match state with the occasional migration.

While SQL Source Control enables a single step between the current database state and the desired database state by versioning each object in the database as a CREATE script, it also lets you code certain parts of the deployment process manually as a migration.

Some changes such as column splits or similar data motion scenarios can’t be easily managed via a comparison approach. The tools don’t understand the context of these changes and there’s a risk of data loss by, for example, dropping a renamed table.

The migrations feature in SQL Source Control lets you assign custom SQL for these kinds of changes to replace the relevant part of the deployment script, which would have otherwise been generated by the comparison engine.

More details on working with migration scripts can be found on our documentation pages.

Branching and merging

Generally, every version control system will allow you to copy a particular file version and use and amend it for a different purpose, without affecting the original files, which is referred to as creating a branch.

With SQL Source Control, you can set up branching in a couple of ways:

1. Unlink and relink the database: You work on one database in SSMS, and link it to the branch in your version control system. Once you’ve created the branch, un-link the database in SQL Source Control, then link the database again. When you link, specify the location of the branch in version control.
2. Create a new database for the branch: You create the branch in your version control system, then create a new database to link with it.

For more detail on working with branches, visit the SQL Source Control documentation pages.

SQL Source Control doesn’t provide automation or line-by-line merging of branches, but you can use it or SQL Compare to merge at an object level. When you merge with SQL Source Control or SQL Compare, you choose a version of each object to keep, as our documentation pages demonstrate.

SQL Source Control also, of course, allows you to resolve or merge a much simpler conflict when, for example, two people change the same object. The Conflict to resolve bar will be shown alongside the changes, and to resolve the conflict you can choose to Keep mine or Take theirs.

For more information about SQL Source Control and to download a free trial, visit our web page.

To find out more about the advances made in version control practices, from manufacturing through to software development, you can also read part I in the series, A brief history of version control.

And to discover the challenges databases bring to version control, and why it’s so important to include them, see Part II of our Version Control and the Database series.

This post was originally published in November 2016 and has remained relevant – and popular – ever since. It was reviewed in August 2020 to ensure the content is up-to-date.