Configuring a CI server with SQLCE and MigSharp on TeamCity

Posted : Sunday, 31 March 2013 07:26:00

Recently I’ve been working for a client in London, they’re an SAAS provider and have recently developed a whole suite of new products utilising the latest features of the .net framework to compliment their existing codebase. One of the aspects of the product suite that I have been responsible for is to ensure that unit tests are an integral part of the development team strategy. Since reading The Art of Unit Testing, I have been a total convert to unit-testing, while there are a small number of scenarios in which TDD doesn’t make sense (for example intensive IO operations), in the vast majority of cases the first line of code I write when starting out on a new project will invariably be a test method. Prior to my joining them, my current client was using a SqlLite in memory database for their database tests and had built a large number of unit tests which were run as part of a TeamCity CI process. For reasons unknown the decision was made to switch to use SQL Compact Edition, from what I can tell from this point on the database based unit tests no longer worked and, Quel Dommage!, the CI build was turned off. In previous roles I have spent considerable effort establishing the practice of unit-testing and continuous integration and cannot understate the benefits it I have seen it bring. I made it a goal to re-establish the CI process. Given the choice I would probably have opted for Bamboo, it has a nice UI and integrates really well with JIRA and Confluence (both used by the SAAS client), in this case I did not have the choice so TeamCity it was.

The first thing I needed to do was get a testless build running. While not ideal, at the point I started this role there was a significant amount of development time wasted due to breaking commits, typical this would be down to Mercurial merges causing some file or another to contain invalid syntax or a developing forgetting to add a new file. In an ideal world developers would always perform a full clean and  recompile after any merge and this broken code situation wouldn’t happen but this is both time consuming and not failsafe. Generally this problem manifests itself after a developer commits and pushes a change/fix last thing in the day and then shoots home. Anyone trying to get latest the next morning can not compile code until the responsible developer is on hand to resolve the error, or revert the commit history to the last working version – definitely not ideal. After a bit of to-ing and fro-ing and some help from the development manager we got the CI build working to perform a full rebuild on every commit with full shame emails to the entire team in the event that any member of the team pushed a breaking commit – not perfect but so much better than nothing. Within a day the first shame email went out and the effort expended was already beginning to show a return.

The next stage of the process was to get some unit tests running. The product suite on which I am working features a number of we based applications, a range of distributed components and a service bus, I was tasked with writing a few service bus command consumers and in this scenario, TDD was the only way to go – wiring up a full code stack and bus on my dev machine and all the associated development effort just to hit a breakpoint was ridiculous compared to writing a single unit test method in which I could spin up a new instance of the bus command consumer and supply any required dependencies. Fore this reason the code I had written was pretty well covered so it was then merely a question of getting these tests to run as part of the CI build.

In TeamCity I added a unit test step to  the existing build and included the assemblies containing all my unit tests. When I ran the build I kept getting error messages of the type “The specified table schemaname_tablename does not exist.”, needles to say they worked perfectly on my machine. Database versioning for the product suite is managed using the open-source MigSharp project. I hadn’t used this before but my best summation would be that its an early/non-Microsoft version of (and possibly inspiration for?) Entity Framework codefirst migrations. The process by which a database-based unit-test is executed consists of copying an empty SqlCe database into the test bin directory, executing all migrations then running the test before finally deleting the database ready for the next test. I wanted to try and figure out what was going on so I ignored all but one test and commented the line of code which deletes the database at the end of the test. I did this, reran the build and then checked the TeamCity build directory, sure enough the database was empty indicating that for some reason none of the migrations were being run. I created a fork of the code base and added copious amounts of logging – in particular I found a few try/catch blocks with empty catch – this is a total no-no as far as I’m concerned so I added logging into these catch blocks. After running the build again and viewing the output I found this this line of output :

“Unable to find the requested .Net Framework Data Provider. It may not be installed.”.

So how was this working on my machine? I haven’t installed SQL Server Compact Edition? Maybe visual studio did it for me? Who knows? Who cares! I added the following xml to machine.config on the build server:

<system.data>
  <DbProviderFactories>
    <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0"
      description=".NET Framework Data Provider for Microsoft SQL Server Compact"
      type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
   </DbProviderFactories>
</system.data>

Then I manually triggered a build and Bingo! My test ran. I un-ignored all my the other tests, merged in my logging fork, re-implemented the line of code to delete the db on teardown,  committed and pushed the code. I did this a couple of weeks ago and we’ve already had a numerous occurrences of tests breaking from seemingly unrelated changes. I am now going through the arduous task of getting all the old tests running again – the horrible reality of this situation of that this time and effort would not be required if the CI process hadn’t been turned off. Still, you gotta love unit tests right?

how to configure sql express as a default instance

Posted : Thursday, 24 January 2013 09:38:08

Quite often during team development we come across situations whereby connection strings used in .Net configuration files (e.g. web.config and app.config) refer to an server and optionally instance name, for production,staging & test deployments this isn’t a problem as config transformations can be used to produce consistent and repeatable deployment configurations. During development however (particurlarly during web development) the story is a bit different. Almost invariably different developer machines will have different instance configurations, some may have no default instance, some may only have SQLExpress, some may have different versions of SQL Server etc. this makes the management of a common configuration file (eg web.config) a bit of a pain as developers will lneed to make sure that during debugging each connection string in use is pointing at their database. Various strategies for managing this exist, such as all developers using a central development database the two major flaws with this strategy is that it doesn’t support remote development and that developers could easily make breaking changes that require the whole team to update their development environment (for example adding a foreign key constraint to an existing table). Another (and more frequently employed) strategy is for each developer to make changes to the configuration file as required, this works but is mildly annoying and hurts productivity.

A strategy that has served me well for managing this is to update the configuration file to use a named server with no instance name such as that shown below:

This is then committed to the VCS and will remain unchanged (in terms of teach particular database connection string at least).

Next add an entry to your hosts file to map this name to whatever machine your dev database is on (typically 127.0.0.1):

#local host mapping for APPLICATION_DATABASE
127.0.0.1    APPLICATION_DATABASE

The final step is not required if the database you want to use is on the default instance. If it isn’t (for example you want to use SQLExpress) then you need to use the SQL Server Configuration Manager to add an alias for the instance you want to use. To do this open SQL Server Configuration Manager and under the SQL Native Client xxx Configuration Node, right click Aliases and select “New Alias…”. Then enter the name you used above and the instance that wish to map to:

NB if you have 64bit SQL Server installed make sure you set up the alias for both 32 and 64 bit client configurations:

You can test if this works by connecting to the non-default instance using SQL Server Management Studio:

This strategy enables a common common application configuration to be used while allowing each developer to configure their machine however they choose, it also allows for a disconnected development scenario.