Click here to monitor SSC
  • Av rating:
  • Total votes: 8
  • Total comments: 0
Simple-Talk Editorial Team

Simple-Talk Source Control

24 September 2013

There are a large number of articles on Simple-Talk that tackle the subject of Source Control, and the extension of source control into the practicalities of build and the deployment of application code and database together. Together, they cover a wide range of topics and contain a great deal of information and experience.

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

Rate this article:   Avg rating: from a total of 8 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
How to Build and Deploy a Database from Object-Level Source in a VCS

It is easy for someone who is developing a database to shrug and say 'if only my budget would extend to buying fancy... Read more...

 View the blog

Top Rated

SQL Server Statistics Basics
 Distribution statistics are used by SQL Server's Query Optimiser to determine a good execution plan for... Read more...

Clone, Sweet Clone: Database Provisioning Made Easy?
 One of the difficulties of designing a completely different type of development tool such as SQL Clone... Read more...

Database Lifecycle Management: Deployment and Release
 So often, the unexpected delays in delivering database code are more likely to happen after the... Read more...

The PoSh DBA: Assigning Data to Variables Via PowerShell Common Parameters
 Sometimes, it is the small improvements in a language that can make a real difference. PowerShell is... Read more...

Issue Tracking for Databases
 Any database development project will be hard to manage without a system for reporting bugs in the... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.