Sql Server

SSDT Deploy / Publish Performance

  • Posted on: 1 March 2017
  • By: Ed Elliott

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.

Process Overview

Roughly speaking the process for deploying dacpacs is:

  • 1. Open dacpac (zip file)
  • 2. Open database
  • 3. Compare dacpac to database
  • 4. Come up with a list of changes
  • 5. Generate the T-SQL for the changes
  • 6. Run the deploy script

1. Open the dacpac (zip file)

The dacpac contains the xml representation of the model of the database and the original source code. To be able to deploy changes we need the original source code - the model contains the definitions of the objects such as which columns the table has and the parameters a stored procedure has but not the body of the stored procedure including comments.

The dacpac is a zip file, this means to read from it the deployment must create a file handle to the dacpac and uncompress anything it wants to read (or compress anything it wants to write), the larger the dacpac logically the longer it will take to decompress and also read from disk so there are our first two important things to note, the deploy will need:

  • CPU to decompress the contents of the dacpac
  • Fast disk as it has to read from disk

When you run the publish, by default the model is read from disk into memory, however, for very large dacpac's you might find that you don't have enough memory for it - a windows system with low memory is bad, two things happen:

  • The paging file is used
  • Applications are told to free memory

This is painful for an application and when you get paging, especially on non-ssd drives, you are going to find everything about the whole system slow.

If you have a large model in your dacpac and you don't have much memory then you will probably need to load the model from disk rather than into memory, but you best make sure you have an ssd to run it from!

For more details on how to do that see: https://redphoenix.me/2013/06/14/why-is-sqlpackage-using-all-the-build-s... (Richie Lee FTW!)

2. Open database

The database contains all sorts of goodies like the version that will be deployed to, the database settings and the definitions that will be compared to the dacpac to see what changes need to be made. If your SQL Server responds slowly then the publish will be affected by that so make sure your SQL Server box is up to scratch and expect slower deploy times on resource limited instances.

3. Compare dacpac to database

I had always wondered how they compared the two different objects so I bust out reflector and used it to have a poke about. What happens is a model is build of the source and target - a model is a representation of each object, it has its name, its properties and relationships to other objects for example a table has a relationship to its columns and each column has a relationship to its type (not a property of the type).

If you think about this it means, for each deploy we actually need to store the model of both the database and the dacpac so doubling the size requirements - this isn't to say an exact double of the size of the source code becauuse what it compares is things like the object properties and relationships but also the tokens that make up the body of the object, so there are a lot more objects that are created around the code. In short if your source code is 100 mb you will need some multiple of 2 * 100mb to upgrade an existing database - I am not sure if there are any figures to show what that multiple is but if you do a publish and you run low on memory or you get lots of garbage collection in your deploy then consider either storing the model on disk or throwing in some more memory.

There are a couple of interesting extra things that happen, in particular to do with the size of code in objects and how much to store in memory, to store in memory compressed or to write to disk. If for example you had a stored proc that was less that 500 characters long, the publish will store it in memory.

If the string representation of a procedure was over 500 characters but less than 16 thousand characters the it will be stored in memory but compressed first.

Finally if the stored proc (I am using procs as a example but I think it is any code unit minus the create + name so everything after "as") is over 16 thousand characters then a temp file is generated in your user temp folder and the contents are written to that.

So three different behaviours depending on the size of your objects:

  • Under 500 chars - you need memory
  • Over 500 chars, under 16,000 you need memory and CPU
  • Over 16,000 chars you need memory and fast disk for your temp folder

I created three demo databases and deployed them over and over, the first contains a couple of hundred procedures with slightly under 500 characters, the second with slightly over 500 characters and the last with slightly over 16,000 characters.

When I ran the publish this is what they looked like:

Under 500 char procs:

under 500 chars procs, fastest to deploy

Slightly over 500 char procs:

just over 500 chars procs, similar to under 500 chars but includes some time to compress some strings

What we can see here is that the under 500 char procs, takes the least amount of time but the interesting thing is the database with slightly over 500 chars procs has a little bit of extra umpf from the CPU - I have showed this by using the pink circle - both deploys follow the same pattern for sqlpackage.exe CPU usage - it goes from around 90% cpu up to 100% and then drops down sharply to around 80% and then back up to 90% but the second project with the slightly larger procs - also has an extra splurge of CPU which I am putting down to the extra compression that those strings are getting - it certainly tallies up, even if it is not scientific :)

Aside from the extra CPU usage they are pretty similar, the black highlighted line is garbage collection and the way this counter works is every time it changes, it is doing some garbage collection - the first version does I think 3 collections and the seconds does 4 which sort of makes sense as once the strings has been compressed the original strings can be thrown away.

It is when we start to look at the database with lots of objects over 16,000 characters long we see some interesting things and some other forces come into play:

over 1600 chars procs, lots of disk activity

The first thing to note is the garbage collection, there are like 8 changes to the line so 8 sets of garbage collection that happen which is expensive for a .net app. We get much more processor usage for a more prolonged time and we really start to see some disk activity, write activity just has one peak while the files are written but reading stays quite high throughout the whole process. It could be that it is sql reading from disk (my demo was from a single ssd with a local sql instance) but we can see after the sql cpu settles down the green disk read line stays high so it is unlikely to be pure sqlserver.exe.

What does this tell us?

Well if you have large objects, multi-thousand line stored procs then you better get yourself some serious ssd's to deploy from. If you have smaller objects then you'll need CPU and memory - don't scrimp here!

4. Come up with a list of changes

So when the properties and tokens in the source / destination object have been compared they are added to a list of modifications - this is fairly straight forward but that is not the final list of modifications because there are lots of options to customize the deployment such as ignoring certain things - what happens next is that there is some post-processing done on the list to remove anything that the options say should be removed.

We then get the opportunity to interrupt the deploy process with a contributor and remove steps as we want - if we do that anything we do is overhead so be warned, be fast in what you do or slow down the whole process!

There are some things like default constraints, these are stored differently in SQL Server to our project - so when a difference is found, as part of the post-processing each constraint is "Normalized" using the script dom and a visitor to find out if the changes are actually different or just appear different - this means that we don't keep getting constraints deployed over and over (a relatively new feature as this was something that used to happen a lot - a great new feature added by the SSDT BTW!) but it does mean for every default constraint we need to go through this normalization using the script dom which isn't exactly lightening fast.

Takeaways from this are that the deploy does a lot of things to help us - if it was a straight string comparison then it would be faster but the deploy options would not be as configurable. The more objects you have, the longer the deploy will take!

5. Generate the T-SQL for the changes

6. Run the deploy script

I'm going to stop this post here - I think these are a bit more straight forward, the more things you have to change, the more time it will take :)


If the deploy is slow, use perfmon measure the cpu, disk and garbage collection of sqlpackage.exe (or whatever is doing the deploy) - have a think about whether streaming the model from disk would be better and if you are CPU bound add a more powerful CPU, if you are low on memory, add more memory and if you are on an old slow disk - put it on an ssd.

If you are low on CPU you might be tempted to add more CPU's - understand that a single deploy happens in serial so adding more CPU's won't make a deploy quicker if this is the only thing running on a box - if it is fighting for resources then more CPUs may help.

Refactoring in SQL Server Data Tools - SSDT

  • Posted on: 27 September 2016
  • By: Ed Elliott

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.

While SQL Server Data Tools (SSDT) cannot guarantee that we do not break any code it helps us make small improvements and, as an IDE, it offer us some refactoring abilities that do not exist in either SQL Server Management Studio (SSMS) or Notepad.

Just so we don’t get distracted by some of the third-party add-ins that give more comprehensive support for refactoring in SSDT or SSMS, this article will talk only about what is out-of-the-box with SSDT.

What refactoring support does SSDT have?

SSDT helps us to refactor code by automating the actions of:

  • Expanding wildcards
  • Fully qualifying object names
  • Moving objects to a different schema
  • Renaming objects

Aside from this list SSDT also, of course, helps us to refactor code manually with its general editing facilities.

Expand Wildcards

SSDT allows you to highlight a “*" from a SELECT statement and have it replace the “*" with a comma-delimited list of the column names in the table. As an example if we take these table definitions:

CREATE TABLE dbo.person
first_nmae VARCHAR(25) NOT NULL, --typo is on purpose!
last_name VARCHAR(25) NOT NULL

CREATE TABLE hr.departments
funny_name_ha_ha_I_am_so_cool_my_last_day_is_tomorrow VARCHAR(25) NOT NULL

CREATE TABLE hr.department
( person_id INT,
department_id INT

And the following stored procedures:

CREATE PROCEDURE hr.get_employee ( @employee_id INT )
FROM person p
JOIN hr.department dep ON p.person_id = dep.person_id
JOIN hr.departments deps ON dep.department_id = deps.id;
EXEC hr.get_employee 1480;

To improve this code the first thing we will do is to change the “SELECT *" in the hr.get_employee procedure to specify just those columns that we need. If we open the stored procedure in SSDT and right click the “*" we can choose ‘refactor’ and then ‘expand wildcards’:

We are then given a preview of what will change, and we can either cancel or accept this:

Now that we have the actual columns, we can remove the ones we do not need, save and check-in our changes. The procedure should look like:

CREATE PROCEDURE hr.get_employee ( @employee_id INT )
SELECT [p].[first_nmae], [p].[last_name], [deps].[funny_name_ha_ha_I_am_so_cool_my_last_day_is_tomorrow]
FROM person p
JOIN hr.department dep ON p.person_id = dep.person_id
JOIN hr.departments deps ON dep.department_id = deps.id;

Fully qualify object names

We need to make sure that our SQL Code uses fully qualified object names. This is because …. In this example, the get_employee stored procedure references the person table without a schema which means that the user must have dbo as their default schema. To fix this we right click anywhere in the stored procedure and choose Refactor and then ‘Fully-qualify names’, we could also use the default shortcut of ctrl+r and then q. Again get a preview window:

If we accept the preview, then we end up with the following code:

CREATE PROCEDURE hr.get_employee ( @employee_id INT )
SELECT select [p].[first_nmae], [p].[last_name], [deps].[funny_name_ha_ha_I_am_so_cool_my_last_day_is_tomorrow]
FROM [dbo].person p
JOIN hr.department dep ON p.person_id = dep.person_id
JOIN hr.departments deps ON dep.department_id = deps.id;

This doesn’t seem like a massive deal as we could just have written ‘dbo.’ but if we had a more than one to update or a number of different tables then it would have saved more work for us.
The ‘fully qualify object names’ goes further than just table names, it will fill in tables in join statements and also columns where it is needed.

For example if I had the following query:

SELECT first_nmae
FROM [dbo].[person] p
JOIN hr.department d ON department_id = p.person_id

When using the refactoring we are offered the chance to fully qualify first_nmae and the department_id in the join:

If we decided we did not want to apply the refactoring to one or the other we could uncheck them in the dialog and only apply the ones that we actually required.

If we apply both of the ‘refactorings’, we end up with:

SELECT [p].first_nmae
FROM [dbo].[person] p
JOIN hr.department d ON [d].department_id = p.person_id

Move objects to a different schema

We can refactor the schema that an object belongs to. This is a three-stage process that:

  • 1. Changes the schema that the object belongs to
  • 2. Changes all references to the original object to specify the new schema
  • 3. Adds an entry to the refactorlog.refactorlog to help the deployment process

If we want to move the person table from the dbo schema into the hr schema, we can simply right-click the table and then choose ‘refactor’ and then ‘move schema’:

If we look at the preview we can see that, as well as changing the schema on the object itself, it is also going to change the references to the table everywhere else in the database:

This refactoring also adds a new file to our SSDT project, the Refactor.refactorlog file in order to assist in a correct deployment of the change that preserves the data in the table. Inside the refactorlog is some xml:

<?xml version="1.0" encoding="utf-8"?>
<Operations Version="1.0" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
<Operation Name="Move Schema" Key="5df06a6f-936a-4111-a488-efa0c7f66576" ChangeDateTime="11/10/2015 07:10:39">
<Property Name="ElementName" Value="[dbo].[person]" />
<Property Name="ElementType" Value="SqlTable" />
<Property Name="NewSchema" Value="hr" />
<Property Name="IsNewSchemaExternal" Value="False" />

What this does is to save the fact that an object has changed from one schema to another. SSDT reads the refactorlog when generating a deployment script. Without the refactorlog, SSDT would look at the source dacpac and the target database and drop the table ‘dbo.person’, deleting all its’ data, and create a new empty ‘hr.person’ as they are different objects. Because of the refactorlog, SSDT generates a schema transfer rather than a drop / create:

SSDT stops the change happening again by recording, in the target database, that the refactor key has been run so you could create a new table called hr.people and it would not get transferred as well:

Renaming Objects

The final type of built-in refactoring is to rename objects, this works similar to the move schema object but it allows us to rename any object such as a procedure, table, view or column. SSDT renames all the references for us:

SSDT also adds an entry into the refactorlog:

And finally generates a “sp_rename" as part of the deployment rather than a drop/create:

Renaming objects really becomes pretty simple and safe so you can go though and correct small mistakes like spelling mistakes or consistency mistakes. Without SSDT or another IDE to do it for you it is really difficult to rename objects as part of a quick refactoring session.

Other ways SSDT helps to refactor

Aside from the in-built refactors that SSDT has it helps us to refactor because it allows us to find where we have references to an object. For example if you wanted to add a column to a table but did not know whether there were stored procedures that did a select * from the table and then did something that would be broken by adding a new table you could right click on the table name and do “Find All References":

We can also do the same thing for column names and so we can really easily get a picture of how and where objects are used before we change them.

General advice on refactoring SQL Server databases

Refactoring SQL Server databases is really helped by using SSDT but there are two things that you can do which really give you the freedom to refactor your code as you go along:

  • Use stored procedures / views rather than access tables directly
  • Have a suite of unit/integration tests
  • Use stored procedures / views rather than access tables directly

If you access your data from your application using the base tables it means you cannot change anything in your tables without also changing the application. It also makes it really hard to find references to your objects to know where they are used. Instead you should use stored procedures as an API to expose your data. If you do this then instead of having to manually find references you simply need to find the places where the stored procedure is called.

Have a suite of unit/integration tests

If you have both unit and integration tests then, as well has having a tool that helps you to refactor your code, you also get the confidence of knowing that you can make changes without breaking anything else. Without these test suites it is hard to known whether you have broken that year-end critical task that is so easily forgotten about.


SQL Server Data Tools have the basics of refactoring tools, but it isn’t really what one comes to expect from a SQL IDE. What about a tool to automatically insert a semi-colon after every statement, if there is none? Why is there nothing that changes the case of keywords according to SQL Conventions? One looks in vain for a way of reformatting code correctly. One could be more ambitious and ask for ways of finding variables that aren’t used, splitting tables, encapsulating code within a stored procedure, or checking for invalid objects. Fortunately, there a number of add-ins that fill the gap, and in the next article we take a look at SQL Prompt, which is the leading

Database Deployments in Uncontrolled Environments

  • Posted on: 20 September 2016
  • By: Ed Elliott

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! The tickets are moved into the "Done" state when they have been deployed into production - if you do nothing but investigate and try to reduce your cycle time you will make a massive difference to your development process.

There have been a few discussions on stack overflow recently about how to manage deployments in uncontrolled environments, specifically data migrations. The questions were from an SSDT perspective, I don't think that SSDT is a great choice for these uncontrolled environments and there are some additional requirements for these uncontrolled environments that need some additional thought and care when creating release scripts (whether manually or using a tool).

What is an uncontrolled environment?

I define it as a database that is managed by a customer, typically a vendor sells a product and it includes a database. The database is on a customer server and the customer is sysadmin and can make changes.There is a difference to databases where customers are allowed to make changes and ones where they are not allowed - but in either case you still need to take extra care, even if it is only to add additional logging and warnings to the output so any upgrade scripts help your support diagnose issues rather than displaying an error like "DBUpdate Error" - yes I have seen that with a vendor product once!

When you own and manage the deployment for your database application you can do these things because you can take for granted:

You can Because
Drop objects not in source code If it isn't in your source it does not exist
Rely on scripts generated / created by your dev team If someone wants to create an object called X they can see if an object called X already exists or not
Ensure each deployment happens successfully Run each deployment, run tests and verify the results
Write data migration scripts using accurate data You have the data
Use truncate on tables to clear data The script author knows there are not any foreign keys pointing to the table and that the data can be restored by backup rather than a transactopn

If you do not control the environment then you cannot do these things because:

You can not Because
Drop objects not in source code Who knows what the user has changed
Rely on scripts generated / created by your dev team Users may have made non compatible changes, you want to create a new view called "users_blah"? Well it turns out they have a audit stored procedure called users_blah
Ensure each deployment happens successfully Run each deployment, run tests and verify the results
Write data migration scripts using accurate data You have the data
Drop objects not in source code If it isn't in your source it does not exist
Use truncate on tables to clear data The script author does not know there are not any foreign keys pointing to the table and that the data can be restored by backup

So what can we do?

I really don't think that there is a 1-sized fits all solution here so you will need to look at your database and what changes you need to make but some randomish thoughts are:

  • Compare / Merge type deployments will drop any changes the customer has made - that is bad
  • If you had each version of the source, you could verify whether there have been any changes before deploying - that sounds good but potentially a support nightmare
  • The migrations approach sounds better but you need to ensure that every change is actually deployed
  • Adding additional logging and verification code is a must, instead of "truncate table" then "print truncating, check for things that will block this, truncate" - making sure that a un-reversable command like truncate has forced the user to backup or at least make sure the users understand that they need a back (even in 2016 this isn't guaranteed!)
  • Taking simple precautions like not using "select *" and using column lists and "order by column" rather than "order by ordinal" will help you in the long run with odd issues that will be hard to disagnose!

I guess the end answer is to offer a hosted solution and move to continuous deployment in a controlled environment as that actually makes a lot of these things simpler!

Controlling a dacpac deployment

  • Posted on: 21 August 2016
  • By: Ed Elliott

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:

In the beginning there was sqlpackage.exe

sqlpackage is great, it gives you a well documented command line and you can pass whatever arguments you need. The amount of times I have typed sqlpackage /action:script or /action:publish, the a,c,t keys on my keyboard are a little bit faded (they aren't but you get the picture!).

What I used to do was to check into my unit test ssdt projects, a directory with sqlpackage and all the dependent dll's so I could run the deploy from any build agent without having to have the DacFx or SSDT installed. This worked really well but there were a couple of downsides, firstly, the version checked in would hardly ever be updated and with an almost monthly (actual?) release cycle for ssdt builds this means you get behind pretty quickly. The other main issue is that you have to build up a lot of command line arguments so invariably I would end up with a powershell script or piece of .net code to manage that complexity. This is made even more complex when you have to consider the pain that is spaces in windows and an automated deployment process. There is an alternative to passing arguments on the command line that is to use a publish profile - I completly discounted these, I am not sure why but it was a long time ago - let's just agree that the important thing is that this approach to using sqlpackage.exe left me feeling a little uneasy in a few places.

After sqlpackage.exe there came code

The next approach was to write some code, this would either be powershell or c#. Typically powershell would be as part of a production ci/cd process - I would write a script to call the DacFx and then call the script from jenkins/vsts etc. I also found myself calling the DacFx from c# but this was typically limited to integration tests for tools that I was writing.

I liked the scripted approach because it meant that I could still pass some argumenst like the server name, user credentials etc on the command line (or encrypt them where necessary) and put a whole load of arguments in the scruipt to be shared by each environment. There were still a couple of problems, firstly as with the sqlpackage.exe approach I still had the problem that the DacFx needed to be installed and available so I would check the files into source control (or make them available etc). There was one additional problem that I did not forsee, that is when you use sqlpackage.exe you can load contributors from a sub folder called "Extensions", when you used the DacFx yourself you had to install them into program files which went against my (pretty strong) desire to be able to deploy from any build agent (windows for now, but i'm hoping!).

Then came the flip-flopping

For a while I meandered between the two approaches until the ssdt team announced that they had released a nuget package with the DacFx in and I decided that I would move over to that as it meant that I no longer had to check in the dll's into source control which in itself is a big win. I also decided to fix the extensions thing and so figured out a (slightly hacky) way to get the DacFx dll's in the nuget package to behave like sqlpackage and allow a sub-directory to be used to load dll's - I fixed that using this powershell module that wraps a .net dll (https://the.agilesql.club/blogs/Ed-Elliott/DacFxed-Nugetized-DacFx-Power...). Now I have the problem of not having to check in dll's and still being able to load contributors without having to install into program files sorted BUT I still had the problem of lots of command line args which I was sharing in powershell scripts and passing in some custom bits like server/db names etc.

The final piece of the puzzle

<blink>Publish Profiles<blink> ta daaaa.

I had actually been using publish profiles, I normally had at least one as part of my unit test project that I would use to dpeloy my whole database locally before running tests before I did a git push (obviously during dev I would do a quick deploy rather than constant deploys). My current grand scheme is to put everything I need in publish profiles and use those to deploy dacpac's. I realise that this isn't a great revelation but I am happy with them and pleased with where I am now, who knows where I will end up!

*I know blink doesn't work :)

tSQLt Visual Studio Test Adapter

  • Posted on: 17 August 2016
  • By: Ed Elliott

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. It is also available for use in your favorite build server via the vstest.console.exe tool or if you have vsts then you can use the default test task.

What does it look like?

Two screen shots, the first is the tSQLt sample tests in Visual Studio:

tSQLt tests are shown in the Visual Studio SSDT project

A couple of things to mention, firstly if you double click on the test in the Test Explorer window it will jump you to the test in the code (whoop). Secondly if you run a test and it fails you get a failure messages (it gives you a red cross) and clicking the test shows the tSQLt error message below.

This screen shot is the test running on vsts:

Output of tSQLt tests on vsts

Oooh that looks shiny! You see the step failed because the tests failed (I hadn't actually deployed them whoopsie). You get some nice graphs and to get this all you need to do is add the adapter to the project and configure the "Test Assemblies" task.

How do I use it?

To run tests you will need a .runsettings file and select it using "Test --> Test Settings --> Select Test Settings File" in the runsettings file you will neeed at a minimum the connection string to connect to:

<?xml version="1.0" encoding="utf-8"?>
<Parameter name="TestDatabaseConnectionString" value="server=.;initial catalog=tSQLt_Example;integrated security=sspi" />
<Parameter name="IncludePath" value="AcceleratorTests" />

If you run your tests and get an error with the connection string not being set make sure you actually have the runsettings file attached.

If you have a large project you really will want to limit the processing of tests to just test files so you can add a filter which tells the adapter to only parse files under paths that match a specific regex. To discover tests we need both the test procedure and also the schema that defines the test class so if you use a filter ensure that both are included.

Once you have your run settings, install the Visual Studio extension:


If you want to run this on VSTS then you can either download the VSIX, extract all the files and check them into a folder in your solution or use nuget to add the package AgileSQLClub.tSQLtTestAdapter (aka https://www.nuget.org/packages/AgileSQLClub.tSQLtTestAdapter/0.59.0) to your solution. Nuget doesn't support SSDT projects so you will need at least one .net dll which can do nothing at all except reference this package. Once you have the test adapter in your project then configure a test task like:

Configuring the test task on vsts

The things to note here are you can either add a filter to the runsettings file or you can filter which .sql files are passed to the test adapter, you will need to make sure both the schemas and tests are passed in otherwise we can't work out what is a test and what is a stored procedure.

An Oddity

Because of the way the test adapter framwork runs and discovers tests and the way that us sql developers like to seperate our schema's and tests into different files it means I need to add a fake test with the name of the test class if you try to run it you will get a message "Not Run" and it won't do anything but all of the individual tests will work. I tried to make this generic so you don't need SSDT to run and ironically if I had relied on dacpac's it wouldn't have been a problem!

What else?

It isn't the full expression of what I would like to do with this, there are a couple of things I will add in the future but this is a start, these are:

  • Ability to display the result sets rather than just the outcomes
  • In SSDT I would like to deploy any changed objects that are referenced by a test so you make your changes, then run the test and the code is dpeloyed and then run - cool hey!

Minimum Requirements

VS 2015, Update 3 - if you use an earlier version you will get slots of "0 test cases found" messages - if you are desperate for an older version of vs let me know and I will take a look but I am not planning on supporting older versions unless there is a good reason.

Open Source

Of course this is open source, it will be on;


Any issues shout!


DacFxed - Powershell Nugetized DacFx wrapper

  • Posted on: 2 August 2016
  • By: Ed Elliott

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.

Problem 2 - Where is the DacFx installed?

Once you know that the DacFx is available, where is it? Depending how you installed it, whether when you installed Visual Studio or via an MSI it will be installed in a different location. Further if you get a recent build it will be in the 130 folder, older builds the 120 or 110 folder. Which one do you have??

Solution 1

So what I used to do to combat these first two issues is to check into my test projects the DAC folder which includes sqlpackage and just shell out to that to do the install, this works but updates to SSDT come between every 1 and every 3 months, the projects I have done this on are all on old builds of the DacFx and probably will be until something goes wrong and someone updates it. That sucks :(

Solution 2

It was with great excitement, and I don't say that lightly, that in July the SSDT team announced that they would be maintaining a nuget package of the DacFx. This is really exciting because it means that problem 1 and 2 no longer exist, we can simply reference the nuget package and keeping up to date is pretty simple. While you recover from the excitement that is the DacFx in a nuget package I have something else to get you really excited...

This means no install to Program Files

I know right, exciting! What this means is that even on hosted build servers (vsts for example) where we don't have admin rights we can still keep up to date with recent copies of the DacFx without having to commit the source control sin of checking in libraries.

Problem 3 :( Deployment Contributors

If we no longer need admin rights to use the DacFx it means we no longer can rely on admin rights to deploy dacpacs - this means that deployment contributors cannot be copied to program files which is where the DacFx loads contributors from. Remember also that contributors are specifically loaded from the program files (or x86) version of SQL Sever or Visual Studio or whatever version of SSDT you have so it could be from any one of the version DAC folders i.e. 110, 120, 130, 1xx etc.

Solution 3

There is a solution? Well yes of course otherwise I wouldn't have been writing this! DacFxed is a powershell module which:

  • 1. References the DacFx nuget package so updating to the latest version is simple
  • 2. Implements a hack (ooh) to allow contributors to be loaded from anywhere
  • 3. Is published to the powershell gallery so to use it you just do "Install-Module -Name DacFxed -Scope User -Force"
  • 4. Has a Publish-Database, New-PublishProfile and Get-DatabaseChanges CmdLets

Cool right, now a couple of things to mention. Firstly this is of course open source and available: https://github.com/GoEddie/DacFxed

Secondly, what is up with the cool name? Well I did't want to call the module DacFx as I was publishing it to the powershell gallery and hope that one day maybe the SSDT team will want to create a supported powershell module that publishes dacpac's and didn't want to steal the name. DacFxed is just DacFx with my name appended, what could be cooler than that?

In the list I mention that it uses a hack to allow contributors to be loaded, lets talk some more about this.

Currently to use a deployment contributor you either need to copy it into the Program Files directory or use sqlpackage and put it in a sub-folder called Extensions - neither of these two options are particularly exciting. I needed a better way to be able to include a deployment contributor in some user writable folder and then load the dll's from there. I hope (there is a connect somewhere) that one day the SSDT team will give us an option when using the DacFx to say where to load contributors from - when that happens I commit here to modify this package to support their method so if you do use this then fear not, I will make sure it stops using a hack as soon as possible.

What is this so called hack?

When the DacFx tries to load deployment contributors it does a search of a number of well known directories to find the dll's, it also has a fairly unique way to determine which folder it should use when being called from visual studio - what happens is that it checks whether two folders above the folder the dll is in, there is a file called "Microsoft.VisualStudio.Data.Tools.Package.dll" - if this file exists then it searches in the folder the dll is in to find out if there are any deployment contributors to load. The interesting thing about this is that it doesn't actually load the file, just checks the existence of it - if it exists it searches itself for extensions. So if we have this structure:

Folder 1\Microsoft.VisualStudio.Data.Tools.Package.dll (this can be an empty text file)
Folder 1\Folder 2\folder 3\DacFx Dll's

When you load the DacFx dll's from this folder (to be specific "Microsoft.Data.Tools.Schema.Sql.dll") we get the ability to load contributors from user writable folders (which is the end goal for this).

Problem Solved?

Well no, it would be if it wasn't for the way .net resolved assemblies and powershell CmdLets. If our powershell module is structured like this:

WindowsPowershell\Modules\DacFxed\Microsoft.VisualStudio.Data.Tools.Package.dll (remember this is an empty text file)

What would happen is that out Cmdlet.dll would try to resolve the DacFx and it would not find it as .net doesn't search every sub-folder of the current directory to find dll's to load. If .net can't find the dll locally it will search horrible things like the GAC and if it finds the dll there, load it. This means our sneaky trick to trick the DacFx to load our extensions doesn't work.

What I came up with is a DacFxLoadProxy and DaxFxProxy, the CmdLet.dll has a reference to DacFxLoadProxy. DacFxLoadProxy does a Assembly.Load on each of the DacFx dll's in the bin\dll folder and then overrides Assemnbly.Resolve and tries to load the DacFxProxy.dll. Loading the DacFxProxy causes the DacFx dll's to be loaded and the DacFxLoadProxy passes the dll's that it has already loaded from the correct place.

Phew, I said hack :)

This sounds dangerous

Well yes and no, yes it is a little exotic but no in that if you tell the DacFx to load a contributor if this process doesn't work for some reason the worst thing that will happen is you get a "Deployment contributor could not be loaded" error - you won't deploy to a database without contributor you weren't expecting.

So no not really dangerous, just annoying if it doesn't work. I have tests setup and a release pipeline for this that I will cover in another post that make it easy for me to ensure each update to the DacFx can be taken while this still works. If the SSDT team break this behaviour then I won't deploy and then anyone using it can update in their own time.

How does it work?

You need a dacpac and a publish profile, if you have a dacpac and no publish profile then New-PublishProfile will create a template you can use to get started with.

Publish-Database -DacpacPath "path\to\dacpac" -PublishProfilePath "path\to\publish-profile"


Publish-Database -DacpacPath "path\to\dacpac" -PublishProfilePath "path\to\publish-profile" -verbose


Publish-Database -DacpacPath "path\to\dacpac" -PublishProfilePath "path\to\publish-profile" -verbose -DacFxExtensionsPath "c:\path\one;c:\path-two"

Anyway, enjoy!

SQLCover v 0.2 - Bug fixes and Azure V12 Support

  • Posted on: 5 May 2016
  • By: Ed Elliott

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.agilesql.club/SQLCover/download.php) and if you also get reportgenerator (https://github.com/danielpalme/ReportGenerator/releases/tag/v2.4.5.0):

Once you have downloaded SQLCover, extract the files and right click "SQLCover.dll" go to properties and click "Unblock"

Then in powershell run:

. .\SQLCover.ps1
$result = Get-CoverTSql "path\to\SQLCover.dll" "connection string" "database name" "query"

$outputFolder = "C:\some\path"
mkdir $outputFolder

Export-OpenXml $result "$outputFolder"
Start-ReportGenerator "$outputFolder" "c:\path\to\ReportGenerator.exe"

Change the path to SQLCover, the connection string, database name, query (tSQLt.RunAll), output path and path to report generator (phew) then run it and it should create an "out" directory under the output folder - open index.html and see the awesomeness of the reportgenerator output:

look how awesome code coverage is for t-sql

If you want to run mstest tests or nunit or something else completely then have a look in the SQLCover.ps1 which includes some examples at the bottom but Get-CoverExe is probably your friend.

Any questions please shout!


Where do you put your business logic? In stored procedures?

  • Posted on: 5 May 2016
  • By: Ed Elliott

There seems to be two trains of thought and I think this is mainly down to who and where your developers are. The first is that a stored procedure or a function is a great place to put all the business logic that an application needs. The second is that no logic should be in code deployed to SQL Server at all.

You can see these two extremes if you compare the difference between what stack overflow does (.Net/SQL Server BUT highly optimized for performance and deployments) where they have no stored procedures and embed T-SQL directly in their code to the many many thousand line procedures that we see on a daily basis, this quora answer from Kevin Kline I think is extreme but not unexpected:


The largest I ever personally wrote was a 16kb text file, which was many dozens of printed pages, and much personal misery.

Personal misery for anyone tasked with fixing that is exactly right, he goes on to say:

Otoh, I was once at the Microsoft campus in Redmund in a customer lab (what would later become the SQLCAT group) where I was shown a berserk 3rd party report writing tool which would routinely create individual stored procedures over over 30mb in size. The largest I saw at that time was 38mb.

38mb stored procedure, that is 39,845,888 characters, where do you even start with that, will SSMS even open it?

If we take those as the two extremes stack overflow and 38mb procedures then somewhere between those two sit most of the stored procedures in production today.

When we look at the T-SQL development landscape today we have the ability to write using a development IDE:

  • Visual Studio SSDT
  • SSMS with SQL Prompt
  • DataGrip

We have the ability to unit test code:

  • tSQLt
  • dbUnit
  • ssdt tests

If we use tSQLt then we can mock tables and procedures!

We can measure code coverage (https://the.agilesql.club/blogs/Ed-Elliott/2016-04-08/SQLCover-Code-Cove...).

When we are ready to deploy we are literally spoiled for choice at ways to do deployments:

  • Redgate SQL Compare
  • Redgate ReadyRoll
  • SSDT (Yeah!)
  • Flyway
  • Liquibase
  • blah blah blah

So we really have this ability to start to write and maintain code in SQL Server but the question is, should we?.

I personally believe there are two factors that we need to understand:

  • Maintaining existing applications
  • Data locality

So in reverse order there is "Data Locality" this is the idea that you should work on your data where it is located, so if you want to get a total of a specific customers transactions do you really want to pull every record back to your app and then calculate the value or do you want to run a query or a set of queries that does the work for you?

If you have come to the decision that you want to do the calculation where the data is located then do you write in-line (or bleurgh generated) code to do it or do you want to write a stored procedure? The correct answer is to write a stored procedure.

Stackoverflow doesn't use stored procedures so we don't need to

Stackoverflow is a specific use case and they decided to use .Net so they have a specific set of problems to deal with in terms of performance. They deploy (as I understand it) 10 times a day so if they need to change a query then they can quickly and easily - how quickly can you modify code and get it to production to fix a problem causing downtime on your mission critical app written in powerbuilder 20 years ago? (I jest but you get the point)

Why is writing a stored procedure the correct answer?

When I say stored procedure I mean, stored procedure, function, view, computed columns, etc, something that runs where the data is located.

If we look at the first factor to understand about writing business logic, "Maintaining existing applications" then we need to understand that there are many reasons why you might not be able to modify the code and you need to change the database and it boils down to the fact that:

  • A compiled application does not change
  • The volumne of data in a database does change

As much as I would like to, sometimes you are dealt a crappy hand with a database you have to support and if the database is used, over time as more data is added it is unavoidable that the performance behaviour will change.

If the code is available to change without re-compiling the application then you can adjust to support the different data volumes and characteristics.

So you should put all your business logic in stored procedures?

No, personally I believe that you should only put in the code that must be run locally to where your data is. When you do put logic in your stored procedures then it should have unit tests (tSQLt really helps here).

When else should you have business logic in your stored procedures?

You shouldn't but as I said sometimes you are dealt a bad hand and are stuck with business logic (vendor app, old unmaintained, new maintained badly architected apps) - when this happens then make sure you use the tools available to you to help maintain and make changes. Make sure that you:

  • Use an IDE and refactor
  • Use source control
  • Write unit tests (tSQLt)
  • Live the dream


Business logic is often complex and better modelled in a language that handles that better - T-SQL is about interacting with data, how to store it, how to read it, how to change it in an efficient manor. T-SQL isn't about modelling objects (or functions) it is about data. Applications use data to do things with.

Keep your data locality in mind, think about performance but try to keep business logic out of stored procedures. Where you cannot avoid business logic in stored procedures use tools and processes to help.

How do you audit automatically generated sql deployment scripts?

  • Posted on: 3 May 2016
  • By: Ed Elliott

I saw recently an email where someone stated that the reason that they manually create deployment scripts is that because of the restirctions put on them by the requirements for auditing and the ability to be able to demonstrate exactly what was deployed at what time.

When automating deployments it really makes sense to use a tool to generate the deployment so you can concentrate on writing productive code instead of deployment scripts. How do you balance the ideal of spending less time writing deployments but also ensure you can audit what happened?

If your deployment process looks something like:

  • 1. Compare source and destination + Deploy changes

Then you don't have much chance, but if you split it into two discrete steps:

  • 1. Compare source and destination + Create deploy script
  • 2. Run deploy script against target
    • Then this means that you automatically have an audit trail of what was deployed (the deployment script). You also get the added benefit that the deployment script can be manually reviewed and a friendly dba can run it manually or automatically if they have the capabilities.

      If you are writing a deployment pipeline for a database and you miss out the continuous delivery step and go straight for continuous deployment then life is actually a little more complicated, especially from an auditing / non-trust of tools point of view.

What is code coverage for?

  • Posted on: 11 April 2016
  • By: Ed Elliott

Code coverage gives you an indication of how well tested a particular area is. It is not a measure of code quality and having a statement covered by a test does not mean that the code is accurate.

Using code coverage you can see how risky changes are to a particular area of code. If you have a high level of code coverage you can be fairly confident in changes, if you have low code coverage in an area then when you make changes you will need be careful.

If we take an example, you could have this code:

create procedure blah
if (select count(*) from table where col = 'a') >= 1
declare @res int = (select count(*) from table where col = 'a' and (select count(*) from table where col 'a') = 0;
select @res;

If you also have this test:

exec tSQLt.FakeTable 'table';
exec blah;

Technically there is a test around the procedure but because it doesn't setup any test data or check the result it is only covering the "if" statement and not the "then" part of it.

If we use code coverage we can see that the actual work to generate "@res" is not run so it gives us a pointer that we have poor tests covering this procedure.

Should I aim for 100% code coverage?

This is a difficult one to answer because although having a high percentage of code coverage is useful, it really depends on the code and what it is doing. If you have a stored procedure such as:

create procedure blah
if (select count(*) from table where col = 'a') >= 1
declare @res int = (select count(*) from table where col = 'a' and (select count(*) from table where col 'a') = 0;
select @res;

The I would like to see 100% code coverage, if I had something like:

create procedure blah
begin try
insert into table(column) select 'value';
end try
begin catch
print 'this should never happen'
end catch

Then is it the most important thing to have the print statement covered?

Where it gets really complex is if you have different branches which mean that 100% for a particular area just isn't enough and you want to aim for each branch to be hit the right number of times, for example:

create procedure blah
declare @operation int = (select count(*) from table where value = 'a')
select @operation = @operation + (select count(*) from table where value = 'b')
if @operation > 1
declare @res int = (select count(*) from table where col = 'a' and (select count(*) from table where col 'a') = 0;
select @res;

In this case I am interested in the two different ways to get into the "then" part of the if statement, whether we have values in the table for a or b.

The thing to note is that coverage is used to give you an indication of where you have a full set of tests rather than as a way to gauge the correctness of the code.

What about sub-queries?

It is possible to write queries such that sql will not use part of them when executing the statement and although it would be interesting to know what parts of the query run and return valid results that is really the point of writing unit tests and proving the correctness of the code. Code coverage tells you whether you have tests that execute the actual statement and it is down to you to make sure the code works as you expect.


To view code coverage in SQL Server grab a copy of SQLCover: