Jump to content
Jaster

MSSQL Docker on UnRaid

54 posts in this topic Last Reply

Recommended Posts

Hi Guys, 

 

I'd like to run the MSSQL Docker on unraid but I have no idea what it is telling me after I try starting it:

 

/opt/mssql/bin/sqlservr: Invalid mapping of address 0x14fc0f8c7000 in reserved address space below 0x400000000000. Possible causes:
1) the process (itself, or via a wrapper) starts-up its own running environment sets the stack size limit to unlimited via syscall setrlimit(2);
2) the process (itself, or via a wrapper) adjusts its own execution domain and flag the system its legacy personality via syscall personality(2);
3) sysadmin deliberately sets the system to run on legacy VA layout mode by adjusting a sysctl knob vm.legacy_va_layout.

Hints? Ideas?

Thanks!

Share this post


Link to post

Hey man,

 

Just bumped into the same thing setting up Bitwarden on my unRaid build.

I had it running in on a Debian build which I'm looking to phase out now that unRaid will be the Docker host.

 

Seems like we're hitting that exact same thing.

Microsoft wrote an article about this: check it out!

Comparing the two settings mentioned in the article between my unRaid build and my Debian host:

 

     Debian

     [%%%%%%%%%] :/# sysctl vm.legacy_va_layout
     vm.legacy_va_layout = 0

     [%%%%%%%%%] :/# ulimit -s
     8192

 

     unRaid

     [%%%%%%%%%] :/# sysctl vm.legacy_va_layout
     vm.legacy_va_layout = 0

     [%%%%%%%%%] :/# ulimit -s
     unlimited

 

Apparently the ulimit for the stack size is set differently on my unRaid system.

Now I'm not really a fan in modifying these kind of things system wide, since unRaid might have had a good reason to set the to unlimited in the first place, so I was looking for other ways to do it.

 

Fortunately Docker seems to have a switch to modify these on a per container basis:
Docker docs

 

Now in my case Bitwarden is using Docker Compose to build the Bitwarden environment, so I had to tweak the yml code.

The default docker-compose.yml (located in bwdata/docker) lists the following lines for the mssql container:

version: '3'

services:
  mssql:
    image: bitwarden/mssql:1.32.0
    container_name: bitwarden-mssql
    restart: always
    volumes:
      - ../mssql/data:/var/opt/mssql/data
      - ../logs/mssql:/var/opt/mssql/log
      - ../mssql/backups:/etc/bitwarden/mssql/backups
    env_file:
      - mssql.env
      - ../env/uid.env
      - ../env/mssql.override.env

Now if we create an override file named docker-compose.override.yml and also place it in the same bwdata/docker directory we only have to put these lines in:

#
# Override file for the auto-generated docker-compose.yml file provided 
#   by Bitwarden
# This file sets ulimits on the 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:
   ulimits:
    stack:
     soft: "8192000"
     hard: "8192000"

After this we can safely start up the container as it merges the settings together with the mssql service, effectively setting the stack size limits for our mssql container environment the same as they were on Debian.

 

Be aware though that the soft and hard limits use bytes instead of kilobytes (that the ulimit -s command takes), so you'd have to convert the values as I did.

 

Hope this helps!

 

Cheers, Sidney
 

Share this post


Link to post
-e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Yourpasword1" -m 8g --ulimit stack=8192000:8192000

Adding this to the extra parameters does the magic!

 

PS: Remeber to map the /var pathes and set a correct SA password

 

 

Edited by Jaster

Share this post


Link to post

I see you have it figured out in the meantime.

All other parameters are managed by the Compose file in my case so didn't really apply.

Great to see you have it up and running.

And thanks for sharing back your own findings, might definitely help someone in the future.

 

Cheers man!

Share this post


Link to post
On 9/30/2019 at 6:55 AM, Jaster said:

-e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Yourpasword1" -m 8g --ulimit stack=8192000:8192000

Adding this to the extra parameters does the magic!

 

PS: Remeber to map the /var pathes and set a correct SA password

 

 

Are you able to map the volumes to appdata? Whenever I do that it crashes.  The only way I could get it to run was by using a named volume instead of a host path.

 

Was it the ulimit and stack parameter that made it work?

Share this post


Link to post

Anyone still working on running MS-SQL?  I use it for development and I'd really hate to have to load the Windows version in a VM.

 

I tried the memory and ulimit settings above, and it crashes.

 

To be clear, it crashes when I map /var/opt/mssql to anything.  I had this trouble on a Mac host before I started running unRAID, but I used a named volume instead.  I can't see that that is a good idea for unRAID though - since it is all going to fill up the docker image?  Is that my only option?

 

This works:

mssql:/var/opt/mssql

 

This crashes the server:

/mnt/user/appdata/mssql:/var/opt/mssql

 

Also these crash the server:

/mnt/user/appdata/mssql/data:/var/opt/mssql/data

/mnt/user/appdata/mssql/log:/var/opt/mssql/log

/mnt/user/appdata/mssql/backups:/var/opt/mssql/backups

 

 

Share this post


Link to post
1 hour ago, BoxOfSnoo said:

Anyone still working on running MS-SQL?  I use it for development and I'd really hate to have to load the Windows version in a VM.

 

I tried the memory and ulimit settings above, and it crashes.

 

To be clear, it crashes when I map /var/opt/mssql to anything.  I had this trouble on a Mac host before I started running unRAID, but I used a named volume instead.  I can't see that that is a good idea for unRAID though - since it is all going to fill up the docker image?  Is that my only option?

 

This works:


mssql:/var/opt/mssql

 

This crashes the server:


/mnt/user/appdata/mssql:/var/opt/mssql

 

Also these crash the server:


/mnt/user/appdata/mssql/data:/var/opt/mssql/data

/mnt/user/appdata/mssql/log:/var/opt/mssql/log

/mnt/user/appdata/mssql/backups:/var/opt/mssql/backups

 

 

Well, in my case it runs perfectly fine with just the ulimit changes.

 

I checked my configfile for Bitwarden and it seems to map to appdata also (as this is were my bwdata directory resides, ../mssql maps it to /mnt/appdata/bitwarden/mssql)

 

See attached screenshot.

 

And mounting it to a random other directory on appdata? So create /mnt/appdata/test and mount it in there? 

 

Mine seems to be on version 1.32.0, might also be worth checking that specific version.

 

Cheers!

32BE169E-B659-4759-BDE9-AEDC234DEABB.jpeg

Share this post


Link to post
16 minutes ago, S1dney said:

Well, in my case it runs perfectly fine with just the ulimit changes.

 

I checked my configfile for Bitwarden and it seems to map to appdata also (as this is were my bwdata directory resides, ../mssql maps it to /mnt/appdata/bitwarden/mssql)

 

See attached screenshot.

 

And mounting it to a random other directory on appdata? So create /mnt/appdata/test and mount it in there? 

 

Mine seems to be on version 1.32.0, might also be worth checking that specific version.

 

Cheers!

32BE169E-B659-4759-BDE9-AEDC234DEABB.jpeg

Ok thanks, I will try the specific version...

 

How are you managing this through unRAID?  Are you doing this through the command line and this docker-compose file?

Share this post


Link to post
12 minutes ago, BoxOfSnoo said:

Ok thanks, I will try the specific version...

 

How are you managing this through unRAID?  Are you doing this through the command line and this docker-compose file?

Ok man, good luck!

 

For now I start things up manually, but when my scripts for backups etc are done I’ll be writing a script that runs when the array is started and that’ll start all necessary docker containers. That will use a mixture of docker start and docker-compose commands or maybe I just out everything in a compose file, haven’t decided yet.

 

Cheers!

Share this post


Link to post

I've used docker to fetch a sql server docker using the steps here: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-linux-ver15&pivots=cs1-cmd and it actually works, which is very nice :) the docker (and sql server) is running and i can connect to it from other machines within my network using the SQL Server Management Studio.

 

I do have some questions:

- where is the docker downloaded/installed to? It is not in appdata, nor in the location i used to enter the commands?

- the sql server docker can be seen in UNRAID's docker menu, with a default ? icon, and no settings... how do i make an actual 'UNRAID' (?) docker that has settings (like port, SA password etc) and is placed in appdata, like all other dockers?

Share this post


Link to post
44 minutes ago, jowi said:

I've used docker to fetch a sql server docker using the steps here: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-linux-ver15&pivots=cs1-cmd and it actually works, which is very nice :) the docker (and sql server) is running and i can connect to it from other machines within my network using the SQL Server Management Studio.

 

I do have some questions:

- where is the docker downloaded/installed to? It is not in appdata, nor in the location i used to enter the commands?

- the sql server docker can be seen in UNRAID's docker menu, with a default ? icon, and no settings... how do i make an actual 'UNRAID' (?) docker that has settings (like port, SA password etc) and is placed in appdata, like all other dockers?

The docker container is downloaded to a subdirectory in /var/lib/docker, which is actually a loop device.

The loop device is the docker.img image being mounted to that location.

The docker image itself is located in /mnt/user/system/docker, which is most likely on your cache (if present).

 

Any changes will therefore be stored inside docker.img (on the array/cache) but will be probably be lost upon upgrading the container (cause it get's overwritten) or rebuilding the docker.img file.

 

That's why you would normally mount local folders to folder in the container, to have data persist.

If you want to make it an actual docker, you'd download it from the community applications (https://synaptiklabs.com/posts/setup-unraid-to-pull-from-docker-hub/)

You can download every container from the docker hub directly (takes on extra click in the apps section), but certain ones that are more easily found there have been made into a template (if I'm not mistaking) by fellow unraid enthusiasts. This would ask you to create certain paths instead of you having to look up the docker hub for options to specifiy.

 

I usually prefer using the one directly from the developer's docker repository, but that's personal.

 

Cheers!

Share this post


Link to post
18 minutes ago, S1dney said:

certain ones that are more easily found there have been made into a template (if I'm not mistaking) by fellow unraid enthusiasts. This would ask you to create certain paths instead of you having to look up the docker hub for options to specifiy.

 

I usually prefer using the one directly from the developer's docker repository, but that's personal.

The Unraid templates in most cases are created by the docker developers themselves. Those docker developers are the fellow Unraid enthusiasts of which you speak. You can find those docker developers in the support threads they have also created for their containers in our Docker Containers subforum.

 

This MSSQL container was not one of these of course.

Share this post


Link to post
6 minutes ago, trurl said:

The Unraid templates in most cases are created by the docker developers themselves. Those docker developers are the fellow Unraid enthusiasts of which you speak. You can find those docker developers in the support threads they have also created for their containers in our Docker Containers subforum.

 

This MSSQL container was not one of these of course.

Ah, that’s great to hear.

Thanks for completing that statement.

I might be browsing that part of the forums one of these days 😃

Share this post


Link to post

Be informed, though, that the problem I am encountering is exactly do do with persistence. Once you configure volumes, expect the container to crash.

 

I haven figured out a great solution yet.

Share this post


Link to post
13 hours ago, S1dney said:

If you want to make it an actual docker, you'd download it from the community applications (https://synaptiklabs.com/posts/setup-unraid-to-pull-from-docker-hub/)

The sql server dockers in de CA dockerhub section aren't what i need, mostly are personal test projects or obsolote versions. What i am looking for is a way to 'push' the Microsoft SQL Server docker in an UNRAID 'template' or container ( => i think this is what it is called?), so when i install it, it will have some settings/user interface like the 'normal' dockers from CA, where you can add settings, change network, set an icon etc. Is there some section on the forum on how to 'encapsulate' a dockerfile so it acts like an UNRAID docker (by lack of a better word)?

 

Like i said, when i run the docker provided by Microsoft, it installs and runs perfectly, it also shows up in UNRAID's dockers tab, but... it has no options to set environment vars, paths icon etc. Nor does it seem to be installed in /appdata/ where all other dockers are? 

Edited by jowi

Share this post


Link to post
3 hours ago, jowi said:

The sql server dockers in de CA dockerhub section aren't what i need, mostly are personal test projects or obsolote versions. What i am looking for is a way to 'push' the Microsoft SQL Server docker in an UNRAID 'template' or container ( => i think this is what it is called?), so when i install it, it will have some settings/user interface like the 'normal' dockers from CA, where you can add settings, change network, set an icon etc. Is there some section on the forum on how to 'encapsulate' a dockerfile so it acts like an UNRAID docker (by lack of a better word)?

 

Like i said, when i run the docker provided by Microsoft, it installs and runs perfectly, it also shows up in UNRAID's dockers tab, but... it has no options to set environment vars, paths icon etc. Nor does it seem to be installed in /appdata/ where all other dockers are? 

Who not?

If searched via the docker hub you can just insert your own paths and variables?

206132310_2019-12-0211_18_42-Window.thumb.png.2badaba263dcc16819b6bfacba7c6270.png

You would add Paths to make it write data to your hosts appdata..

 

Not really sure what else you're searching for :)

 

The Microsoft article actually describes this behavior:

image.png.6c14c353c4a8d6f0e15424c911b66e3b.png

 

The link describing data persistence point you to the -v options, which is what adding a path lets DockerMan feed docker under the hood.

Running docker pull bypasses DockerMan so it'll show up as an orphaned container when it's off.

Edited by S1dney

Share this post


Link to post
1 hour ago, S1dney said:

Why not?

If searched via the docker hub you can just insert your own paths and variables?

Not really sure what else you're searching for :)

I did not use a sql server docker from (someone else's) docker hub, i got it directly from microsoft using:

docker pull mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

and installed it according the instructions on https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-linux-ver15&pivots=cs1-cmd so i got a 'bare metal' docker running in which does show up in UNRAID's docker tab but i can NOT change ANYTHING from the unraid docker tab.

 

The one you show is just one of many sql server dockers on dockerhub, don't know what version etc. mostly for personal use; don't know what they use as SA password etc, don't know if it's sql server 2015, 2017, 2019 etc. 

 

What they DO have is the 'UNRAID container' (by lack of a better word, still don't know what it is called) but it is what you are showing in the screenshot. What i want is to make THAT,  using the docker pull i showed earlier.

 

And... what is 'DockerMan'?

Edited by jowi

Share this post


Link to post
1 hour ago, jowi said:

what is 'DockerMan'

That is essentially the Add/Edit Container page in the Unraid webUI, as seen in @S1dney screenshot in the previous post. It is a form for entering all the things that you would need to put into a docker run or docker create command. When you submit the form, it is saved as a template on flash for you to reuse, and the docker run command with all the items on the form is executed. And once you have that template, you can work with it just like any other docker in Unraid.

Share this post


Link to post
1 hour ago, jowi said:

I did not use a sql server docker from (someone else's) docker hub, i got it directly from microsoft using:


docker pull mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

and installed it according the instructions on https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-linux-ver15&pivots=cs1-cmd so i got a 'bare metal' docker running in which does show up in UNRAID's docker tab but i can NOT change ANYTHING from the unraid docker tab.

 

The one in the screenshot is actually the docker from Microsoft's official docker hub:

 

(Expand the search to DockerHub)

2095143041_2019-12-0211_15_54-Window.thumb.png.6eb6525724433854760af6cd0eede4bc.png

 

(Search Microsoft's official image, I guess you should be able to view on Publisher to get a better listing)

870145433_2019-12-0211_18_20-Window.thumb.png.579ff95de89810d17f0c6f0aea9d178c.png

 

Now it will be converted into a DockerMan template that @trurl is referring about.

 

You just need to figure out which specific image name you're pulling and and which repo it came from.

If you use docker pull, DockerMan has no template to work with.

 

Hope this clears is up.

Share this post


Link to post

Tested the one from the screenshot, but it gives an error (Error: image operating system "windows" cannot be used on this platform). Looks like it is a docker built on windows not on linux? Also unknown what version etc. and passwords... it is all ment well i guess but i'm not gonna try 40 (!) pages of mssql dockers that people created mostly for their own use. I will go try a few, but this is not what you want... i tested another one and looked at the dockerhub page for info on passwords etc, it was there but nothing works... so unless i get lucky i think i still need to make my own docker container?

Edited by jowi

Share this post


Link to post
On 12/2/2019 at 5:45 PM, jowi said:

Tested the one from the screenshot, but it gives an error (Error: image operating system "windows" cannot be used on this platform). Looks like it is a docker built on windows not on linux? Also unknown what version etc. and passwords... it is all ment well i guess but i'm not gonna try 40 (!) pages of mssql dockers that people created mostly for their own use. I will go try a few, but this is not what you want... i tested another one and looked at the dockerhub page for info on passwords etc, it was there but nothing works... so unless i get lucky i think i still need to make my own docker container?

 

I think you're right on this one.

I only picked that container as an example.

 

According to the Community Apps GitHub, the Apps page does a lookup to the Docker Hub with this command:

shell_exec("curl -s -X GET 'https://registry.hub.docker.com/v1/search?q=$filter&page=$pageNumber'");

 

I first noticed that the Apps searchbar filters special character so I thought I try to hack the URL itself:

Somthing like:

   - 'https://registry.hub.docker.com/v1/search?q=mcr.microsoft.com'

   - 'https://registry.hub.docker.com/v1/search?q=mssql/server'

 

Tried all kinds of variations but nothing works.

 

The problem appears to be not with the community apps however, but with Microsoft moving it's container to their own repo.

See here:

https://dbafromthecold.com/2019/02/22/displaying-the-tags-within-the-sql-server-docker-repository/

 

I think that to get this to work you need to have some PHP skills as it probably requires building/modifying the community apps search_dockerhub function.

See lines 318 till 372 here:

https://github.com/Squidly271/community.applications/blob/master/source/community.applications/usr/local/emhttp/plugins/community.applications/include/exec.php

 

............Now there might be another way ;)

Download an official image you CAN download, I've downloaded pihole/pihole for example.

 

This gives you a template to work with:

cat /boot/config/plugins/dockerMan/templates-user/my-pihole.xml

 

You can copy it and modify the values you need.

After all settings are changed you can deploy the template from the docker tab -> Add Container -> User-Templates.

 

I've created a short MSSQL image briefly using this template below and it pulls the image just fine:

root@Tower:/# cat /boot/config/plugins/dockerMan/templates-user/my-mssql.xml 
<?xml version="1.0"?>
<Container version="2">
  <Name>mssql</Name>
  <Repository>mcr.microsoft.com/mssql/server</Repository>
  <Registry>https://hub.docker.com/_/microsoft-mssql-server</Registry>
  <Network>bridge</Network>
  <MyIP/>
  <Shell>sh</Shell>
  <Privileged>false</Privileged>
  <Support>https://hub.docker.com/_/microsoft-mssql-server</Support>
  <Project/>
  <Overview>The official MSSQL container from the new mcr.microsoft.com repository.   Converted By Community Applications   Always verify this template (and values) against the dockerhub support page for the container</Overview>
  <Category/>
  <WebUI/>
  <TemplateURL/>
  <Icon>/plugins/dynamix.docker.manager/images/question.png</Icon>
  <ExtraParams/>
  <PostArgs/>
  <CPUset/>
  <DateInstalled>1575321381</DateInstalled>
  <DonateText/>
  <DonateLink/>
  <Description>The official MSSQL container from the new mcr.microsoft.com repository.   Converted By Community Applications   Always verify this template (and values) against the dockerhub support page for the container</Description>
  <Networking>
    <Mode>bridge</Mode>
    <Publish>
      <Port>
        <HostPort>1433</HostPort>
        <ContainerPort>1433</ContainerPort>
        <Protocol>tcp</Protocol>
      </Port>
    </Publish>
  </Networking>
  <Data/>
  <Environment/>
  <Labels/>
  <Config Name="TCP_1433" Target="1433" Default="" Mode="tcp" Description="TCP port for SQL Communication" Type="Port" Display="always" Required="false" Mask="false">1433</Config>
</Container>

 

When you deploy your container from this image you can modify all other settings (such a paths and ports) from the DockerMan GUI, which is a lot easier.

Note that this will also save the changes to the template so this appears to be a one time action ;) 

Be aware though that you need to feed additional stuff (like accepting EULA) to the container to get it to work, but this should give you a starting point.

 

Also another thing to be aware of: https://github.com/Microsoft/mssql-docker/issues/407. Mentioned in the thread:

"Unfortunately, we've had quite a few issues reported with Unraid and it's not something we've tested or supported so far. You can see a list of the issues with Unraid in this query:

https://github.com/Microsoft/mssql-docker/issues?utf8=✓&q=is%3Aissue+is%3Aopen+unRAID"

 

MAC OSX has a known bug (listed on the MS Docs also) which appears similar. Maybe unRAID on slackware is hitting the same?

Strangely though for me is that my MSSQL bitwarden container just works... So it should be possible right.

I don't use the MSSQL docker container other then for Bitwarden at the moment. I'm just curious.

 

 

Edited by S1dney

Share this post


Link to post

Thanks, tried it and this works. Added SA_PASSWORD and ACCEPT_EULA as environment variables, sql server is running and i can connect to it from other machines in the network using sql management studio etc. so great :)

 

What i'm not seeing is any entry in /mnt/cache/appdata, where all other dockers seem to live (partially?). Now i know sql server uses .mdf/.ldf etc files to store it's data; where are they now? My guess is they are not persistent and in memory somewhere? Or, i hope not, on the usb stick taking up space? The latter i *really* don't want ;) or do they live in the docker.img somehow?

 

*edit* did some more reading; .mdf etc live in /var/opt/mssql/data inside the container. There is a way to connect to .mdf db's outside the container (so e.g. in appdata...) but it looks like they stay persistent in /var/opt/mssql/data when stopping the docker or after a reboot. Don't know what happens when the docker gets updated. Will look into that!

Edited by jowi

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.