Jump to content
Jaster

MSSQL Docker on UnRaid

67 posts in this topic Last Reply

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

Share this post


Link to post
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!

Share this post


Link to post
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/

 

Share this post


Link to post
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.

Share this post


Link to post

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.

Share this post


Link to post

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?

Share this post


Link to post
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

Share this post


Link to post
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?

Share this post


Link to post

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?

Share this post


Link to post
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?

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post

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

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post

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.