Click here to monitor SSC
  • Av rating:
  • Total votes: 4
  • Total comments: 0
Michael Sorens

Database Source Control Basics: The Essential Operations

09 October 2012

Database Source Control operates much like a conventional file-based source control, particularly with the Commit and Update operations.  The complexities of applying updates to databases, doing  rollbacks and deployment, come from the requirement to preserve the data in the database and ensure that it goes in the right place as the schema changes

In Part 1 of this series, I introduced database source control by introducing SQL Source Control and SQL Connect. I then described how to get started. In this second installment, I’ll go into further detail, covering the crucial update and commit tasks, and then explain the operation at the heart of source control: rolling back to a different revision. I’ll talk about the art of deploying a database automatically, including the mechanism of migration scripts provided by SQL Compare. Be sure to read part 3: besides covering the mechanics of Visual Studio and SQL Connect, I’ll be saving the best for last: creating a unified source control solution using SQL Server Management Studio, Visual Studio, and TortoiseSVN (or equivalent) all at the same time, all operating in harmony together.

Basic Operations with SQL Source Control

The Commit Operation

Like any other form of source control, the process in SQL Server Management Studio (SSMS) has two stages. You make some change in the database and then you commit it to source control. Picking up from part 1 where you had just connected the database to source control, the change in this case is that you have created each object anew, from the perspective of the source control repository. You can clearly see this when you switch to the Commit Changes tab (Figure 15)

The Change Type indicates New for each object. This Commit Changes tab is likely to be where you will spend most of your time with SQL Source Control. From here, you migrate changes that you have made in the database to your source control repository. As shown in the figure, there are three subpanels on the tab. You start with the middle pane, the object list pane, checking the boxes against the objects that you want to commit, whether it is just one or all of the objects with changes.

The object list pane tells you the type of change, the name of the object, and the type of the object. You can also see, in the bottom or ‘difference’ pane, the differences between the object in your database and the last version of the object that was committed to your repository.

 

The Show button between the two panes provides some convenience options such as the line numbers, text size and grouping of changes. The Previous and Next buttons allow you to navigate among the set of differences within the current object if the changes are extensive.

The three pane-sizing buttons at the extreme right edge let you adjust the relative heights of the object list pane and the difference pane.

The Commit Changes screen

Figure 15: This tab shows pending changes in the middle pane, in this case all the new objects you are adding to source control. The lower pane shows the changes of the item selected in the middle pane. Since the selected object is new, the latest version of it in source control (right side) is completely empty.

Finally, the top pane—the control pane—provides an input field where you enter your commit message, the reason for the commit, along with the Commit button that initiates the actual commit operation. Pressing Commit opens a progress window (Figure 16) where SQL Source Control details its steps to migrate your database changes into your source control system—it is quite a bit more involved than a simple file interface to source control.

Once you initiate a commit operation, SQL Source Control goes through an extensive checklist to reliably and accurately maintain your database

Figure 16: Once you initiate a commit operation, SQL Source Control goes through an extensive checklist to reliably and accurately maintain your database.

The Update Operation

The commit migrates your local changes to the source control repository; the update operation does the converse: migrating changes that other team members have made to the repository into your database. If you linked your database to source control using the dedicated database model (i.e. each developer has a separate copy of the database), then you can think of this in exactly the same way as with a simple file interface to source control. Commit takes local changes in your working copy and publishes them to your team , whereas Update takes others’ published changes and brings them into your local working copy for you to consume. If, instead, you chose the shared database model then the database is no longer “local” but everything still works the same; you just need to think about it slightly differently. Use the Get Latest tab to perform an Update.

Rollback, Deployment, and Migrations

At the heart of source control is the facility to roll your world view back (or forward) from one version of code to another. If, for example, you are working on new features but need to go back to the previous release to work on an important bug fix: roll it back, do your work, then return to the “present”, to where you left off. This process is well understood, and very easy to do, with conventional source control. With database source control it is not quite so straightforward because the data within the database has to be preserved across revisions, but thanks to Red Gate’s SQL Source Control and SQL Compare, it is almost easy! Open up the commit log (by opening the context menu on your database and selecting Show History), select the desired target revision in the top pane, and select Update your database to this version using SQL Compare at the very bottom of the window (Figure 17).

SQL Source Control’s display of your revision history

Figure 17: SQL Source Control’s display of your revision history. You can update your database to a different revision directly from this dialog by selecting it in the top pane then opening SQL Compare (or SQL Data Compare) at the bottom of the window.

This launches the SQL Compare application and opens a new project dialog with the appropriate selections already configured for you (Figure 18).

SQL Compare’s initial screen where you select your source and your target

Figure 18: SQL Compare’s initial screen where you select your source and your target, either of which may be a database or your source control repository. Depending on your selection, you can update your local database to a different revision or deploy your current database to another server.

You will glean from the choices of source and target at the top of this illustration that you have some remarkable options. Not only can you restore your database to any revision already stored in source control but, by simply targeting a different server, you can deploy any source-controlled revision of your database to your staging or even production server. Every little tweak and change you make to your database schema in development and in testing could be automatically, and without any manual intervention, applied to your production server when you deploy! Notice that you can use SQL Compare to deploy from and to a variety of sources and targets. For example, you could deploy your new schema from your database rather than from source control if you prefer.

For deployment purposes, there is an even easier way to set up SQL Compare and save you some work. Select a database in the object explorer in SSMS. Open the context menu, select SQL Compare/Deploy>> Set as Source (or Set as Target if you like). This immediately opens up the SQL Compare dialog within a tab in SSMS with the source pre-selected with your database. As the window indicates (Figure 19), you can simply drag-and-drop another database to the target side and then you are ready to run the tool. At the time of writing you must manually install Red Gate's SQL Server Management Studio Integration Pack, available as part of the SQL Toolbelt or SQL Developer bundle. This requirement will disappear in the near future: when you install SQL Compare or SQL Data Compare, the functionality of the SSMS Integration Pack will be included automatically. As discussed in Deploying a database from source control, this extra bit of integration makes deployment tasks much easier!

Red Gate’s SSMS Integration Pack provides another convenient way to access SQL Compare

Figure 19: Red Gate’s SSMS Integration Pack provides another convenient way to access SQL Compare embedded within SSMS itself. A context menu choice on a database in the object explorer opens a new tab with your source pre-selected; drag a second database onto the target side.

Because databases have both a schema and data, there is an extra layer of difficulty in deploying a change. SQL Compare is a tremendous tool, but when it is synchronizing two databases, it needs to change only the schema, but not the data; thus, it needs to be able to deduce how to preserve the existing data. Most of the time it can do this automatically, but there are certain situations where it is impossible for SQL Compare to ferret out. In these cases, you need to provide some guidance in the form of migration scripts. Migration scripts simply allow for customizing or overriding the default scripts that SQL Compare generates to perform a deployment. Borrowing from the reference page I’ve cited above, you need to use migration scripts in some circumstances to avoid data loss and/or to avoid having to make manual changes to the synchronization script. According to Red Gate, the most common circumstances requiring migration scripts for data changes are these:

Circumstance

Use a migration script to…

Adding a NOT NULL constraint to a column lacking a default will fail.

Specify a default value.

Renaming a table is treated as dropping then creating the table so data would be lost.

Override the default action to instead use sp_rename to rename the table.

Column splits and merges are treated as dropping and creating columns so data would be lost.

Override default actions to use ALTER TABLE.

Changing data type or column size could lose data.

Modify rows that might be truncated with custom code.

Configuration changes, on the other hand, include anything you might need that is particular to your environment or practices, such as disabling replication before deployment and re-enabling afterward. Add these to migration scripts as well so you can completely automate your deployment.

Once you define your migration scripts, they also become part of the source-controlled revision so that any subsequent deployments of that revision will automatically include the migration script. SQL Compare constructs the overall deployment script from your individual migration scripts and fills in the gaps with auto-generated scripts; so whenever you include a revision that has a migration, that migration is automatically included. Say, for example, that you prepare revision 14 with a migration script. When you deploy revision 14, SQL Compare shows a schematic indicating that it applies the migration script between revisions 13 and 14. Now we will imagine that you make further revisions 15 and 16, neither of which requires migration scripts. Let’s now assume that your production DB is at revision 10, so it requires the 13-to-14 migration script. When you specify that you wish to deploy the latest revision (16), SQL Compare automatically inserts the 13-to-14 migration script because your range of revisions (10 through 16) crosses that threshold (see Figure 20).

With a migration script from revision 13 to 14 in place, anytime you cross that boundary the migration script is automatically included

Figure 20: With a migration script from revision 13 to 14 in place, anytime you cross that boundary—as in this example going from revision 10 to revision 16, the migration script is automatically included.

What would happen if you also require a migration script from revision 24 to 25 as well as the 13-to-14 migration script? If you then want to deploy revision 25 (or later) to a system that is at revision 10, SQL Compare automatically inserts both migration scripts.

That concludes part 2, giving you a notion of how SQL Source Control operates much like conventional file-based source control. I also showed how it makes it easy, in concert with SQL Compare, to both update a development database to different revisions as well as deploy your database from source control for error-free releases. The final part of this saga expands the focus beyond SSMS to also include Visual Studio with SQL Connect, the springboard towards a fully integrated source control solution for your application and your database.

Michael Sorens

Author profile:

Michael Sorens is passionate about software to be more productive, evidenced by his open source libraries in several languages (see his API bookshelf) as well as SqlDiffFramework (a DB comparison tool for heterogeneous systems including SQL Server, Oracle, and MySql). With degrees in computer science and engineering he has worked the gamut of companies from Fortune 500 firms to Silicon Valley startups over the last 25 years or so. Current passions include PowerShell, .NET, SQL, and XML technologies (see his full brand page). Spreading the seeds of good design wherever possible, he enjoys sharing knowledge via writing (see his full list of articles), teaching, and StackOverflow. Like what you have read? Connect with Michael on LinkedIn and Google +

Search for other articles by Michael Sorens

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


Poor

OK

Good

Great

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.
 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... 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...

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

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... 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...

Why Join

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