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).
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
