Database Change Scripts - Mambo Style
One of the more traditional approaches to database development is where developers simply work against a shared database instance through SQL Management Studio or Enterprise Manager. Changes are thought of, designed through a GUI interface, and DDL is created and applied right then and there. If you've ever taken this approach, you already know it's full of challenges. When you're ready to deploy the latest and greatest database schema, you simply script out the current schema and apply it to your target. This is fine for the early stages of development, but as you progress, you realize that having your data "blown away" everytime you deploy due to DROP & CREATE statements becomes annoying and even unproductive. And once you go to production, blowing away the data may not even be an option.
Application developers aren't plagued with this issue. First of all, they're not faced with the burden of preserving large amounts of data simply because applications query this data, not store it. Furthermore, they have the luxury of evolved and tightly integrated source control working against the most granular unit of development - the file. So how can database development overcome some of these challenges? Through mambo-style change scripts.
So what is mambo? We'll get to the true meaning of the name in a minute, but what's most important are it's features:
- Allows you to upgrade the schema of any database (even blank) to the tip revision.
- Automates the upgrade process to avoid manual application of changes.
- Preserves existing data during an upgrade.
- Can upgrade the data itself by specifying how to transform existing data to the new schema.
- Enables developers to work off of local instances on laptops or at home and "sync up" their work with everyone elses. This feature is crucial in large projects.
Okay, enough reading, what is it? Actually, nothing fancy. It's simply a scripting method where changes are encompassed in sequenced change scripts (DDL) and applied, in sequence, against the current schema. For example, John and Larry are both working from home. John adds a new column to DimAccount called PreferredMember and scripts the change out as "0004 - Preferred Member Column.sql", and Larry also adds a new column to DimAccount called Currency and scripts the change as "0005 - Default Currency Column.sql". The next morning, both John and Larry checkin their change scripts (whoever checks in last must change their number to 0030), and now whoever wants these changes simply executes them in order. Here's a visual to better understand:
The key thing to remember about each change script is that they MUST USE ALTER STATEMENTS WHEREVER POSSIBLE. There's two problems that still aren't addressed: 1) Considering that you could have hundreds of these scripts, how do you automate their application? 2) How do you know what's been applied and what hasn't? This is where mambo comes in...Mambo is a tool that will compile each and every change script into a single "mambo" script such that a given change script is guaranteed to only run once. It does this by inserting a record for each and every script it executes into a metadata table, and always checks this table before running each script. Therefore, you can upgrade ANY database at ANY version to the tip revision.
Enough talk. Download "Mambo Sample.zip" below and see for yourself.
Considerations:
- Change scripts should avoid USE statements, and instead inherit the USE statement defined in the mambo template.
- Mambo should be compiled and deployed as part of your daily build and deploy process.
- Unless an object is being created for the first time, developers should write change scripts. Also, be wary of the change script Management Studio creates for you as it typically involves creating a Tmp table with the new schema and copying the data over. If executing mambo on production, this could be a costly operation.
- Always backup your database before applying mambo, even if it's a developer instance. If you encounter an error, or something doesn't upgrade properly, you might lose your data (and time).