教程：使用 SQL Server 数据库在边缘存储数据

2024-07-08

适用于： IoT Edge 1.4

重要

支持 IoT Edge 1.5 LTS 和 IoT Edge 1.4 版本。如果你使用的是较低的版本，请参阅更新 IoT Edge。

部署 SQL Server 模块，以在运行具有 Linux 容器的 Azure IoT Edge 的设备上存储数据。

使用 Azure IoT Edge 和 SQL Server 在边缘存储和查询数据。 Azure IoT Edge 具有在设备脱机时缓存消息的基本存储功能，然后在重新建立连接时转发消息。但是，你可能想要更高级的存储功能，例如能够在本地查询数据。 IoT Edge 设备可以使用本地数据库来执行更复杂的计算，而无需维护与 IoT 中心的连接。

本文提供有关将 SQL Server 数据库部署到 IoT Edge 设备的说明。在 IoT Edge 设备上运行的 Azure Functions 会构造传入数据，然后将其发送到数据库。本文中的步骤也可以应用于在容器中工作的其他数据库，例如 MySQL 或 PostgreSQL。

本教程中，您将学习如何：

使用 Visual Studio Code 创建 Azure 函数
将 SQL 数据库部署到 IoT Edge 设备
使用 Visual Studio Code 生成模块并将其部署到 IoT Edge 设备
查看生成的数据

如果没有 Azure 帐户，请在开始前创建一个免费帐户。

先决条件

在开始本教程之前，应该先完成上一教程，为 Linux 容器开发设置开发环境：使用 Visual Studio Code 开发 Azure IoT Edge 模块。完成该教程后，应具备以下先决条件：

Azure 中的免费或标准层 IoT 中心。
使用 Linux 容器运行 Azure IoT Edge 的 AMD64 设备。可以使用快速入门设置 Linux 设备或 Windows 设备。
- ARM 设备（如 Raspberry Pis）无法运行 SQL Server。如果要在 ARM 设备上使用 SQL，可以使用 Azure SQL Edge。
容器注册表，如 Azure 容器注册表。
使用 Azure IoT Edge 和 Azure IoT 中心扩展配置的 Visual Studio Code。 适用于 Visual Studio Code 扩展的 Azure IoT Edge 工具处于维护模式。
在开发计算机上下载并安装 Docker 兼容的容器管理系统。将其配置为运行 Linux 容器。

本教程使用 Azure Functions 模块将数据发送到 SQL Server。若要使用 Azure Functions 开发 IoT Edge 模块，请在开发计算机上安装以下附加先决条件：

创建函数项目

若要将数据发送到数据库，需要一个模块，该模块可以正确构造数据，然后将其存储在表中。

创建新项目

以下步骤演示如何使用 Visual Studio Code 和 Azure IoT Edge 扩展创建 IoT Edge 函数。

打开 Visual Studio Code。
通过选择“查看>命令”面板打开 Visual Studio Code命令面板。

在命令面板中，键入并运行 Azure IoT Edge：新的 IoT Edge 解决方案命令。在命令面板中，提供以下信息来创建解决方案：

领域	价值
选择文件夹	选择 Visual Studio Code 开发计算机上的位置以创建解决方案文件。
提供解决方案名称	输入解决方案的描述性名称（如 SqlSolution）或接受默认值。
选择模块模板	选择 Azure Functions - C#。
提供模块名称	将模块命名为 sqlFunction。
为模块提供 Docker 映像存储库	映像存储库包括容器注册表的名称和容器映像的名称。容器映像是从上一步预填充的。将 localhost：5000 替换为 Azure 容器注册表中的登录服务器值。可以从 Azure 门户中容器注册表的“概述”页检索登录服务器。最终字符串类似于 <注册表名称>.azurecr.io/sqlfunction。

Visual Studio Code 窗口加载 IoT Edge 解决方案工作区。

添加注册表凭据

环境文件存储容器注册表的凭据，并与 IoT Edge 运行时共享它们。运行时需要这些凭据才能将专用映像拉取到 IoT Edge 设备。

IoT Edge 扩展尝试从 Azure 拉取容器注册表凭据，并在环境文件中填充这些凭据。检查凭据是否已包含。如果没有，请立即添加它们：

在 Visual Studio Code 资源管理器中，打开 .env 文件。
使用从 Azure 容器注册表复制的用户名和密码值更新字段。
保存此文件。

注释

本教程使用 Azure 容器注册表的管理员登录凭据，这对于开发和测试方案来说很方便。准备好用于生产方案时，我们建议使用最低特权身份验证选项，例如服务主体。有关详细信息，请参阅管理对容器注册表的访问权限。

选择目标体系结构

需要为每个解决方案选择面向的体系结构，因为容器是为每个体系结构类型构建和运行不同的。默认值为 Linux AMD64。

打开命令面板并搜索 Azure IoT Edge：设置 Edge 解决方案的默认目标平台，或选择窗口底部侧栏中的快捷图标。
在命令面板中，从选项列表中选择目标体系结构。在本教程中，我们将 Ubuntu 虚拟机用作 IoT Edge 设备，因此将保留默认 amd64。

使用自定义代码更新模块

在 Visual Studio Code 资源管理器中，打开sqlFunction>sqlFunction.csproj模块>。

查找包引用组，并添加新包以包含 SqlClient。

<PackageReference Include="System.Data.SqlClient" Version="4.5.1"/>

保存 sqlFunction.csproj 文件。
打开 sqlFunction.cs 文件。

将文件的全部内容替换为以下代码：

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 threshold.
                    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;}
    }
}

在第 35 行中，将字符串 <sql 连接字符串> 替换为以下字符串。 数据源属性引用尚不存在的 SQL Server 容器。在下一部分中，你将使用名称 SQL 创建它。为 Password 关键字选择强密码。
```
Data Source=tcp:sql,1433;Initial Catalog=MeasurementsDB;User Id=SA;Password=<YOUR-STRONG-PASSWORD>;TrustServerCertificate=False;Connection Timeout=30;
```
保存 sqlFunction.cs 文件。

添加 SQL Server 容器

部署清单声明 IoT Edge 运行时将在 IoT Edge 设备上安装的模块。你提供了用于在上一部分进行自定义函数模块的代码，但 SQL Server 模块已在Microsoft项目注册表中生成并可用。只需告知 IoT Edge 运行时包含它，然后在设备上对其进行配置。

在 Visual Studio Code 中，通过选择 “查看>命令”面板打开命令面板。

在命令面板中，键入并运行 Azure IoT Edge 命令：添加 IoT Edge 模块。在命令面板中，提供以下信息来添加新模块：

领域	价值
选择部署模板文件	命令面板突出显示当前解决方案文件夹中 deployment.template.json 文件。选择该文件。
选择模块模板	选择现有模块（输入完整图像 URL）。
提供模块名称	输入 sql。此名称与 sqlFunction.cs 文件中连接字符串中声明的容器名称匹配。
为模块提供 Docker 映像	输入以下 URI，从Microsoft项目注册表中拉取 SQL Server 容器映像。对于基于 Ubuntu 的映像，请使用 `mcr.microsoft.com/mssql/server:latest`。对于基于 Red Hat Enterprise Linux（RHEL）的映像，请使用 `mcr.microsoft.com/mssql/rhel/server:latest`。

Azure SQL Edge 容器映像是可在 IoT Edge 设备上运行的轻型容器化 SQL Server 版本。它针对边缘方案进行了优化，可在 ARM 和 AMD64 设备上运行。

在解决方案文件夹中，打开 deployment.template.json 文件。
查找 “模块 ”部分。应会看到三个模块。 模块 SimulatedTemperatureSensor 默认包含在新解决方案中，并提供用于其他模块的测试数据。模块 sqlFunction 是最初使用新代码创建和更新的模块。最后，模块 sql 已从Microsoft项目注册表导入。

小窍门

SQL Server 模块附带部署清单环境变量中的默认密码集。在生产环境中创建 SQL Server 容器时，应更改默认的系统管理员密码。
关闭 deployment.template.json 文件。

生成 IoT Edge 解决方案

在前面的部分中，你创建了一个包含一个模块的解决方案，然后将另一个模块添加到部署清单模板。 SQL Server 模块由Microsoft公开托管，但需要在 Functions 模块中容器化代码。在本部分中，你将生成解决方案，为 sqlFunction 模块创建容器映像，并将映像推送到容器注册表。

在 Visual Studio Code 中，通过选择“查看>终端”打开集成终端。
在 Visual Studio Code 中登录到容器注册表，以便可以将映像推送到注册表。使用添加到 .env 文件的同一 Azure 容器注册表（ACR）凭据。在集成终端中输入以下命令：
```
docker login -u <ACR username> -p <ACR password> <ACR login server>
```
你可能会看到建议使用 --password-stdin 参数的安全警告。虽然本文的使用超出了本文的范围，但建议遵循此最佳做法。有关详细信息，请参阅 docker 登录命令参考。
在 Visual Studio Code 资源管理器中，右键单击 deployment.template.json 文件，然后选择 “生成和推送 IoT Edge 解决方案”。

生成和推送命令启动三个作。首先，它会在名为 config 的解决方案中创建一个新文件夹，用于保存完整部署清单，该清单是在部署模板和其他解决方案文件中的信息生成的。其次，它运行 docker build 以基于目标体系结构的相应 dockerfile 生成容器映像。然后，它将运行 docker push 以将映像存储库推送到容器注册表。

此过程可能需要几分钟时间，但在下次运行命令时速度更快。

可以验证 sqlFunction 模块是否已成功推送到容器注册表。在 Azure 门户中，导航到容器注册表。选择 存储库 并搜索 sqlFunction。其他两个模块 SimulatedTemperatureSensor 和 sql 不会推送到容器注册表，因为它们的存储库已在Microsoft注册表中。

将解决方案部署到设备

可以通过 IoT 中心在设备上设置模块，但也可以通过 Visual Studio Code 访问 IoT 中心和设备。在本部分中，你将设置对 IoT 中心的访问权限，然后使用 Visual Studio Code 将解决方案部署到 IoT Edge 设备。

在 Visual Studio Code 资源管理器的 “Azure IoT 中心 ”部分下，展开 “设备” 以查看 IoT 设备列表。
右键单击要针对部署的设备，然后选择 “为单个设备创建部署”。
在配置文件夹中选择 deployment.amd64.json 文件，然后单击“选择边缘部署清单”。请勿使用 deployment.template.json 文件。
在设备下，展开 “模块 ”以查看已部署和正在运行的模块的列表。单击“刷新”按钮。应会看到与 SimulatedTemperatureSensor 模块以及$edgeAgent和$edgeHub一起运行的新 sql 和 sqlFunction 模块。

还可以检查设备中的所有模块是否已启动并运行。在 IoT Edge 设备上，运行以下命令以查看模块的状态。
```
iotedge list
```
模块启动可能需要几分钟时间。 IoT Edge 运行时需要接收其新的部署清单，从容器运行时拉取模块映像，然后启动每个新模块。

创建 SQL 数据库

将部署清单应用到设备时，将运行三个模块。 SimulatedTemperatureSensor 模块生成模拟环境数据。 sqlFunction 模块获取数据并将其格式化为数据库。本部分将指导你设置 SQL 数据库以存储温度数据。

在 IoT Edge 设备上运行以下命令。这些命令连接到设备上运行的 sql 模块，并创建一个数据库和表来保存要发送到它的温度数据。将 YOUR-STRONG-PASSWORD> 替换为<连接字符串中选择的强密码。

在 IoT Edge 设备上的命令行工具中，连接到数据库。
```
sudo docker exec -it sql bash
```

打开 SQL 命令工具。

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YOUR-STRONG-PASSWORD>'

创建数据库：

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

定义表。

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

可以自定义 SQL Server docker 文件，以自动将 SQL Server 设置为部署在多个 IoT Edge 设备上。有关详细信息，请参阅 Microsoft SQL Server 容器演示项目。

查看本地数据

创建表后，sqlFunction 模块将开始将数据存储在 IoT Edge 设备上的本地 SQL Server 2017 数据库中。

从 SQL 命令工具内部运行以下命令以查看格式化表数据：

SELECT * FROM MeasurementsDB.dbo.TemperatureMeasurements
GO

查看本地数据库的内容

清理资源

如果打算继续阅读下一篇建议的文章，可以保留创建的资源和配置并重复使用它们。还可以继续使用与测试设备相同的 IoT Edge 设备。

否则，可以删除本文中创建的本地配置和 Azure 资源，以避免产生费用。

删除 Azure 资源

删除 Azure 资源和资源组不可逆。请确保不要意外删除错误的资源组或资源。如果在具有要保留的资源的现有资源组内创建了 IoT 中心，请仅删除 IoT 中心资源本身，而不是资源组。

若要删除资源，请执行以下操作：

登录到 Azure 门户，然后选择 “资源组”。
选择包含 IoT Edge 测试资源的资源组的名称。
查看资源组中包含的资源列表。若要删除这一切，可以选择“删除资源组”。如果只想删除其中一些资源，可以单击每个资源以单独删除它们。

在本教程中，你创建了一个 Azure Functions 模块，其中包含用于筛选 IoT Edge 设备生成的原始数据的代码。准备好生成自己的模块时，可以详细了解如何使用 Visual Studio Code 开发 Azure IoT Edge 模块。

后续步骤

若要尝试边缘的其他存储方法，请阅读有关如何在 IoT Edge 上使用 Azure Blob 存储的信息。

使用 IoT Edge 上的 Azure Blob 存储在边缘存储数据

通过