Configure Azure SQL Edge

Azure SQL Edge supports configuration through one of the following two options:

• Environment variables
• An mssql.conf file placed in the /var/opt/mssql folder

Configure by using environment variables

Azure SQL Edge exposes several different environment variables that can be used to configure the SQL Edge container. These environment variables are a subset of the ones available for SQL Server on Linux. For more information on SQL Server on Linux environment variables, see Environment variables.

The following new environment variables were added to Azure SQL Edge.

The following SQL Server on Linux environment variable isn't supported for Azure SQL Edge. If defined, this environment variable is ignored during container initialization.

Specify the environment variables

Specify environment variables for SQL Edge when you deploy the service through the Azure portal. You can add them either in the Environment Variables section of the module deployment or as part of the Container Create Options.

Add values in Environment Variables.

Add values in Container Create Options.

Configure by using an mssql.conf file

Azure SQL Edge doesn't include the mssql-conf configuration utility like SQL Server on Linux does. You need to manually configure the mssql.conf file and place it in the persistent storage drive that is mapped to the /var/opt/mssql/ folder in the SQL Edge module. When you're deploying SQL Edge through Azure IoT Hub, this mapping is specified as the Mounts option in the Container Create Options.

{
  "Mounts": [
    {
      "Type": "volume",
      "Source": "sqlvolume",
      "Target": "/var/opt/mssql"
    }
  ]
}

The following new mssql.conf options were added for Azure SQL Edge.

The following mssql.conf options aren't applicable to SQL Edge:

The following sample mssql.conf file works for SQL Edge. For more information on the format for an mssql.conf file, see mssql.conf format.

[EULA]
accepteula = Y

[coredump]
captureminiandfull = true
coredumptype = full

[filelocation]
defaultbackupdir = /var/opt/mssql/backup/
defaultdatadir = /var/opt/mssql/data/
defaultdumpdir = /var/opt/mssql/data/
defaultlogdir = /var/opt/mssql/log/

[language]
lcid = 1033

[memory]
memorylimitmb = 6144

[sqlagent]
errorlogfile = /var/opt/mssql/log/sqlagentlog.log
errorlogginglevel = 7

[traceflag]
traceflag0 = 3604
traceflag1 = 3605
traceflag2 = 1204

Run Azure SQL Edge as non-root user

By default, the Azure SQL Edge containers run with a non-root user/group. When deployed through the Azure IoT Hub (or using docker run), unless a different user/group is specified, SQL Edge containers starts up as the mssql (non-root) user. To specify a different non-root user during deployment, add the *"User": "<name|uid>[:<group|gid>]"* key-value pair under container create options. In the following example, SQL Edge is configured to start as the user *IoTAdmin*.

{
    ..
    ..
    ..
    "User": "IoTAdmin",
    "Env": [
        "MSSQL_AGENT_ENABLED=TRUE",
        "ClientTransportType=AMQP_TCP_Only",
        "MSSQL_PID=Premium"
    ]
}

To allow the non-root user to access DB files that are on mounted volumes, ensure that the user/group you run the container under, has read & write permissions on the persistent file storage. In the following example, we set the non-root user with user_id of 10001 as the owner of the files.

chown -R 10001:0 <database file dir>

Upgrade from earlier CTP releases

Earlier CTPs of Azure SQL Edge were configured to run as the root users. The following options are available when upgrading from earlier CTPs.

• Continue to use the root user - To continue using the root user, add the *"User": "0:0"* key-value pair under container create options.

• Use the default mssql user - To use the default mssql user, follow these steps:

  • Add a user named mssql on the Docker host. In the example below, we add a user mssql with ID 10001. This user is also added to the root group.

    sudo useradd -M -s /bin/bash -u 10001 -g 0 mssql
    

  • Change the permission on the directory/mount volume where the database file resides

    sudo chgrp -R 0 /var/lib/docker/volumes/kafka_sqldata/
    sudo chmod -R g=u /var/lib/docker/volumes/kafka_sqldata/
    

• Use a different non-root user account - To use a different non-root user account

  • Update the container create options to specify add *"User": "user_name | user_id* key-value pair under container create options. Replace user_name or user_id with an actual user_name or user_id from your Docker host.
  • Change the permissions on the directory/mount volume.

Persist your data

Your Azure SQL Edge configuration changes and database files are persisted in the container even if you restart the container with docker stop and docker start. However, if you remove the container with docker rm, everything in the container is deleted, including Azure SQL Edge and your databases. The following section explains how to use data volumes to persist your database files even if the associated containers are deleted.

Mount a host directory as data volume

The first option is to mount a directory on your host as a data volume in your container. To do that, use the docker run command with the -v <host directory>:/var/opt/mssql flag. This allows the data to be restored between container executions.

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 -v <host directory>/data:/var/opt/mssql/data -v <host directory>/log:/var/opt/mssql/log -v <host directory>/secrets:/var/opt/mssql/secrets -d mcr.microsoft.com/azure-sql-edge

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -p 1433:1433 -v <host directory>/data:/var/opt/mssql/data -v <host directory>/log:/var/opt/mssql/log -v <host directory>/secrets:/var/opt/mssql/secrets -d mcr.microsoft.com/azure-sql-edge

This technique also enables you to share and view the files on the host outside of Docker.

Use data volume containers

The second option is to use a data volume container. You can create a data volume container by specifying a volume name instead of a host directory with the -v parameter. The following example creates a shared data volume named sqlvolume.

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 -v sqlvolume:/var/opt/mssql -d mcr.microsoft.com/azure-sql-edge

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -p 1433:1433 -v sqlvolume:/var/opt/mssql -d mcr.microsoft.com/azure-sql-edge

Even if you stop and remove this container, the data volume persists. You can view it with the docker volume ls command.

docker volume ls

If you then create another container with the same volume name, the new container uses the same Azure SQL Edge data contained in the volume.

To remove a data volume container, use the docker volume rm command.

Next steps

• Connect to Azure SQL Edge
• Build an end-to-end IoT solution with SQL Edge