Database installer revised
Summary: Overview of changes made to the previously published database installer script (dbsetup.vbs).
I'm not sure what happened to the new and "improved" MSDN Magazine (the web site), but all of a sudden, I started getting messages from readers asking to send them the VBScript file (dbsetup.vbs) accompanying my Data Deployment: Streamline Your Database Setup Process with a Custom Installer article (from the September 2004 issue of the magazine). I checked the site and did not find a download link, so I'm offering* the most recent version of the script here:
I'd also like to mention a couple of alternatives which you may like better than my approach, but first, let me describe modifications I made to the script in case you insist on using it. (If you don't know how the script works, please read the article; it explains the script execution logic.)
The most recent update to dbsetup.vbs was triggered by an idiosyncrasy in Visual Studio 2008. For some reason, Visual Studio 2008 saves new SQL source files added to database projects in the UTF-8 format, even if the files only contain regular 7-bit ASCII characters. [I could not find an explanation to this phenomenon, but since new files added to other (non-database) projects get saved in the ASCII format, I suspect that the issue is caused by the default file templates associated with the Visual Studio 8 database projects (someone at Microsoft must have accidentally saved these files encoded in UTF-8). I may be wrong, though.]
UTF-8 encoded files containing English (ASCII) text are almost identical to regular ASCII files, except that the former include three special bytes called byte-order mark (BOM) identifying the encoding format in the beginning of the file. Most programs, such as text editors, can handle UTF-8 encoding (you may not even notice that the file is UTF-8 encoded), but unfortunately, VBScript (the scripting engine) is not one of them.
When opening a text file in VBScript, you can specify whether the file is encoded in ASCII (TristateFalse) or Unicode (TristateTrue), but if you specify the Unicode format, VBScript will assume that the file is encoded as UTF-16, which is not the same as UTF-8. Because VBScript did not (and still does not) support the UTF-8 format, the original version of dbsetup.vbs could not process UTF-8 formatted files (the returned database error complained about invalid characters).
| Tip: | To learn about Unicode, read Joel Spolsky's article The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!). |
To handle UTF-8 encoded files, I added a workaround: when processing text files (SQL scripts and others), dbsetup.vbs now looks for the Unicode byte-order marks. If the script finds the UTF-8 specific bytes, it discards them and processes the rest of the file as regular ASCII text (Note: If you want to include real Unicode characters in text files, use UTF-16 encoding, which VBScript and dbsetup.vbs can handle). This workaround is not very elegant, but it seems to work.
Two other modifications affect repair scripts (scripts executed to repair a database without incrementing its version). To provide support for multiple database versions (in source control), dbsetup.vbs now expects to find repair scripts under the version labels in the Repair folder (the Repair folder is expected to have subfolders named after the database versions to which they apply, similar to the approach used for grouping the upgrade scripts). Also, to make sure that repair scripts added during test cycles get executed during production deployment, dbsetup.vbs will process them after an upgrade (only repair scripts defined for the current version will be processed).
Finally, dbsetup.vbs now supports conditional execution of scripts. If you want to selectively include certain scripts, keep them in a subfolder (under a regular folder) named after a directive you'll use when running setup and/or enclose the file references in the #if DIRECTIVE ... #endif code block (replace DIRECTIVE with a meaningful name) if you explicitly name the scripts in of the Files.txt files. To include the script, launch dbsetup.vbs with the /set command-line parameter followed by a directive you want to include, such as /set:DIRECTIVE. When dbsetup processes a folder, it first handles regular scripts, and then, if launched with the /set command, it will process all conditional files from the subfolders matching the directives included in the /set command, or the conditional sections of the Files.txt file.
I have to say that there may be better database deployment methods than my simple process. For example, you can try using Database Schema Build & Deployment Tools offered by Database Edition and Team Suite of Visual Studio Team System 2008.
| Tip: | In addition to Microsoft, other third-party vendors started offering database installation tools, such as:
|
A couple of weeks ago, I read an article Deploying Database Developments** in which Alexander Karmanov describes an alternative database deployment methodology. Although Alexander's approach seems a bit more complicated (then, say, my technique), it may offer a more comprehensive solution. I didn't have much time to delve into Alexander's solution, but you may want to give it a try.
Additional references:
Get Your Database Under Version Control by Jeff Atwood
Evolutionary Database Design by Martin Fowler and Pramod Sadalage
11 Tools for Database Versioning by secretGeek
| * | I wrote dbsetup.vbs in a rush a few years ago, so now, when I'm looking at the source code, I can see how imperfect it is. Frankly, I'm a bit ashamed that this code has been made public, but I also do not want to waste time rewriting the parts that work. Anyway, if you are eager to criticize the programming style and approach, I'm with you. |
| ** | Does the title Deploying Database Developments sound right to you? Development is a process, so how do you deploy a process? You can deploy an application, code, etc, but development... I don't think so. |
Hi Alek,
ReplyDeleteArticle name was proposed by simple-talk.com editor.
Originally it was "Database deployment and deployment integration".
There were substantial changes made to the original writing, so I may offer you the original version of that text.
Alexander Karmanov
"Database deployment and deployment integration" makes more sense to me, but, hey, I'm an ESL speaker, so I wouldn't argue with the Brits (I assume simple-talk editors are British). :-) Thanks for the article, BTW; it's nice to see other people addressing the database deployment issue. I would like to see the original text.
ReplyDelete