SQL Server 2017 and 2019 on Docker inside Synology DS918+
Introduction
Docker is perfect for launching DEV and TEST SQL Server environments, when you need many independent SQL instances, or ability to run and stop instances in seconds, only when you need them. Launched from one common image (1GB in size), all SQL instances “containers” share that common image, spending almost no additional space, expect for databases you add. That means super-low disk space consumption. Also memory (RAM) of the host is shared, used as needed by every instance and without RAM footprint of OS and apps we have in classic VMs. Therefore, much more SQL instances fit into same amount of RAM. One can launch different SQL Editions from the same image within seconds. It is demonstrated in the video.
Synology NAS
Docker software can be installed and usually we setup everything with command-line interface. But, all that is much simpler if you have a Synology NAS. Installation is with few mouse clicks, web-based GUI, super-easy creation and management of containers, no command line required. Software is awesome and is free, or included in the price of device:
DS918+ was used in the demo. A “small beast” for small offices, costs around 450 EUR without disks/SSD/RAM. It can fit 4 HDDs (eg. 36TB of space), 2x M2 NVMe SSD for cache, 16GB RAM, 4 CPU cores. Gives around 15 000 IOPS and 200-500 MB/s sequential speed. It can serve iSCSI LUNs too, at around 220MB/s sequential, and 15 000 random IOPS, with avg. latency around 10ms. It can also run VMs, which is shown in the demo too, but because it is limited with only 16GB RAM and 4 CPU cores, for bigger VMs I would recommend to run them on separate server (with lot of CPU and RAM) and use this device only as an iSCSI disk (LUNs). Great for backup, docker, dev, test, and less demanding VMs/iSCSI.
Limitations
- SQL Server 2017+ (SQL Server 2019) only. Previous SQL versions cannot run on linux, therefore cannot run in linux-based dockers natively, but only inside docker’s virtual machine which has more overhead than native docker.
- SQL Engine only. SQL Agent is not supported, no SSRS, SSAS, etc. Nothing is there except naked SQL server database engine, a SQL Server instance process.
- Running SQL in Docker for production is not officially supported. But for dev and test purpose it is, and is great for that.
- Databases data will NOT persist (will be LOST on “clear” command!) unless you mount internal folder “/var/opt/mssql/” to host’s path to persist database files (shown in the video). Clearing container to a initial point is sometimes desired behavior, especially for repeating tests.
- Limit RAM of SQL container! With “Unlimited”, after some serious data processing and container spent 13.5GB of 16GB synology, I experienced inability to turn off container. Docker stucked, and some other services on Synology went frozen too. Working on that with Synology support. If you put a limit on the container’s RAM (eg. I put 8GB), it works fine, even with my data-torture workload.
- UPDATE 2.12.2018: UNSTABLE! Even with RAM limit set, SQL 2017 docker containers still make Synology “freeze” from time to time. Support says it is because I have unsupported 16GB of RAM. Anyway, I would not recommend using docker on Synology. There are alternatives though to put your big dev/test databases on Synology, but that is for some other blog post.
Enjoy the video
How to setup SQL Server 2017 as a Docker container, using Linux-based Docker on Synology NAS:
SQL Server 2019 in Docker
Microsoft containers moved to new location: mcr.microsoft.com, which I don’t know how to include in Synology’s list of image providers. If you know, please send me and I will include that info here. Therefore, you need to telnet into Synology, and run “docker pull” command as in this picture:
After that, SQL 2019 (preview) image is available in web UI of Synology’s Docker, and you can launch SQL 2019 containers as usual. Here are few screenshots:
Conclusion
For DEV and TEST labs, SQL inside the docker is a perfect match. Saves a ton of resources you would need otherwise. Especially convenient if you already have a stronger Synology NAS device.
Links
- Configuring Docker Image (including AG) https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-docker?view=sql-server-2017
Is there a way to install full text search on this
I think not. Docker image of SQL runs only one process. FTS is a separate process, SQL Agent too, therefore – not included in docker. Docker is limited to db engine only. And even not all engine features are supported, eg replication is included for linux in SQL 2019 just recently, but I think not in Docker. And I cannot find official page that lists all features supported (or not supported) by SQL in Docker. If you find such a page, please post here, so everyone benefits. Thanks.
Seems some people managed to build their own docker image that includes FTS.
Here are links that might be helpful to you:
Including FTS in docker image:
https://gist.github.com/avernet/a8a79ba9835056d9456c55441a602184
Including FTS in docker image2:
https://stackoverflow.com/questions/46100827/full-text-index-microsoft-mssql-server-windows-express-docker-container?rq=1
Installing FTS on Linux:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-full-text-search?view=sql-server-2017
Getting started with sql on docker:
https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017&pivots=cs1-bash
I just followed these steps (including using Putty to pull the container from Microsoft). It showed that step completed successfully but the image does not appear in Docker on the Synology.
Any ideas?
I take that back. It does appear and I have the instance running. Thanks very much for the video and additional comments!
Thanks Frosty for sharing!
The registry you are using in the video is now deprecated (https://registry.hub.docker.com/r/microsoft/mssql-server-linux/).
Do you know how to add the new supported version? (https://hub.docker.com/_/microsoft-mssql-server)
Unfortunately, adding a mcr.microsoft.com registry to Synology does not work. At least I haven’t found a way that works. Instead, use “docker pull” command as described in this blog chapter “SQL Server 2019 in Docker” with screenshots of commands.
Hi,
I done as you have shown and all went perfect for a year .
Now since a couple of weeks the mssql server (2017 – on Synology Diskstation) gets slower and slower.
First I had 4 GB RAM and now 16 GB RAM. But it won’t help.
Do you have any recommandations? Is there a possibility to upgrade the mssql inside my docker?
Hi Martin. There are many possible reasons why SQL Server would become slower over time, on any environment. One example: there is a query that is doing a full scan of the table because it is missing an index. Since over time table gets bigger and bigger, query gets slower, as it has to go through more and more data. Find queries with the most logical reads and tune them. Or if CPU is high, then find the ones with highest CPU time. I do that routinely as a SQL Server consultant as a part of SQL performance tuning package.
But your question is, can you upgrade from sql2017 to sql2019? You can spin up a SQL2019, and attach your user database files to that instance. Or do a backup-restore. That should work. SQL 2019 has some amazing features, like ADR (Accelerated Database Recovery = instant recovery, instant rollback, aggressive log clearing), performance optimizations etc, but if index is really missing, upgrade to SQL2019 wont fix that.
I there a way to mount the shared folder on the NAS in SQL2019?
After SQL2019 not runs as root like SQL2017 but as mssql user do I hit a wall.
Try to change file owner of your mounted filesystem folder.
chown 10001:0 [the-path-of-the-mounted-volume]
ex.: chown 10001:0 /volume1/docker/mssql/demo
Did you ever figure this out? I can run 2019 without the /var/opt/mssql/ volume specified and it runs fine, but as soon as I add it I get the following error:
/opt/mssql/bin/sqlservr: Error: The system directory [/.system] could not be created. File: LinuxDirectory.cpp:420 [Status: 0xC0000022 Access Denied errno = 0xD(13) Permission denied]
I tried running chown 10001:0 /var/opt/mssql/ when no volume is specified, but I get an “operation not permitted” error. I probably need to run it when the volume is specified, but the container shuts down too quickly to try anything.
Ben,
Did you ever figure this out. I am having the same problem.
With a SSH connexion on your NAS, go to your home directory cd /var/services/homes/ , then mkdir mssql, sudo chown admin mssql, mkdir SQL2019, sudo chmod 777 SQL2019 . then in the “volume” tab , add a folder, homes//mssql/SQL2019 linked to /var/opt/mssql … and it works.
How to find the SQL Server name. Please share something that will be helpful to connect my SQL Server running on the my Synology NAS to any node on the LAN that has SSMS in it.
Thanks Arun for the comment. To connect you need to know two things: the name (or IP) of your NAS, and external PORT number you have your SQL listening at. Let’s say it is “NAS1” and the port is 14330. Then the server name to connect would be: “NAS1,14330”. Of course you also need valid sql credentials to log in (username and pwd).
Hello
Thank you for video!
Ynfortunately i have problem with password, i set SA_PASSWORD variable and value but i can’t login in.
Any ideas?
Hello Vedra,
I greet you cordially and congratulate you for your collaboration.
I have a SYNOLOGY NAS, to which I intend to migrate some web services, which are currently in a windows ISS node, these sites connect to databases hosted on a local network server with microsoft SQL SERVER.
I have not been able to establish the connection of the web pages, with the SQL-SERVER, apparently I need to install ODBC extensions in synology, it does not bring the extensions by default which is a real shame.
I would like to know if you know any method to achieve this communication towards MS SQL SERVER from pages hosted in synology web station
Hello Reykzel,
It all depends on what this web service uses as a driver to connect to SQL – that driver you should have. Also depends on what OS it is, is it a VM with windows or a docker container (linux), etc. Troubleshooting is probably complex, beyond this blog post.
when i try to save to mounted folder it says permission error.
Microsoft.Data.SqlClient.SqlError: Cannot open backup device ‘/mnt/myhost/sql/sales.bak’. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)