A Day In The Lyf

…the lyf so short, the craft so longe to lerne

Archive for the ‘Configuration Management’ Category

Managing Config Files

There’s a discussion on the altdotnet Yahoo group about managing configuration files. How do you manage updating multiple configuration files to change the appropriate values when deploying to a different environment?

The solution I hit on was to create a custom MSBuild task. When called from our build script, it looks something like this:

    <ConfigFiles Include="$(DeployDir)/**/*.exe.config"/>
    <ConfigFiles Include="$(DeployDir)/**/*.dll.config"/>
    <ConfigFiles Include="$(DeployDir)/**/web.config"/>

    <HibernateFiles Include="$(DeployDir)/**/hibernate.cfg.xml"/>

    <Log4NetFiles Include="$(DeployDir)/**/log4net.config"/>

<Target Name="UpdateConfig">
        Environment="$(Environment)" />
        NamespacePrefix="hbm" />
        Environment="$(Environment)" />

Notice that each call to UpdateConfig takes the list of config files that will be changed and a config mapping file. That mapping file is what is read to update the config files given the environment. Here’s an example of what the mapping file looks like:

    <add xpath="configuration/appSettings/add[@key='dbserver']">
            <add key="dbserver" value="stagingServer"/>
            <add key="dbserver" value="productionServer"/>

Each config file is scanned looking for each XPath expression in the mapping file. On each match, the entire node (and all its child nodes) of the original config file are replaced with the node under the appropriate environment tag in the mapping file. It’s a bit verbose, but simple enough, and it supports as many environments as you want to have.

The MSBuild task itself is fairly simple, delegating most of its work to a separate object called XmlMerger:

private void MergeChanges()
    foreach (ITaskItem item in ConfigFiles)
        string configFile = item.ItemSpec;
        XmlDocument configFileDoc = LoadXmlDocument(configFile);
        XmlDocument configMappingDoc = LoadXmlDocument(configMappingFile);

        XmlMerger merger = new XmlMerger(configFileDoc, configMappingDoc);
        if (!string.IsNullOrEmpty(NamespaceUri) && !string.IsNullOrEmpty(NamespacePrefix))
            merger.AddNamespace(NamespacePrefix, NamespaceUri);


XmlMerger just finds the nodes that need updating and replaces them from the mapping file. Notice that it also accepts namespace information (see the NHibernate example in the build script snippet above), which is occasionally needed:

public class  XmlMerger
    private readonly XmlDocument configFile;
    private readonly XmlDocument configMapping;
    private readonly XmlNamespaceManager namespaces;

    public XmlMerger(XmlDocument configFile, XmlDocument configMapping)
        this.configFile = configFile;
        this.configMapping = configMapping;
        namespaces = new XmlNamespaceManager(configFile.NameTable);

    public void AddNamespace(string prefix, string uri)
        namespaces.AddNamespace(prefix, uri);

    public void Merge(string environment)
        foreach (XmlNode mappingNode in configMapping.SelectNodes("/configOptions/add"))
            string xpath = mappingNode.Attributes["xpath"].Value;
            XmlNode replacementNode = FindNode(mappingNode, environment).FirstChild;
            XmlNode nodeToReplace = configFile.SelectSingleNode(xpath, namespaces);
            if (nodeToReplace != null)
                ReplaceNode(nodeToReplace, replacementNode);

    private void ReplaceNode(XmlNode nodeToReplace, XmlNode replacementNode)
        nodeToReplace.InnerXml = replacementNode.InnerXml;

        // Remove attributes not in nodeToReplace.  There's probably a cleaner solution,
        // but I didn't see it.
        for (int i = nodeToReplace.Attributes.Count - 1; i >= 0; i--)
            if (replacementNode.Attributes[nodeToReplace.Attributes[i].Name] == null)

        foreach (XmlAttribute attribute in replacementNode.Attributes)
            if (nodeToReplace.Attributes[attribute.Name] == null)

            nodeToReplace.Attributes[attribute.Name].Value = attribute.Value;

    private XmlNode FindNode(XmlNode node, string xpath)
        XmlNode result = node.SelectSingleNode(xpath);
        if (result == null)
            throw new ApplicationException("Missing node for " + xpath);
        return result;

That's it. Now the whole process is hands-free, so long as you remember to update the mapping file when needed. The config files we put into subversion are set to work in the development environment (everything is localhost), so anybody can checkout our code and start working without having to tweak a bunch of settings first. The deployment process calls our build script, which ensures that the appropriate config values get changed.


Written by Brandon Byars

January 10, 2008 at 9:39 pm

Posted in .NET, Configuration Management

Tagged with

Configuring ActiveRecord to work with SQL Server 2005

As much as possible, I like a zero-install configuration. In other words, I want to simply checkout a codebase, run an automated build process, and start working. Ideally, I’d like to be able to do that on a clean machine.

It doesn’t always work, of course. For instance, even though most of our production code is written in .NET, we use Ruby extensively for automation. Installing Ruby is one of those dependencies that we live with. But installing Ruby isn’t enough; we also need Rails (or at least ActiveRecord) for some data management scripts we have, Watir, and some fixes for ActiveRecord to work with SQL Server.

All of that can be done fairly easily by somebody who knows what they’re doing, but new developers often don’t know what they’re doing, and I strive to be dispensable. We wrote a script that configured
Ruby and the necessary libraries to work for our development environment.

First, we needed to install the necessary gems. This is quite easy to do on the command line, but it took me a little digging before I figured out how to do it in code:

needed_gems = ["rails"]

require "rubygems"

needed_gems.each do |gem|
  puts "Installing gem #{gem}..."
  Gem::GemRunner.new.run ["install", gem, "--include-dependencies"]

SQL Server requires an ADO adapter that doesn’t ship with Ruby. You can read about it here. All that’s needed is to download the ADO.rb file (which we have versioned next to our setup script) and copy it to the right place, creating the directory if needed:

setup_dir = File.dirname(__FILE__)
ruby_dir = "C:/ruby/lib/ruby"
ruby_version = 1.8

# The ADO adapter needed for using dbi (the standard db access)
# with SQL Server, which does not come with ruby by default.
# See http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServer.

puts "creating ADO adapter..."
ado_dir = "#{ruby_dir}/site_ruby/#{ruby_version}/DBD/ADO"
system "if not exist #{ado_dir} mkdir #{ado_dir}"
system "copy /Y #{setup_dir}/ADO.rb #{ado_dir}"

Finally, we use SQL Server 2005, and we want to use Windows Authentication for all of our Ruby scripts. Neither SQL Server 2005 nor Windows Authentication is supported by rails out of the box. The problem, described on the SQL Server wiki for rails, is the connection string rails builds. At first, we were taking the suggestions of some of the comments on the wiki and changing the sqlserver_adapter.rb file that ships with rails. This obviously isn’t ideal, so now we monkey-patch it in our code that accesses the database:

module DBI
  # We have to monkey patch this because the SQL Server adapter that comes
  # with rails (active_record\connection_adapters\sqlserver_adapter.rb)
  # doesn't work with SQL Server 2005 or with Integrated Security yet.
  class << self
    alias_method :original_connect, :connect

    def connect(driver_url, username, password)
      # Change to SQL 2005
      driver_url.sub! "SQLOLEDB", "SQLNCLI"

      # Change to Windows Authentication
      driver_url.sub! /User Id=[^;]*;Password=[^;]*;/, "Integrated Security=SSPI;"

      original_connect(driver_url, username, password)

And that’s it. You still can’t checkout the codebase and start working on a clean machine, but it’s not bad. Install Ruby, run setup.rb. All database code has been patched to deal with our environment.

Written by Brandon Byars

October 23, 2007 at 7:13 pm

Auto-merging fixes

Paul Gross recently blogged about a rake task to automatically merge changes to the trunk if the change was made in a branch. This seemed like a useful feature, even though we don’t use rake.

Fixing productions bugs and merging is no fun, but why not take some of the pain out of the process? Depending on the developer and their mood, we either fix the bug in the trunk and merge to the branch, or fix it it the branch and merge to the trunk. Where it really gets ugly is when we have to merge two release branches back, because we make our release branch a few days before actually pushing it to production. Any urgent bug fix requiring a merge during that time has to be merged both to the new release branch as well as to the release branch currently in production.

Using Paul’s code as a base, I wrote automerge.rb, which, by default, either merges to the trunk (if you’re in a branch), or merges to the latest release branch (if you’re in the trunk). Alternatively, you can pass a release number, and automerge.rb will merge to that release branch. In all cases, you have to have the working copy checked out on your machine, and, if you’re on Windows, you need to make sure to put patch in your path.

The script assumes that your directory structure looks something like the following:


The Release-major-minor format is just our branch naming standard; it’s easy to change.

if !ARGV[0].nil?
  dest_dir = "branches/Release-#{ARGV[0]}"
  replace_pattern = /(trunk|branches).*$/i
elsif %x[svn info].include? "branches"
  dest_dir = "trunk"
  replace_pattern = /branches.*$/i
elsif %x[svn info].include? "trunk"
  pattern = /^Release-(\d+).(\d+)\/$/

  branches = %x[svn list http://devtools01:8080/svn/lazarus/branches]
  releases = branches.split.find_all {|branch| branch =~ pattern}

  # sort them in reverse order by major, minor
  releases.sort! do |first, second|
    first_major, first_minor = pattern.match(first)[1..2]
    second_major, second_minor = pattern.match(second)[1..2]

    if first_major != second_major
      second_major.to_i <=> first_major.to_i
      second_minor.to_i <=> first_minor.to_i

  dest_dir = "branches/#{releases[0]}"
  replace_pattern = /trunk/

puts "Merging changes into #{dest_dir}.  Don't forget to check these in."
dest_path = Dir.pwd.gsub(replace_pattern, dest_dir)
puts dest_path
system "svn diff | patch -p0 -d #{dest_path}"

The only tricky part here is figuring out the latest release branch, which is done by using the svn list command followed by a custom sort.

Written by Brandon Byars

October 22, 2007 at 8:06 pm

Posted in Configuration Management, Ruby

Tagged with ,

.NET Database Migrations

Pramod Sadalage and Scott Ambler have suggested using a series of numbered change scripts to version your database. Start with a base schema, and every subsequent change gets its own change script, grabbing the next number. That version number is stored in a table in the database, which makes it easy to update—you just run all change scripts, in order, greater than the version stored in your database.

The Ruby on Rails team implemented this technique in their migrations code. It’s quite elegant. This blog uses a Rails application called Typo; here’s one of its migrations:

class AddArticleUserId < ActiveRecord::Migration
  def self.up
    add_column :articles, :user_id, :integer

    puts "Linking article authors to users"
    Article.find(:all).each do |a|

  def self.down
    remove_column :articles, :user_id

That migration is called 3_add_article_user_id.rb, where 3 is the version number. Notice that it’s written in Ruby, not in SQL. It adds a column called user_id to the articles table and updates the data. The data update is particularly interesting—we get to use the ActiveRecord O/RM code instead of having to do it in SQL (although you can use SQL if you need to). The Rails migration code can also rollback changes; that’s what the down method is for.

The problem I’ve always had with this scheme is that we have many database objects that I’d like to version in their own files in our source control system. For example, here’s our directory structure:


We have several files in each directory, and it’s convenient to keep them that way so we can easily check a subversion log and see the history of changes for the database object. For us to use the migrations scheme above, we’d have to create a stored procedure in a migration, and later alter it in a separate migration. Since the two migrations will be in separate files, our source control wouldn’t give us a version history of that stored procedure.

We came up with a hybrid solution. Schema changes to the tables use a migration scheme like Rails. Database objects are versioned in separate files. Both the schema changes and the peripheral database object changes are updated when we update the database.

For this to work, we have to be a little careful with how we create the database objects. We want them to work regardless of whether we’re creating them for the first time or updating them, which means ALTER statements won’t work. The solution is simply to drop the object if it exists, and then create it. This is a fairly common pattern.

I wrote an NAnt and MSBuild task to do the dirty work. It runs both the schema migrations and the database object updates. Both are optional, so if migrations are all you want, that’s all you need to use. It expects all migrations to be in the same directory, and match the pattern 1.comment.sql, where 1 is the version number. It will be stored in a database table whose default name is SchemaVersion, with the following structure:

CREATE TABLE SchemaVersion (
  Version int,
  MigrationDate datetime,
  Comment varchar(255)

I’ve only tested it on SQL Server, but I think the task should work for other DBMS’s as well (it uses OLEDB). Migrations can contain batches (using the SQL Server GO command) and are run transactionally. Unlike the Rails example, the .NET migrations use SQL, and I don’t yet have any rollback functionality.

You can include any extra SQL files you want in the DatabaseObjects property. Both NAnt and MSBuild have convenient ways to recursively add all files matching an extension.

Here’s an NAnt example:

<target name="migrate" description="Update the database">
    <loadtasks assembly="Migrations.dll" />
            <include name="db/functions/**/*.sql"/>
            <include name="db/procedures/**/*.sql"/>
            <include name="db/triggers/**/*.sql"/>
            <include name="db/views/**/*.sql"/>

And here it is using MSBuild:

    <DatabaseObjects Include="db/functions/**/*.sql"/>
    <DatabaseObjects Include="db/procedures/**/*.sql"/>
    <DatabaseObjects Include="db/triggers/**/*.sql"/>
    <DatabaseObjects Include="db/views/**/*.sql"/>

<Target Name="dbMigrate">
        TableName="version_info" />

The source code and binaries can be found here.

Written by Brandon Byars

April 14, 2007 at 10:35 pm