jenkins - Speed up integration test that hits real SQL Server database -

my application has business layer heavily talks database. layer implemented bunch of ado.net calls wrapped dapper. initialise database schema, use entity framework 6 code first, has nice side-effect of being able use c# poco both ddl , dao.

the raw sql use in dapper mixture of plain old sql, user-defined functions, , things specific sql server, e.g. built-in functions , full-text indices.

in jenkins ci build, have integration test suite test layer using nunit. ensure tests isolated each other, database initialisation should happen on [setup] method.

i went through several options implement integration test:

1. use in-memory database. pros: test setup fast, running test logic fast. cons: non-option, because sql commands use specific sql server.
2. use sqlite or sql compact. pros: syntax similar sql server, setting , tearing down matter of deleting files. cons: non-option moment start making use of user-defined functions , full-text index.
3. use local sql express server serve test. pros: behaviour guaranteed match production setup, including built-in functions , full-text index. cons: set slower , more complicated.

i went option 3, following approach:

1. on [setupfixture]'s [setup], initialise seed database. once initialised, create backup of database, save file. happens once on whole namespace of tests.
2. on each [testfixture]'s [setup], restore backup. since happens every time before test run, each test isolated other tests.

the disadvantage running test suite becomes slow. step no. 2 above takes 2-5 seconds every test. on top of that, each test takes time execute test fixture itself, quite bit slower typical unit test because hits real database.

in current state, moderately-sized test suite of 143 tests takes 13 minutes on build machine, vm on azure, specced a3 (4 cores 7 gb), running windows server 2008 r2.

is there anyway can speed process other pumping more hardware machine?

you use transaction scope in setup , dispose in teardown method:

[myfixture] public class mytests {     private string connstring= "...";     private transactionscope ts= null;     [setup]     public void setup()     {         ts = new transactionscope(transactionscopeoption.required);     }     [teardown]     public void teardown()     {         ts.dispose();     }      [test]     public void mytest()     {         // tests etc.         using (sqlconnection conn = new sqlconnection(connstring))         {             using (sqlcommand cmd = new sqlcommand("exec myprocecure"))             {                 cmd.connection = conn;                 conn.open();                 int retval = cmd.executenonquery();                 assert.greater(retval, 0);             }         }     } } 

edit work if unit under test has it's own transaction scope.

see msdn

voting inside nested scope

although nested scope can join ambient transaction of root scope, calling complete in nested scope has no affect on root scope. if scopes root scope down last nested scope vote commit transaction, transaction committed.