How to test database applications in QA testing environments?

Question

Process

Our current process is:

1. Build process produces a windows setup (msi).

   This setup consists of a web site deployment and also database scripts - which can create a new database or upgrade an existing version.

2. The QA person needs to snapshot their Application server and their Database server.

3. The QA person then runs the setup on their own windows server.
4. The setup installs a web site and updates the database.

The purpose of the snapshots is to give them something to rollback to - to compare any behavioural changes between different versions of the application.

Current Technology and Environments

We rollout to Windows Server 2008r2 and above. The Database servers are Windows Server 2008r2 and above with SQL Server 2008r2.

Our QA is a bit mixed in terms of what Virtualisation is being used. Our teams work in two ways, one with SQL Installed on the server and another process with the traditional app server / sql servers being separate. The servers are running on mix of storage using SSD / SAN and SAS drives, each server generally has 60gb HD, running 4gb ram. SQL Servers have 8gb or more

Drawbacks

The biggest drawback is the speed at which the environments are backed up - in terms of speed and the amount of space they take up.

My Thoughts

One of my ideas is to either create an app / script or use some tool to backup / snapshot the application server and MS SQL database at the same time.

Questions

I would like to know how other people are rolling out changes with a similar process to what we go through.

Additionally:

1. What other tools are available for example?
2. Also what recommendations would you make?

Answer 1

The biggest problem with database backups is that you cannot simply snapshot them. You first have to make sure the DB is not mid-transaction when taking a snapshot, otherwise you will restore to an inconsistent state. In the case of windows, you can leverage the Microsoft SQL VSS writer to snapshot your database. You will want to be sure that when you take a snapshot of the VM, HyperV is capable of leveraging this VSS writer.

If you have a particularly large dataset, it may be better to leverage your storage filer to use delta-clones if your filer has this feature and it can help to address the size/time issue you are experiencing with backups.

In terms of deploying the new application to QA there are several ways to do this. One is to have an automated build system such as Jenkins, Travis CI or a similar tool automatically trigger a backup and deployment upon a code commit. This can be done by leveraging a configuration management tool such as SaltStack, Puppet or Chef (which all run on Windows) that can automatically set up and configure a SQL server for you and deploy your application if you are able to script a SQL install with PowerShell. Personally, I would recommend SaltStack as it's Reactor will allow you to broadcast a beacon when your backup/snapshot completes and trigger a build (Puppet lacks this capability).

One option for spooling up a VM each time is to leverage a system like The Foreman which has a HyperV plugin. You could then use SaltStack (vis-à-vis Reactor. This is probably the best way if you backup system blocks until the entire backup is complete, which I doubt) or your automated build system (Jenkins/Travis) interact with The Foreman's REST API to request a host be spun up. Once this comes up, it would automatically check in with your configuration management system to install SQL server and restore the snapshot in the new VM. If you combined this with an automated testing battery, you might even be able to implement a "Push on Green" system to repeat this process into your next environment if all the QA tests pass.

The advantage to using a piece of middleware like The Foreman is that it will allow you to deploy into any virtualization system or even the cloud and prevent you from being locked into HyperV and make your environment more portable.

I am sure there are other ways to get there, but this is one way I have used in the past to implement similar deployment workflows.