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.
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.
- 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:
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.
- Configuring Docker Image (including AG) https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-docker?view=sql-server-2017