Publishing dacpac’s is a little bit of a pain when you have multiple databases, it can easily start to take minutes to hours to deploy changes depending on how many databases and the size of those databases. I wanted to understand more about the publish process and what we can do to speed it up as much as possible so I did some digging and this is a randomish post about how it all works and what we can do to make it faster.

I did a talk at the london .net meetup if you want to get an overview of what SSDT is and how to get started then I would recommend it: https://skillsmatter.com/skillscasts/9274-londondot-net-january-meetup This was aimed at .net developers rather than DBA’s so there isn’t much talk about “why you should use source control” etc as everyone in the room used source control already :)

I was talking to someone at a meetup recently who was really keen on doing continuous deployment for their database but they had a number of issues, the main was that because management wasn’t sold on the idea and the DBA’s had complete control to push back on all and every idea he had - there was no way he could deploy continuously. The accepted route for devops is management buy-in, if you do not have management buy-in then you can’t do devops.

There have been a couple of fixes in SQLCover this week, kindly submitted by John Mclusky (https://github.com/jmclusky): Code coverage not reported correctly for CTEs at the end of a stored procedure if the ‘with’ is immediately preceded with a semicolon and DeclareTableVariableStatement statements cannot be covered, so report falsely as missing coverage I have also changed where the releases can be downloaded from to use the github releases: https://github.com/GoEddie/SQLCover/releases The previous version is still available but I would recommend the latest version.

In this post I will talk about the in-built refactoring support in SSDT – the language is slightly different from my normal style as originally it was going to be published else but rest assured it is written by myself What is refactoring? In programming , the term ‘refactoring’ essentially means taking some code and improving it without adding features and without breaking the code. When we refactor code we ideally want to make small improvements over time, using an IDE that automates as many of the tasks as possible for us.

The ideal is to make a change and see that change deployed to production, in a perfect world we would be told to work on something, write the code + tests, deploy to a test environment, prove it works and deploy - this is the cycle time and the faster you can get this the easier many things become. The cycle time is easy to measure - it is the time the ticket arrives in the backlog to the time it moves to the done column, if your issue tracking system can’t tell you this easily then use something else!

I have been thinking quite a lot recently (ok not that much but my thinking has changed) about how to deploy dacpac’s. In particular I am talking about how to control the deployment, what tool is used to deploy the dacpac, what connection details are passed in, which of the hundreds of parameters are passed in and how. I think that as I configured and deployed databases using dacpac’s in a number of different environments my approaches are been:

tSQLt Visual Studio Test Adapter What is this? This lets you use Visual Studio to run tSQLt tests easily. Visual Studio has a built in framework for finding and executing tests so that if you have tSQLt tests in an SSDT project for example, although this just requires you have the .sql files in source control and does not require ssdt - you can easily see and execute your tests via the Visual Studio Test Explorer window.

Deploying a dacpac from powershell should be pretty easy, there is a .net api which you can use to open a dacpac, compare to a database and either create a script or get the database to look the same as the dacpac but there are a couple of problems with the approach. Problem 1 - Is the DacFx installed? The first problem is whether the DacFx is installed, the chances are if Visual Studio or SQL Server have been installed then it will be there.

I have released a new version of SQLCover which is a code coverage tool for T-SQL (let’s you identify where you need to focus when writing tests). This includes a few minor fixes but also support for SQL Azure so if you run your test in a v12 database or higher you can now get an idea of code coverage from that. If you are interested in using this but don’t know where to start, there is a powershell script in the download (https://the.