Search code examples
sql-servervisual-studio-2013tfssql-server-data-toolstsqlt

Implement tSQLt Framework with database which is part of TFS SQL Project

Environments

  • SQL Server - IaaS.

  • TFS deployer to build and deploy the SQL files to respective database

Scenario

  • I have a TFS SQL Project for my database. We make the changes in TFS SQL Project and deploy these changes using the TFS build.

  • TFS Build generates the differential script by comparing the my database and TFS SQL Project.TFS Build generates the environment specific script.

  • Basically,if the objects are not present in the database but present in SQL Project then it will generate the Create statements and if the objects are present in the database but missing in SQL project then it will generate the Drop statements

Problem

  • I want to implement the tSQLt framework with my database.What will be the best practices we can follow by considering the above scenario?

  • Basically - if we create the objects in SQL project, then We will deploy these tsqlt objects till PROD. Which is not good practice?

Solution

  • You are correct in your belief that that you should not be deploying the tSQLt framework or associated unit tests to production.

    The solution you are looking for is composite projects. Your solution presumably already has a database project with all your code - called MyDb for example. You will need to add another database project to the same solution - you could call this MyDb.tests (although the name isn't important). This second database will hold the tSQLt framework and all your tests. This is a very quick step by step:

    1. Create an empty database called MyDb.tests on your sandbox and add the tSQLt framework to this database
    2. In Visual Studio, import the tSQLt objects from MyDb.tests into the new MyDb.tests database project in your solution. Alternatively you could create a dacpac of the tSQLt database and reference that. Personally, when importing, I usually choose to group objects by schema as this then nicely sorts your project by test class.
    3. Still in Visual Studio, in MyDb.tests, add a database reference to "master" Then add another database reference to your MyDb database project in your solution. Make sure you specify same server, same database. Start adding tests to MyDb.tests whilst continuing to write your production code in MyDb.
    4. When you build or publish MyDb.Tests to your sandbox, the resulting database will include the tSQLt framework, your tests and all the objects and code from MyDb.
    5. In MyDb.tests, you could even add a post-deployment script which includes a call to tSQLt.RunAll so that all your tests are run everytime you re-bublish the MyDb.tests project.

    When you come to deploy to test, UAT or Production, you just build and publish from the MyDb project and none of the tSQLt framework or tests will be included.

    There is a bit more to it than this - especially if you prefer to develop in SSMS then import your objects back in to Visual Studio but here are a couple of links that may help:

    https://kzhendev.wordpress.com/2014/01/08/setting-up-ssdt-database-projects-and-tsqlt/

    https://www.red-gate.com/simple-talk/sql/sql-tools/sql-server-data-tools-ssdt-and-database-references/