Setting up a diverse database testing environment

Question

I'm writing some code that needs to work against an array of different database products.

• MySql
• Sql Server 2000 to 2008
• PostgreSQL
• Oracle 9i & 10g
• Jet 4.0 (MS Access)
• MSDE
• Sybase Adaptive Server Anywhere
• Sybase Sql Anywhere
• Progress OpenEdge

We have a set of generic integration tests that we need to run against each database product, as part of a continuous integration build.

I'm sure other people have had to set-up similar test environments and I would like to tap into some of that wisdom - what strategies do you end up using, what worked well or did not work well?

Some thoughts:

• Separate virtual machines for each of the products, each allocated a small amount of memory (easier to manage for certain scenarios, or where we have slightly different setup's for individual products).
• A couple of virtual machines or even a single virtual machine for all the products (i.e. perhaps an ubuntu box for postgresql & mysql, and a windows 2008 server machine for the remaining products) - I like one or two vm's because this is a more portable environment for running the tests i.e. when on the road / off-site, as my laptop would probably crawl to a halt running 8 or 10 small VM's.

And finally how have you tackled the prohibitive cost of some of these commercial products i.e. Oracle or Progress OpenEdge, and are the previous versions still available i.e. are there free "single-developer" editions available, or cheaper routes to purchasing these products?

Answer 1

• We also use vmware for our servers, one vmware host per database. You should be OK with putting several databases on one vmware.
• You shouldn't have much a problem with expensive software licenses. Oracle, for example, allows you to have a development license for all of their products. So long as you are not running a production DB on your laptop vmware, you should be OK. Of course, IANAL.
• For Oracle, get the Enterprise Edition if that's what your customers will be using (most likely).
• Be sure and take advantage of vmware snapshots. You can get the databases configured perfectly for running your tests, run your tests, and then revert the databases back to the pre-test state ready for running your tests again.

Answer 2

What we do here is (mostly) a VM for each configuration required, we also target different operating systems so have something like 22 configurations we are testing. These VM's are hosted in an ESX server, and scripted to start and stop when required so that they are not all running at once. It was a lot of work to set up.

In terms of cost of the Database servers, the kind of software we are building required top-end versions so the testing costs have been factored into the overall dev costs (we just had to "suck it up")

Answer 3

Oracle does have a free 'Express Edition'. It doesn't have all of Oracle's functionality but then if you are also running against Jet then that shouldn't be an issue.

Answer 4

Architecturally, it may be better off creating an integration testing farm of server(s) that are powerful enough to run the variously configured VMs. It could be fronted with a queue process listening for integration testing requests (which could be triggered using svn commits or other source control check in triggers).

For the most accurate range of testing, you probably want to have a separate VM for each configuration. This will reduce the risk of conflicting configs and also increase the flexibility of running a custom set of VMs (e.g. Oracle + MySQL + PostgreSQL and not the others, etc.). Depending on your build process, it may also allow you to run 10-minute builds.

A benefit of running an integration test farm is that if you're on the road using your laptop, you can trigger off the integration build after code check-in, run all the tests and have it notify you of the results. You can also archive each request and the results to help diagnose failing builds.