Administer your SQL Server in Docker

As with many things, you have to do it, to believe it.

The same goes for the Azure IoT Edge solution.

With the new IoT Edge solution, Microsoft provides a platform, both powerful and scalable. It’s based on Docker and logic is put in Docker containers.

And in many presentations, this (correct) picture is used to show the capabilities:

I am already working with the IoT Edge preview quite some time and there is a ‘weak’ spot in the image.

I already marked it in red so it’s not that hard to find.

On several occasions, non-technical people explained the local storage as being a database to persist data from internal logic.

I understand the confusion, but this is just a ‘database’ used by the IoT Edge internals (I assume mostly the Edge Hub module) and it is not accessible by users. I know for sure it’s used for the store-and-forward pattern used to send ‘upstream’ messages to the cloud.

Once a message is routed to be sent to the cloud, it’s first stored by the EdgeHub. If the connection to the Azure IoT Hub cannot be established, the message is stored with a certain retention time ( see its configuration “storeAndForwardConfiguration”: { “timeToLiveSecs”: 7200} ).

So, how can we add local storage to our IoT Edge if we want to do something with custom code and databases, etc.

Well, Microsoft already has written a great piece of documentation here.

There, you can see how you create an SQL Server database both on Linux and Windows containers and you learn how to create a database and a table inside it. Finally, you access it using some Azure function.

Let’s dig a bit deeper into this.

We will look on other  (simpler) ways to work with the database.

Creating the SQL Server

If you follow the manual from Microsoft, you will have to manipulate a ‘deployment.json’. This is not that intuitive.

Here is how to add an SQL Server module to the Azure portal.

Just use ‘Set modules’ and add a custom module named ‘sqlServer’ with the Image Uri ‘microsoft/mssql-server-linux:2017-latest’.

Update: a newer version exists. Please check the Azure IoT Edge modules marketplace. Integration is added in the portal now.

Add the following Container Create Options:

{
  "Env": [
    "ACCEPT_EULA=Y",
    "MSSQL_SA_PASSWORD=p@ssw0rd!Sql"
  ],
  "HostConfig": {
    "PortBindings": {
      "1433/tcp": [
        {
          "HostPort": "1401"
        }
      ]
    },
    "Mounts": [
      {
        "Type": "volume",
        "Source": "sqlVolume",
        "Target": "/var/opt/mssql"
      }
    ]
  }
}

Update: Microsoft has introduced a new container registry and documentation. Please check also the type of SQL Server you need: Developer, Express, Standard, etc. Some can deployed on production others can not.

Check out the Eula question. With Yes, you state you have the actual license needed in case you do not choose the Express edition.

This result in this module:

Note: For extra, I also added the tempSensor with URI microsoft/azureiotedge-simulated-temperature-sensor:1.0-preview. This is only for demonstration purposes. Update: a newer version for this module exists now.

Once deployed, on the Edge gateway you will see the modules being installed:

Keep in mind, these SQL Server images are not small (450MB or even bigger)

So this results in this situation:

Sql Server is running inside a Docker container on Docker.

Administer a database in Sql Server

The SQL Server is empty right now, no extra databases, no tables, etc.

Currently, there are three ways to change and administer the Sql Server instance:

  1. Use the Sql Command prompt within the Bash shell prompt within your Sql Server Linux image
  2. Use the Sql Command on your Windows host machine
  3. Use Sql Server Management Studio

1. SQL Command prompt within the Bash shell 

This situation is explained in the original Microsoft documentation.

Note: The Bash shell is available within the Linux container. Within a Windows container, a Windows prompt is available.

And for both situations, a ‘sqlcmd’ is aviable. You run it inside the shell.

I do not really like this situation, too much ‘inception’ for me, creating prompts inside prompts.

2. Running SqlCmd just on your host machine

Luckily for me, Microsoft also supports the ‘Microsoft® Command Line Utilities 14.0 for SQL Server®’ on my host Windows operating system also.

Just download and install this tool and restart a dos prompt to get access to the tool:

After that, we look at the configuration of the Docker container. Do you see, the internal Docker port is 1401?

Remember that internal port number!

Next, find out the IP address of your own gateway (your laptop?)

Finally, combine this IP address and the internal port (here 192.168.1.71,1401) to get access from your own dos prompt:

Here you see how a database is created, it works!

3. Use SQL Server Management Studio 17.7

If this is still a bit technical for you, you can go for option three: use the Management studio.

Download the version without an actual SQL Server, which we do not need because one is already running in Docker.

Note: this download is still 800+ MB.

Once downloaded and installed you can make access to the SQL Server running inside the Docker container:

Now access the database using ‘192.168.1.71,1401’ and the SA account.

Note: between the IP address and port is a comma add, not a colon character.

The application then shows the database created:

Conclusion

Using the Management Studio makes it so much more easy to add tables, indexes or even stored procedures.

I do not recommend to install this tool on your production gateway but this will really speed up development and testing.

Notice the minimal memory size limitation. The Azure IoT Edge module will not start unless you have more than 2GB of ram available in your device.

Advertentie

Een gedachte over “Administer your SQL Server in Docker

Reacties zijn gesloten.