title: Strategy to run tests on a database
body: <p>I have started working on an existing project with over 1800 functional/integration tests. These have been coded with MSTest.</p>
  
  <p>Many of those connect directly to the SQL Server database. The database is generated by a code generator which amongst many things create the database. Generating the db is slow and cumbersome. </p>
  
  <p>This as the following problems:</p>
  
  <ul>
  <li>The test clean the db which mean we must maintain a seperate db for tests and another for using the application. The procedure right now is to change the database when changing between running test and running the app.</li>
  <li>Each branch needs to have it's own db as the db model can be different in each db (which means 2 db per branch with step 1)</li>
  <li>It's slow</li>
  <li>An installation of SQL Server and of the db must be present for the test to run </li>
  </ul>
  
  <p>I would like the existing tests not to be dependent on such on installation, run faster if possible and not have to deal with maintaining databases through the code generator, juggling connection strings, etc.</p>
  
  <p>I am trying to acheive this as quickly as possible since <strong>a rewrite of the tests is not in the budget</strong>. <strong>I have already introduced mocking to help new test be less dependent on the database, my problem now is for the existing tests.</strong></p>
  
  <p>My first though was to change our base unit test class to connect to a SQLite db which would be created by the code generator which already generates the main db instead of the SQL Server db. The SQLite could then be deleted and recopied to the test folder between each run. This would have been fast, not require having 2 SQL Server database, in fact if just running the tests no SQL Server installation would have been required.</p>
  
  <p>My problems were that the generated code uses many concepts not included in SQLite; T-SQL, SQL Server specific syntax, schemas, stored procs and embedded clr assemblies. </p>
  
  <p>I then tried SQL Server CE 4, which had many of the same limitations as SQLite.</p>
  
  <p>Is there any other alternatives available other than rewriting the code to be compatible with SQLite (or CE), rewriting the existing tests, or a system in which we maintain 2 seperatedb ? </p>
  
  <p>EDITs: Changed unit test to functional tests, clarified some things. Put some things in bold. 
  I agree that those tests aren't proper unit tests. I agree that mocking would have been nice here. What I am trying to do is try to fix the mess I am faced with.</p>