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

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 2

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 3

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

Answer 5

Oracle license excerpt:

We grant you a nonexclusive, nontransferable limited license to use the programs only for the purpose of developing, testing, prototyping and demonstrating your application, and not for any other purpose.

So, you can download full Oracle versions 9, 10 and 11 and use them free of charge as stated in license. Check downloads section at www.oracle.com

Answer 6

It sounds like you have a good idea of what to do already. I'd suggest the following VM layouts:

A Windows Server 2008 box hosting the following:

• MSDE
• Jet 4.0 (MS Access)
• Sybase Adaptive Server Anywhere
• Sybase Sql Anywhere
• Progress OpenEdge

On an Ubuntu or Red Hat box:

• MySql
• PostgreSQL
• Oracle 9i Download it here
• Oracle 10g (Use the free express edition)

I don't see any need to test against the full blown SQL and Oracle editions. The free editions are stripped down, so there is very little risk of that code breaking when you run it against the full version of those servers. If you had tested against the full server and went down to the free stuff, then yes some things might break, but by testing with the least common denominator, you should ensure good quality.