How to install SQL Server on Linux via Docker
Certifications:
- How we enable Unified Auditing in Oracle Database - 7 October 2024
- What is PostgreSQL and how do we do a full installation - September 2, 2024
- How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024
In previous article we had seen the installation SQL Server 2019 in Red Hat Enterprise Linux. In this article we will see how to install SQL Server 2017 again on RHEL but with only 1 core and 1GB Ram with use Docker containers in Ubuntu.
The reason I used his solution Docker containers it was because i had to find an easy way around it limit of 2GB RAM to create and start SQL Server instance.
Otherwise, during the installation I got the message:
"sqlservr: This program requires a machine with at least 2000 megabytes of memory."Because first things first the first thing we need to do is open the SQL Server door to firewall.
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent sudo firewall-cmd --reload
Bypass system specs
The solution exists from one type who had the same problem and made a Dockerfile ready to boot container with SQL Server instance in Docker.
The trick was the use LD_PRELOAD which loads a bypass library that will be loaded first and when the SQL Server binary goes to run, the information will be bypassed with our own fake library that will have 2GB of RAM.
The information we wanted to skip:
The code for the library that needs to be compiled (wrapper.c):
int sysinfo(struct sysinfo *info){
// clear it
//dlerror();
void *pt=NULL;
typedef int (*real_sysinfo)(struct sysinfo *info);
// we need the real sysinfo function address
pt = dlsym(RTLD_NEXT,"sysinfo");
//printf("pt: %x\n", *(char *)pt);
// call the real sysinfo system call
int real_return_val=((real_sysinfo)pt)(info);
// but then modify its returned totalram field if necessary
// because sqlserver needs to believe it has "2000 megabytes"
// physical memory
if( info->totalram < 1000l * 1000l * 1000l * 2l){ // 2000 megabytes
info->totalram = 1000l * 1000l * 1000l * 2l ;
}
return real_return_val;
}
So when executing LD_PRELOAD=/root/wrapper.so /opt/mssql/bin/sqlservr SQL Server will start with the fake information we want.
Time to install Docker
But what is the Docker; It is a platform that allows us to run applications on containers which can be on any operating system regardless of what we have on our machine. So it allows us to download ready images and upload applications in a very fast time without any special extra configuration.
For its installation Docker:
yum install -y docker-engine
To activate the service:
sudo systemctl start docker sudo systemctl enable docker
To download git with the Dockerfile and wrapper it has created:
*Obviously if we already have git installed, we don't need to install it again…
sudo yum install git sudo git clone https://github.com/justin2004/mssql_server_tiny.git cd mssql_server_tiny
What is Dockerfile
The Dockerfile is a text file that contains all the commands to build one image with automated commands during its creation and execution.
Essentially as we see the Dockerfile in the FROM has them images ** of the operating system to be downloaded, in our case Ubuntu with pre-installed SQL Server 2017, with RUN what it will perform when creating it, with the ADD file that will add from the folder we are in the image and with the CMD we define what will be executed in any container that uses this image during execution.
The search for images that we can use in FROM, we do it on the website hub.docker.com .
FROM debian:stretch-slim AS build0
WORKDIR /root
RUN apt-get update && apt-get install -y binutils gcc
ADD wrapper.c /root/
RUN gcc -shared -ldl -fPIC -o wrapper.so wrapper.c && cp /root/wrapper.so /root/
FROM mcr.microsoft.com/mssql/server:2017-latest-ubuntu
COPY --from=build0 /root/wrapper.so /root/
CMD LD_PRELOAD=/root/wrapper.so /opt/mssql/bin/sqlservr -x*The -x at the end I have set it so that the instance is raised (startup option) with as few metrics as possible to save memory. If we didn't have a memory issue we would remove it.
** The reason we use two images in the FROM and all the extra commands is to bypass SQL Server's resource limits, in case that didn't matter, only the FROM line would be needed mcr.microsoft.com/mssql/ server:2017-latest-ubuntu.
FROM mcr.microsoft.com/mssql/server:2017-latest-ubuntuHow we make the image
When we have it ready Dockerfile we must do build the image with a name. As it is created, it will also download any image that we have not already downloaded.
To build, run the following:
sudo docker build -t mssql . sudo docker image ls
If we want to delete it:
sudo docker image rm mssql
The creation of the container
Now we want to fix it container which will contain the instance of SQL Server as a separate entity within the operating system.
The command is done with docker run and we can set all the parameters in its environment so that it does all the installation automatically. Also I have done mount a folder where I will put my database files and see them outside the container. At the end as mssql I declare its name image which I will also use as –name the name the container will have.
sudo docker run -d \ -e "ACCEPT_EULA=Y" \ -e 'MSSQL_PID=Developer' \ -p 1433:1433 \ -e 'SA_PASSWORD=password' \ --mount type=bind,source=$(pwd)/dbfiles,target=/dbfiles \ --name mssql \ --memory-swap -1 \ --restart unless-stopped \ mssql
*To save a few more resources we can also put the version Express.
**Because RAM is limited with memory-swap -1 we can let the container use as much swap memory as the host machine has.
With the following command we see that the container has been created:
sudo docker container ls -a
If we want to delete the container:
sudo docker stop mssql sudo docker rm /mssql
Connect to the SQL Server container
Before we go to connect we need to see if the instance is up. To see the logs of the SQL Server container, simply execute the command:
sudo docker logs mssql
If something goes wrong, we do stop / start the container:
sudo docker stop mssql sudo docker start mssql
Once completed we can connect to sqlcmd directly from docker command:
sudo docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -USA -Pkwdikos -e
Either connect to the container first and then call sqlcmd:
sudo docker exec -it mssql bash /opt/mssql-tools/bin/sqlcmd -S localhost -Usa -Pkwdikos -e
Either from another computer, putting the hostname and its port will redirect to the container (the port was defined in the creation of the container above):
Useful SQL Server commands in Docker
To do restart of SQL Server using docker we upload and download the entire container:
sudo docker stop mssql sudo docker start mssql
To activate one traceflags using docker:
sudo docker exec -it mssql /opt/mssql/bin/mssql-conf traceflag 7412 on
To enable SQL Server Agent using docker:
sudo docker exec -it mssql /opt/mssql/bin/mssql-conf set sqlagent.ena bled on
To change it collation in docker:
docker exec -it mssql /opt/mssql/bin/mssql-conf set-collation
Closing
Of course, through all this effort, the time was not wasted, on the other hand, of course, with so many limitations in the memory of the resource pool, many queries do not run on the first try. As a first attempt I had tried to install SQL Server 2019 image instead of 2017 and things were much worse. But I think it's functional.
Good evening Army,
mainly through linkedin I pass the occasion to read some of your articles, I am not that relevant to docker and I would like to ask the following, in the dockerfile in case we have Windows 10 as the operating system on our computer, can we mention a version in FROM of ubuntu, I have not understood does the basic layer of the image always have to go along with the operating system? Essentially, each container in the image will be a separate process with its own separate file system from what I understand and with some specific properties...
Good morning Spyro,
The meaning of containers is that you can use whatever operating system you want in the image, regardless of the operating system that docker is running.
Don't be confused that I used 2 FROM, I did this to bypass the resource limits that SQL Server has.
The images you can use in docker can be found here: https://hub.docker.com/search?q=&type=image
Some are images with "plain" functionality and some others have pre-installed applications such as e.g. here SQL Server.