Automating Multi-Platform Database Integration Tests

When you have to support multiple database platforms it can be challenging to automate integration tests against more than one DBMS. Dovetail is using build automation driven by with NAnt and virtual machines running on VMWare Server to automate the setup and execution of our integration test suite against multiple versions of Microsoft SQL Server and Oracle databases. I want to share some of the lessons learned from our journeys along the way .

Integration Tests

Make your integration tests data driven. If at all possible setup your tests so that they create their own test data. Tests responsible for their own data setup are simply less fragile. A lot of dev cycles got wasted trying to understand why a test is failing that was caused by one test messing up the data another was dependent on.

Automate automate automate. We use NAnt to build our application, run our unit and integration tests, create the installer, and ship the product to test and production, this includes copying the documentation out to our public web site. Having all this in place, makes it easy to add just one more step to test the build against different databases.

Use Continuous Integration (CI). It doesn’t always work on another person’s machine. Getting your build automated is the first step. Having your build do a test run on every check in on a non-developer machine is priceless. We currently use a mix of CC.Net (Cruise Control) and TeamCity.

Commit your build scripts and CI configuration to source control.

Try to make sure that your builds do not step on each other. Since version 1.3 CC.Net has an integration queue feature which is quite handy.

Virtual Machines Rock

VMWare’s revert to snapshot feature is your friend. A snapshot is a spot in time for that Virtual Machine that you can rollback to. It is that perfect moment in time where everything you want on a machine is setup, booted, and ready for your tests to run. Once you have a snapshot in place you can remotely tell a VMWare server to revert to snapshot using a command line tool called VMRun. Here are the NAnt targets that I use to tell a remote virtual machine to revert to a snapshot and when all done stop the virtual machine. 

property name="vmware.vmrun" value="${tools.dir}/VMWare/vmrun.exe" /

  property name="vmware.authentication" value="-h -P 902 -u __account_name__ -p __account_password__" /


  target name="setup-oracle9-virtualmachine"

      property name="virtualmachine.path" value="c:Virtual Machines2k3-oracle9i2k3-oracle9i.vmx"/

      setenv name="path" value="${oracle9.client.path};%PATH%" /

      exec program="${vmware.`}" commandline="${vmware.authentication} revertToSnapshot ${virtualmachine.path}"/

      property name="nant.onsuccess" value="teardown-virtualmachine"/



  target name="setup-oracle10-virtualmachine"

      property name="virtualmachine.path" value="C:Virtual Machines2k3-oracle10-unicode2k3-oracle10-unicode.vmx"/

      setenv name="path" value="${oracle10.client.path};%PATH%" /

      exec program="${vmware.vmrun}" commandline="${vmware.authentication} revertToSnapshot ${virtualmachine.path}"/

      property name="nant.onsuccess" value="teardown-virtualmachine"/



  target name="teardown-virtualmachine"

      exec program="${vmware.vmrun}" commandline="${vmware.authentication} stop ${virtualmachine.path}"/


If you can’t get VMRun to talk to your Virtual Machine Host.  Try disabling TCP chimney.

Keep backups of your virtual machines. It takes a long time to setup an VM, install the database, and get it just the way you want it. Things go wrong. Snapshots can get wedged by VMWare. Someone could remote to your VM and mess it up. You will want a copy of that known good version of your VM. Trust me.

Get yourself a dedicated fast virtual machine server. We have a quad processor server with speedy hard drives and lots and lots memory running Windows 2003 64bit. This thing can host 8 virtual machines simultaneously without a hiccup. I recommend getting the fastest hard drives you can afford.

Not all is perfect

I have been running this type configuration for multi-platform testing for sometime. It has a set of troubles that plague us:

  • NAnt is often too darn much XML and takes experience and time to perfect long running automation scripts.
  • We have too many integration tests and they take a long time to run.
  • Sometimes the build will fail for no good reason. Sometimes it is Oracle. Sometimes it is a virtual machine revert to snapshot failure. Sometimes is random cosmic rays from the phantom zone that cause false build failures. These are very frustrating.

What did I miss? What do you want to know more about?