MSSQL Docker on UnRaid


Recommended Posts

1 hour ago, jowi said:

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...) so they stay persistent when stopping the docker or after a reboot. Will look into that!

Good to hear! You're welcome :)

 

You would indeed map a volume to the container pointing to a location on the host.

This is were it crashes according to multiple sources though, so I would be curious how that goes for you.

 

You would add a PATH from the DockerMan page and map /var/opt/mssql/ or /var/opt/mssql/data to a path on your host to make the data persistent.


Cheers!

Link to comment
4 hours ago, jowi said:

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!

Any path that isn't mapped to Unraid storage is in the docker image (and will possibly fill docker image and corrupt it). It won't persist after the docker gets updated. Why don't you just map that path to /mnt/cache/appdata/mssql?

Link to comment

I don't think mapping is enough? If i map it, the docker has access to it; but i need to tell sql server that it actually has to use the mapped path in stead of the (internal) /var/opt/mssql/data. By the looks of it, this can be done by adding some other environment variable which will be used by startup of mssql, that is something i have to check and figure out.

 

*edit* i did not think just mapping e.g. /appdate/sqlserver to the container's /var/opt/mssql/data would work but it does :) i thought with adding paths this way you would just 'create' access to an external path, but in fact it replaces the path inside the container? So my /mnt/cache/appdata/sqlserver is actually replacing the container's '/var/opt/mssql/data'?

 

Anyway, this works, but... for some reason, if you restart the docker, the path i just added in dockerman, is no longer in the settings? Environment paramters like SA_PASSWORD and ACCEPT_EULA are still there but the path is gone... SQL Server is still using /cache/appdata so that is ok...

Edited by jowi
Link to comment
33 minutes ago, jowi said:

So my /mnt/cache/appdata/sqlserver is actually replacing the container's '/var/opt/mssql/data'?

Map just means a correspondence. Just as a location on a road map corresponds to an actual location in the physical world, path mappings just setup up a correspondence between paths on the host and paths in the container.

 

So the application inside the container is still using the container path /var/opt/..., but on the host that corresponds to the path /mnt/cache/....

 

This is all pretty basic docker volume mapping stuff.

 

37 minutes ago, jowi said:

Anyway, this works, but... for some reason, if you restart the docker, the path i just added in dockerman, is no longer in the settings? Environment paramters like SA_PASSWORD and ACCEPT_EULA are still there but the path is gone...

Are you saying that the Add/Edit Container page no longer shows the mapping? Try moving the slider at the top right to switch from Basic View to Advanced View. Some of the "default" settings are hidden in the Basic View.

 

Link to comment

What i did not realize was that if you map an external folder to an existing 'internal' container folder, the internal folder gets replaced by the external one... until now i've just added external folders to containers so they became available in the container as an additional folder, like /music for /mnt/disk1/music etc.

 

The added/mapped folder no longer shows up, also not when moving the slider to advanced or 'show more settings'. It does however is stored in the dockerman xml file, and if i stop/restart the container it is still mapped and used.

Link to comment
1 minute ago, jowi said:

What i did not realize was that if you map an external folder to an existing 'internal' container folder, the internal folder gets replaced by the external one... until now i've just added external folders to containers so they became available in the container as an additional folder, like /music for /mnt/disk1/music etc.

 

The added/mapped folder no longer shows up, also not when moving the slider to advanced or 'show more settings'. It does however is stored in the dockerman xml file, and if i stop/restart the container it is still mapped and used.

You seem to be using the word "replaced" to mean "mapped". As I said, the application inside the container is still using the same path it was, and in fact, it knows nothing at all about any host paths.

 

Not sure what is happening with the way this is showing up on the Add/Edit container page, assuming that is what you mean. Did you actually create this xml template using that page, or did you create it manually somehow and then put it on flash where it would be found by dockerMan?

 

Post the xml file, a screenshot of the Add/Edit container page (Advanced Mode) where you don't see the mapping, and post the docker run command it generates as explained in this very first link in the Docker FAQ:

 

https://forums.unraid.net/topic/57181-docker-faq/#comment-564345

 

Link to comment

After removing the docker and re-installing it, all seems to be ok. Path is now also showing, so i think i'm good. For completeness and for others, here are the settings and the xml.


<?xml version="1.0"?>
<Container version="2">
  <Name>SQLServer</Name>
  <Repository>mcr.microsoft.com/mssql/server</Repository>
  <Registry>https://hub.docker.com/_/microsoft-mssql-server</Registry>
  <Network>bridge</Network>
  <MyIP/>
  <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>http://www.xydata.co.uk/wp-content/uploads/2016/02/SQL-Server-250x250.jpg</Icon>
  <ExtraParams/>
  <PostArgs/>
  <DateInstalled>1575384584</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>
    <Volume>
      <HostDir>/mnt/cache/appdata/SqlServer/database/</HostDir>
      <ContainerDir>/var/opt/mssql/data</ContainerDir>
      <Mode>rw</Mode>
    </Volume>
  </Data>
  <Environment>
    <Variable>
      <Value>P@ssw0rd</Value>
      <Name>SA_PASSWORD</Name>
      <Mode/>
    </Variable>
    <Variable>
      <Value>Y</Value>
      <Name>ACCEPT_EULA</Name>
      <Mode/>
    </Variable>
  </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>
  <Config Name="SA_PASSWORD" Target="SA_PASSWORD" Default="" Mode="" Description="Container Variable: SA_PASSWORD" Type="Variable" Display="always" Required="false" Mask="false">P@ssw0rd</Config>
  <Config Name="ACCEPT_EULA" Target="ACCEPT_EULA" Default="" Mode="" Description="Container Variable: ACCEPT_EULA" Type="Variable" Display="always" Required="false" Mask="false">Y</Config>
  <Config Name="PersistentStorage" Target="/var/opt/mssql/data" Default="" Mode="rw" Description="Storage location of SQL Server persistent database files" Type="Path" Display="always" Required="false" Mask="false">/mnt/cache/appdata/SqlServer/database/</Config>
</Container>

 

Screen Shot 2019-12-03 at 17.41.10.png

Edited by jowi
Link to comment
26 minutes ago, jowi said:

After removing the docker and re-installing it, all seems to be ok. Path is now also showing, so i think i'm good. For completeness and for others, here are the settings and the xml.

 


<?xml version="1.0"?>
<Container version="2">
  <Name>SQLServer</Name>
  <Repository>mcr.microsoft.com/mssql/server</Repository>
  <Registry>https://hub.docker.com/_/microsoft-mssql-server</Registry>
  <Network>bridge</Network>
  <MyIP/>
  <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>http://www.xydata.co.uk/wp-content/uploads/2016/02/SQL-Server-250x250.jpg</Icon>
  <ExtraParams/>
  <PostArgs/>
  <DateInstalled>1575384584</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>
    <Volume>
      <HostDir>/mnt/cache/appdata/SqlServer/database/</HostDir>
      <ContainerDir>/var/opt/mssql/data</ContainerDir>
      <Mode>rw</Mode>
    </Volume>
  </Data>
  <Environment>
    <Variable>
      <Value>P@ssw0rd</Value>
      <Name>SA_PASSWORD</Name>
      <Mode/>
    </Variable>
    <Variable>
      <Value>Y</Value>
      <Name>ACCEPT_EULA</Name>
      <Mode/>
    </Variable>
  </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>
  <Config Name="SA_PASSWORD" Target="SA_PASSWORD" Default="" Mode="" Description="Container Variable: SA_PASSWORD" Type="Variable" Display="always" Required="false" Mask="false">P@ssw0rd</Config>
  <Config Name="ACCEPT_EULA" Target="ACCEPT_EULA" Default="" Mode="" Description="Container Variable: ACCEPT_EULA" Type="Variable" Display="always" Required="false" Mask="false">Y</Config>
  <Config Name="PersistentStorage" Target="/var/opt/mssql/data" Default="" Mode="rw" Description="Storage location of SQL Server persistent database files" Type="Path" Display="always" Required="false" Mask="false">/mnt/cache/appdata/SqlServer/database/</Config>
</Container>

 

 

Screen Shot 2019-12-03 at 17.41.10.png

DUDE, it works!

 

What's the difference from what I was doing?  I made it all manually using your settings, maybe that was it.  I also noted that the path has a trailing / - which I don't think I had before?  Not sure if that makes any difference.

 

The only thing it doesn't do is grab the icon, not sure why

  • Like 1
Link to comment

Try stopping and restartng it, the logo might show up after all. I had the icon in the dockerman/images folder at first, but it could not find it... now i point to some online icon. Will try changing that as well, don’t want to point to an online icon. But i’m glad the docker is working :)

Edited by jowi
Link to comment
2 minutes ago, jowi said:

I had the icon in the dockerman/images folder at first, but it could not find it... now i point to some online icon. Will try changing that as well, don’t want to point to an online icon. But i’m glad the docker is working :)

It was a change made in 6.8 to have the system work correctly if a user changes the Icon URL to actually update the icon (previously, it never did), and lower pointless writes to the flash drive.

 

It only grabs the icon from the URL once.  After that, it is served locally from a file within the docker.img file.

  • Like 1
Link to comment
1 hour ago, BoxOfSnoo said:

DUDE, it works!

 

What's the difference from what I was doing?  I made it all manually using your settings, maybe that was it.  I also noted that the path has a trailing / - which I don't think I had before?  Not sure if that makes any difference.

 

The only thing it doesn't do is grab the icon, not sure why

 

Did you download the MSSQL image from the Apps section?

This only gives you the image from the Microsoft Hub that is not being updated anymore and is more then a year old (see post)

By manually constructing the user-template xml you download the newest image of the Microsoft Container Repository by pointing it to the repo/image, which the Community Apps isn't able to find (yet).

 

Nice to see this is resolved, good addition to dockerland on unRAID 🙂

Link to comment
4 minutes ago, S1dney said:

 

Did you download the MSSQL image from the Apps section?

This only gives you the image from the Microsoft Hub that is not being updated anymore and is more then a year old (see post)

By manually constructing the user-template xml you download the newest image of the Microsoft Container Repository by pointing it to the repo/image, which the Community Apps isn't able to find (yet).

 

Nice to see this is resolved, good addition to dockerland on unRAID 🙂

I actually forget exactly how I found it - I know I tried to get the most current one from Microsoft's repo.  I think I may have started with the Dockerhub template and changed the repository source.

Link to comment
4 hours ago, jowi said:

What is needed to add a container like this to the main page of Community Applications?

1 hour ago, jowi said:

Ok... did not realize this was a stupid question. I leave it as is then.

Not a stupid question at all.

 

Here is the Docker FAQ post about that:

 

https://forums.unraid.net/topic/57181-docker-faq/#comment-566084

 

As mentioned there you should also create a support thread where you can support it.

  • Like 1
Link to comment
5 hours ago, jowi said:

Ok... did not realize this was a stupid question. I leave it as is then.

Well, I don't think that's a stupid question at all.

I would assume someone has to take ownership on maintaining it, like the ones @binhex is maintaining? (Not sure how it works exactly, but thanks anyway for your work! :D )

 

Now @BoxOfSnoo's comment got me thinking.

Modifying the user-template can be done more easily then copying the XML files on the flashdrive via the command line.

So in case anyone else wants to download the image from the new Microsoft Container Repository I would take these steps:

 

 Search.png.c8b6254ed894d15e17d78b3bcd5e6c1d.png

Search for mssql in the Community Apps (make sure that searching the DockerHub is enabled in the settings).

And click "Click Here To Get More Results From DockerHub".

 

 

719763327_MSSQLDeprecated.png.174843fb3b4b1e6a39e5c1cf0e70c711.png

Install the mssql server linux container (allthough it can be any container of choice, doesn't has to be mssql either)

 

1560559498_OriginalAdvanced.thumb.png.18838fe5ae7f6a346d19b1747036560e.png

Make sure "Advanced" view is selected, this will show you which Repository and Docker Hub URL you're downloading the image.

 

397058769_MSSQLCustom.thumb.png.555e712275184341f4d3d9ac3cd68b89.png

Change the following values:

 

Name: Name of your container obviously

Repository: mcr.microsoft.com/mssql/server

Docker Hub URL: https://hub.docker.com/_/microsoft-mssql-server

Icon URL: If you want a custom icon.

Description: To something descriptive (although not really necessary).

 

Then add the required Ports, Paths and Variables from the GUI.

 

After clicking Apply the docker is created from the new Microsoft Repository and the template is written to Flash, without even using the command line.

In my case it wrote (my-TEST-MSSQL-BasedOnMCR.xml to /boot/config/plugins/dockerMan/templates-user/).

 

Easier then messing around with XML's from flash ;) 

 

EDIT:

Downloading a new container isn't even necessary, as you can just click "Add Container" from the docker tab.

Just making sure the "Advanced" button is selected gives you the exact same options.

Docker_AddContainer.thumb.png.a480eb2e0bb9e25e393be2ab5803a9c3.png

Edited by S1dney
  • Like 2
Link to comment
32 minutes ago, trurl said:

Not a stupid question at all.

 

Here is the Docker FAQ post about that:

 

https://forums.unraid.net/topic/57181-docker-faq/#comment-566084

 

As mentioned there you should also create a support thread where you can support it.

Hahaha saw this after I hit apply.

Thanks, I will also check it out!

 

Until the time some developer wants to take ownership of publishing the MSSQL container, my way described above also works I guess :D 

Edited by S1dney
  • Like 1
Link to comment
44 minutes ago, trurl said:

Ok... did not realize this was a stupid question. I leave it as is then.

sorry if my answer was a bit short, it was done on my mobile, i didnt mean for it to come across as curt, the answers above are much more detailed and should get you (or whoever) a good start to getting it included in CA.

  • Like 1
Link to comment
1 hour ago, trurl said:

That is the standard way of working with dockers on Unraid. Most people are probably mostly unaware of doing it from the command line or manually editing templates. 

On second thought you're right.

I know that DockerMan/GUI is the way to interact with docker on unRAID, but I didn't know (yet) how to pull a docker container that could not be located from the Apps (or DockerHub within Apps) section.

Therefore I thought to include it if anyone end up here with the same questions.

 

I've edited my post cause adding a container from scratch using "Add container" might even be better, this is the way it's meant to be used I guess 😅, I always found any container in the Apps or DockerHub section so never had the need to look into this more.

 

Thanks for time taken to elaborate/help though :)

Link to comment
Just now, S1dney said:

Oh darn, so much irony.

I updated to 6.8 rc7 yesterday and this seemed to have killed my bitwarden mssql container.

Stuck in a restart loop, and the errors are kind of not getting me anywhere.

 

The ones of you actually using the mssql container already running a 6.8 build?

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

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.