Ed Elliott's blog

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"?>
<RunSettings>
<TestRunParameters>
<Parameter name="TestDatabaseConnectionString" value="server=.;initial catalog=tSQLt_Example;integrated security=sspi" />
<Parameter name="IncludePath" value="AcceleratorTests" />
</TestRunParameters>
</RunSettings>

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:

https://visualstudiogallery.msdn.microsoft.com/cba70255-ed97-449c-8c82-e...

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;

https://github.com/GoEddie/tSQLt-TestAdapter

Any issues shout!

ed

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\Cmdlet.dll
WindowsPowershell\Modules\DacFxed\Microsoft.VisualStudio.Data.Tools.Package.dll (remember this is an empty text file)
WindowsPowershell\Modules\DacFxed\bin\dll\Microsoft.Data.Tools.Schema.Sql.dll
WindowsPowershell\Modules\DacFxed\bin\dll\OtherDacFxDll's

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"

or

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

or

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!

ed

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:

https://www.quora.com/How-many-lines-have-the-longest-MSSQL-script-you-h...

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

Finally

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
as
if (select count(*) from table where col = 'a') >= 1
begin
declare @res int = (select count(*) from table where col = 'a' and (select count(*) from table where col 'a') = 0;
select @res;
end

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
as
if (select count(*) from table where col = 'a') >= 1
begin
declare @res int = (select count(*) from table where col = 'a' and (select count(*) from table where col 'a') = 0;
select @res;
end

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

create procedure blah
as
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
as
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;
end

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.

SQLCover

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

https://the.agilesql.club/blogs/Ed-Elliott/2016-04-08/SQLCover-Code-Cove...

ed

SQLCover Code Coverage for SQL Server T-SQL

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

Open Source code coverage tool for T-SQL, SQL Server 2008+

What is code coverage?

Code coverage is a way to see how many statements in your database code have been executed when you ran your tests(s). It is a way to see how well covered with tests a particular area is - the better covered with tests, the less likely you will miss issues when you make changes in those areas.

What is code coverage for SQL Server?

SQL Server gives us a great tracing mechanism, either profiler or extended events which details exactly which statements have been run. SQL Server doesn't do so well at telling us what statements we could possibly run in some code but the Transact Sql Script Dom that is part of the DacFx does give us the ability to break T-SQL code into statements so combining the two we have the ability to take a stored procedure such as:


create procedure abc.def
as

--select 100 from a_table but_is_commented_out_so_do_not_count_me
select 100;
select 200
select 300
begin
select 400;
end
if 1=2
begin
select 500
end

go

and we can use the scriptdom to tell us that there are 6 statements or "lines of code" in this procedure and when we run it while covering it we get the details that all of the statements apart from "select 500" are run which gives us a code coverage of 5 out of 6 statements.

Who decides what a statement is?

Partly me but mostly SQL Server itself, if you run a trace with SQL Server/sp_statement_xx you can pretty easily see that the select's are statements, the begin/end by itself is not a statement and the if is a statement - this makes sense to me, the begin/end doesn't actually do anything it is just to help you keep your code tidy, for example this is valid SQL and is only 1 statement:


begin
begin
begin
begin
select 'wowsers'
end
end
end
end

Where can this be used

This version of code coverage is a .net dll with some examples in powershell so it can be used with any build server or test framework, but it was written primarily for tSQLt and has some filtering to filter out tSQLt objects and test procedures so anything that is in a tSQLt test class will be ignored. It was also written to fit into the Redgate DLM automation suite so if you have that then I would recommend using it.

How do I use it?

This first example I will use the SQLRelease module from the DLM suite...

  • 1. Grab the zip from: https://the.agilesql.club/SQLCover/download.php
  • 2. In powershell dot source the "SQLCover.ps1" file
  • 3. Call "Get-CoverRedgateCITest", you will need to pass in the path to the "SQLCover.dll", the connection string to the database, the path to the nuget package to test and a server and database where the code can be deployed to.
  • 4. The return from Get-CoverRedgateCITest is a 2 object array, the first is the Redgate results from the tests themselves "RedGate.SQLRelease.Compare.SchemaTesting.TestResults" which can output a junit xml file. The second is a SQLCover.CoverageResult object which as well as giving the count of statements in the database and covered statements while running the tests lets you output the code coverage results as xml or in the open coverage xml format which can be converted to a pretty report using the https://github.com/danielpalme/ReportGenerator tool.

In powershell code that is:


. .\SQLCover.ps1
$results = Get-CoverRedgateCITest ".\SQLCover.dll" "server=.;initial catalog=database_name;integrated security=sspi;" ".\path\to\nuget.nupkg" "." "database_name"
Export-OpenXml $results[1] "c:\output\path\for\xml\results"
Start-ReportGenerator "c:\output\path\for\xml\results" "c:\path\to\reportgenerator.exe"

You will need to obviously provide the correct paths, a nupkg file and if you want a pretty report then reportgenerator.exe.

If you don't have SQLRelease then you can do the same thing but you must deploy the code yourself first:

  • 1. Grab the zip from: https://the.agilesql.club/SQLCover/download.php
  • 2. In powershell dot source the "SQLCover.ps1" file
  • 3. Call "Get-CoverTSql" this returns a CoverageResult object which as well as giving the count of statements in the database and covered statements while running the tests lets you output the code coverage results as xml or in the open coverage xml format which can be converted to a pretty report using the https://github.com/danielpalme/ReportGenerator tool.

In powershell code that is:


. .\SQLCover.ps1
$result = Get-CoverTSql ".\SQLCover.dll" "server=.;initial catalog=database_name;integrated security=sspi;" "database_name" "exec tSQLt.RunAll"
Export-OpenXml $result "c:\output\path\for\xml\results"
Start-ReportGenerator "c:\output\path\for\xml\results" "c:\path\to\reportgenerator.exe"

If you wanted to call an exe, for example an nunit test runner or msbuild then instead of calling Get-CoverTsql you can call Get-CoverExe which will start the coverage, then start the exe and stop the coverage when it completes. If you want to completely control when coverage sessions are started or stopped then SQLCover.dll has a .Start() and .Stop() methods, see Get-CoverRedgateCITest for examples.

What output do you get?

You get a CoverageResult object which has two public properties:


public long StatementCount;
public long CoveredStatementCount;

These give you the ability to react to a poor coverage result or you can convert the CoverageResult to:

  • 1. Basic Html report "$result.Html()"
  • 2. Open Cover Xml format which can be converted to something pretty or you can parse it yourself

I would strongly suggest you grab a copy of reportgenerator so you get reports that look like:

Code coverage report generated using reportgenerator.exe

Code coverage detail generated using reportgenerator.exe

Oooh pretty....

SSDT Dev Pack

The ssdt dev pack already has code coverage but it is only within an ssdt project and it isn't possible to generate code coverage results as part of a build process so although I will still work on that, I will migrate that over to using this at some point.

Finally.

I just wanted to say a big thanks to David Atkinson at Redgate, he sponsored the project which meant that I could focus on it and provided some great feedback and guidance along the way :)

Post Deploy Scripts In Composite Dacpac's not deploying

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

Composite projects in SSDT are a really useful way to logically split databases up while still being able to deploy into a database as a whole. Even if you have a fairly simple database in a single SSDT project you should put your unit tests somewhere. What I like to do is have my solution configured like:

  • Main.dbproj - actual production code and reference data
  • Main.UnitTests.dbproj - unit test code + references to tSQLt etc

The unit tests have a "Same Database" reference pointing to the main project so it is as if my tests were in the same project.

To deploy the unit tests I deploy the unit test project and use option to include composite projects, this then also deploys the production database. When I go to other environments I can deploy the production code without the unit tests. This works really well except when you deploy like this, only the pre/post deploy scripts from the dacpac explicitly being deployed are used. In this case, the scripts in the unit test project are run but not the ones in the main project but when the main project is deployed by itself the scripts are deployed.

To fix this issue what I do is to have an msbuild copy task in the unit test project:

<Target Name="BeforeBuild">
<Message Text="Copying Post Deploy Script" Importance="high"/>
<Copy SourceFiles="$(SolutionDir)\DeployScripts\Deploy\PostDeploy.sql"
DestinationFiles="$(SolutionDir)\DeployScripts.UnitTests\Deploy\PostDeploy.sql" OverwriteReadOnlyFiles="true"/>
</Target>

What this means is that the script from the main project is always copied into the test project and so I know that it is always run.

If you do this you need to ensure that people don't use the script in the test project as the changes are overwritten every build, people don't normally make that mistake more than once!

It is a little clumsy but a pretty simple solution.

There are cases where this doesn't work and more complicated things you can do include:

  • Before you deploy, iterate through all the dacpacs and use the .net packaging api to pull out the pre/post deploy scripts then order them as you want and write them to your main dacpac.
  • Write a deployment contributor to do it but there are challenges

I have put a sample on github:

https://github.com/GoEddie/PostDeployCopy

Hopefully it helps someone else.

Ed