ACI sql server container drops databases after restart

Question

I have running sql server container with a file storage persistence in Azure instance container(ACI).

The sql server have some database connected located in the file storages and it is working perfectly until the container restart.

After restart all the database is gone from the sql server and i have to manually attach all the database again.

It is possible automatic attach all the databases again after a restart ?

I have tried with a template like this

"resources": [
    {
        "type": "Microsoft.ContainerInstance/containerGroups",
        "apiVersion": "2018-10-01",
        "name": "[parameters('containerGroups_mssql_2019_name')]",
        "location": "WestEurope",
        "properties": {
            "containers": [
                {
                    "name": "[parameters('containerGroups_mssql_2019_name')]",
                 "properties": {
                    "command": [
                       "/opt/mssql-tools/bin/sqlcmd",
                       "-l",
                       "60",
                       "-U",
                       "SA",
                       "-P",
                       "!thisWillMakeMyDayEveryDayIn2019",
                       "-i",
                       "/mnt/mydata/scripts/attach_all_databases.sql"
                    ],

It does not work.

But if i execute the same command inside the container it works correctly.

Anyone who can give a direction ?

Thank you in advance

Mikael

Do you still work on this problem? I didn't see any updates. If there is a favor I can do for you? — Charles Xu, Apr 17 '20 at 07:06

score 0 · Answer 1 · answered Apr 14 '20 at 14:22

0

You'll need to persist your database in a volume. Here's a useful link:

https://learn.microsoft.com/en-us/azure/container-instances/container-instances-volume-azure-files

answered Apr 14 '20 at 14:22

Thiago Custodio

17,332
6
45
90

Hi Thiago, thank you for the reply, i am already using file storage persistence and even after the container restart, it is still connected to the file storage with all the database, but the databases is gone from the the sql server instanse and i need to attach them again. – Mikael Braad Nielsen Apr 15 '20 at 06:11

score 0 · Answer 2 · answered Apr 15 '20 at 09:26

0

For the issue you meet, there are two concepts of the Azure container instance you need to know.

One is that when you stop and start the container instance, it will begin a new deployment with the same container configuration. So it will not persist the data in the old container instance if you do not set the persistent storage yourself. You can learn the details about the stop and start the container.

Another one is that the Azure container instance does not support the command contains more than one word. For example, it can run the command ls, but it does not support the command ls -al. See more details about the Restriction.

So the solution is that creates a new image to put the command inside the image. And to persist the data in the container instance, you need to mount the Azure File share to the container instance. Take care, if you mount a share into a container directory in which files or directories exist, these files or directories are obscured by the mount and are not accessible while the container runs. So the mount point should be a directory that does not contain files useful or create a new one yourself to persist the data.

answered Apr 15 '20 at 09:26

Charles Xu

29,862
2
22
39

Hi Chales, thank you for the reply, i think i understand the first two concepts. The last part with mounting a fileshare into a container, the existing directories and files are not accessable. I don't understand that, if i create the container without the command, and enter the sql server i have no problems attaching the databases from the filestorage. So when in the container creation/running process are the current files accessable ? – Mikael Braad Nielsen Apr 20 '20 at 14:19
@MikaelBraadNielsen When the files already in the file storage, not the container, then the files are accessible. The feature just means the mount file storage will cover the files already in the directory. – Charles Xu Apr 21 '20 at 01:42
Hi again Charles I have tried this "resources": [ { "properties": { "containers": [ { "properties": { "command": [ "/mnt/mydata/scripts/attachdatabases.sh" ], The sh file contains /opt/mssql-tools/bin/sqlcmd -l 60 -U SA -P !thisWillMakeMyDayEveryDayIn2019 -i /mnt/mydata/scripts/attach_all_databases.sql This fails when the container is starting up and it just continuing retrying starting. The error message in the log is: standard_init_linux.go:178: exec user process caused "exec format error" – Mikael Braad Nielsen Apr 21 '20 at 07:58
Both the the sh and the attach_all_databases.sql file i located on the fileshare /mnt/mydata If i create a template without the sh command, its startup correctly and i am able to login into the container and execute the sh file without any problems and all the databases are attached. – Mikael Braad Nielsen Apr 21 '20 at 07:59
@MikaelBraadNielsen I do not suggest you set the command, it will cover the CMD command in the image and if you start the application at the end of the image, then the application will not start if you set the command. – Charles Xu Apr 21 '20 at 08:27

ACI sql server container drops databases after restart

2 Answers2