Late in December, one the long awaited portions of functionality inside SSDT was released to an eager public, that being unit testing.  There has been a lot of noise generated about sql unit testing as a concept recently and hopefully you have decided that that is now the way to go.  Bill Gibson of Microsoft has blogged at quick beginners guide to SSDT unit testing here.

This is not a post about unit testing per se or the operation of SSDT unit testing, but what you need to do to run the same unit tests across multiple environments.  Even the smallest shop will have multiple SQL servers that you will wish to run tests against (DEV, UAT, integration etc) and if you use SSDT for more than 5 minutes you will discover that there is no easy way to aim the tests at these servers.

Jamie Thomson(b|t) has previously blogged about this inside datadude (SSDTs’ predecessor) and what a pain it can be.  Well, it is still a pain and the supported route is still via this method and it does work in SSDT.  I will say two things though,  firstly the config override file has HAS to be called machinename.sqlunittest.config or username.sqlunittest.config nothing else will do, and secondly the file is very sensitive to formatting.  I had a single leading whitespace in mine, which meant that the file couldn’t be processed correctly ! Also note that the element naming has changed to SQLUnitTesting_VS2010 ( im not running VS2012 to see if it is still the same there)

As I say, this does work, but is not very clear and quite limiting in scope.  I can only have effectively one override file and what if i have a centralised CI build server I wish to run our tests from against multiple environments ?  Well, without more confusing workarounds (that I can think of but haven't tried) you cant.

So, do we have any other options ?

Our friends in the application development world have faced and solved this problem,  though I don't believe their problems are exactly analogous, but this is probably the simplest route to solve it.  Using a VS plugin called Slow Cheetah the app.config file can be transformed at build time to support multiple environments.  Each build configuration (Debug / Release) can have a transform for app.config, after installing the plugin and R-Clicking on the app.config, you should see an ‘Add Transform’ option.

After selecting that, for each of your build configurations (you can always add more) you will see an app.buildname.config file.

The transform to be added to files is:

<?xml version="1.0" encoding="utf-8" ?>

<!-- For more information on using transformations

     see the web.comfig examples at http://go.microsoft.com/fwlink/?LinkId=214134. -->

<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">

  <SqlUnitTesting_VS2010>

    <ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=Server;Initial Catalog=DataBase;Integrated Security=True;Pooling=False" CommandTimeout="30"

           xdt:Transform="Replace" />

    <PrivilegedContext Provider="System.Data.SqlClient" ConnectionString="Data Source=Server;Initial Catalog=DataBase;Integrated Security=True;Pooling=False" CommandTimeout="30"

           xdt:Transform="Replace" />

  </SqlUnitTesting_VS2010>

</configuration>

When you now build and run your tests Slow Cheetah will apply the transforms to app.config and you will connect to the specified server.  Slow Cheetah includes a ‘Preview Transform’ option to aid with development so you don't have to guess   To me this seems like a much more preferable route that the mstest overriding method.

This is all well and good, but what if you want even more control. 

Disclaimer: I have not tried this any more that detailing it here, I cannot vouch for long term (or even short term) stability.  You are on your own if it all goes wrong.

There is a method by which you can specify exactly which instance/ database to use.  You can programmatically override the connection.  If, in Visual Studio, you select ‘View Code’ on one of your C# unit test modules,  you will see a member called TestInitialize.  Inside this member we can close the existing connection and open one to the server we really wish to test against like so:

        public void TestInitialize()

        {

            base.InitializeTest();

            base.ExecutionContext.Connection.Close();

            base.ExecutionContext.Connection = new System.Data.SqlClient.SqlConnection("Data Source=Server;Initial Catalog=Database;Integrated Security=True;Pooling=False");

            base.ExecutionContext.Connection.Open();

        }

Obviously I have hardcoded the connection string here,  but you could have it read from an environment variable or topology file or somewhere else.

Have fun with any of these methods and any one should get you closer to the goal of more reliable code.

Do you know of a more user friendly and workable solution ? I’d love to hear it.