MSSQL Docker on UnRaid


Recommended Posts

On 12/4/2019 at 6:53 PM, BoxOfSnoo said:

Yes, I'm on rc7.  I built mine using "Add Container" and plugging in the values as recommended above.

Well isn't that great, of course the Bitwarden version is crashing.

I just have to wait until they decide to upgrade the SQL container to a newer version.

I did notice the MCR version of SQL doesn't require the ulimits override anymore so that's nice.

 

I've collected my Bitwarden MSSQL logging and rolled back to 6.7.2, everything's fine now (so said cause I liked the new features).

Thanks for confirming this.

 

EDIT:

Now that were an additional four hours well spend.

So apparently the combination of the newer docker build with the MSSQL container trips over mounting volumes relatively to /mnt/user/appdata (so the unRAID filesystem).

The docker version shipped with unRAID version 6.7.2 (that was 18.x?) doesn't seem to care but now on 19.x this becomes a problem all of a sudden.

 

The reason why your container worked (I assume) was that you mounted directly onto the btrfs (orwhichever filesystem you're using) mountpoint on /mnt/cache. Docker seems to now like this better. Although this would break if the mover kicks in I don't really mind cause my appdata is cache only anyways.

 

I've updated the docker-compose.override.yml file to map them differently and the container is running fine now.

#
# Override file for the auto-generated docker-compose.yml file provided 
#   by Bitwarden
# This file mounts the volumes inside the MSSQL container directly onto 
#   the btrfs mountpoint instead of a relative path in the unRAID 
#   filesystem as the MSSQL container fails otherwise.
# The file also sets ulimits on the same MSSQL container, because with the 
#   ulimit stack size set to ulimited systemwide (as is the case on unRaid),  
#   the container refuses to start.
#
#########################################################################

version: '3'
   
services:
  mssql:
    volumes:
      - /mnt/cache/appdata/bitwarden/bwdata/mssql/data:/var/opt/mssql/data
      - /mnt/cache/appdata/bitwarden/bwdata/logs/mssql:/var/opt/mssql/log
      - /mnt/cache/appdata/bitwarden/bwdata/mssql/backups:/etc/bitwarden/mssql/backups
    ulimits:
     stack:
      soft: "8192000"
      hard: "8192000"

Cheers! 🍻

Edited by S1dney
Link to comment
21 hours ago, S1dney said:

Well isn't that great, of course the Bitwarden version is crashing.

I just have to wait until they decide to upgrade the SQL container to a newer version.

I did notice the MCR version of SQL doesn't require the ulimits override anymore so that's nice.

 

I've collected my Bitwarden MSSQL logging and rolled back to 6.7.2, everything's fine now (so said cause I liked the new features).

Thanks for confirming this.

 

EDIT:

Now that were an additional four hours well spend.

So apparently the combination of the newer docker build with the MSSQL container trips over mounting volumes relatively to /mnt/user/appdata (so the unRAID filesystem).

The docker version shipped with unRAID version 6.7.2 (that was 18.x?) doesn't seem to care but now on 19.x this becomes a problem all of a sudden.

 

The reason why your container worked (I assume) was that you mounted directly onto the btfs mountpoint on /mnt/user/cache. Docker seems to now like this better. Although this would break if the mover kicks in I don't really mind cause my appdata is cache only anyways.

 

I've updated the docker-compose.override.yml file to map them differently and the container is running fine now.

 

I wa about to say “no that’s not it” but I went and looked at my config and yup, that’s exactly it.  Since I copied and pasted, the /mnt/cache path was entered when I was typically using the /mnt/user/appdata path in all of my previous tests.

 

Interesting, and fantastic detective work!

Link to comment
On 12/4/2019 at 2:11 PM, S1dney said:

So apparently the combination of the newer docker build with the MSSQL container trips over mounting volumes relatively to /mnt/user/appdata (so the unRAID filesystem).

The docker version shipped with unRAID version 6.7.2 (that was 18.x?) doesn't seem to care but now on 19.x this becomes a problem all of a sudden.

 

The reason why your container worked (I assume) was that you mounted directly onto the btfs mountpoint on /mnt/user/cache. Docker seems to now like this better. Although this would break if the mover kicks in I don't really mind cause my appdata is cache only anyways.

Interesting. And of course you have a typo in this text that you didn't repeat in your mapping:

 

/mnt/user/cache would be a user share named cache, not the actual cache disk.

 

There have been reports of user shares giving problems with databases on other dockers. See here:

 

https://forums.unraid.net/bug-reports/stable-releases/sqlite-db-corruption-testers-needed-r598/

 

Link to comment
2 hours ago, BoxOfSnoo said:

Interesting, and fantastic detective work!

Hahah thanks, trial and error though this time ;)

 

4 minutes ago, trurl said:

Interesting. And of course you have a typo in this text that you didn't repeat in your mapping:

 

/mnt/user/cache would be a user share named cache, not the actual cache disk.

You're right. It was a long night :P

I will adjust it right away, I meant the mountpoint of the cache indeed instead of a user share.

 

6 minutes ago, trurl said:

There have been reports of user shares giving problems with databases on other dockers. See here:

 

https://forums.unraid.net/bug-reports/stable-releases/sqlite-db-corruption-testers-needed-r598/

 

Yeah I was aware of this, I wasn't affected by the bug though so I haven't dug into this too deep.

I might try a few versions later to switch back to the /mnt/user shares, but until that time for me this is a perfect workaround.

Link to comment
  • 3 weeks later...

Thank you all for the help with this. I was able to get Sql Server running great on my unraid setup. I would also recommend adding the Enviroment variable MSSQL_AGENT_ENABLED so you can set it to true or false to make things complete. I also added in the Enviroment variable MSSQL_PID so it can be set to different license versions, (default is developer, but you can set standard, express, etc)

 

Now what I can't figure out is how to get SSIS in the mix. Anyone have any ideas? I think it also needs an integration server but i'm not sure.

Link to comment
51 minutes ago, neruve said:

One other thing I can't figure out. It always says there is an update to the container I made for mssql. Do you guys experience the same problem? Is there a fix where it will look at the repo to see if there is actually an update?

It's an extremely active project, there are a lot of updates.  You can pick a more stable tag if you prefer... there's a list here: https://hub.docker.com/_/microsoft-mssql-server

Link to comment
13 hours ago, BoxOfSnoo said:

It's an extremely active project, there are a lot of updates.  You can pick a more stable tag if you prefer... there's a list here: https://hub.docker.com/_/microsoft-mssql-server

Even when i pull the 2019-GA-ubuntu-16.04 it still says apply update every time I check for updates. For some reason it thinks there is always and update. Is it because I built the template myself?

Link to comment

Hey Guys,

 

A recent (docker template) update seems to have some impact as the container starts and stops after a few seconds, saying:

 

This container is running as user root.
Your master database file is owned by root.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
2020-01-07 20:48:26.95 Server Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64)

I tried adding  -u 0:0 as a parameter, but it wouldn't change anything. Any ideas on how to fix it?

Link to comment
On 1/7/2020 at 2:54 PM, Jaster said:

Hey Guys,

 

A recent (docker template) update seems to have some impact as the container starts and stops after a few seconds, saying:

 


This container is running as user root.
Your master database file is owned by root.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
2020-01-07 20:48:26.95 Server Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64)

I tried adding  -u 0:0 as a parameter, but it wouldn't change anything. Any ideas on how to fix it?

Do you have volumes mapped?  If so, what are the paths?

Link to comment
  • 3 weeks later...
On 1/13/2020 at 2:22 PM, BoxOfSnoo said:

Do you have volumes mapped?  If so, what are the paths?

Sorry, I didn't notice the reply

/root/mnt/data -> /mnt/user/apps/SQL
/var/opt/mssql/data -> /mnt/user/apps/SQL/data
/var/opt/mssql/log -> /mnt/user/apps/SQL/Log

Thats my mapping. I tried chmod 777 there, but it hasn't changed anything.

Link to comment
On 2/4/2020 at 1:24 PM, Jaster said:

Sorry, I didn't notice the reply


/root/mnt/data -> /mnt/user/apps/SQL
/var/opt/mssql/data -> /mnt/user/apps/SQL/data
/var/opt/mssql/log -> /mnt/user/apps/SQL/Log

Thats my mapping. I tried chmod 777 there, but it hasn't changed anything.

 

I had the same problem and looking at logs inside Log folder showed that it was corrupt master database. The problem was fixed using Unassigned Device instead of array. Cache drive might work also. This is on 6.6.7.

 

Updated to 6.8.2, works fine.

Edited by Macrossru
Link to comment
On 2/4/2020 at 5:24 AM, Jaster said:

Sorry, I didn't notice the reply


/root/mnt/data -> /mnt/user/apps/SQL
/var/opt/mssql/data -> /mnt/user/apps/SQL/data
/var/opt/mssql/log -> /mnt/user/apps/SQL/Log

Thats my mapping. I tried chmod 777 there, but it hasn't changed anything.


Yeah that crashes the server task.  If you turn off the mapping it runs - but who wants that!

 

I think the solution for me was to change /mnt/user to /mnt/cache.  If that share is not in your cache drive then you *might* have to use a named volume instead of a mapped volume. Those sit in your docker image file though, unfortunately.

Link to comment

I am actually running 6.7.2 and the mapping is pointing to the cache. As far as I remember the issues could also have started after a reboot of the server, not just a docker update.

 

With the recent container update, this happened:

 

SQL Server 2019 will run as non-root by default.
This container is running as user root.
Your master database file is owned by root.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
2020-02-06 15:51:31.81 Server Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)

and the server just shuts down.

 

edit: I tried updating the server to the latest image... same story. It starts and just shuts down without any further notice.

Edited by Jaster
Link to comment
2 hours ago, Jaster said:

I am actually running 6.7.2 and the mapping is pointing to the cache. As far as I remember the issues could also have started after a reboot of the server, not just a docker update.

 

With the recent container update, this happened:

 


SQL Server 2019 will run as non-root by default.
This container is running as user root.
Your master database file is owned by root.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
2020-02-06 15:51:31.81 Server Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)

and the server just shuts down.

 

edit: I tried updating the server to the latest image... same story. It starts and just shuts down without any further notice.

Your master database is corrupted. Try to map to a new location to see if it works. If it does, either repair the master database or delete it.

Link to comment
20 hours ago, Macrossru said:

Your master database is corrupted. Try to map to a new location to see if it works. If it does, either repair the master database or delete it.

As mentioned, I tried it with no effect.

 

SOLUTION: My Cache drive is not a pool and runs as XFS, which seems not to be working with MSSQL. I mounted and unassinged NTFS SSD with RW/Slave option. Now it runs.

Link to comment
  • 2 months later...

I've been trying to get this running for a couple of days. I've created the container and it appears to running.

 

image.thumb.png.873adc21e523d5f3de350d4c78139773.png

 

When I run docker pas -a I get this:-

 

image.thumb.png.be190caac00449dce52e3129477b63f4.png

 

but when I try to log in with SA and the password with this:-

 

image.png.8f7d7fc8b392fd0c65bb4c141a40a802.png

 

Anyone see any blindingly obvious mistakes?  SQL Error log reports:-

 

image.png.2bcb7b72b17e531434d338d86b8e5bbe.png

 

 

image.png

Edited by io1901
Link to comment

1. The SA password is only set when the container is set up. So if you set the container up, shut it down and change the password, it won't have any effect.

2. The whole thing fails if your password does not match the requirements (I'd say you are missing a special char there).

Link to comment
7 minutes ago, Jaster said:

1. The SA password is only set when the container is set up. So if you set the container up, shut it down and change the password, it won't have any effect.

2. The whole thing fails if your password does not match the requirements (I'd say you are missing a special char there).

1. I've not had any success with any passwords I've removed the container and started afresh - the password was setup when I set the container up as you can see from the the initial setup command (1st image) -  

2. The MS website quote 3 from 4 UC, LC, Numbers or special chars. 

 

I'm probably doing something wrong - but have tried most suggestions I've found.

 

 

 

Link to comment
  • 1 month later...

I've been attempting to set up MS SQL Server too, I seem to get it up and running fine pointing the paths at /mnt/cache etc. (I'm using my own template repo here if it helps anyone, I'd also welcome any feedback on cleaning that template up a bit). I haven't given it any serious work to do yet so I may still run in to more issues.

 

The only issue I'm having currently is getting Unraid to detect available updates, I currently always get 'Not Available' - is anyone aware of a workaround for that other than an occasional force update? I'm guessing it's to do with MS using the mcr.* repo but I'm quite new to tinkering with Docker and these Unraid templates.

  • Thanks 1
Link to comment
  • 2 months later...

It was something "Stupid". The data of course was persistent and that was using the original wrong password (not complex enough) - deleted the data started it up again and "Bingo" I could log in. Now to get the Service Agent running..

 

Edited by io1901
typo
Link to comment
  • 4 weeks later...

Thanks to everyone for sharing their knowledge in this thread.  I've been curious about getting a SQL Server Docker image working for some time but never dabbled until today.  I was losing faith when I saw that the official container was not supported anymore but this thread got me up and running.  I'm not very strong with Linux, or Docker.... but this thread made it very easy.

 

What i did: I started with Merrrp's config on GitHub.  

 

I went to the Dockerman folder (on my the unraid flash drive) and created an xml file with the contents of Meerp's config.  

 

Restarted the UnRaid docker engine. 

 

Went to the docker tab and choose ' add container '.  I was able to choose Meerp's config from there.

 

I set the password, port etc and hit apply.  In a few minutes SQL Server was up and running... sort of.  Seems that there is a new variable needed. 'MSSQL_COLLATION' is required.  ( I figured this out by looking at the error log which was located in the app-data folder ).  I added the parameter, and set it to 'SQL_Latin1_General_CP1_CI_AS' (which, google told me was the default).  

 

After adding the parameter SQL Server started up.  I went to a windows system, installed the latest Management Studio and was able to connect.

 

I'm running UnRaid 6.9 Beta 25 and Microsoft SQL Server 2019 (RTM-CU7) (KB4570012) - 15.0.4063.15 (X64)

 

Best of luck to anyone else who finds this thread.

Edited by trevisthomas
Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.