Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

UpgradeDB

Rate me:
Please Sign up or sign in to vote.
4.79/5 (13 votes)
10 Nov 2014MIT8 min read 30.7K   540   53   7
An incremental database upgrade utility for SQL Server.

 

Introduction

UpgradeDB is a simple utility designed to help small development teams apply changes to a SQL Server relational database in an incremental and orderly fashion. It allows a development team to "commit" database changes in a manner that is familiar to users of standard version control software, allowing the team to apply an Agile approach to database development. Once all of the team's changes have been "committed", UpgradeDB then produces a simple upgrade wizard that customers can click through in order to upgrade their local database.

Background

I recently began collaborating on a Commercial-Off-The-Shelf Software (COTS) project, which was designed to be distributed to customers in a shrink wrap box, on DVD media. This may not seem abnormal to some, but as a developer who has spent his entire career building web services and server-side utilities, this whole concept of "physical media" was completely new to me. During the development process, we used Subversion as our version control software to manage changes to our source code, but when it came to database changes we were basically saving snapshots of the latest schema dump.

This process of modifying the database, dumping the schema, and then committing it to SVN worked fine; up until the moment we published our first customer release. After that, we began to fret over how we could ever make changes to the database again without having track each change, or ultimately go back and figure out what changes were made.

What we needed was something similar to version control, only for databases. We needed to be able to make changes to the database incrementally, as we were developing the code (not after the fact); and once all was said and done, we needed an upgrade wizard that our customers could run in order to upgrade their local database. Finally I had an idea, and I spent three weeks worth of evenings, late nights, and weekends to produce this first revision of UpgradeDB.

Using the code

To use UpgradeDB, simply download the project and open it in Visual Studio 2013 (note that "Express" users will need to open the project in "Visual Studio Express 2013 for Desktop"). Within the root directory, you will see two C# files named DatabaseUpgrades.cs and DatabaseUpgrades.examples.cs.

Image 1

The DatabaseUpgrades.cs file is where all of your "upgrades" need to go. Examples can be found in the DatabaseUpgrades.examples.cs file, but essentially it all boils down to this:

C#
class AddColumnToHumanResourcesTable : DatabaseUpgrade
{
    public AddColumnToHumanResourcesTable()
    {
        // Set the required fields.
        this.CommitID = "1.0.0.1001";
        this.CommitTimestamp = new DateTime(2014, 1, 3, 10, 0, 0, DateTimeKind.Utc);
        this.Description = "Adds a column to the Department table.";
        this.UpgradeCausesDataLoss = false;
    }
 
    public override bool Upgrade(
        SqlConnection OldDB, SqlConnection NewDB, out string Error, out List<string> Warns)
    {
        // Initialize the error and warning message objects.
        Error = null;
        Warns = new List<string>();
 
        string sql = @"
            ALTER TABLE [HumanResources].[Department]
                ADD [PurposeStatement]
                    varchar(max)
                    NOT NULL
                    DEFAULT 'To maximize profits, and minimize loss.';
        ";
        using (SqlCommand SqlCmd = new SqlCommand(sql, NewDB))
        {
            SqlCmd.ExecuteNonQuery();
        }
 
        // Return True if upgrade succeeded, and False if it failed.
        return true;
    }
} 

There are several things to note about this class:

  1. All "upgrades" must be classes that inherit from the DatabaseUpgrade class.
  2. A unique CommitID and CommitTimestamp must be set in the class constructor.
    a. The CommitID must be a string consisting of only numbers and periods.
    b. Upgrades are applied one-by-one, in the order of their CommitTimestamp.
  3. The "Upgrade" method passes in two database connections, one that connects to the "Old" database, and one that connects to the "New" database. Apply all of your database changes to the "NewDB" connection, and use the "OldDB" connection to query data from the existing database.
  4. The Upgrade() method must return True if the upgrade succeeded, and false if it failed. An explanation of what caused the "upgrade" to fail should be written to the Error string. If your upgrade produces warning messages that do not cause the upgrade to fail, then they may be added to the Warnings list.

Developers can add as many of these "upgrades" as they like to the DatabaseUpgrades.cs file, and they will all be processed in the order of their CommitTimestamp. That's it! Once you're done with that, all you need to do is compile the program and run the executable. At that point the UpgradeDB Wizard will appear, and will walk you through the following series of pages: Welcome, Agreement, Settings, Upgrades, Confirmation, Progress, and Completion.

The two most important wizard pages are the Settings page, and the Upgrades page. The Settings page looks like this:

Image 2

On this page, the user must provide database connection information, specific to their setup. The two most important settings to look out for are the "Database Name" fields in both the top and bottom sections (shown above, in red boxes). The first Database Name field represents the name of the database that we are attempting to upgrade, the second one is the name that we want the new database to have after the upgrade completes. When UpgradeDB performs its upgrade, it first connects to the existing database, performs a full backup of that database, and then restores that backup giving it the name specified in the second Database Name field. This way, if anything goes wrong, the original database still exists in an untouched and functional state! (The only exception is if you choose to give your new database the same name as your old one. In that case, the old database itself is renamed; and if the upgrade fails, the user will need to manually delete the upgraded database, and rename the prior database.)

Once the database settings have been made, the wizard moves on to the Upgrades page, which looks like this:

Image 3

On this page, the user will see all of the "upgrades" that were added to the DatabaseUpgrades.cs file, listed in order of CommitTimestamp, and all checkmarked by default. After this page comes a brief confirmation page, and after that the upgrading begins!

Custom welcome and legal agreement text can be added to the config.json file, located in the project's root directory. Default values for the Settings page can be set there as well.

Finally, it is important to note that UpgradeDB creates an [UpgradeDB].[UpgradeHistory] table in each upgraded database that it creates. This table is used to store a listing upgrades that have already been applied to the upgraded database. If UpgradeDB is run against a database that has already been upgraded, UpgradeDB will determine its state by reading the UpgradeHistory table:

Image 4

 

Points of Interest

This project contains several pros and cons that are worth delving into.

PROS:

  1. Incremental Development: This project finally allows me to develop database changes in an Agile fashion (incrementally), just like we do with our source code. I don't have to track my changes anymore, and I never have to go back and figure out who broke what, or merge this change with that. If one developer's change negatively impacts me, I simply add a new "upgrade" that overwrites theirs!
  2. Flexibility: Another big advantage to this approach is flexibility. Thanks to the fact that I always have a connection to the existing and untouched database, my upgrade can be as simple or complex as I need it to be. If all I need to do is add a column, I can ignore the old database connection entirely. If I have a list of external scripts to run i can read the files from disk and run them; and if I have to move or transpose data I can query it from the old database and map it to the new one.
  3. Fault Tolerance: If an error occurs, the old database is still there, intact and untouched. If that isn't enough, I can always go to the SQL Server DATA directory and find the BAK backup of the original database, which was taken BEFORE any of the upgrades was ever applied!

CONS:

  1. No enforcement: Developers are responsible for ensuring that they do not write to the old database.
  2. Disk Space Heavy: The upgrade process makes a BAK backup of the target database (in the DATA directory), and then restores it as a new database within SQL Server (and doesn't delete anything after the fact). This is probably fine in most cases, but if your DB is large and your disk space is small, then you may get into trouble.

History

  • Version 1 released on 2/16/2014.
  • Version 1.1 released on 4/21/2014 (REMOVED)
    • This was a premature release, and has been removed. Please use version 1.1.1 instead!
  • Version 1.1.1 released on 4/22/2014.
    • Fixed progress-bar bug
    • Added "silent-mode" installation
    • Added file-based logging
    • NOTE: Although the source code hints to MySQL and PostgreSQL support, these features are not yet implemented (look forward to these features in a future release!)
  • Version 1.2.0 released on 4/28/2014.
    • Added "Rollback" feature! When a database fails to upgrade properly, the user can now rollback to their previously working database by clicking the "Rollback" button.
    • Added "ShowSettingsPage" and "ShowUpgradesPage" variables to the JSON configuration file. These allow the developer to decide whether the Wizard should display a Settings and/or Upgrades page.
      • When the Settings page is disabled, users cannot change the default database connection settings.
      • When the Upgrades page is disabled, users cannot choose which upgrades to apply (all will be applied).
      • It is recommended that you set both variables to TRUE; unless you have total knowledge of the database connection settings being used.
    • Fixed progress bar bug, that was preventing all upgrade progress from being displayed.
    • Please note, PostgreSQL and MySQL are still under development, and ARE NOT supported in this release (even though some code exists in the source).
    • Also, please forgive the fact that I have not updated the article recently. (It's a choice between coding and writing, and we all know how that goes...)
    • Finally, if anyone is actually reading this, and is interested in helping out; I could sure use some QA support! If you find any bugs, please post them on the forum, and I will try to get to them ASAP. Thanks and enjoy!

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
x3F
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionExcellent idea and work. Pin
McFadden, Scott (1 each)22-Apr-14 8:06
McFadden, Scott (1 each)22-Apr-14 8:06 
AnswerRe: Excellent idea and work. Pin
x3F23-Apr-14 1:21
x3F23-Apr-14 1:21 
Questionabout elabrating this artile Pin
Dholakiya Ankit17-Feb-14 16:03
Dholakiya Ankit17-Feb-14 16:03 
AnswerRe: about elabrating this artile Pin
x3F17-Feb-14 20:30
x3F17-Feb-14 20:30 
I can give a couple of examples:

1. If you're working in a team, everyone can add their database changes in one place. When each developer has finished, they can submit the changes to version control, and everyone has them. At any given time, any developer can upgrade their database by running a baseline script, and then running the latest UpgradeDB wizard.

2. If you have a complex database change to make, UpgradeDB is helpful because it does not limit what you can do. For instance, if you need to split a "FullName" column into two new columns colled "FirstName" and "LastName", you can do that easily using the OldDB and NewDB connections, without running any risks of data loss. On the other hand, if you were to try this in T-SQL, your algorithm might fail on a customer's machine; because one of their user's had funky characters in their "FullName". This would leave the customer's database in an unstable state; but with UpgradeDB you don't have to worry because the old database is still alive, in tact, and unchanged.

3. It serves as a historical record of all of the changes that your team has made over time.

4. It comes with a free click-through wizard that you would otherwise have to write for your customers anyways.

Let me know if that answers your question. If not, let me know how you manage database changes in your projects, and I might be able to give better examples. Thanks!
x3F

SuggestionRe: about elabrating this artile Pin
thatraja9-Mar-14 23:38
professionalthatraja9-Mar-14 23:38 
GeneralRe: about elabrating this artile Pin
x3F12-Mar-14 2:35
x3F12-Mar-14 2:35 
GeneralRe: about elabrating this artile Pin
thatraja12-Mar-14 2:38
professionalthatraja12-Mar-14 2:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.