教程:使用 SQL Server 数据库存储边缘中的数据Tutorial: Store data at the edge with SQL Server databases

部署 SQL Server 模块,以便在运行 Azure IoT Edge 的 Linux 设备上存储数据。Deploy a SQL Server module to store data on a Linux device running Azure IoT Edge.

使用 Azure IoT Edge 和 SQL Server 在边缘存储和查询数据。Use Azure IoT Edge and SQL Server to store and query data at the edge. Azure IoT 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. IoT Edge 设备可以使用本地数据库来执行更复杂的计算,而不需要与 IoT 中心保持连接。Your IoT Edge devices can use local databases to perform more complex computing without having to maintain a connection to IoT Hub.

本文提供有关将 SQL Server 数据库部署到 IoT Edge 设备的说明。This article provides instructions for deploying a SQL Server database to an IoT Edge device. 在 IoT Edge 设备上运行的 Azure Functions 可将传入的数据结构化,然后将其发送到数据库。Azure Functions, running on the IoT Edge device, structures the incoming data then sends it to the database. 本文中的步骤也适用于在容器中运行的其他数据库,例如 MySQL 或 PostgreSQL。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:

  • 使用 Visual Studio Code 创建 Azure FunctionsUse Visual Studio Code to create an Azure Function
  • 将 SQL 数据库部署到 IoT Edge 设备Deploy a SQL database to your IoT Edge device
  • 使用 Visual Studio Code 生成模块并将其部署到 IoT Edge 设备Use Visual Studio Code to build modules and deploy them to your IoT Edge device
  • 查看生成的数据View generated data

如果没有 Azure 订阅,可在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.


在开始学习本教程之前,应已完成上一篇教程来设置用于开发 Linux 容器的开发环境:开发适用于 Linux 设备的 IoT Edge 模块Before beginning this tutorial, you should have gone through the previous tutorial to set up your development environment for Linux container development: Develop IoT Edge modules for Linux devices. 完成该教程后,已应准备好以下必备组件:By completing that tutorial, you should have the following prerequisites in place:

本教程使用 Azure Functions 模块将数据发送到 SQL Server。This tutorial uses an Azure Functions module to send data to the SQL Server. 若要通过 Azure Functions 开发 IoT Edge 模块,请在开发计算机上安装下述额外的必备组件:To develop an IoT Edge module with Azure Functions, install the following additional prerequisites on your development machine:

创建函数项目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.

创建新项目Create a new project

以下步骤将介绍如何使用 Visual Studio Code 和 Azure IoT 工具来创建 IoT Edge 函数。The following steps show you how to create an IoT Edge function using Visual Studio Code and the Azure IoT Tools.

  1. 打开 Visual Studio Code。Open Visual Studio Code.

  2. 打开 VS Code 命令面板,方法是选择“视图” > “命令面板”。Open the VS Code command palette by selecting View > Command palette.

  3. 在命令面板中,键入并运行 Azure IoT Edge:New IoT Edge solution 命令。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 ValueValue
    选择文件夹Select folder 在适用于 VS Code 的开发计算机上选择用于创建解决方案文件的位置。Choose the location on your development machine for VS Code to create the solution files.
    提供解决方案名称Provide a solution name 输入解决方案的描述性名称(例如 SqlSolution),或者接受默认名称。Enter a descriptive name for your solution, like SqlSolution, or accept the default.
    选择模块模板Select module template 选择“Azure Functions - C#”。Choose Azure Functions - C#.
    提供模块名称Provide a module name 将模块命名为 sqlFunctionName your module sqlFunction.
    为模块提供 Docker 映像存储库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. 将 localhost:5000 替换为 Azure 容器注册表中的“登录服务器”值 。Replace localhost:5000 with the Login server value from your Azure container registry. 可以在 Azure 门户的容器注册表的“概述”页中检索登录服务器。You can retrieve the Login server from the Overview page of your container registry in the Azure portal.

    最终字符串类似于 <registry name> azurecr.cn/sqlfunction。The final string looks like <registry name>.azurecr.cn/sqlfunction.

    VS Code 窗口将加载你的 IoT Edge 解决方案空间。The VS Code window loads your IoT Edge solution workspace.

添加注册表凭据Add your registry credentials

环境文件存储容器注册表的凭据,并将其与 IoT Edge 运行时共享。The environment file stores the credentials for your container registry and shares them with the IoT Edge runtime. 此运行时需要这些凭据才能将专用映像拉取到 IoT Edge 设备中。The runtime needs these credentials to pull your private images onto the IoT Edge device.

IoT Edge 扩展尝试从 Azure 中拉取容器注册表凭据并将其填充到环境文件中。The IoT Edge extension tries to pull your container registry credentials from Azure and populate them in the environment file. 检查凭据是否已包含在内。Check to see if your credentials are already included. 如果没有,请立即添加:If not, add them now:

  1. 在 VS Code 资源管理器中,打开 .env 文件。In the VS Code explorer, open the .env file.
  2. 使用从 Azure 容器注册表复制的 usernamepassword 值更新相关字段。Update the fields with the username and password values that you copied from your Azure container registry.
  3. 保存此文件。Save this file.

选择目标体系结构Select your target architecture

需要选择面向每个解决方案的体系结构,因为每种体系结构类型的容器的生成和运行方式均不相同。You need to select which architecture you're targeting with each solution, because the container is built and run differently for each architecture type. 默认值为 Linux AMD64。The default is Linux AMD64.

  1. 打开命令面板并搜索 Azure IoT Edge:Set Default Target Platform for Edge Solution,或选择窗口底部侧栏中的快捷方式图标。Open the command palette and search for Azure IoT Edge: Set Default Target Platform for Edge Solution, or select the shortcut icon in the side bar at the bottom of the window.

  2. 在命令面板中,从选项列表中选择目标体系结构。In the command palette, select the target architecture from the list of options. 在本教程中,我们使用 Ubuntu 虚拟机作为 IoT Edge 设备,因此将保留默认的“amd64” 。For this tutorial, we're using an Ubuntu virtual machine as the IoT Edge device, so will keep the default amd64.

使用自定义代码更新模块Update the module with custom code

  1. 在 VS Code 资源管理器中,打开“modules” > “sqlFunction” > “sqlFunction.csproj”。In the VS Code explorer, open modules > sqlFunction > sqlFunction.csproj.

  2. 找到包引用所在的组,并添加一个新组来包含 SqlClient。Find the group of package references, and add a new one to include SqlClient.

    <PackageReference Include="System.Data.SqlClient" Version="4.5.1"/>
  3. 保存 sqlFunction.csproj 文件。Save the sqlFunction.csproj file.

  4. 打开 sqlFunction.cs 文件。Open the sqlFunction.cs file.

  5. 将文件的全部内容替换为以下代码:Replace the entire 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
            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))
                        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.
                        using (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;}
  6. 在第 35 行中,将字符串 <sql connection string> 替换为以下字符串。In line 35, replace the string <sql connection string> with the following string. Data Source 属性引用 SQL Server 容器,该容器尚不存在。The Data Source property references the SQL Server container, which doesn't exist yet. 在下一部分,我们将使用名称 SQL 来创建该容器。You will create it 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;
  7. 保存 sqlFunction.cs 文件。Save the sqlFunction.cs file.

添加 SQL Server 容器Add the SQL Server container

部署清单声明将要由 IoT Edge 运行时安装在 IoT Edge 设备上的具体模块。A Deployment manifest declares which modules the IoT Edge runtime will install on your IoT Edge device. 在上一部分提供的代码可生成自定义的函数模块,但是,Azure 市场中已生成并提供了 SQL Server 模块。You provided the code to make a customized Function module in the previous section, but the SQL Server module is already built and available in the Azure Marketplace. 只需要求 IoT Edge 运行时包括它,然后在设备上配置它即可。You just need to tell the IoT Edge runtime to include it, then configure it on your device.

  1. 在 Visual Studio Code 中,选择“视图” > “命令面板”打开命令面板。 In Visual Studio Code, open the command palette by selecting View > Command palette.

  2. 在命令面板中,键入并运行 Azure IoT Edge: Add IoT Edge module”。In the command palette, type and run the command Azure IoT Edge: Add IoT Edge module. 在命令面板中,提供以下信息以添加新模块:In the command palette, provide the following information to add a new module:

    字段Field Value
    选择部署模板文件Select deployment template file 命令面板会突出显示当前解决方案文件夹中的 deployment.template.json 文件。The command palette highlights the deployment.template.json file in your current solution folder. 选择该文件。Select that file.
    选择模块模板Select module template 选择“Azure 市场中的模块”。Select Module from Azure Marketplace.
  3. 在 Azure IoT Edge 模块市场中,搜索并选择“SQL Server 模块”。In the Azure IoT Edge module marketplace, search for and select SQL Server Module.

  4. 将模块名称更改为 sql(全小写)。Change the module name to sql, all lowercase. 此名称与 sqlFunction.cs 文件中的连接字符串内声明的容器名称相匹配。This name matches the container name declared in the connection string in the sqlFunction.cs file.

  5. 选择“导入”,将模块添加到解决方案。Select Import to add the module to your solution.

  6. 在解决方案文件夹中,打开 deployment.template.json 文件。In your solution folder, open the deployment.template.json file.

  7. 找到 modules 节。Find the modules section. 应会看到三个模块。You should see three modules. 模块 SimulatedTemperatureSensor 默认已包含在新解决方案中,并提供与其他模块配合使用的测试数据。The module SimulatedTemperatureSensor is included by default in new solutions, and provides test data to use with your other modules. 模块 sqlFunction 是最初使用新代码创建和更新的模块。The module sqlFunction is the module that you initially created and updated with new code. 最后,模块 sql 是从 Azure 市场导入的。Finally, the module sql was imported from the Azure Marketplace.


    在部署清单的环境变量中,为 SQL Server 模块设置了默认密码。The SQL Server module comes with a default password set in the environment variables of the deployment manifest. 每当在生产环境中创建 SQL Server 容器时,都应该更改默认的系统管理员密码Any time that you create a SQL Server container in a production environment, you should change the default system administrator password.

  8. 关闭 deployment.template.json 文件。Close the deployment.template.json file.

生成 IoT Edge 解决方案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. SQL Server 模块由 Microsoft 公开托管,但你需要容器化 Functions 模块中的代码。The SQL Server module is hosted publicly by Microsoft, but you need to containerize the code in the Functions module. 在本部分,你将生成解决方案,创建 sqlFunction 模块的容器映像,然后将映像推送到容器注册表。In this section, you build the solution, create container images for the sqlFunction module, and push the image to your container registry.

  1. 在 Visual Studio Code 中选择“视图” > “终端”,打开集成终端 。In Visual Studio Code, open the integrated terminal by selecting View > Terminal.

  2. 在 Visual Studio Code 中登录到你的容器注册表,以便将映像推送到你的注册表。Sign in to your container registry in Visual Studio Code so that you can push your images to your registry. 使用已添加到 .env 文件中的相同 Azure 容器注册表 (ACR) 凭据。Use the same Azure Container Registry (ACR) credentials that you added to the .env file. 在集成终端中输入以下命令:Enter the following command in the integrated terminal:

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

    此时可能会出现一条安全警告,建议使用 --password-stdin 参数。You might see a security warning recommending the use of the --password-stdin parameter. 虽然本文中未介绍它的用法,但我们建议按照此最佳做法进行操作。While its use is outside the scope of this article, we recommend following this best practice. 有关详细信息,请参阅 docker login 命令参考。For more information, see the docker login command reference.

  3. 在 VS Code 资源管理器中右键单击“deployment.template.json”文件,然后选择“生成并推送 IoT Edge 解决方案”。 In the VS Code explorer, right-click the deployment.template.json file and select Build and Push IoT Edge solution.

    “生成并推送”命令会启动三项操作。The build and push command starts three operations. 首先,它在解决方案中创建名为 config 的新文件夹,用于保存基于部署模板和其他解决方案文件中的信息生成的完整部署清单。First, it creates a new folder in the solution called config that holds the full deployment manifest, which is built out of information in the deployment template and other solution files. 其次,它会运行 docker build,以基于目标体系结构的相应 dockerfile 生成容器映像。Second, it runs docker build to build the container image based on the appropriate dockerfile for your target architecture. 然后,它会运行 docker push,以将映像存储库推送到容器注册表。Then, it runs docker push to push the image repository to your container registry.

    首次执行此过程可能需要几分钟时间,但下次运行命令时速度会变快。This process may take several minutes the first time, but is faster the next time that you run the commands.

    可以验证 sqlFunction 模块是否已成功推送到容器注册表。You can verify that the sqlFunction module was successfully pushed to your container registry. 在 Azure 门户中,导航到容器注册表。In the Azure portal, navigate to your container registry. 选择“存储库”并搜索 sqlFunctionSelect repositories and search for sqlFunction. 另外两个模块 SimulatedTemperatureSensor 和 sql 不会推送到容器注册表,因为其存储库已经在 Microsoft 注册表中。The other two modules, SimulatedTemperatureSensor and sql, won't be pushed to your container registry because their repositories are already in the Microsoft registries.

将解决方案部署到设备Deploy the solution to a device

可以通过 IoT 中心设置设备上的模块,但是也可以通过 Visual Studio Code 访问 IoT 中心和设备。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. 在此部分,请先设置对 IoT 中心的访问权限,然后使用 VS Code 将解决方案部署到 IoT Edge 设备。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. 在 Visual Studio Code 资源管理器中的“Azure IoT 中心”部分下,展开“设备”可查看 IoT 设备的列表。 In the Visual Studio Code explorer, under the Azure IoT Hub section, expand Devices to see your list of IoT devices.

  2. 右键单击要将其作为部署目标的设备,然后选择“为单个设备创建部署”。Right-click on the device that you want to target with your deployment and select Create Deployment for Single Device.

  3. 选择 config 文件夹中的 deployment.amd64.json 文件,然后单击“选择 Edge 部署清单”。 Select the deployment.amd64.json file in the config folder and then click Select Edge Deployment Manifest. 不要使用 deployment.template.json 文件。Do not use the deployment.template.json file.

  4. 在设备下,展开“模块”可查看已部署的正在运行的模块的列表。Under your device, expand Modules to see a list of deployed and running modules. 单击“刷新”按钮。Click the refresh button. 此时应看到新的 sql 和 sqlFunction 模块与 SimulatedTemperatureSensor 模块以及 $edgeAgent 和 $edgeHub 一起运行 。You should see the new sql and sqlFunction modules running along with the SimulatedTemperatureSensor module and the $edgeAgent and $edgeHub.

    也可查看设备上的所有模块是否都已启动并运行。You can also check to see that all the modules are up and running on your device. 在 IoT Edge 设备上运行以下命令,以便查看模块的状态。On your IoT Edge device, run the following command to see the status of the modules.

    iotedge list

    启动模块可能需要数分钟时间。It may take a few minutes for the modules to start. IoT Edge 运行时需要接收其新的部署清单,从容器运行时下拉模块映像,然后启动每个新模块。The IoT Edge runtime needs to receive its new deployment manifest, pull down the module images from the container runtime, then start each new module.

创建 SQL 数据库Create the SQL database

对设备应用部署清单时,会运行三个模块。When you apply the deployment manifest to your device, you get three modules running. SimulatedTemperatureSensor 模块生成模拟环境数据。The SimulatedTemperatureSensor module generates simulated environment data. sqlFunction 模块会提取数据并针对数据库设置其格式。The sqlFunction module takes the data and formats it for a database. 本部分介绍如何设置用于存储温度数据的 SQL 数据库。This section guides you through setting up the SQL database to store the temperature data.

在 IoT Edge 设备上运行以下命令。Run the following commands on your IoT Edge device. 这些命令连接到在你的设备上运行的 sql 模块,并创建数据库和表来存放发送到它的温度数据。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. 在 IoT Edge 设备上的命令行工具中,连接到你的数据库。In a command-line tool on your IoT Edge device, connect to your database.

    sudo docker exec -it sql bash
  2. 打开 SQL 命令工具。Open the SQL command tool.

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

    CREATE DATABASE MeasurementsDB
    (NAME = MeasurementsDB, FILENAME = '/var/opt/mssql/measurementsdb.mdf')
  4. 定义表。Define your table.

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

可以自定义 SQL Server docker 文件,以自动设置要在多个 IoT Edge 设备上部署的 SQL Server。You can customize your SQL Server docker file to automatically set up your SQL Server to be deployed on multiple IoT Edge devices. 有关详细信息,请参阅 Microsoft SQL Server 容器演示项目For more information, see the Microsoft SQL Server container demo project.

查看本地数据View the local data

表创建好以后,sqlFunction 模块就会开始在 IoT Edge 设备的本地 SQL Server 2017 数据库中存储数据。Once your table is created, the sqlFunction module starts storing data in a local SQL Server 2017 database on your IoT Edge device.

在 SQL 命令工具中运行以下命令,以便查看格式化的表数据:From inside the SQL command tool, run the following command to view your formatted table data:

SELECT * FROM MeasurementsDB.dbo.TemperatureMeasurements


清理资源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. 还可以继续使用相同的 IoT Edge 设备作为测试设备。You can also keep using the same IoT Edge device as a test device.

否则,可以删除本文中创建的本地配置和 Azure 资源,以避免收费。Otherwise, you can delete the local configurations and the Azure resources that you created in this article to avoid charges.

删除 Azure 资源Delete Azure resources

删除 Azure 资源和资源组的操作不可逆。Deleting Azure resources and resource groups is irreversible. 请确保不要意外删除错误的资源组或资源。Make sure that you don't accidentally delete the wrong resource group or resources. 如果在现有的包含要保留资源的资源组中创建了 IoT 中心,请只删除 IoT 中心资源本身,而不要删除资源组。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. 登录到 Azure 门户,然后选择“资源组”。 Sign in to the Azure portal and select Resource groups.

  2. 选择包含 IoT Edge 测试资源的资源组的名称。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.

后续步骤Next steps

本教程介绍了如何创建 Azure Functions 模块,其中包含用于筛选 IoT Edge 设备生成的原始数据的代码。In this tutorial, you created an Azure Functions module that contains code to filter raw data generated by your IoT Edge device. 做好生成自己的模块的准备以后,即可详细了解如何使用用于 Visual Studio Code 的 Azure IoT Edge 开发 Azure FunctionsWhen 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.

若要在边缘上尝试另一存储方法,请了解如何在 IoT Edge 上使用 Azure Blob 存储。If you want to try another storage method at the edge, read about how to use Azure Blob Storage on IoT Edge.