Tutorial: Store data at the edge with SQL Server databases

Use Azure IoT Edge and SQL Server to store and query data at the edge. Azure IoT Edge has basic storage capabilities to cache messages if a device goes offline, and then forward them when the connection is reestablished. However, you may want more advanced storage capabilities, like being able to query data locally. By incorporating local databases, your IoT Edge devices can perform more complex computing without having to maintain a connection to IoT Hub. For example, a sensor on a machine uploads data to the cloud once a month for reporting and improving a machine learning module. However, if a field technician is working on the machine, they can access the last few days of sensor data locally.

This article provides instructions for deploying a SQL Server database to an IoT Edge device. Azure Functions, running on the IoT Edge device, structures the incoming data then sends it to the database. The steps in this article can also be applied to other databases that work in containers, like MySQL or PostgreSQL.

In this tutorial, you learn how to:

  • Use Visual Studio Code to create an Azure Function
  • Deploy a SQL database to your IoT Edge device
  • Use Visual Studio Code to build modules and deploy them to your IoT Edge device
  • View generated data

If you don't have an Azure subscription, create a free account before you begin.

Prerequisites

An Azure IoT Edge device:

  • You can use your development machine or a virtual machine as an Edge device by following the steps in the quickstart for Linux or Windows devices.

    Note

    SQL Server only supports Linux containers. If you want to test this tutorial by using a Windows device as your Edge device, you must configure it so that it uses Linux containers. See Install Azure IoT Edge runtime on Windows for the prerequisites and installation steps for configuring the IoT Edge runtime for Linux containers on Windows.

Cloud resources:

  • A free or standard-tier IoT Hub in Azure.

Development resources:

Create a container registry

In this tutorial, you use the Azure IoT Tools for Visual Studio Code to build a module and create a container image from the files. Then you push this image to a registry that stores and manages your images. Finally, you deploy your image from your registry to run on your IoT Edge device.

You can use any Docker-compatible registry to hold your container images. Two popular Docker registry services are Azure Container Registry and Docker Hub. This tutorial uses Azure Container Registry.

If you don't already have a container registry, follow these steps to create a new one in Azure:

  1. In the Azure portal, select Create a resource > Containers > Container Registry.

  2. Provide the following values to create your container registry:

    Field Value
    Registry name Provide a unique name.
    Subscription Select a subscription from the drop-down list.
    Resource group We recommend that you use the same resource group for all of the test resources that you create during the IoT Edge quickstarts and tutorials. For example, IoTEdgeResources.
    Location Choose a location close to you.
    Admin user Set to Enable.
    SKU Select Basic.
  3. Select Create.

  4. After your container registry is created, browse to it, and then select Access keys.

  5. Copy the values for Login server, Username, and Password. You use these values later in the tutorial to provide access to the container registry.

Create a function project

To send data into a database, you need a module that can structure the data properly and then stores it in a table.

The following steps show you how to create an IoT Edge function using Visual Studio Code and the Azure IoT Tools.

  1. Open Visual Studio Code.

  2. Open the VS Code command palette by selecting View > Command palette.

  3. In the command palette, type and run the command Azure IoT Edge: New IoT Edge solution. In the command palette, provide the following information to create your solution:

    Field Value
    Select folder Choose the location on your development machine for VS Code to create the solution files.
    Provide a solution name Enter a descriptive name for your solution, like SqlSolution, or accept the default.
    Select module template Choose Azure Functions - C#.
    Provide a module name Name your module sqlFunction.
    Provide Docker image repository for the module An image repository includes the name of your container registry and the name of your container image. Your container image is prepopulated from the last step. Replace localhost:5000 with the login server value from your Azure container registry. You can retrieve the login server from the Overview page of your container registry in the Azure portal. The final string looks like <registry name>.azurecr.io/sqlFunction.

    The VS Code window loads your IoT Edge solution workspace.

  4. In your IoT Edge solution, open the .env file.

    Whenever you create a new IoT Edge solution, VS Code prompts you to provide your registry credentials in the .env file. This file is git-ignored, and the IoT Edge extension uses it later to provide registry access to your IoT Edge device.

    If you didn't provide your container registry in the previous step but accepted the default localhost:5000, you won't have a .env file.

  5. In the .env file, give the IoT Edge runtime your registry credentials so that it can access your module images. Find the CONTAINER_REGISTRY_USERNAME and CONTAINER_REGISTRY_PASSWORD sections and insert your credentials after the equals symbol:

    CONTAINER_REGISTRY_USERNAME_yourregistry=<username>
    CONTAINER_REGISTRY_PASSWORD_yourregistry=<password>
    
  6. Save the .env file.

  7. In the VS Code explorer, open modules > sqlFunction > sqlFunction.cs.

  8. Replace the contents of the file with the following code:

    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Text;
    using System.Threading.Tasks;
    using Microsoft.Azure.Devices.Client;
    using Microsoft.Azure.WebJobs;
    using Microsoft.Azure.WebJobs.Extensions.EdgeHub;
    using Microsoft.Azure.WebJobs.Host;
    using Microsoft.Extensions.Logging;
    using Newtonsoft.Json;
    using Sql = System.Data.SqlClient;
    
    namespace Functions.Samples
    {
        public static class sqlFunction
        {
            [FunctionName("sqlFunction")]
            public static async Task FilterMessageAndSendMessage(
                [EdgeHubTrigger("input1")] Message messageReceived,
                [EdgeHub(OutputName = "output1")] IAsyncCollector<Message> output,
                ILogger logger)
            {
                const int temperatureThreshold = 20;
                byte[] messageBytes = messageReceived.GetBytes();
                var messageString = System.Text.Encoding.UTF8.GetString(messageBytes);
    
                if (!string.IsNullOrEmpty(messageString))
                {
                    logger.LogInformation("Info: Received one non-empty message");
                    // Get the body of the message and deserialize it.
                    var messageBody = JsonConvert.DeserializeObject<MessageBody>(messageString);
    
                    //Store the data in SQL db
                    const string str = "<sql connection string>";
                    using (Sql.SqlConnection conn = new Sql.SqlConnection(str))
                    {
                        conn.Open();
                        var insertMachineTemperature = "INSERT INTO MeasurementsDB.dbo.TemperatureMeasurements VALUES (CONVERT(DATETIME2,'" + messageBody.timeCreated + "', 127), 'machine', " + messageBody.machine.temperature + ");";
                        var insertAmbientTemperature = "INSERT INTO MeasurementsDB.dbo.TemperatureMeasurements VALUES (CONVERT(DATETIME2,'" + messageBody.timeCreated + "', 127), 'ambient', " + messageBody.ambient.temperature + ");"; 
                        using (Sql.SqlCommand cmd = new Sql.SqlCommand(insertMachineTemperature + "\n" + insertAmbientTemperature, conn))
                        {
                            //Execute the command and log the # rows affected.
                            var rows = await cmd.ExecuteNonQueryAsync();
                            logger.LogInformation($"{rows} rows were updated");
                        }
                    }
    
                    if (messageBody != null && messageBody.machine.temperature > temperatureThreshold)
                    {
                        // Send the message to the output as the temperature value is greater than the threashold.
                        var filteredMessage = new Message(messageBytes);
                        // Copy the properties of the original message into the new Message object.
                        foreach (KeyValuePair<string, string> prop in messageReceived.Properties)
                        {filteredMessage.Properties.Add(prop.Key, prop.Value);}
                        // Add a new property to the message to indicate it is an alert.
                        filteredMessage.Properties.Add("MessageType", "Alert");
                        // Send the message.       
                        await output.AddAsync(filteredMessage);
                        logger.LogInformation("Info: Received and transferred a message with temperature above the threshold");
                    }
                }
            }
        }
        //Define the expected schema for the body of incoming messages.
        class MessageBody
        {
            public Machine machine {get; set;}
            public Ambient ambient {get; set;}
            public string timeCreated {get; set;}
        }
        class Machine
        {
            public double temperature {get; set;}
            public double pressure {get; set;}         
        }
        class Ambient
        {
            public double temperature {get; set;}
            public int humidity {get; set;}         
        }
    }
    
  9. In line 35, replace the string <sql connection string> with the following string. The Data Source property references the SQL Server container name, which you create with the name SQL in the next section.

    Data Source=tcp:sql,1433;Initial Catalog=MeasurementsDB;User Id=SA;Password=Strong!Passw0rd;TrustServerCertificate=False;Connection Timeout=30;
    
  10. Save the sqlFunction.cs file.

  11. Open the sqlFunction.csproj file.

  12. Find the group of package references, and add a new one for SqlClient include.

    <PackageReference Include="System.Data.SqlClient" Version="4.5.1"/>
    
  13. Save the sqlFunction.csproj file.

Add a SQL Server container

A Deployment manifest declares which modules the IoT Edge runtime will install on your IoT Edge device. You provided the code to make a customized Function module in the previous section, but the SQL Server module is already built. You just need to tell the IoT Edge runtime to include it, then configure it on your device.

  1. In the Visual Studio Code explorer, open the deployment.template.json file.

  2. Find the modules section. There should be two modules listed: tempSensor, which generates simulated data, and your sqlFunction module.

  3. Add the following code to declare a third module. Add a comma after the sqlFunction section and insert:

    "sql": {
      "version": "1.0",
      "type": "docker",
      "status": "running",
      "restartPolicy": "always",
      "env":{},
      "settings": {
        "image": "",
        "createOptions": ""
      }
    }
    

    Add SQL server module to manifest

  4. Update the sql module parameters with the following code:

    "env": {
      "ACCEPT_EULA": {"value": "Y"},
      "SA_PASSWORD": {"value": "Strong!Passw0rd"}
    },
    "settings": {
      "image": "mcr.microsoft.com/mssql/server:latest",
      "createOptions": {
        "HostConfig": {
          "Mounts": [{"Target": "/var/opt/mssql","Source": "sqlVolume","Type": "volume"}],
          "PortBindings": {
            "1433/tcp": [{"HostPort": "1401"}]
          }
        }
      }
    }
    

    Tip

    Any time that you create a SQL Server container in a production environment, you should change the default system administrator password.

  5. Save the deployment.template.json file.

Build your IoT Edge solution

In the previous sections, you created a solution with one module, and then added another to the deployment manifest template. Now, you need to build the solution, create container images for the modules, and push the images to your container registry.

  1. Sign in to your container registry in Visual Studio Code so that you can push your images to your registry. Use the same credentials that you added to the .env file. Enter the following command in the integrated terminal:

    docker login -u <ACR username> <ACR login server>
    

    You are prompted for the password. Paste your password into the prompt (your password is hidden for security) and press Enter.

    Password: <paste in the ACR password and press enter>
    Login Succeeded
    
  2. In the VS Code explorer, right-click the deployment.template.json file and select Build and Push IoT Edge solution.

When you tell Visual Studio Code to build your solution, it first takes the information in the deployment template and generates a deployment.json file in a new folder named config. Then, it runs two commands in the integrated terminal: docker build and docker push. These two commands build your code, containerize the module, and then push the code to the container registry that you specified when you initialized the solution.

Deploy the solution to a device

You can set modules on a device through the IoT Hub, but you can also access your IoT Hub and devices through Visual Studio Code. In this section, you set up access to your IoT Hub then use VS Code to deploy your solution to your IoT Edge device.

  1. In the VS Code command palette, select Azure IoT Hub: Select IoT Hub.

  2. Follow the prompts to sign in to your Azure account.

  3. In the command palette, select your Azure subscription then select your IoT Hub.

  4. In the VS Code explorer, expand the Azure IoT Hub Devices section.

  5. Right-click on the device that you want to target with your deployment and select Create deployment for single device.

    Create deployment for single device

  6. In the file explorer, navigate to the config folder inside your solution and choose deployment.amd64. Click Select Edge deployment manifest.

If the deployment is successful, a confirmation message is printed in the VS Code output.

Refresh the status of your device in the Azure IoT Hub Devices section of VS Code. The new modules are listed and will start to report as running over the next few minutes as the containers are installed and started. You can also check to see that all the modules are up and running on your device. On your IoT Edge device, run the following command to see the status of the modules.

iotedge list

Create the SQL database

When you apply the deployment manifest to your device, you get three modules running. The tempSensor module generates simulated environment data. The sqlFunction module takes the data and formats it for a database. This section guides you through setting up the SQL database to store the temperature data.

Run the following commands on your IoT Edge device. These commands connect to the sql module running on your device and create a database and table to hold the temperature data being sent to it.

  1. In a command-line tool on your IoT Edge device, connect to your database.

    sudo docker exec -it sql bash
    
  2. Open the SQL command tool.

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Strong!Passw0rd'
    
  3. Create your database:

    CREATE DATABASE MeasurementsDB
    ON
    (NAME = MeasurementsDB, FILENAME = '/var/opt/mssql/measurementsdb.mdf')
    GO
    
  4. Define your table.

    CREATE TABLE MeasurementsDB.dbo.TemperatureMeasurements (measurementTime DATETIME2, location NVARCHAR(50), temperature FLOAT)
    GO
    

You can customize your SQL Server docker file to automatically set up your SQL Server to be deployed on multiple IoT Edge devices. For more information, see the Microsoft SQL Server container demo project.

View the local data

Once your table is created, the sqlFunction module starts storing data in a local SQL Server 2017 database on your IoT Edge device.

From inside the SQL command tool, run the following command to view your formatted table data:

SELECT * FROM MeasurementsDB.dbo.TemperatureMeasurements
GO

View contents of local database

Clean up resources

If you plan to continue to the next recommended article, you can keep the resources and configurations that you created and reuse them. You can also keep using the same IoT Edge device as a test device.

Otherwise, you can delete the local configurations and the Azure resources that you created in this article to avoid charges.

Delete Azure resources

Deleting Azure resources and resource groups is irreversible. Make sure that you don't accidentally delete the wrong resource group or resources. If you created the IoT hub inside an existing resource group that has resources that you want to keep, delete only the IoT hub resource itself, instead of deleting the resource group.

To delete the resources:

  1. Sign in to the Azure portal and select Resource groups.

  2. Select the name of the resource group that contains your IoT Edge test resources.

  3. Review the list of resources contained in your resource group. If you want to delete all of them, you can select Delete resource group. If you want to delete only some of them, you can click into each resource to delete them individually.

Delete local resources

If you want to remove the IoT Edge runtime and related resources from your device, use the appropriate commands for your device operating system.

Windows

Uninstall the IoT Edge runtime.

. {Invoke-WebRequest -useb aka.ms/iotedge-win} | Invoke-Expression; `
Uninstall-SecurityDaemon

When the IoT Edge runtime is removed, the containers that it created are stopped, but still exist on your device. View all containers.

docker ps -a

Delete the runtime containers that were created on your device.

docker rm -f edgeHub
docker rm -f edgeAgent

Delete any additional containers that were listed in the docker ps output by referring to the container names.

Linux

Remove the IoT Edge runtime.

sudo apt-get remove --purge iotedge

When the IoT Edge runtime is removed, the containers that it created are stopped, but still exist on your device. View all containers.

sudo docker ps -a

Delete the runtime containers that were created on your device.

docker rm -f edgeHub
docker rm -f edgeAgent

Delete any additional containers that were listed in the docker ps output by referring to the container names.

Remove the container runtime.

sudo apt-get remove --purge moby

Next steps

In this tutorial, you created an Azure Functions module that contains code to filter raw data generated by your IoT Edge device. When you're ready to build your own modules, you can learn more about how to Develop Azure Functions with Azure IoT Edge for Visual Studio Code.

Continue on to the next tutorials to learn about other ways that Azure IoT Edge can help you turn data into business insights at the edge.