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…
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:
Just to verify that it is running let’s run
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
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:
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
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.
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).
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:
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.
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.
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…
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…!!
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
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: