Project Description

Command line app, DbUpdater keeps a simple record of all scripts that have previously been run against a database and which 'version' of the database each script was run against.

It recurses through a given directory tree, looks for a defined location then runs all scripts found in that directory (that is has not run before) within a transaction, if any of the scripts fail they all are rolled back.

Each 'defined location' may be for a different database and with a few simple changes it can be environmentally aware (e.g. Dev, QA, Prod etc).

Especially useful on your build box with continuous integration. DbUpdater should be one of the first things run when the build kicks off. If a developers checkin includes scripts to update the database, these changes are made to the database first, ensuring the developers unit tests work...and importantly ensuring the tests fail if the developer forgets to check in a script.

Disclaimer

How It Works

FutureImprovements

Example Of Use

Goals

  1. Improve the release process by simplifying it (once a developer checks the script into source control they can forget about it, the process will ensure it gets run at the appropriate time against other environments.) and making it more robust.
  2. Eliminate problems caused by database inconsistencies on developer machines and also problems caused by database changes that are 'forgotten'.
  3. Have a simple program that can run all update scripts against all databases inside one transaction that rolls back if any script fails. (see note about multiple databases here ProjectSetup)
  4. Must be able to be run by the continuous build process (i.e. require no manual intervention).
  5. Must be environmentally aware (e.g. Dev, UAT, Production etc).
  6. Record what scripts were run against which database version.

Process Changes

  1. ALL changes to the database must scripted (one big one or lots of small ones doesn't matter...prefer lots of small ones though). Not essential but ideally scripts should be re-runable. WhyReRunable
  2. ScriptNamingConvention must be followed.
  3. 'Ideally' each developer has their own version of the database on their local machine. It's better if each developer works in their own isolated environment, that way developers cannot 'step on each others toes'!
  4. Everytime someone gets the latest version of the source, the first thing they do is run DBUpdater. This ensures all required database changes for the latest source are made against their local database(s). A nice way to to this is to have a batch file that a) gets the latest version; b) Runs DBUpdater. OtherNiceBatchFileActions
  5. DBUpdater must be one of the first things that the automated build does, especially must be run before any tests are run. If DBUpdater fails, the build fails.
  6. DBUpdater will also be a part of your release process to every environment.

Quick Start

There are a few things that you need to change or install if you want to use DbUpdater. These are all listed below, please let me know if you follow these steps and it still won't work and I'll figure out what is missing and add it here...

Dependencies

  1. Minimum .Net 4.0 (NOT the Client version)
  2. SQL Server (2008+)
  3. Enterprise Library 5.0 (http://msdn.microsoft.com/en-us/library/aa480453.aspx) (no need to install it, the required dll's are included)
  4. NUnit 2.4.3 (http://www.nunit.org/index.php?p=download). Although you could remove the test projects, then you don't need NUnit.

Oracle Note
Because I'm using the enterprise library DAB, DBUpdater should work against Oracle but with one significant problem. Whenever you run DDL script against an Oracle DB, Oracle automatically does a COMMIT. Therefore the nice 'wrap in a transaction' that DBUpdater provides won't work. So if you want to use DBUpdater against Oracle you'll need to implement a different strategy, probably have a backup somewhere that can be restored if any of the scripts fail.

Setup

  1. Install the dependencies.
  2. Setup the database for automated updating. In the project 'DBUpdater' go to the 'SQL\DBCreation' directory. In that you will find a script called SetupDatabaseForDBUpdater.sql. Run that against your database (see below for notes on what this script does and why).
  3. Create a 'Build.Environment' file. See BuildEnvironment
  4. Open VS2008
  5. Edit the App.Config
  6. Add the 'structure' (place for your change scripts to go) to your project. (see ProjectSetup)
  7. So that you can easily run it from the command line, I usually just add the release directory to the 'Environmental Variables' Path (in System Properties, advanced.) There are many ways to achieve this though so just do whatever works best for you.
  8. On your build box in the previous step make sure that you are running a release build, otherwise you'll get a popup box when an exception gets thrown (which happens whenever a script fails). The result of this is that your build will 'hang' until someone manually closes the popup form.

And if you want the Unit Tests to work
  1. Run CreateDBUpdaterTestDB.sql (creates a database called DBUpdaterTest which has a few tables)
  2. Run SetupDatabaseForDBUpdater.sql (against the new DBUpdaterTest database)

Database Changes

SetupDatabaseForDBUpdater.sql creates two tables
  • DatabaseVersion
  • DatabaseVersionScriptsRun

and also their associated CRUD stored procedures.

The purpose of the tables is to maintain a record of what the current version is and also what scripts have been run and the best place to store this information is on the database that is being updated.

CommonProblemsAndFixes

Points of Interest

If you found this useful, check out these codeproject articles I've written up on related topics:-
http://www.codeproject.com/KB/architecture/CI_Setup_Secrets.aspx
http://wallism.wordpress.com/2009/02/23/getting-started-with-continuous-integration/
and my other codeplex project for generating NAnt build files
http://www.codeplex.com/nantgenie

Last edited Feb 26, 2013 at 3:04 AM by wallism, version 41