Keeping tSQLt tests separate in SSDT

Users of SQL Test and the tSQLt framework upon which it is based have noted in the past that because test objects are stored in the database, they can be difficult to differentiate from the objects under test.  This has been a barrier to some users adopting tSQLt for unit testing, as it prevented separate management of tests and raises the potential danger of tests being accidentally deployed within a production environment.

Recently I have been using tSQLt within SSDT (following this method by Ken Ross), and it occurs to me that this method of controlling unit tests allows us to keep the code under test in a different project to our tests, and therefore overcome the difficulty of keeping our tests as database objects. When we have our tests in a separate project within the same solution, we can choose to deploy either the tests with our code under test or simply the code itself without the tests.  By configuring the test project to ensure that it requires the code under test to also be deployed at the same time and into the same database, we can set up publish definitions for the tests to our development machine and also a publish definition for just the code under test. It's the latter which we would use to deploy outside of our development workstation, for example to UAT. It also means that if you deploy by DACPAC the tests have never been in that package, so it is ready to deploy to each environment without your needing to take any additional steps.

Of course, a Continuous Integration engine has access to both projects within the solution from source control so it can either include unit tests or not, depending on your desired build action.

By having both the code under test and the tests themselves within the same solution, we can use the same source control process allowing both the code under test and the tests themselves to be in one place, which prevents drift between the test and the code under test.

When developing databases in SSDT, using this method gives me the best of both worlds; tests which to live with the database under development  including within source control, and yet do not need to be removed from the database as part of or after the deployment process.

Book Review - Tribal SQL

How many of you have written a book? Probably not many. Yet how people working with SQL Server have thought that they knew better, or that someone else would really appreciate how you do something? Probably a lot more.

Trying to encourage new authors to share a small, manageable amount of knowledge on a subject, like the lightening talk of the book world, is one of the aims of the book I'm looking at in this post - Tribal SQL.

It came about from a wish of Jen and Sean McCown (The Midnight DBAs) to let people read the insight and enthusiasm which is present in the less often heard corners of the SQL Community. I think they've done a good job in encouraging new voices.

The subjects covered are as varied as the topics - and all are in an informal, insightful manor, which encourages this both as an introduction to areas into which you might not have previously strayed, and also as a reference material / revision of best practice for those who have.

I'd strongly recommend this book to Accidental DBAs, who may find themselves suddenly confronted with a lot of new requirements and challenges, not the least of which is a distinct lack of training budget! (I'd also encourage attendance at SQL Community events, like SQL Saturday Exeter which is being held in March in the South West of England, and is free to attend).

This book doesn't restrict itself to the technical subjects (although it certainly does cover them, for example in Storage Internals and Data Compression) but also covers softer skills, such as project management skills, and how to get a stellar performance review. There's more basic introductory topics too, such as Stephanie Locke's chapter on reports, and how to gather requirements, implement a good relationship with others in the organisation, and produce them a great report.

I found the breadth really refreshing - the authors clearly want you to learn, and the fact that they've donated their royalties to charity really underlines their wish to help those around them.

If you aren't convinced, Red Gate have currently got a sample chapter on their website (UPDATE: as Melanie pointed out in the comments below, there are three sample chapters from the book on the Simple Talk website - Agile Database Development, Guerrilla Project Management for DBAs and SQL Server Storage Internals 101), and you can buy the book from Amazon - there's even an e-book available! The code samples are all available from the Tribal SQL website.


Disclosure: I was sent a review copy of this book free of charge by Red Gate Publishing, but otherwise received no remuneration or reward for this review.