Set up IoT Edge modules and connections

Article
09/18/2023

Important

Azure SQL Edge no longer supports the ARM64 platform.

In part two of this three-part tutorial for predicting iron ore impurities in Azure SQL Edge, you'll set up the following IoT Edge modules:

Azure SQL Edge
Data generator IoT Edge module

Specify container registry credentials

The credentials to the container registries hosting module images need to be specified. These credentials can be found in the container registry that was created in your resource group. Navigate to the Access Keys section. Make note of the following fields:

Registry name
Login server
Username
Password

Now, specify the container credentials in the IoT Edge module.

Navigate to the IoT hub that was created in your resource group.
In the IoT Edge section under Automatic Device Management, select Device ID. For this tutorial, the ID is IronOrePredictionDevice.
Select the Set Modules section.
Under Container Registry Credentials, enter the following values:

Field Value

Name Registry name

Address Login server

User Name Username

Password Password

Field	Value
Name	Registry name
Address	Login server
User Name	Username
Password	Password

Build, push, and deploy the Data Generator Module

Clone the project files to your machine.
Open the file IronOre_Silica_Predict.sln using Visual Studio 2019

Update the container registry details in the deployment.template.json

"registryCredentials": {
     "RegistryName": {
         "username": "",
         "password": "",
         "address": ""
     }
}

Update the modules.json file to specify the target container registry (or repository for the module)
```
"image": {
     "repository":"samplerepo.azurecr.io/ironoresilicapercent",
     "tag":
}
```
Execute the project in either debug or release mode to ensure the project runs without any issues
Push the project to your container registry by right-clicking the project name and then selecting Build and Push IoT Edge Modules.
Deploy the Data Generator module as an IoT Edge module to your Edge device.

Deploy the Azure SQL Edge module

Deploy the Azure SQL Edge module by selecting on + Add and then Marketplace Module.
On the IoT Edge Module Marketplace pane, search for Azure SQL Edge and pick Azure SQL Edge Developer.
Select the newly added Azure SQL Edge module under IoT Edge Modules to configure the Azure SQL Edge module. For more information on the configuration options, see Deploy Azure SQL Edge.
Add the MSSQL_PACKAGE environment variable to the Azure SQL Edge module deployment, and specify the SAS URL of the database dacpac file created in step 8 of Part one of this tutorial.
Select update
On the Set modules on device page, select Next: Routes >.

On the routes pane of the Set modules on device page, specify the routes for module to IoT Edge hub communication as described below. Make sure to update the module names in the following route definitions.

FROM /messages/modules/<your_data_generator_module>/outputs/IronOreMeasures
INTO BrokeredEndpoint("/modules/<your_azure_sql_edge_module>/inputs/IronOreMeasures")

For example:

FROM /messages/modules/ASEDataGenerator/outputs/IronOreMeasures
INTO BrokeredEndpoint("/modules/AzureSQLEdge/inputs/IronOreMeasures")

On the Set modules on device page, select Next: Review + create >
On the Set modules on device page, select Create

Create and start the T-SQL Streaming Job in Azure SQL Edge.

Open Azure Data Studio.

In the Welcome tab, start a new connection with the following details:

Field	Value
Connection type	Microsoft SQL Server
Server	Public IP address mentioned in the VM that was created for this demo
Username	sa
Password	The strong password that was used while creating the Azure SQL Edge instance
Database	Default
Server group	Default
Name (optional)	Provide an optional name

Select Connect.
In the File menu tab, open a new notebook or use the keyboard shortcut Ctrl + N.

In the new Query window, execute the script below to create the T-SQL Streaming job. Before executing the script, make sure to change the following variables:

@SQL_SA_Password: The MSSQL_SA_PASSWORD value specified while deploying the Azure SQL Edge Module.

USE IronOreSilicaPrediction;
GO

DECLARE @SQL_SA_Password VARCHAR(200) = '<SQL_SA_Password>';
DECLARE @query VARCHAR(MAX);

/* Create objects required for streaming */

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStr0ng3stP@ssw0rd';

IF NOT EXISTS (
        SELECT name
        FROM sys.external_file_formats
        WHERE name = 'JSONFormat'
        )
BEGIN
    CREATE EXTERNAL FILE FORMAT [JSONFormat]
        WITH (FORMAT_TYPE = JSON)
END

IF NOT EXISTS (
        SELECT name
        FROM sys.external_data_sources
        WHERE name = 'EdgeHub'
        )
BEGIN
    CREATE EXTERNAL DATA SOURCE [EdgeHub]
        WITH (LOCATION = N'edgehub://')
END

IF NOT EXISTS (
        SELECT name
        FROM sys.external_streams
        WHERE name = 'IronOreInput'
        )
BEGIN
    CREATE EXTERNAL STREAM IronOreInput
        WITH (
                DATA_SOURCE = EdgeHub,
                FILE_FORMAT = JSONFormat,
                LOCATION = N'IronOreMeasures'
                )
END

IF NOT EXISTS (
        SELECT name
        FROM sys.database_scoped_credentials
        WHERE name = 'SQLCredential'
        )
BEGIN
    SET @query = 'CREATE DATABASE SCOPED CREDENTIAL SQLCredential
                 WITH IDENTITY = ''sa'', SECRET = ''' + @SQL_SA_Password + ''''

    EXECUTE (@query)
END

IF NOT EXISTS (
        SELECT name
        FROM sys.external_data_sources
        WHERE name = 'LocalSQLOutput'
        )
BEGIN
    CREATE EXTERNAL DATA SOURCE LocalSQLOutput
        WITH (
                LOCATION = 'sqlserver://tcp:.,1433',
                CREDENTIAL = SQLCredential
                )
END

IF NOT EXISTS (
        SELECT name
        FROM sys.external_streams
        WHERE name = 'IronOreOutput'
        )
BEGIN
    CREATE EXTERNAL STREAM IronOreOutput
        WITH (
                DATA_SOURCE = LocalSQLOutput,
                LOCATION = N'IronOreSilicaPrediction.dbo.IronOreMeasurements'
                )
END

EXEC sys.sp_create_streaming_job @name = N'IronOreData',
    @statement = N'Select * INTO IronOreOutput from IronOreInput';

EXEC sys.sp_start_streaming_job @name = N'IronOreData';

Use the following query to verify that the data from the data generation module is being streamed into the database.
```
SELECT TOP 10 *
FROM dbo.IronOreMeasurements
ORDER BY timestamp DESC;
```

In this tutorial, we deployed the data generator module and the SQL Edge module. Then we created a streaming job to stream the data generated by the data generation module to SQL.

Next steps

Deploy ML model on Azure SQL Edge using ONNX