<p>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 <a href="https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-writer-service" rel="nofollow noreferrer">Microsoft SQL VSS writer</a> 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.</p>

<p>If you have a particularly large dataset, it may be better to <a href="https://devops.stackexchange.com/questions/742/what-practices-or-tools-enable-continuous-deployment-of-databases/1220#1220">leverage your storage filer to use delta-clones</a> if your filer has this feature and it can help to address the size/time issue you are experiencing with backups.</p>

<p>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 <a href="https://docs.saltstack.com/en/latest/topics/reactor/" rel="nofollow noreferrer">Reactor</a> will allow you to broadcast a beacon when your backup/snapshot completes and trigger a build (Puppet lacks this capability).</p>

<p>One option for spooling up a VM each time is to leverage a system like <a href="https://www.theforeman.org/" rel="nofollow noreferrer">The Foreman</a> which has a <a href="https://github.com/ace13/foreman_hyperv" rel="nofollow noreferrer">HyperV</a> 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 <a href="https://theforeman.org/api/1.15/" rel="nofollow noreferrer">The Foreman's REST API</a> 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.</p>

<p>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. </p>

<p>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.</p>

CC BY-SA 3.0
2017-11-08T20:49:25.023Z
2017-11-08T20:49:25.023Z
James Shewey
<h3>Process</h3>

<p>Our current process is:</p>

<ol>
<li><p>Build process produces a windows setup (msi).  </p>

<p>This setup consists of a web site deployment and also database scripts - which can create a new database or upgrade an existing version. </p></li>
<li><p>The QA person needs to snapshot their Application server and their Database server.</p></li>
<li>The QA person then runs the setup on their own windows server.</li>
<li>The setup installs a web site and updates the database. </li>
</ol>

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

<h3>Current Technology and Environments</h3>

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

<p>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</p>

<h3>Drawbacks</h3>

<p>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.</p>

<h3>My Thoughts</h3>

<p>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.</p>

<h3>Questions</h3>

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

<p>Additionally:</p>

<ol>
<li>What other tools are available for example?</li>
<li>Also what recommendations would you make?</li>
</ol>

CC BY-SA 3.0
2017-11-08T15:46:20.487Z
Interesting to hear answers on this...  I feel we have similar predicament.  I would assume the website is IIS being Microsoft stack?
Andez
CC BY-SA 3.0
2017-11-08T15:49:49.830Z
which virtualisation system are used ? you're saying your QA is mixed, that not very clear to think about this approach. (hyper V or Vsphere would have different systems to tackle this kind of problems.)
Tensibai
CC BY-SA 3.0
2017-11-08T17:07:00.413Z
HyperV and it's IIS
user5078
<testing><backup>
How to test database applications in QA testing environments?
user5078
# How to test database applications in QA testing environments?

(Asked by: username_0 on 2017-11-08)

### 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?

**Tags:** testing, backup

> **Comments:**
> **username_1**: Interesting to hear answers on this...  I feel we have similar predicament.  I would assume the website is IIS being Microsoft stack?  
> **username_2**: which virtualisation system are used ? you're saying your QA is mixed, that not very clear to think about this approach. (hyper V or Vsphere would have different systems to tackle this kind of problems.)  
> **username_0**: HyperV and it's IIS  

---

## 1 answers

### Answer by username_3 on 2017-11-08. Score: 2

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](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-writer-service) 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](https://devops.stackexchange.com/questions/742/what-practices-or-tools-enable-continuous-deployment-of-databases/1220#1220) 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](https://docs.saltstack.com/en/latest/topics/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](https://www.theforeman.org/) which has a [HyperV](https://github.com/ace13/foreman_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](https://theforeman.org/api/1.15/) 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.


---

