SqlDropDatabase, SqlCreateDatabase MSBuild tasks

When working with SQL, I often find I need to quickly spin up/tear down local developer database instances – for example, setting up a clean environment for integration tests on the build server, or blowing away test data.

To help make this easier, here are a couple of MSBuild tasks I wrote that allow you to drop an existing database:

<SqlDropDatabase ConnectionString="Server=.SQLEXPRESS;Database=AdventureWorks;Integrated Security=SSPI;"                 Database="AdventureWorks" />

… and to create a new (empty) one.

<SqlCreateDatabase ConnectionString="Server=.SQLEXPRESS;Database=AdventureWorks;Integrated Security=SSPI;"                   Database="AdventureWorks" />

It’s also sometimes helpful to be able to parse individual individual keys out of a connection string (e.g. the the database name). This can be very tedious with RegexMatch/RegexReplace, so I wrote a separate MSBuild task to do it:

<SqlParseConnectionString ConnectionString="Server=.SQLEXPRESS;Database=AdventureWorks;Integrated Security=SSPI;">    <Output PropertyName="myDb" TaskParameter="InitialCatalog" />    <Output PropertyName="myServer" TaskParameter="DataSource" />    <Output PropertyName="myTimeout" TaskParameter="ConnectTimeout" /></SqlParseConnectionString><Message Text="Parsed the $(myDb) database on server $(myServer) with timeout = $(myTimeout)." />

Fragmented integration tests – aka the questionable value zone

In TDD, different styles of tests can be applied to cover different levels in your code base. Two or three years ago, if you asked me what they were, I would probably have listed them as:

Highest granularity – unit tests, quick to run, drive low-level class design

Fragmented – integration tests, testing higher-level components and external dependencies (e.g. real SQL database, fake message bus)

Whole system at once – slow end-to-end tests, testing for story/feature acceptance at the UI or client API level

However, it has become increasingly clear that fragmented integration tests (somewhere in the middle between unit and full-blown end-to-end integration tests) don’t really provide the same value as their brothers.

They suffer all the disadvantages of both unit and end-to-end tests:

Like unit tests, they require setting up mocks/test doubles for collaborating modules

Like end-to-end tests, they have a high cost of maintaining external components like databases and integrated third party systems (setting them up, populating them with the right test data, reverting changes for next time etc)

Like unit tests, they are brittle and not friendly to refactoring

Like end-to-end tests, they are slow to run

… and, over the lifetime of the code base, the only real benefit they provide is slightly faster feedback of problems than end-to-end tests. Is it really worth keeping them?