Set up IoT Edge modules and connections

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.

1. Navigate to the IoT hub that was created in your resource group.

2. In the IoT Edge section under Automatic Device Management, select Device ID. For this tutorial, the ID is IronOrePredictionDevice.

3. Select the Set Modules section.

4. Under Container Registry Credentials, enter the following values:

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

Build, push, and deploy the Data Generator Module

1. Clone the project files to your machine.

2. Open the file IronOre_Silica_Predict.sln using Visual Studio 2019

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

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

4. Update the modules.json file to specify the target container registry (or repository for the module)

   "image": {
        "repository":"samplerepo.azurecr.io/ironoresilicapercent",
        "tag":
   }
   

5. Execute the project in either debug or release mode to ensure the project runs without any issues

6. Push the project to your container registry by right-clicking the project name and then selecting Build and Push IoT Edge Modules.

7. Deploy the Data Generator module as an IoT Edge module to your Edge device.

Deploy the Azure SQL Edge module

1. Deploy the Azure SQL Edge module by selecting on + Add and then IoT Edge Module.

2. Set the configuration options in Add IoT Edge Modules page to deploy Azure SQL Edge module. For more information on the configuration options, see Deploy Azure SQL Edge.

3. 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.

4. Select update

5. On the Set modules on device page, select Next: Routes >.

6. 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")
   

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

8. On the Set modules on device page, select Create

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

1. Open Azure Data Studio.

2. 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

3. Select Connect.

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

5. 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';
   

6. 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.

Related content

• Deploy ML model on Azure SQL Edge using ONNX