HANDS-ON LAB: Emulating Devices for Azure IoT Hub with SQL Server

In this post, I will explain how you can easily simulate dozens (or hundreds) of IoT devices to test your Azure IoT Hub configuration. Simulating IoT devices with dynamic data can help you test your Azure IoT Hub configuration settings, along with downstream consumers such as Stream Analytics and Power BI reports. In order to facilitate the communication between SQL Server and an Azure IoT Hub, we will use Enzo Unified (http://www.enzounified.com) which abstracts the underlying cloud APIs, allowing native SQL commands to be executed against the Azure IoT Hub. With Enzo Unified, simulating dozens of IoT devices can be done with a simple T-SQL statement.

image

Pre-Requities

To emulate IoT devices, you will need a Microsoft Azure account, and have a desktop (or server) with the following technologies installed:

  • Windows 8 (or higher) with .NET 4.6 installed
  • SQL Server 2014 Express or higher
  • Enzo Unified 1.6 (please contact info@enzounified.com to obtain a download link and installation instructions)

We will use SQL Server to drive test cases (simulating devices) by running SQL scripts, and Enzo Unified as a bridge allowing the SQL scripts to send data to the Azure IoT Hub from SQL Server directly.

Configure Microsoft Azure Azure IoT Hub

Let's first configure our Azure IoT Hub.

image

  • Click on IoT Hub (under Internet of Things); another window will show up
  • Enter a name for the service (for example: IoTEnzoTest) - the name for your IoT Hub will be called YOUR_IOT_HUB_NAME
  • Specify a Resource Group name, such as EnzoTest
  • You will reuse this resource group later when creating a Streaming Analytics job
  • Change the pricing tier level to F1 if you can to minimize the cost of the Hub
  • Select the East US location
  • Check the Pin to Dashboard checkbox
  • Click Create

image

When the IoT Hub has been created (this make take a few minutes) click Settings on your IoT Hub page. Under Settings, select Shared Access Policies, and select the iothubowner policy; the Shared Access Keys will be displayed.
Save the Connection String Primary Key (you will use the entire connection string when configuring Enzo Unified later). The connection string to the IoT Hub looks like this:

HostName=YOUR_IOT_HUB_NAME.azure-devices.net;SharedAccessKeyName=iothubowner;SharedAccessKey=YOUR_SHARED_ACCESS_KEY

Configure Enzo Unified for the Azure IoT Hub

In this section, we will configure Enzo Unified to connect to the Azure IoT Hub service using SQL Server Management Studio (SSMS). Enzo Unified will serve as the bridge between SQL Server and the Azure IoT Hub environment by allowing SQL Server to send test messages to Azure.

  • Connect to Enzo Unified using SSMS
  • Explore some of the AzureIoTHub methods you have access to by running the built-in help command:

EXEC AzureIoTHub.help

image

One of the available methods is _configCreate; let's use this method to add your IoT connection string. To learn how to use this command, run the following statement:

EXEC AzureIoTHub._configCreate help

image

Let's create a configuration called "default" (replace the IoT Hub name and connection string with you values):

EXEC AzureIoTHub._configCreate 'default', 1, 'YOUR_IOT_HUB_NAME', 'YOUR_CONNECTION_STRING'

  • You are now ready to access the hub through Enzo Unified.
    • If you need to update the configuration settings, use the _configUpdate command (same parameters); then use the _configFlush command to apply the new settings. 
    • If you create multiple configuration settings, you can use the _configUse to switch the active configuration. 
    • To list all available configuration settings, use the _configList command.

Run the following command to confirm you can connect to the Azure IoT Hub; no records will be returned yet as we have not yet configured our monitoring environment.

EXEC AzureIoTHub.ListDevices

Each SQL Server (i.e. device) has its own Access Key into the IoT Hub; you do not need to remember this information as Enzo Unified will work from the device name (DEVLAP03-SQL2014 in my example).

Create Virtual IoT Devices

We are now ready to add virtual IoT Devices, so that the Azure IoT Hub can accept incoming data from your SQL Server. We will create a few virtual IoT devices for this test.

  • Connect to Enzo Unified using SSMS
  • Run the following command (replace YOUR_DEVICE_NAME with a friendly name for the SQL Server; I used 'DEVLAP03-SQL2014')
  • NOTE: The backslash '\' is not a valid character for a device name; you can use the dash '-' instead to specify a SQL Server instance name

EXEC AzureIoTHub.CreateDevice 'test1’

EXEC AzureIoTHub.CreateDevice 'test2'

EXEC AzureIoTHub.CreateDevice 'test3'

EXEC AzureIoTHub.CreateDevice 'test4'

EXEC AzureIoTHub.CreateDevice 'test5'

You should now see the devices when you run the ListDevices command:

image

Let's test our new IoT Device by sending a JSON document from SSMS (through Enzo Unified):

Run the following command:

EXEC bsc.AzureIoTHub.SendData 'test1', '{"deviceId":"test1", "location":"home", "messurementValue":700, "messurementType":"darkness","localTimestamp":"2016-4-14 16:35:00"}'

You will soon see the message count go up on your Microsoft Azure IoT Hub dashboard.

Simulating a Test From Multiple Virtual Devices

At this time we are ready to send multiple test messages on behalf of virtual devices to the Azure Hub. To achieve this, we will use the SendTestData method; this method allows you to send messages (with different values to simulate actual devices) from multiple devices. To obtain help on this method, you can run this command:

EXEC AzureIoTHub.SendTestData help

The following command sends at least 12 messages, with 100ms interval, using four virtual devices. One of the parameters of this method is the list of devices that will participate in the test. The message is built using the template provided, which uses these functions, so that every message sent has different data sets:

  • #deviceid() – the name of the device
  • #pick(home,work,car) – selects one of the values randomly
  • #rnddouble(a,b) – selects a double value randomly between [a,b[
  • #utcnow() – the current time in UTC

EXEC bsc.AzureIoTHub.SendTestData 
    12,
    100,
    'test1,test2,test3,test4',
    '{"deviceId":"#deviceid()", "location":"#pick(home,work,car)", "messurementValue":#rnddouble(400.0,700.0), "messurementType":"darkness","localTimestamp":"#utcnow()"}'

The above command will generate at least 12 messages and output the messages that were actually sent to the Azure IoT Hub.

image

Saving Output to SQL Server for Analysis

Last but not least, let’s create a similar test and save the output provided by the SendTestData method to a local table in SQL Server so that it can be analyzed later.  In order to do this, we will need to call Enzo Unified through a Linked Server. In order to configure Linked Server to Enzo Unified, follow the instructions provided in the help of Enzo Unified.

First, connect SSMS to your local SQL Server, and create a database with a table where the data will be stored.

IF (NOT EXISTS(SELECT * FROM master..sysdatabases WHERE name = 'mytestdb'))
CREATE DATABASE mytestdb
GO

IF (NOT EXISTS(SELECT * FROM mytestdb.sys.tables WHERE name = 'iotrawresults'))
  CREATE table mytestdb..iotrawresults (dateCreated datetime, id nvarchar(50), messageId nvarchar(50), data nvarchar(1024), props nvarchar(1024), durationms int)
GO

To save the output of the SendTestData method into the iotrawresults table previously created, you will run the following command:

INSERT INTO mytestdb..iotrawresults EXEC [localhost,9590].bsc.AzureIoTHub.SendTestData
    10,
    100,
    'test1,test2',
    '{"deviceId":"#deviceid()", "location":"#pick(home,work,car)", "messurementValue":#rnddouble(400.0,700.0), "messurementType":"darkness","localTimestamp":"#utcnow()"}'

We have created a simple way to simulate IoT devices and send random data to an Azure IoT Hub; to scale this test system you can improve this lab by adding the following items:

  • Build a SQL Server job to run the test on a schedule
  • Build multiple jobs/tests to increase the number of devices and send a different message mix

Conclusion

This lab introduces you to the Azure IoT Hub, Enzo Unified and its AzureHub adapter, and how to leverage SQL Server to create an ecosystem of virtual devices simulating data emission to the Microsoft Azure cloud.

About Herve Roggero

Herve Roggero, Microsoft Azure MVP, @hroggero, is the founder of Enzo Unified (http://www.enzounified.com/). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress, aPluralSight author, and runs the Azure Florida Association.

Print | posted @ Monday, July 11, 2016 4:44 PM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.