This article sets out to tell the story of the evolution of database source control through articles published on Simple-Talk, and illustrate some of the way that Red Gate’s thinking developed while working on some of the technical problems and special considerations of using source control to build and deploy databases.
Why use Database Source Control?
The benefits of using source control in general are pretty well understood in the industry, though there’s no harm in spelling it out occasionally (see The 10 Commandments of Good Source Control Management May 2011). Not long ago, surprisingly, it was less-common for database developers to use full version control, and this was a considerable pain-point in developing a database-driven application (see the application is in source control, but the database isn’t. ) Simple-Talk has always had an editorial bias towards the idea of putting databases into source control as well as application code. An early article by Pop Rivett in February 2007, Pop Rivett and the Uncontrolled Release offered a humorous lesson in the dangers of an uncontrolled software release involving a database. Because the tools provided with SQL Server and Oracle lacked any sort of built-in support for Source Control, this was generally done with scripting, but it was often difficult to disentangle who had been responsible for particular changes.
Why create SQL Source Control?
Red Gate felt sure that the only way to get database developers to accept the idea of full version control was to make the process as easy as possible (see When Database Source Control Goes Bad by Mike Mooney). Although there are ways to allow database source to be placed in source control without a source-control tool (described in Auditing DDL Changes in SQL Server databases from Oct 2011 by Grant Fritchey), Red Gate believed that a dedicated tool would make it significantly easier. In the case of SQL Server, that tool had to be so well-integrated into SQL Server Management Studio that it would seem as if it were part of it. Red Gate also developed a Source Control tool for Oracle soon afterwards which was a stand-alone Windows application.
SQL Source Control for SQL Server was the first to be developed. It was written as an add-in for SQL Server Management Studio. The development team recounted their experiences in SQL Source Control: The Development Story (Jul 2010 ) and SQL Source Control: The Development Story, Part II (Sep 2010). The development of the tool had its challenges but progressed well due in part to the adoption of Agile (as described in Agile Techniques for developing SQL Source Control and Regular Rapid Releases: An Agile Tale).
What is Database Source Control and why is it different?
After SQL Source Control was released, Grant Fritchey set out the principles of using source control for team-based development in Change Management and Source Control , describing such processes as testing, continuous integration, and managing data. A more management-focussed view came with William Brewer’s Database Source Control – The Cribsheet (Nov 2011), which gave an overview of the issues in the debate between the connected and disconnected models of database development and explained the debate between the Single-user development and Shared-Database Development model. An enthusiastic advocate of the Single-user development was Troy Hunt whose The unnecessary evil of the shared development database (Mar 2011 ) set out the reasoning behind trying to encourage developers to use this model. Troy also explained how, once the database was in source control, it could be used for Foolproof Atomic Versioning of Applications and Continuous Integration for SQL Server Databases. Red Gate decided to develop the tool to be able to support both the single-user and shared-database ways of developing databases.
Some of the reasons that source control for SQL Server databases was not entirely straightforward, even with the SQL Source Control tool, were described by Dave Ballantyne in Cleaning Up SQL Server Deployment Scripts. A particular problem for databases was that the build process needed to preserve the data. Sometimes, this couldn’t be done automatically and the build process needs to run a ‘migration script’ to allocate the data in the right place and do any necessary transformations on the data. Phil Factor showed how to create such a script and why it was necessary. The developer can use SQL Source Control to save these migration scripts in the correct place and SQL Compare now has the means to get these scripts from source control and run them when appropriate.
One of the trickiest areas in database source control is the fact that parts of the database, such as scheduled tasks and alerts, can be contained in parts of the server other than the database, as Phil Factor explained.
Using SQL Source Control
Simple-Talk has published a developer’s view of how to set up and get started with SQL Source Control in Database Source Control Basics: Getting Started, which followed up with The Essential Operations which did a tour of everything needed to get going, and The Unified Solution, which explained how the various tools fitted together.
Simple Talk published several accounts of the use of SQL Source Control by some of the ‘early adopters’ SQL Source Control – no more database development without it (0Aug 2010), SQL Source Control – Less Pain for Red Jungle (Nov 2010), Snapper’s Sweet Source Control (Oct 2010) and Working with Continuous Integration in a BI Environment Using Red Gate Tools with TFS (Jun 2012).
Version Control Systems
SQL Source Control doesn’t provide the source control system, of course, but provides the way to make your choice of VCS easy to use when developing databases. When SQL Source Control was first introduced, Visual SourceSafe was still being sold by Microsoft, though TFS was soon to replace it. Life After Retirement: Replacing Visual SourceSafe, and distributed Version Control systems such as Mercurial were becoming popular (see Beginning Distributed Version Control with Mercurial, subversion (see Michael Pilato: Geek of the Week featuring one of the open-source developers.) and GIT (see Geek of the Week: Linus Torvalds and Aversion to Version Control).
Since Subversion was the most popular VCS being used at the time with SQL Source Control and Oracle Source Control, Simple-Talk published a long series of article on how Subversion could be used with TortoiseSVN for doing all the processes that were required for source control. These were
- The Basics
- File Operations and Subversion Filtering
- Putting source code in and taking it out
- Sharing Common Code
- Instrumenting Files with Version Information
- Using Snapshots
- Managing Revisions
- Handling Log Messages
- Server, Repository, and Statistics
- Extending the reach of Subversion
- Subversion and Oracle