THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude]

[This blog post assumes a familiarity with the terms Continuous Integration (CI), MSBuild & MSTest. If you don’t have a good appreciation of those terms then this blog post probably isn’t for you anyway so don’t worry about it!]

Over the past few days I have been working to get database unit tests executing as part of our Continuous Integration (CI) build and in this blog post I’ll explain how I went about it because it is decidedly not straightforward.

We are using the DB Tools in Visual Studio 2010 (aka DBPro or Datadude - I will refer to it as Datadude from here on in) which includes unit testing functionality. The rest of this blog post also assumes a familiarity with database unit testing in Datadude although if you want to do some background reading an excellent place to start is Jamie Laflen’s whitepaper Apply Test-Driven Development to your Database Projects on MSDN.


We got to the point where we had a C# test project containing database unit tests that executed successfully inside Visual Studio. For demonstration purposes I have put together a solution that contains a simple database project and a test project containing a database unit test:

solution explorer

And just to prove that the test executes successfully in Visual Studio:

Visual Studio Test Results screenshot

N.B. The code for the database object(s) and the unit test itself are not important, that is outside the scope of this blog post.

At this point we have some tests that run in our development sandbox, the configuration for which is done using the Database Test Configuration dialog:

database test configuration choice

database test configuration dialog

Those config settings are stored in the app.config file which exists as part of our test project (screenshot of which is at the top of this blog post). If we take a look inside that file we can see the ConnectionString that we defined in the Database Test Configuration dialog:

app config connectionString DatabaseUnitTesting

Note the <DatabaseUnitTesting> element, we’re going to be coming back to that a little later!!


Once we have the tests running in Visual Studio the next step is to get them running inside a CI build and for that we call out to MSTest.exe from inside an MSBuild script:

msbuild script mstest

When we execute that script we get some positive results and all looks peachy:

msbuild mstest output


OK, we now have a test that runs successfully both in Visual Studio and by calling MSTest.exe directly from MSBuild. The problem I have to solve now though (and this is the real crux of this blog post) is that the test is still running against the server I specified in app.config; I don’t want this, I want the test to run against a server of my choosing, namely my build server. For this I had to call on the help of the aforementioned Jamie Laflen (who, luckily, I have met in the past) and he gave me guidance on how to achieve it. Below are the steps that you need to take.

Firstly you need to tell Datadude that you want to specify some different credentials and that is done by adding an attribute AllowConfigurationOverride="true" to the <DatabaseUnitTesting> element of app.config like so:

app config DatabaseUnitTesting AllowConfigurationOverride

Don’t forget to build your test project after changing the app.config file!!!

This will cause datadude unit testing framework to go and search for a file called *.dbunittest.config where the * indicates either:

  • The name of machine upon which the tests are being run or
  • The name of the user running the tests

According to Jamie L datadude runs the following precedence checks:

  1. Is an override specified in app.config? If not, use app.config
  2. Does an override file exist called <machinename>.dbunittest.config? If so, use it, if not…
  3. Does an override file exist called <username>.dbunittest.config? If so, use it, if not…
  4. Fail!

“So”, you may be asking, “what goes in this *.dbunittest.config file then?”. Fair question, that’s where the <DatabaseUnitTesting> element that I mentioned earlier comes in. Copy that element from the app.config file into your *.dbunittest.config file, remove the AllowConfigurationOverride="true" attribute and change the ConnectionString property accordingly:

dbunittest.config ConnectionString

The next step is to tell MSTest.exe that there is another file that it needs to be aware of when it runs the tests and to do that we need to create a custom testsettings file that will be used instead of the default Local.testsettings. To create a new testsettings file use the Add New Item dialog:

add new test settings

In the Test Settings dialog go to ‘Deployment’, click ‘Enable deployment’, then ‘Add File…’. Browse to your *.dbunittest.config file and add it to the project:

test settings dialog

At this point you’re good to go and all you need to do edit your call to MSTest.exe and tell it to use your new testsettings file:

msbuild script mstest testsettings

Notice the extra information in the output:

msbuild script output

You’ll have to trust me that it used the new config file and therefore the new ConnectionString, although having said that you do get some useful information in the test results file (*.trx) that MSTest.exe creates:

mstest test results trx output


I think its fair to say that this process is a little fiddly to say the least so I’m hoping that Microsoft come up with a better solution in the future, one that integrates better into the whole testing infrastructure. Meanwhile you can download the demo that I put together for this blog post from my SkyDrive.

Hope that helps! Comments are welcome.

Jamiet

UPDATE: Atul Verma has written a 3-part blog series on how to write your database unit tests so if Jamie Laflen's whitepaper (linked to above) doesn't tickle your fancy try reading Atul's series instead:

Published Friday, August 20, 2010 8:41 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alexander Kuznetsov said:

Hmmm, that is quite tricky, is it not. Plenty of possibilities to make a mistake.

In our team we just run all unit tests against a local instance of SQL Server, and this includes TeamCity builds. This is much simpler and so far does not break. We dont use Data Dude, we just use Visual Studio and NUnit - less moving parts, less possibilities to make mistakes.

August 20, 2010 3:47 PM
 

jamiet said:

Alexander,

Definitely tricky, yes. And hence why I wanted to put a blog post up about it because I don't believe that this is documented anywhere else (at the time of writing anyway).

August 22, 2010 4:59 AM
 

SSIS Junkie said:

Three days ago I wrote a blog post entitled Setting up database unit testing as part of a Continuous

August 23, 2010 7:42 AM
 

Harry said:

Great post. VS2010 has more advantages over VS2008 in database testing.

I would like to introduce another DB UnitTest tool, named AnyDbTest. It comes from www.anydbtest.com . It is worth having an evaluation.

AnyDbTest is declarative style testing tool. We will not need to program at all. What we do is to express what we want to test, rather than how to test. We only need to configure an Xml test file to tell AnyDbTest what we want to test. So AnyDbTest is the right choice for DBA or DB developers.  

Features specific to AnyDbTest:

*Writing test case with Xml, rather than Java/C++/C#/VB test case code.

*Many kinds of assertion supported, such as StrictEqual, SetEqual, IsSupersetOf, Overlaps, and RecordCountEqual etc.

*Allows using Excel spreadsheet/Xml as the source of the data for the tests.

*Supports Sandbox test model, if test will be done in sandbox, all database operations will be rolled back meaning any changes will be undone.

*Unique cross-different-type-database testing, which means target and reference result set can come from two databases, even one is SQL Server, another is Oracle.

November 11, 2010 1:24 AM
 

Ken Powers said:

Jamie:

Some users are reporting that the *.dbunittest.config files only work if you strip out certain elements in the file.  

Please see the following post on MSDN:

http://social.msdn.microsoft.com/Forums/en/vstsdb/thread/318a8669-638f-4e56-9536-a9eb59de95bb

Have you experienced similar behavior?

February 14, 2011 2:45 PM
 

jamiet said:

Hi Ken,

agreed, I suspect that it won't work with all that <Configuration> stuff there but then - I've never seen it there anyway.

-Jamie

February 14, 2011 6:06 PM
 

Jesus said:

Jamie, have you found a way to automate the unit testing of SSIS packages to be incorporate in a CI cycle? There's a Codeplex tool (SsisUnit), but it's not very robust. Any ideas?

March 23, 2011 1:33 PM
 

jamiet said:

Jesus,

I'm afraid SsisUnit is the only method I know of - and I have never actually used it. If you find anything please do let me know.

Jamie

March 23, 2011 1:50 PM
 

JSug said:

Are you aware of any way to make this work if you have mutliple DB and DB test projects in a single solution? I find that what inevitably happens is the test deployment process overwrites the multiple copies of <machinename>.dbunittest.config and all the tests fail except for whichever ones didn't have their config overwritten. Is the best practice to only have a single DB per solution?

May 2, 2011 1:03 PM
 

jamiet said:

Hi JSug,

I have used multiple DBs with multiple test projects and it worked OK. Having said that I envisage, given the complexity of this, there may be problems if you want to run those tests against multiple servers. I can't say for sure though as I haven't tried it.

JT

May 2, 2011 3:45 PM
 

JSug said:

So... I'm confused. You have a solution with multiple DB test projects, and you have been able to get it working with Team Build overriding and directing the tests to a different server? The problem I run into is that each set of tests has a different initial catalog in the connection string, so when one file overwrites the others, all the tests are trying to access the same DB, and most of them will fail.

May 2, 2011 7:19 PM
 

jamiet said:

Hi JSug,

Firstly, I have never used Team Build to do this.

"The problem I run into is that each set of tests has a different initial catalog in the connection string"

If memory serves we had a <machinename>.dbunittest.config per project and it worked OK. It used the correct configfiel as appropriate.

Hope that helps

JT

May 3, 2011 4:12 AM
 

SSIS Junkie said:

Over the past eighteen months I have worked on four separate projects for customers that wanted to make

January 2, 2012 11:07 AM
 

SSIS Junkie said:

Test-Driven Development (TDD) is a software development practise that has been around for a few years.

January 26, 2012 4:36 AM
 

salma said:

hi all. I know that ssis unit testing is mainly used with sql server. I am wondering if it is possible to use it with oracle or DB2 ????

December 12, 2012 4:46 AM
 

SSIS Junkie said:

Good news was announced yesterday for those that are using SSDT and want to write unit tests, unit testing

December 14, 2012 7:47 AM
 

SQL and the like said:

Late in December, one the long awaited portions of functionality inside SSDT was released to an eager

January 7, 2013 2:09 PM
 

wattyjnr said:

Hi Jamie, great post as always!

I am doing my database unit tests alongside SSDT and I wondered is there anyway to target the pre deployment of the database (before running the actual tests) using a publish profile?

My current frustration is that the database deployment seems to use the database project's debug/release configuration settings, which differs to the options I have selected for my publish profile to my dev environment for example. Does that make sense?

Cheers.

April 2, 2013 11:45 AM
 

Matthew said:

In order to add the testsettings you must add new item to the solution not to the project

October 17, 2014 3:44 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement