Small story about obtaining Microsoft SQL Server for Linux on my PC
I think all of us had a case in the professional experience when the task that seemed easy on first look needed a lot of efforts in reality. It happened with me when I decided to try new Microsoft SQL Server for Linux on my work PC.
Such task for me was a change the collation of the instance of Microsoft SQL Server for Linux hosted in the docker container. And now I’m going to tell you how I’ve solved that problem and where it led me.
This story happened when I was moved to the new project which used Microsoft SQL Server as DBMS. In better times my colleague shared with me the instance which has been installed on his computer. Unfortunately that time he said that his computer is slowing down under load and he would no longer share his computer with anyone. “Not a problem”, I thought, “With docker I can quickly set up an instance on my own computer”. After a small search I discovered image microsoft/mssql-server-linux and on first glance everything looked perfect, but now I understand that then my adventures had begun.
Changing server collation
The problem appeared when I discovered that server in container has collation
SQL_Latin1_General_CP1_CI_AS and there is no simple way to change it to
Cyrillic_General_CI_AS which I needed. So with command
docker run -p 1433:1433 -e ‘SA_PASSWORD=Password12!’ -e ‘ACCEPT_EULA=Y’ -e -i -d microsoft/mssql-server-linux /bin/bash I ran an interactive container and entered it via command line.
After quick search I found the article with instructions on how to change server collation by using
mssql-conf but unfortunately I met the problem that as it turned out had already been described here. Maybe those problems could be solved by required packages installation and system configuration, but this approach seemed to me too complicated.
Shortly after that failure I had the idea that collation for Microsoft SQL Server on Linux can be changed in the same way as for Microsoft SQL Server on Windows. It was a good idea, and after reading help I executed command
/opt/mssql/bin/sqlservr --force-setup -q Cyrillic_General_CI_AS that did all I needed. Finally I commited container changes into new docker image and started SQL Server with proper collation in new container.
Shortly afterwards I decided to reproduce described process on my home computer and found it too complicated. That is why the obvious solution was to upload image on docker hub and use it on another computer. At first I wanted to upload image which was created recently, but it had several disadvantages. Firstly, image was created on the particular version of the image microsoft/mssql-server-linux and won’t be able to be automatically rebuilt on the basis of a new version in case of it comes. Secondly, collation is specified when creating an image and if instance of the Microsoft SQL Server is needed with another collation then another image will have to be created.
So, after several long evenings I wrote simple setup script
and uploaded them into repository hosted on github. After this I configured public automated build on docker hub, based on github repository, and linked repository microsoft/mssql-server-linux to it.
So now everything looks quite good, image rebuilds automatically after changes in base image or after changes in Docker file or setup script. Moreover, if I want to have an instance of the Microsoft SQL Server on Linux with special collation on my PC, I’ll pull image from docker hub and specify desired collation in environment variable.
But what about image validation? It’s necessary to know that after changes into base image (microsoft/mssql-server-linux) described approach for changing the collation is still working. Checking procedure itself is quite simple, test application can connect to instance of the Microsoft SQL Server on Linux hosted in docker container and compare collation of the
tempdb (it should be the same as server collation) with expected collation. I wrote the simple application that does exact this thing not yet knowing how it will be launched.
Because of built image is based on Linux and validation application is written on .NET Core the most logical approach for the validation launching is to set up it as Travis CI build. As a result, the build script is quite short.
The most nasty thing is to choose right timeout for starting validation utility because the Microsoft SQL Server needs time to start and after several tries I managed it.
Now remains only to connect image build in Docker Hub and image validation build in Travis CI. Fortunately, Docker Hub has webhooks which are triggered after the image build, so again .NET Core can help me.
Now the build cycle is closed and I’m confident that if base image or source files on github are changed new image will be automatically rebuilt and verified.