How to use tSQLt unit test framework with a SQL Server database in a docker container

This blog post describes how to add the tSQLt unit testing framework to a database running in a docker container. You may have already read my article on how easy it is to install against a ‘normal’ SQL Server instance – running on windows…

How to install the tSQLt unit test framework to start unit testing your database code

..but what about SQL Server running in a docker container??


I have a SQL Server instance running in a docker container as described in my previous post:

Setting up CentOS to run docker for SQL Server

The main reason why I run SQL Server in a container is so that I can run up specific versions – all from a script and generally as part of an automated build within Continuous Integration processes.


The first thing we need to do is spin up our docker container which has our SQL Server instance:

1. Spinning up our SQL Server in container
Let’s spin up SQL Server and use the latest version of SQL Server 2017

Just to verify that it is running let’s run

docker ps

We can see that the container has an ID of 789f58fbef3f and a name of sql1 .

I have also mapped the default port of 1433 to 59666

2. container SQL Server is running

We can now connect to SQL Server on port 59666 and do some typical queries whenever we connect to an instance for the first time:

3. What version is running
Connecting to our SQL Server instance running in a container known as 789f58fbef3f

Note that in the screenshot above – the servername result is the name of our container.

Now we can create our database and SQL Authenticated users as normal.

Yes – you interpreted that sentence correctly – right now you cannot use windows authenticated users against SQL Server running in a docker container on Linux. There is a way to get around this that I will be blogging about in late January 2019.

We can add our database to Source Control as per normal and apply the latest changes in our branch. I use the popular Redgate tooling for Source Control (and DevOps) activities

9. Adding stuff from Source Control
We can treat our database in the container like any other database (except for windows auth…)
10. Progress
Applying changes from Source Control to a database running in a container – easy as..!!

So all that is left to do is download the tSQLt framework (a whopping 86KB zip file)  from

Inside the zip file are some files:

7. tSQLt download
tSQLt comes with an example database you can use to try things out!!

There is:


Which is a script that allows you to create a database and run your first unit tests. It’s a great example and you can follow the documentation at


Which is a script to enable CLR. This is partly why you should only run tSQLt on your DEV or selected TEST databases.

This is where I diverge slightly from a stock standard install:

The SetClrEnabled script does the following:

EXEC sp_configure 'clr enabled', 1;

Whereas for Linux based SQL Server we need to run this – as it will not install with clr strict security enabled – note we have to ‘show advanced options’ first:

EXEC sp_configure 'show advanced options', 1

EXEC sp_configure 'clr enabled', 1
EXEC sp_configure 'clr strict security', 0


This is the framework itself. Now before you run this you have to do the following to get it to run in a database running in a container:

In the script you need to change PERMISSION_SET = EXTERNAL_ACCESS to be PERMISSION_SET = SAFE

We now run the script and voila – we have tSQLt in our database and can now write or run all the unit tests we want.

8. Running tSQLt against container SQL Server
Hooray – we now have tSQLt running in a container

So there you have it – you can add tSQLt very easily (just 2 changes to make) to a database running in a docker container.

As mentioned the reason why I am very interested in running tests against databases running on container is around testing – I can spin up the container, download a particular patch level of SQL Server 2017 and above, add my database from source control, apply any referential data from source control and apply tSQLt which allows me to run a plethora of unit tests.

All within seconds and all consistently across multiple environment levels (QA, Integration, Functional test etc).

So go visit

or these other great sites:

and in no time at all you will be writing your very own unit test.


How to install the tSQLt unit test framework to start unit testing your database code

This blog post describes how easy it is to install the tSQLt framework.

Don’t know much about tSQLt? If you are serious about dealing with data in SQL Server then you should be as serious about unit testing the changes you are making to your stored procedures, functions and the like.

In fact I wrote a blog post about unit testing here:

Doing DevOps for your Database? You need to start here…

So I am assuming that you have realised how important it is to unit test your data and code so here’s how to install tSQLt to your database.

First – go to the tSQLt website:

and go to download the framework:

Yip – it is just 86KB of unit testing goodness

The direct URL is

You will get a zip file with some files in it:

7. tSQLt download

There is:


Which is a script that allows you to create a database and run your first unit tests. It’s a great example and you can follow the documentation at


Which is a script to enable CLR. This is partly why you should only run tSQLt on your DEV or selected TEST databases.


This is the framework itself.

We now run the SetClrEnabled & tSQLt.Class scripts and voila – we have tSQLt in our database and can now write or run all the unit tests we want.

I guess at this stage you might ask – why do I need to enable CLR – well let’s say you don’t enable it and just run tSQLt.Class.sql – you will get the following error:

You need CLR – but that’s OK as only install tSQLt in your DEV or selected TEST environments.

So yeah – enable CLR and then when you run the tSQLt.Class.sql script – things will go much better:

This really is the most simple and easy method of  installing a unit test framework for SQL code

So now you can start writing your own tests. The tSQLt framework adheres to the three A’s of unit testing:




It is very easy to create a template that you just use to write your unit tests.

The tSQLt website has some great examples and tutorials and honestly – the learning curve is very small – I’d say most Data Professionals can be up and running unit tests within 1-2 hours.

So go visit

or these other great sites:

and in no time at all you will be writing your very own unit test.

In my opinion tSQLt is that sweet spot of easy to use and comprehensive enough to make a difference.


SQL Server services will not start automatically after server reboots

This blog post is about a situation where after windows patching the SQL Server service and SQL Server Agent services will not start automatically – but will manually.

At a client site they had recently patched the servers – things went swimmingly – except the SQL Server services would not start.

Whilst automatic – the services would not start

Manually starting the services by right clicking | choosing start worked….

It is SQL Server 2017 with CU12 applied.

We tried a few things:

Making the services Automatic (Delayed)

Increasing the service pipeline timeout from 30 seconds to 60 seconds

But nothing worked.

Looking at the event logs I (eventually) found this:

Event ID 7000:

The MSSQLSERVER service failed to start due to the following error:
The account name is invalid or does not exist, or the password is invalid for the account name specified.

Which is bizarre – as the service account had been used for months – but after each reboot the services had to be manually started. GPO and other things had been blamed but no one could actually find out why.

Just for a laugh – I decided to “rename” the service account.

From svcSQL@<domain> to <domain>\svcSQL

Rebooted and it worked.

Just to make sure it wasn’t a false positive I left the SQL Server Agent service how it was – and it did not start.

<domain>\<service account> works – – but why…?

As to why this is the case – I’m not sure. To date I had always set up my SQL Server services with <domain>\<service account> so I have never stumbled across this – until now.

But I don’t care too much – it means that the client can reboot their server and know SQL Server will come back…


Setting up CentOS to run docker for SQL Server

This blog post is about running SQL Server in a docker container on CentOS Linux distribution.

This was both an experiment and also for me to re-learn CentOS – years ago I had done a fair bit with CentOS – back when I was a true Operations Consultant. Up until last weekend I had been using Ubuntu – as it is really easy to use.

All SQL Server container images on docker are built on the Ubuntu image so I wondered – can I run it on a CentOS Linux server running docker?

BTW – the obvious answer is ‘yes, of course because it is just a container”.

But I wanted to try it out and see what differences there were.

The first main difference for me so far – remembering to run sudo when I want to elevate my permissions. This is a good thing – I did many bad things in Ubuntu that I should have thought about first – that is why sudo is a good thing for people like me…!!

[Yip – you can run sudo -i but I also like to remind myself not to do bad things…]

So if you are thinking of running docker on CentOS and

Install the latest version of Docker CE:

$ sudo yum install docker-ce

If prompted to accept the GPG key, verify that the fingerprint matches

060A 61C5 1B55 8A7F 742B 77AA C52F EB6B 621E 9F35

and if so, accept it.

Initially start docker:

sudo systemctl start docker

To make it start every time the  Linux server

sudo systemctl enable docker

Download latest SQL server image:

sudo docker pull microsoft/mssql-server-linux:latest

Run up our container and assign a non-default port – in this case I am  swapping out port 1433 to port 56969:

sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' -e 'MSSQL_PID=Developer' -p 56669:1433 --name SQLServer-Docker-DEV -d microsoft/mssql-server-linux:latest

Port mapping is specified via -p host port:container port.

Therefore in my example above -p 56669:1433 means that port 56669 on the host (CentOS VM) is mapped to port 1433 in the container. I am not a fan of using the default port 1433  – mainly as I like to stipulate different ports for different SQL Server instances (QA vs UAT vs PROD). This means that all connection strings I create from outside the container will need to include port 56669. How do I remember this number – that’s where configuration management comes in – another blog post I think…

I also want to change the password to something more secure (and less published on the internet) and this allows me to also show you how to access the SQL tools which are built into the image:

How to use SQL Server tools in the container

sudo docker exec -it SQLServer-Docker-DEV "bash"

Once inside the container, connect locally with sqlcmd. Note that sqlcmd is not in the path by default, so you have to specify the full path.

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourStrong!Passw0rd>'

How to Change SA password in container

Once we have connected with sqlcmd we can then use our native T-SQL to change the password:

Using sqlcmd to change our sa password and then testing it out

Now we can go to the Azure portal and allow access to this port:

Allowing network access to the SQL Server port from certain IP addresses in Azure portal

My preference is to tie it right down to the hosts that I know will access the SQL Server instance running in this Ubuntu container on this CentOS VM hosted in Azure.

Kinda reminds me of the dream layers of the movie ‘Inception’.

In the next blog post – let’s connect to this VM using SQL Server Management Studio on our PC.



This is a company that practises what they preach

How good is a company at engaging with their user base? Obviously when things are going great then it’s wins all around for the company and hopefully the people have invested their money in that company’s products…

When things are not going well – I feel this is how you know what a company is really like and more importantly how their endemic culture actually is.

The company I am talking in this blog post is Redgate, I’ve known Redgate products for the best part of 3 years. Back when I started looking at how I could integrate database deployments with application deployments – specifically around folding in Continuous Delivery principles and processes – one company was leading the rest of the pack.


I like Redgate tools – they make what can be difficult and cumbersome in other technology stacks simple and easy. I like that – mainly because there are enough hard things to do in computers and getting a database into source control (and easily deployable) can be right up there in terms of hair pulling.

I especially like the culture of Redgate – I’ve yet to meet anyone that works there that is not engaging, passionate and knowledgeable about all things DevOps. It’s awesome to deal with them, they are really friendly and even the scariest of DBAs ( t | w) is a guy who is fanatical about making stuff go and better for the community and industry. Heck,  they just hired Kendra Little ( t | w ) so they must be both a cool company and awesome company.

(Side note: It was thanks to Kendra’s awesome blog posts and videos that helped me become a better Data Professional and yeah – I’m a big fan of what she does for the community. Plus, she’s as humorous as she is intelligent).

So what about when things go wrong with a company?

That’s normally when you find out how they really are.

If you’re a customer of Redgate you might have got an email from Tony Payne – their Chief Operating Officer – so the thing that occurred is fairly important and I like the fact it came “from the top”. The issue was a security vulnerability in SQL Monitor – which is a fairly important thing to fix and get right.

I knew about this security vulnerability as I had contacted some of the people I know at Redgate and trust and said “hey, I have noticed something and I think you need to look at SQL Monitor”.  I use SQL Monitor and I recommend SQL Monitor to my clients – it is a fantastic tool and the way it integrates with other tooling really fits in with the ethos of DevOps .

(Another side note: because I’m the Hybrid DBA – I don’t just do databases, I also do stuff like pen testing, configure web nodes with powershell and/or write in ansible to make stuff go… so sometimes I don’t just use tools, I like to break tools)

Now Redgate could have just said “cool, thanks for bringing this to our attention” and left it at that. And whilst that sounds like no company would do that – trust me there are companies that do exactly that.

Redgate responded back to me immediately, the product manager kept me in the loop both of how serious they took this and more importantly what they would do about it. They went to work immediately to fix it and got in outside consultants to audit that things were fixed.

As per the website above detailing what they did – they did all the right things.

What that article doesn’t tell you is they did something that Agile brings to the table – because they practise Agile they were able to React and Adapt to something that was not forecast as part of sprint planning.

Redgate preaches all things DevOps – but could they practise it. The long answer is Yes. Yes they can and for me that is a fantastic measure of a company.

People are fairly intelligent and can tell if you’re wanting to pull the wool over their eyes (you can fool some of the people some of the time…). If they had just swept this under the rug then yeah – their whole brand about delivering value quicker and keeping your data safe would be a fallacy.

But it’s not – they understand how important your data is, they know how to deliver value quicker because – they did.

I’m really impressed by how they handled this situation – I was very lucky to be an insider on what they were doing and I really appreciate that they let me in on that. It made me feel valued and made reinforced why I love both their products and the culture they have within their company.

Nice one team!!

Want to make your database deployment way safer, easier? You should check them out:



Connecting to SQL Server 2017 using Visual Studio 2017 and getting “An incompatible SQL Server version detected”

This blog post details the error you may get when using Visual Studio 2017 and you get errors that you cannot connect to SQL Server 2017 using Test Explorer or SQL Server Object Explorer.

TL;DR – upgrade Visual Studio from base version…..

I had built a new DEMO machine for showcasing Visual Studio, namely SQL Server Data Tools as well as vendor tools that help people “do DevOps” stuff with databases.

I cloned my repository in my Azure Repo (read up on them here ) and kicked off a BUILD in Azure DevOps  (read more here ) (formerly known as Visual Studio Team Services (VSTS)) and everything was going great.

I then wrote some unit tests for some stored procedures and went to build my solution in Visual Studio – which worked, then publish it to my local SQL Server 2017 instance – which failed.

The error was “Ensure you have the same name in master or <database>” which is a weird error.

Thing was – I had used Test Connection – which worked!!.

Initial Error
Don’t believe that things are OK…. while the Test Connection worked – nothing else did

But the actual publish failed – couldn’t connect…. (what???)

So I went to use SQL Server Object Explorer in Visual Studio to try and connect and got the following error:

error connecting VS
I couldn’t add my instance here – but could ‘Test Connection‘ in other parts of VS2017 (!??!)

I upgraded my SQL Server 2017 instance to CU11 – mainly as my version was base version:

SQL server Version Originally
In my defense I did only install this today……. 

But of course in reflection (reflected whilst I waited for CU11 to upgrade my instance) – I could connect to SQL Server 2016 instances. But couldn’t connect to my Azure SQL instances…

…..what the???

Which then made me check the version of Visual Studio 2017.

It was version 15.0 – and lo and behold there was an update waiting in notifications area telling me that an upgrade to 15.8.7 was ready to be installed.


So I upgraded……

At least some things are consistent in Visual Studio  🤣

…….and things now work:

Working Now
This now works – hooray
Don’t worry – I did a Build first before I published!!

….and I can now (after a 2 hour hiatus of upgrades) go back to writing unit tests and running them against instances



Getting error “unable to parse remote unpack status” when attempting to push to Azure Repo in Azure DevOps

This blog post is about a situation where I went to push my latest local changes to my git repository up in Azure DevOps and got the weirdest error:

error: unable to parse remote unpack status: ng refs/heads/..

An error occurred while communicating with the remote host. The error code is 0x800703E5.


Short answer:

Upgrade Git For Windows.

Longer Answer:

it took me a while but I reliased it was related to the fact that the git version (2.16.2) I was using on my windows machine was old.

The only real give away was

remote:  Azure Repos

And something went off in my head “huh – I wonder if it is because I’m using an old version of Git for Windows”.

So I upgraded (the installer for Git for Windows is very easy) and the problem has gone away..


Why you should always build an Azure based VM in a Resource Group

This blog post is based on conversations I witnessed between MVPs around commissioning and more importantly de-commissioning virtual machines (VMs) hosted in Azure. Don’t worry – this blog post is not going to divulge NDA material….

…my MVP award is way too valuable to me to squander it!!

It comes down to this simple fact – you might still pay for resource usage in Azure even after you have deleted your VM….


Because if you just delete your VM – you will leave behind resources that were associated with it.
Let’s look at how we spin up a VM:

Create VM
Creating a VM in Azure – using the Add button

I’m not going to go into all the different configurations I’ll choose – this post is about what is left behind when I delete the VM and why I’ll pay money unnecessarily.

As until I witnessed the discussion between MVPs (a quite vehement discussion BTW but that’s not for this blog post…) I had always plonked all my VMs in one Resource Group. you know to be tidy and concise…

Anyway – our VM is being deployed – this is the bit I love:

VM being created
I love the smell of new VM

……creating resources means I get to try stuff out. This particular VM is for a DEMO I am going to do at a client site – we’re going to build SQL Server using Desired State Configuration (DSC) – in fact we’re going to build 20 SQL Server instances – and then create 10 Availability Groups – all from Source Control and all scripted. That is definitely for another blog post.

It’s gonna be awesome!!

So in the time it takes for me to make a coffee I have a new VM:

deployment done
3 minutes 48 seconds – seriously that is why you need to host stuff in Azure

Let’s look at the resources in our Resource group:

There is more than just our VM in here

So I’ve done the DEMO of SQL Server DSC (blog post coming) and now we want to get rid of the VM.

This is what I used to do:

Go into Virtual Machines and hit Delete

delete VM
See ya VM..

So within about 5 minutes our VM is gone – so let’s look in the Resource Group:

leftover resources.png
There are quite a few things leftover…

So the argument was that this was “designed’ by Microsoft to get money out of people – you know by keeping the resources that are left behind.


Yeah ok….

The actual story is that Azure allows you to share resources like network configs etc and doesn’t actually know if you want to keep these things – so it allows you to piecemeal manage the resources within your resource group.

The best quote I saw was:

” All of these things are how Cloud works. Nothing to do with Azure. The same is true on AWS.  Everything is treated as single resources that can be “combined” to many different things and I don’t want anybody to just go and delete anything unless I explicitly tell them to. 

This means that yes – if you manage your VM from a VM perspective and not all the resources associated with it – you will get charged for things lying around. It’s not a completely free cloud platform – for obvious reasons.

So – if you want to remove your VM in its entirety then place it in a Resource Group and remove the Resource Group. Thus you remove all resources associated with that VM and do not get charged unnecessarily.

There might be times where you want to host associated things within a resource group 0 my advice – label them and put tags on them so that you know what belongs to what if you need to remove things.

Easy as.


How to sync user logins across SQL Server instances – dbatools is brilliant

This blog post is about how brilliant dbatools are. In this case – for syncing user logins between SQL Server instances.


Whenever I do my “DevOps and the DBA” talk I dedicate a minute or two talking about dbatools.

You can find the tools here:

Simply download and install on your server – or a machine that has visibility to what you want to connect to.

When building new servers the most important thing after restoring and securing the database is syncing up the users. This is especially important for Availability Groups as SQL Authenticated users required the SIDS to be the same.

In the past I had some very long winded code that would do the sync – it was a mixture of TSQL and PowerShell. It worked but you know – it was cumbersome.

So I effectively practiced what I preached recently and used the Copy-DbaLogin command – actually I looked at what it did first by running:

Get-Help Copy-DbaLogin -Detailed

For what I needed to do – I needed both the SQL Authenticated users AND all Windows users/groups – so I just ran this:

Copy-DbaLogin -Source OLDSERVER -Destination NEWServer

Which results in the following example:

This is brilliant

The upshot of all this is that syncing users between SQL Server instances has never been easier and means I can throw away my terribly written script.



How to change the TFS Agent _work folder

This blog post is about how to change the default work folder _work that TFS agents use when building a solution.


I’m now a consultant – which is awesome – it means I get to visit clients and make a difference for them.

One particular client had installed TFS and their remote build agent was installed in C:\TFSAgent.


By default when installing TFS Agent you can choose the default for the work folder _work and normally this goes under the root directory of where you install the agent. So in this example they had the agent work folder at:


Which was fine – until the builds were kicking off regularly (thanks to good Continuous Integration practices they were doing builds almost hourly) and C:\ started running out of space.

So a D:\ was added to the server.

but how to change the work folder to D:\TFSAgent\_work

A lot of posts on the internet are saying just remove the old agent and install it again. That to me seems a bit drastic.

If you’ve read my previous blog post on changing agent settings– you will know about the hidden file .agent

The .agent file is our friend for changing settings

Except the settings file is set out in JSON.

Which caught me out – as I made the change D:\TFSAgent\_work and the agent was not happy at all.

So to change the default _work folder to be D:\TFSAgent you need to:

1. Stop the agent service

2. Open the .agent file which will look something like this:

“agentId”: 10,
“agentName”: “BUILDAGENT”,
“poolId”: 3,
“serverUrl”: “https://YourtfsURL.something.local/tfs/&#8221;,
“workFolder”: _work”

3. Edit it like this:

“workFolder”: “D:\\tfsagent\\_work”

Note the double slashes – due to JSON

4. Start the agent service and kick off a build and watch TFS update the directory with what it need.

That really is it – but hopefully by reading this post it will save you time and energy by NOT having to reinstall your agent.