Deploying Database Schema changes in a patching process [duplicate]

Question

I am facing a conceptual development methodology problem that I was hoping you could help me with. Firstly, a little bit of background.

Background

The project I am developing is a niche application that contains a database of information, and the target audience for my application are private individuals on the lower-end of the technical proficiency scale. In fact, that is the primary reason for my project: many similar options in this field exist but the vast majority are fragile and complicated applications that require a high degree of both programming, database and sysadmin experience just to keep running. Worse yet, years or even decades of poor maintenance and "improvement" by amateur programmers who only barely understood what they were doing means many of these applications are simply beyond that ability of a non-professional to maintain.

The reason that I began this project was to give them a low barrier of entry alternative that they could reasonably extend and customise via configuration rather than programming, to centralise and minimise the maintenance. I myself have been programming for nearly 20 years but not until very recently in a professional capacity: I am actually a Civil Engineer, but my main professional experience is in being a client/technical representative for large enterprise engineering and asset management systems. So please excuse me if I am not 100% up to date with all of the professional or academic lingo.

Problem

My big conceptual problem is how to safely and invisibly to the end user merge database changes as part of an upgrade process. Basically, as I release major versions, sometimes extensions to tables or new tables might be made to the database model, which will need to be rolled out as part of the patching process. In the past, I have only ever done this on my own development servers and my approach was fairly haphazard and manual.

How should I go about quantifying, preparing and deploying the required database changes as part of my application? For reference, my program is written in C# and I use SQL Server (deployed to the user bundled with the application as SQL Server Compact Edition). I have not yet selected a patching platform to use, as possible compatability with whatever methodology I come up with here may influence my choice. My thoughts are that when I decide to do a release, there are probably some tools I must use to prepare a database difference between the last release and the current release, and prepare a SQL script to execute. Then at some point in the installation process, it must run the SQL script and bring the database up to date.

It seems simple, but I am really not sure whether it is possible. Most of the tools I can find for databases out there seem aimed at Enterprise-level applications and Database Administrators, and I am hard-pressed to figure out if they can actually do the simple task I need it to do.

Am I on the right track for doing this or is there a better approach?

Answer 1

One relatively simple approach that I have used for a similar SQL CE app is to distribute with the update a list of SQL statements that contain the cumulative updates to the schema since the first version of the schema that was initially distributed. The final SQL statement in the list will update a row in a lookup table so that the version number of the schema will be the same as the release. The SQL statements can be distributed either as a file or as a resource in the application.

UPDATE lookupTable
SET value = 'alarm.wav'
WHERE lookupType = 'sounds' AND lookupId = 'soundFile' AND value = 'test.wav';

ALTER TABLE Users DROP CONSTRAINT UQ__userEmail__00000000000000E0;

UPDATE lookupTable
SET value = '2.8.0.6'
WHERE lookupType = 'version' AND lookupId = 'DataModelVersion';

Then each time the program runs it can check the value of the version number of the program versus that of the current schema and if the schema is not the same then it will run the latest schema file to do the schema changes and then update the schema version, e.g.

if (!Application.ProductVersion.Equals(schemaVersion)) {
    Schema.UpgradeDatabase(Schema.GetDbConnectionString(), upgradeFile);

The UpgradeDatabase method must be able to handle the fact that since the list of SQL statements are cumulative then earlier insert or alter statements may encounter constraint errors when they are run again on a subsequent upgrade. That can be worked around by throwing only exceptions that aren't expected, e.g.

//SQL Lines are split on ';' and put in a commands array 
foreach (string command in commands) {
    cmd.CommandText = command;
    try {
        cmd.ExecuteNonQuery();
    } catch (Exception exc) {
        // Only throw if it's not an expected error due to rerun of the cumulative statements.
        if (!exc.Message.ToUpper().Contains("VIOLATION OF PRIMARY KEY CONSTRAINT") 
          && !exc.Message.ToUpper().Contains("A DUPLICATE VALUE CANNOT BE INSERTED INTO A UNIQUE INDEX") 
          && !exc.Message.ToUpper().Contains("THE FOREIGN KEY CONSTRAINT DOES NOT EXIST"))
            throw;
    }
}

Of course testing must be done on each patch release to make sure the SQL upgrades the DB properly and that expected errors are ignored. The fact that the SQL statements are cumulative eases the burden of handling cases where a user might have skipped several minor releases before installing a release and keeps it pretty simple.

The method is relatively simple and has reliably worked for upgrading client apps that are remotely distributed. The only downside is that it doesn't handle going back to an earlier version, but if a patch needs to be issued to go back to some schema then that can be done by adding more lines to alter it back to the previous schema.

Answer 2

Write SQL scripts with a huge amount of error handling in them (look for a column to not exist before adding it to a table, look for the table to not exist when creating it, etc.). You can run the scripts as a part of your installation package (results may vary as different installers can make that easier or much harder than necessary), or you can run the scripts when your application starts. If you do the latter, I would recommend moving the SQL scripts to another location so that you don't attempt to run them more than once.

You can look at all of the available installers out there. I have used InnoSetup with good success running all sorts of executables after placing my files in the appropriate places. I have heard it is possible to do the same with an MSI, but I never got it working. If you decide to use WiX, its Burn tool can create an executable install that gives you both an executable installer and an MSI installer in the same package.