title: Speed up integration test that hits real SQL Server database
body: <p>My application has a business layer that heavily talks to a database. This layer is implemented as a bunch of <code>ADO.NET</code> calls wrapped with <code>Dapper</code>. To initialise the database schema, I use <code>Entity Framework 6</code> Code First, which has a nice side-effect of being able to use my C# POCO both as DDL and DAO.  </p>
  
  <p>The raw SQL I use in <code>Dapper</code> is a mixture of plain old SQL, some user-defined functions, and things specific to <code>SQL Server</code>, e.g. the built-in functions and the full-text indices. </p>
  
  <p>In my Jenkins CI build, I have an integration test suite that would test this layer using <code>NUnit</code>. To ensure tests are isolated from each other, the database initialisation should happen on the <code>[SetUp]</code> method.</p>
  
  <p>I went through several options to implement the integration test:</p>
  
  <ol>
  <li>Use in-memory database. Pros: test setup is fast, running test logic is fast. 
  Cons: this is a non-option, because the SQL commands that I use are specific to <code>SQL Server</code>.</li>
  <li>Use <code>SQLite</code> or <code>SQL Compact</code>. Pros: syntax is similar to <code>SQL Server</code>, setting up and tearing down is just a matter of deleting files. Cons: also non-option the moment I start making use of user-defined functions and full-text index.</li>
  <li>Use local <code>SQL Express</code> server just to serve the test. Pros: behaviour is almost guaranteed to match production setup, including built-in functions and full-text index. Cons: set up is slower and more complicated. </li>
  </ol>
  
  <p>I went with option 3, with the following approach:</p>
  
  <ol>
  <li>On <code>[SetUpFixture]</code>'s <code>[SetUp]</code>, initialise a seed database. Once initialised, create a backup of this database, save it as a file. This only happens once on the whole namespace of the tests. </li>
  <li>On each <code>[TestFixture]</code>'s <code>[SetUp]</code>, restore the backup. Since this happens every time before a test is run, each test is isolated from the other tests.</li>
  </ol>
  
  <p>The disadvantage is running the test suite now becomes very slow. Step no. 2 above takes 2-5 seconds for every test. On top of that, each test also takes some time to execute the test fixture itself, which is quite a bit slower that typical unit test because it hits real database.</p>
  
  <p>In my current state, a moderately-sized test suite of 143 tests takes 13 minutes on my build machine, which is a VM on Azure, specced as A3 (4 cores 7 GB), running Windows Server 2008 R2.   </p>
  
  <p>Is there anyway that I can speed up this process other than pumping more hardware to the machine? </p>