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.
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.
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:
class AddColumnToHumanResourcesTable : DatabaseUpgrade
this.CommitID = "22.214.171.1241";
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)
Error = null;
Warns = new List<string>();
string sql = @"
ALTER TABLE [HumanResources].[Department]
DEFAULT 'To maximize profits, and minimize loss.';
using (SqlCommand SqlCmd = new SqlCommand(sql, NewDB))
There are several things to note about this class:
- All "upgrades" must be classes that inherit from the DatabaseUpgrade class.
- 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.
- 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.
- 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:
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:
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:
Points of Interest
This project contains several pros and cons that are worth delving into.
- 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!
- 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.
- 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!
- No enforcement: Developers are responsible for ensuring that they do not write to the old database.
- 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.
- 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!