HOWTO: Filter Dacpac Deployments

Updated 2015-02-02 I have changed the details about passing multiple filters at the bottom, see the codepex site for full details. tldr; Download the filter from: put the dll into the same folder as sqlpackage.exe and add these command line parameters to your deployment: /p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(BLAH)” This will neither deploy, drop or alter anything in the BLAH schema. More Info I recently discovered deployment contributors which let you write .

Enforcing T-SQL quality with SSDT Analysis Extensions

What? Simply put, it is possible to include checks in the SSDT (Sql Server Data Tools) build process - you come up with a set of rules or start with these ( and when you check-in your code, as part of your normal build or CI build you run the code analysis - the code analysis verifies that the code is good and either continues the build or fails it if you have committed a crime against your rules.

DacFX Links

I thought it would be a good idea to gather together a list of links for DacFx, blogs code or articles - if you have anything that you think should be included in this list please send it and I will include it. The first thing to read is the “DacFx Public Model Tutorial” this by Kevin Cunnane from the Microsoft SSDT team and is highly recommended:… Then there are the DacFx samples on codeplex:

Make Visual Studio Setup Slightly Faster

If you get the Visual Studio 2012 or 2013 web installer then it uses the BITS service to download the setup files. The BITS service is used to download files but throttles the download so it doesn't overwhelm your bandwidth. If you are also downloading lots of other things like windows updates then the visual studio downloads have to take their time in the queue with everything else. If you want to download to go quicker, you can disable and stop the BITS service, the installer will fall back to traditional HTTP can be much faster (depending on what other things are trying to use BITS).

Running tSQLt tests from C/VB.Net/Java/Whatever

When you already have a test framework setup such as MSTest or NUnit and possibly a CI build in place and you want to add Sql Server tests it is often wise to call the tests from the framework you already have setup. I do this at the moment using C# and I have a helper method that calls tSQLt.Run/RunClass/RunAll passing in the name of the test(s) to run - this means that from within visual studio, I can see and run all of my tSQLt tests alongside my existing NUnit / Moq tests.

For better Sql code use fake tables

When unit testing code there is a really powerful feature that is either called mocking or faking or sometimes using stubs and what these mean is creating objects that look like real objects but aren't. Why would you want something that looks like something but isn't? Unit testing means breaking your code into small chunks and testing that the small chunk does what it is meant to. Testing small chunks of code in isolation is difficult as they often call other chunks of code so we test and pretend that the other thing is correct and to validate those other small chunks, we unit test them.

Unit tests help document your code!

I recently had the chance honour to spend twenty minutes talking to Boris Hristov about testing with Sql Server for his Google Hangouts series, catch the video here One of the things I mentioned was that having a suite of unit tests helps to document the code and in this post I would like to demonstrate that. If we take the first stored procedure in the AdventureWorks2012 database to see how it is documented, if we take the procedure definition, we get:

What collation variables take on in T-SQL

I was asked an interesting question about collations in sql server recently about where variables in a script got their collation's from. I really wasn't too sure of the answer, I generally try to ensure that databases are in the company default collation although I realise that this is not always possible. Given how important this is I thought I would investigate it a little: The first question I had was what collaction is the actual T-Sql script compiled in, is it the database or server?

DBAs - Step away from production

I have worked as a DBA and also as a Sql Server developer and in many roles had full access to production, it is OK though I know what I am doing and I normally don't make mistakes (we'll come back to this!). To be clear, this isn't a moan at DBA's, I am one and can and do make changes myself - I am trying to improve processes that I have input into.


There has been a gigantic shift in attitudes between development and operations, the devops movement, to create a streamlined, automated and high performing team of developers and infrastructure techs which has really helped transform many operations and projects. It occurs to me that when building a development team or planning out a project we should include resources for developers to write tools and improve processes for the other developers in the team.