This is just what I needed. I have a client that the only reason they are using Client / Sever Environment is because of the ERP SQL database driven software they use.. Otherwise they gave a really nice Synology NAS for their share and backups. This is PERFECT! Thank you!
Hi Verdan. I have watched and it is very informant but I can't find the sqlserver image that you are using, I tried some other bu they didn't work. I have a NAS 920+ and Windows 11 on my laptop.
I'm very glad. Watched and liked your video. Excellent to show how to do it on a newer version. It would be even better if you replaced that music with your voice explaining steps along the way.
"nas" is the name of synology device, resolves to IP of my nas device. SQL Studio is installed on my PC. I connect with SSMS from PC to synology nas named "nas".
Hello mr, I have Synology DiskStation "DS220 plus" I followed your tutorial and I'm having problem for start running the container SQL Server 2019, also i have tried other versions same problem, even after I added to environment the variable ACCEPT_EULA=Y the container wouldn't started, in logs showing me "This program requires a machine with at least 2000 megabytes of memory." Also this NAS allready have 2GB of RAM.
But it needs 2GB of FREE memory. If NAS has 2GB RAM, and OS and other programs take 1GB, there is less than 2GB free RAM and container cannot run. Usually it is possible to upgrade RAM in many Synology models.
Hi Vedran - thank you for the tutorial. It looks like the image you used has been deprecated (as of March 12, 2020) and apparently is not available for download. Do you know of another source?
Hi Mark! Please try "telnet" method with typing image address from mcr.microsoft.com, shown in blog post under title "SQL Server 2019 in Docker" and let us know the result. blog.sqlxdetails.com/sql-server-2017-on-docker-inside-synology-ds918/
@@VedranKesegicSQL Yes - I followed the instructions in your blog, and it worked! Thank you. The container pull command is a little different now: /$ sudo docker pull mcr.microsoft.com/mssql/server
Another question: I am getting the following error when I map the folder to : /opt/mssql/bin/sqlservr: Error: The system directory [/.system] could not be created. Errno [13]. Any thoughts?
MSSQL 2019 works well in docker with DMS 7.1 on a DS1019+ nas. However, I cannot get persistance to work as MSSQL 2019 starts up as non-root. What would be a good strategy for enabling persistence with MSSQL 2019 and maintaining non-root startup? Thank you for this great video, I leaned a lot from you!
Thank you so much, Vedran. I was having trouble connecting and I think your info about changing the port from Auto to 1433 solved my problem. I downloaded and installed the 2019 image using SSH client, and set up my environmental variables using the Docker GUI in Synology. I can connect from my PC to the 2019 Server on my Synology now and create/modify databases. My problem is trying to set up /var/opt/mssql/ directory for persistent databases which causes the Docker container to crash. I'm logged into the SSH client using an account with admin privileges but maybe I don't know the right commands. I'm a newbie to SSH so I think I'm doing something wrong. Any suggestions?
Hi Susan, thanks for your comment! Try SELECT * FROM sys.master_files to figure out what internal folder is used, and adjust. Maybe it is not /var/opt/mssql/.
@@VedranKesegicSQL, thank you for your response. I ran the query suggested and the data is stored in /var/opt/mssql. I was able to SSH into the container and browse the directories, so I know they are there. I can also see the directory structure by attempting to restore a database from device and everything looks proper. I'm guessing there are some permissions for /var/opt/mssql that were incorrectly applied to the 2019 version (but are correct for the 2017 version). I was able to mount to /Shared in the container (I didn't try other directories yet), so I need to learn how to modify permissions in the container.
It is Synology DS918+ www.synology.com/en-global/products/DS918+ and yes, I can access that SQL instance from other locations on the local network, eg using SQL Management Studio. Even from outside over VPN.
Yes it is possible, but I would not recommend that for production, only for dev/test. You can use docker to place both SQL and mdf/ldf on NAS. Or place just mdf and ldf on a NAS using iSCSI Initiator (LUNs), or place vhdx file (disk and mdf/ldf inside) on a NAS and attach it to windows or attach it virtual machine as it is a local disk.
@@VedranKesegicSQL un my case, it's a Big data project, I need to move 700 GB of data to the nas. But I want to be sure before I spent money getting that device. If it's possible to perform an ETL and also that other computers can make queries against it, I could do it. After the data is in the nas, people will just perform select statements on it.
@@lagartox1982 That particular device (DS918+) is not meant to be high-performant production server. I'm not sure what you mean by ETL. SQL inside Docker can be used only as a pure database, that means DDL and DML, no reporting services (SSRS) or anything else. If you need something more, like SSIS (Integration Services), then docker is out. But, you can install a VM inside of Synology (it works) with SQL. But performance of that is poor because low CPU power and small amount of RAM. You could use a separate machine with more RAM and CPU power, to install normal SQL Server and all services you need (SSIS for example), and place big mdf and ldf on the NAS. Shared folders are not most reliable (locking issues), so more reliable would be iSCSI LUN on the NAS and mdf/ldf there. That would give you disk redundancy, and is simple to expand. BUT, speed is only 100MB/s (1Gbit network) x2 if you use separate LUN for ldf over other cable - which is a speed I consider poor if larger workloads are there. If you want good performance, use a machine with 2-3+ local SSDs, create a Storage Pool over them and enjoy good performance. and do a backup to the shared folder of a SAN. By the way, SQL Developer edition is free, but only for test and dev usage.
@@VedranKesegicSQL I'll try it. I don't have many options. Moving the MDF and LDF with all the data already could work for me. Thanks Vendran! You Rock ✊
Hi. Please take a look at the answer to the same question I posted recently in the comments here: blog.sqlxdetails.com/sql-server-2017-on-docker-inside-synology-ds918/
Thanks for watching! It is 16GB (2 modules x8GB) of non-synology but compatible RAM. I don't know exact model. There are also 2x256GB NVMe SSD for cache.
Hi Verdan. Thank you for the tutorial, it was extreamly helpful and I manged to run the SQL Server on my Synology "Thank to you" and I would like to ask abou a raising issue. I could easly connect to the docker SQL SERVER using SSMS 19 while I am in the local network, However it is not connecting throw the internet I already enabled a remote access link and I can accessthe synology NAS from home but SSMS couldn't access SQL server using the same link. If you had this issue before an advice will be very helpful.
Hi. It is a networking thing to get requests from internet to the correct ip and port. From outside, first try is all routed well by doing powershell command: Test-NetConnection -Port 1433 If it gets through, then in SQL Studio instead of server name use "tcp:,1433"
Thanks for this video, Vedran! I followed all steps carefully and the Docker container is running correctly at port 1433. However, when trying to connect from SQL Server Management Studio, immediately an error pops up: "A connection was successfully established with the server, but then an error occurred during the pre-login handshake - The specified network name is no longer available". Any ideas?
@@sovannseung6985 Sure. First make sure you can access your SQL from within your local network. Then access from public network is the same as for any other service on Synology: Setup DDNS on synology to be able to find your device's IP by some site name, port forwarding on your router. But, exposing your local network is dangerous. You should use a VPN (also possible to setup on synology). You will learn a ton about netowrking on this jurney.
Thank you for making this video. Right at 8:00 you scroll past the requirements and it says it needs Docker 18 or later. On the Synology community site there is a post saying the current version of docker on the devices is 17.0.5 - community.synology.com/forum/15/post/119714 Are you running the stock version of docker or did you upgrade it? Also, have you continued using SQL2017 and how do you feel it performs on the NAS?
I run normal version downloadable from Package Center, everything updated to latest non-beta version. Performs well for dev and test purposes. But CPU and RAM amount cannot match strong production database servers. I also installed SQL 2019 preview and works fine. See screenshots in blog post blog.sqlxdetails.com/sql-server-2017-on-docker-inside-synology-ds918/
Hi MrTechGuy! Try "telnet" method with typing of image address from mcr.microsoft.com, shown in blog post under title "SQL Server 2019 in Docker" blog.sqlxdetails.com/sql-server-2017-on-docker-inside-synology-ds918/
You need to map internal port of container to outer port (visible from outside of container). Then you use that port in connection string together with your NAS machine or IP. Eg "tcp:mynas,3333"
Thank you Vedran! You made it very clear. I only have issue with /var/opt/mssql/ folder. In LOG it says "/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]". Does anybody knows how to solve it? Probably easy for somebody who knows linux.
VM at 20:00 on is hosted by Synology NAS, DS918+, upgraded to 16GB RAM and 2 NVMe M.2 drives for cache. But because NAS CPU is more on power-saving side, and 16GB is not much, it is not for big VMs, only small ones that are not compute-hungry.
thank you this is awesome with 2019 you cannot start the container with the volume set to /var/opt/mssql. /opt/mssql/bin/sqlservr: Error: The system directory [/.system] could not be created. Errno [13]
Create a new shared folder and subfolder (don't user the docker folder as shown here). Change the owner to root (admin). Set the permissions to allow for all to have access (chmod 777) for the subfolder. Mount the folder in the container configuration (Volume). You should be able to set it to "/var/opt/mssql" and it will load.
Ditto...2017 works great with persistent /var/opt/mssql/ volume, but 2019 does not. 2019 works fine if no volume is set, but who wants that. Not sure how to change the permissions as Douglas suggested, but I would think you would need to change the owner to mssql (not root) since 2019 does not run under the root like 2017 did. If anyone has been able to get this working, please share.
@@benmillspaugh7776 Like Douglas Buchanan explains. With a SSH connexion. 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.
This is just what I needed. I have a client that the only reason they are using Client / Sever Environment is because of the ERP SQL database driven software they use.. Otherwise they gave a really nice Synology NAS for their share and backups. This is PERFECT! Thank you!
This is legit as hell. I did the same for MySQL, and the process + debugging was essentially identical. Thank you good sir!
Thanks Jose! I'm glad it helps even wider than SQL Server.
thank you Vedran - I‘ve enjoyed watching and amzed by how easy it is to get a sql up and running!!!
Hi Verdan. I have watched and it is very informant but I can't find the sqlserver image that you are using, I tried some other bu they didn't work. I have a NAS 920+ and Windows 11 on my laptop.
Coming from 2022, it appears that Synology is blocking port 1433 nowadays. If I use another port like 4433 it works, but not on 1433.
Thank you Vedran, great training!
Thank you very much for this video, I used it as a starting point for my own video, on the same subject, on my TH-cam channel.
I'm very glad. Watched and liked your video. Excellent to show how to do it on a newer version. It would be even better if you replaced that music with your voice explaining steps along the way.
@@VedranKesegicSQL thank you so mutch for you feedback! It would be better with my voice and perhaps video....next time 💪🏼
Thank you very much. This was very interesting and educational
Thank you. Certainly not for beginners
You are welcome!
is sql server installed on the PC? "name server" at 10:47 is the device name nas or whatever,
"nas" is the name of synology device, resolves to IP of my nas device. SQL Studio is installed on my PC. I connect with SSMS from PC to synology nas named "nas".
Hello mr, I have Synology DiskStation "DS220 plus" I followed your tutorial and I'm having problem for start running the container SQL Server 2019, also i have tried other versions same problem, even after I added to environment the variable ACCEPT_EULA=Y the container wouldn't started, in logs showing me "This program requires a machine with at least 2000 megabytes of memory." Also this NAS allready have 2GB of RAM.
But it needs 2GB of FREE memory. If NAS has 2GB RAM, and OS and other programs take 1GB, there is less than 2GB free RAM and container cannot run. Usually it is possible to upgrade RAM in many Synology models.
Doesn't work when I try to persist ... states that it has no access to the folder??
Hi Vedran - thank you for the tutorial. It looks like the image you used has been deprecated (as of March 12, 2020) and apparently is not available for download. Do you know of another source?
Hi Mark! Please try "telnet" method with typing image address from mcr.microsoft.com, shown in blog post under title "SQL Server 2019 in Docker" and let us know the result. blog.sqlxdetails.com/sql-server-2017-on-docker-inside-synology-ds918/
@@VedranKesegicSQL Yes - I followed the instructions in your blog, and it worked! Thank you. The container pull command is a little different now:
/$ sudo docker pull mcr.microsoft.com/mssql/server
Another question: I am getting the following error when I map the folder to : /opt/mssql/bin/sqlservr: Error: The system directory [/.system] could not be created. Errno [13]. Any thoughts?
I installed mssql 2017 and was able to map volumes as you described. I am missing something with the 2019 install.
@@markcunningham2862, did you find an answer? I'm also using SQL Server 2019 and cannot mount the suggested folder to allow for persistent databases.
MSSQL 2019 works well in docker with DMS 7.1 on a DS1019+ nas. However, I cannot get persistance to work as MSSQL 2019 starts up as non-root. What would be a good strategy for enabling persistence with MSSQL 2019 and maintaining non-root startup? Thank you for this great video, I leaned a lot from you!
Thank you so much, Vedran. I was having trouble connecting and I think your info about changing the port from Auto to 1433 solved my problem.
I downloaded and installed the 2019 image using SSH client, and set up my environmental variables using the Docker GUI in Synology. I can connect from my PC to the 2019 Server on my Synology now and create/modify databases.
My problem is trying to set up /var/opt/mssql/ directory for persistent databases which causes the Docker container to crash. I'm logged into the SSH client using an account with admin privileges but maybe I don't know the right commands. I'm a newbie to SSH so I think I'm doing something wrong. Any suggestions?
Hi Susan, thanks for your comment! Try SELECT * FROM sys.master_files to figure out what internal folder is used, and adjust. Maybe it is not /var/opt/mssql/.
@@VedranKesegicSQL, thank you for your response. I ran the query suggested and the data is stored in /var/opt/mssql. I was able to SSH into the container and browse the directories, so I know they are there. I can also see the directory structure by attempting to restore a database from device and everything looks proper.
I'm guessing there are some permissions for /var/opt/mssql that were incorrectly applied to the 2019 version (but are correct for the 2017 version). I was able to mount to /Shared in the container (I didn't try other directories yet), so I need to learn how to modify permissions in the container.
Excellent tutorial, thanks for posting :-)
Muchas gracias, aunque mi ingles es malo, se entendio clarito un lujo gracias
Hi, is it possible to install on docker named instance?
what model is your NAS? and can you acces to the NAS SQL of other place?
It is Synology DS918+ www.synology.com/en-global/products/DS918+ and yes, I can access that SQL instance from other locations on the local network, eg using SQL Management Studio. Even from outside over VPN.
Is it possible to Install SQL server and perform ETL operations on it??? like literally have the MDF and LDF Files on the nas
Yes it is possible, but I would not recommend that for production, only for dev/test. You can use docker to place both SQL and mdf/ldf on NAS. Or place just mdf and ldf on a NAS using iSCSI Initiator (LUNs), or place vhdx file (disk and mdf/ldf inside) on a NAS and attach it to windows or attach it virtual machine as it is a local disk.
@@VedranKesegicSQL un my case, it's a Big data project, I need to move 700 GB of data to the nas. But I want to be sure before I spent money getting that device. If it's possible to perform an ETL and also that other computers can make queries against it, I could do it. After the data is in the nas, people will just perform select statements on it.
@@lagartox1982 That particular device (DS918+) is not meant to be high-performant production server. I'm not sure what you mean by ETL. SQL inside Docker can be used only as a pure database, that means DDL and DML, no reporting services (SSRS) or anything else. If you need something more, like SSIS (Integration Services), then docker is out. But, you can install a VM inside of Synology (it works) with SQL. But performance of that is poor because low CPU power and small amount of RAM. You could use a separate machine with more RAM and CPU power, to install normal SQL Server and all services you need (SSIS for example), and place big mdf and ldf on the NAS. Shared folders are not most reliable (locking issues), so more reliable would be iSCSI LUN on the NAS and mdf/ldf there. That would give you disk redundancy, and is simple to expand. BUT, speed is only 100MB/s (1Gbit network) x2 if you use separate LUN for ldf over other cable - which is a speed I consider poor if larger workloads are there. If you want good performance, use a machine with 2-3+ local SSDs, create a Storage Pool over them and enjoy good performance. and do a backup to the shared folder of a SAN. By the way, SQL Developer edition is free, but only for test and dev usage.
@@VedranKesegicSQL I'll try it. I don't have many options. Moving the MDF and LDF with all the data already could work for me. Thanks Vendran! You Rock ✊
Hi Verdan, is there a way to install full text search on this?
Hi. Please take a look at the answer to the same question I posted recently in the comments here: blog.sqlxdetails.com/sql-server-2017-on-docker-inside-synology-ds918/
I followed your tutorial to get up and running, thank you :) just out of curiosity, what RAM are you using in your 918+?
Thanks for watching! It is 16GB (2 modules x8GB) of non-synology but compatible RAM. I don't know exact model. There are also 2x256GB NVMe SSD for cache.
Hi Verdan.
Thank you for the tutorial, it was extreamly helpful and I manged to run the SQL Server on my Synology "Thank to you"
and I would like to ask abou a raising issue.
I could easly connect to the docker SQL SERVER using SSMS 19 while I am in the local network, However it is not connecting throw the internet
I already enabled a remote access link and I can accessthe synology NAS from home but SSMS couldn't access SQL server using the same link.
If you had this issue before an advice will be very helpful.
Hi. It is a networking thing to get requests from internet to the correct ip and port. From outside, first try is all routed well by doing powershell command:
Test-NetConnection -Port 1433
If it gets through, then in SQL Studio instead of server name use "tcp:,1433"
Thanks for this video, Vedran! I followed all steps carefully and the Docker container is running correctly at port 1433. However, when trying to connect from SQL Server Management Studio, immediately an error pops up: "A connection was successfully established with the server, but then an error occurred during the pre-login handshake - The specified network name is no longer available".
Any ideas?
Is container still running? Do you have firewall on NAS? Try accessing it with IP.
Also, you need to use sql login, not windows login.
Thank you very much.
I can't find microsoft/mssql-server-linux right now. Is there any other name now?
Duckduckgo search gave me this, but I haven't tried: mcr.microsoft.com/mssql/server
@@VedranKesegicSQLThank you. Found that but unfortunately my Synology doesn't have enough memory. :(
@@VedranKesegicSQL An other quick question, can we connect this sql server from outside network? I mean public.
@@sovannseung6985 Sure. First make sure you can access your SQL from within your local network. Then access from public network is the same as for any other service on Synology: Setup DDNS on synology to be able to find your device's IP by some site name, port forwarding on your router. But, exposing your local network is dangerous. You should use a VPN (also possible to setup on synology). You will learn a ton about netowrking on this jurney.
@@VedranKesegicSQL Thank you for your reply. I will need to learn more regarding security before I can do that.
Thank you for making this video. Right at 8:00 you scroll past the requirements and it says it needs Docker 18 or later. On the Synology community site there is a post saying the current version of docker on the devices is 17.0.5 - community.synology.com/forum/15/post/119714
Are you running the stock version of docker or did you upgrade it? Also, have you continued using SQL2017 and how do you feel it performs on the NAS?
I run normal version downloadable from Package Center, everything updated to latest non-beta version. Performs well for dev and test purposes. But CPU and RAM amount cannot match strong production database servers. I also installed SQL 2019 preview and works fine. See screenshots in blog post blog.sqlxdetails.com/sql-server-2017-on-docker-inside-synology-ds918/
server name 10:47s where , it is name device synology
The registry now says mssql is depracated... Any ideas to fix this?
Hi MrTechGuy! Try "telnet" method with typing of image address from mcr.microsoft.com, shown in blog post under title "SQL Server 2019 in Docker" blog.sqlxdetails.com/sql-server-2017-on-docker-inside-synology-ds918/
Hello Vedran - thank you for the movie - But how to make a connection with management studio to de database
You need to map internal port of container to outer port (visible from outside of container). Then you use that port in connection string together with your NAS machine or IP. Eg "tcp:mynas,3333"
@@VedranKesegicSQL I have an error : untrusted domain
and also error 11001 host unnoun
@@philipbergeman9442 Try creating and using sql login, not windows login.
Thank you Vedran! You made it very clear. I only have issue with /var/opt/mssql/ folder. In LOG it says "/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]". Does anybody knows how to solve it? Probably easy for somebody who knows linux.
Where did the windows VM come from?
VM at 20:00 on is hosted by Synology NAS, DS918+, upgraded to 16GB RAM and 2 NVMe M.2 drives for cache. But because NAS CPU is more on power-saving side, and 16GB is not much, it is not for big VMs, only small ones that are not compute-hungry.
thank you this is awesome with 2019 you cannot start the container with the volume set to /var/opt/mssql. /opt/mssql/bin/sqlservr: Error: The system directory [/.system] could not be created. Errno [13]
Same. 😩
Same for me too. Is there a way to specify a user for the container? Can't seem to find this anywhere...
Create a new shared folder and subfolder (don't user the docker folder as shown here). Change the owner to root (admin). Set the permissions to allow for all to have access (chmod 777) for the subfolder. Mount the folder in the container configuration (Volume). You should be able to set it to "/var/opt/mssql" and it will load.
Ditto...2017 works great with persistent /var/opt/mssql/ volume, but 2019 does not. 2019 works fine if no volume is set, but who wants that. Not sure how to change the permissions as Douglas suggested, but I would think you would need to change the owner to mssql (not root) since 2019 does not run under the root like 2017 did. If anyone has been able to get this working, please share.
@@benmillspaugh7776 Like Douglas Buchanan explains. With a SSH connexion. 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.
Thank you!
Thanks!
Can you guide Install Zabbix (Step by step same this)? :)
Thank you very much.