EMQX Platform

English

简体中文
日本語

English

简体中文
日本語

Appearance

Contact Us

Stream MQTT Data into Azure SQL Database ​

Azure SQL Database is a fully managed relational database service by Microsoft that provides high availability, security, and scalability for structured data. EMQX Platform’s integration with Azure SQL Database allows users to reliably store and manage MQTT data in a structured database, enabling real-time analytics, reporting, and downstream processing. This integration enables EMQX to act as a bridge between MQTT-enabled devices and Azure SQL Database, allowing seamless ingestion of IoT telemetry into relational tables, while also supporting easy integration with other Azure services such as Power BI, Azure Synapse, and Logic Apps for advanced analytics, visualization, and automated workflows.

This page provides a comprehensive overview of integrating EMQX with Azure SQL Database. It covers the creation of Azure SQL Database connectors, setting up rules, and testing them. Additionally, it demonstrates how to transmit simulated temperature and humidity data to EMQX Platform via the MQTT protocol and store this data in Azure Event Hubs through configured data integration.

How It Works ​

Azure SQL Database data integration is an out-of-the-box feature of EMQX Platform, combining EMQX’s real-time device connectivity and message processing capabilities with the structured storage and querying power of Azure SQL Database. Using EMQX’s built-in rule engine, MQTT messages can be transformed, enriched, and stored in Azure SQL Database without writing complex code.

The complete process works as follows:

  1. IoT Devices Publish Messages: Devices connect to EMQX via the MQTT protocol and publish telemetry, status updates, or sensor readings to specific topics. Each incoming message triggers the rule engine for further processing.
  2. Rule Engine Processes Messages: EMQX’s SQL-based rule engine evaluates incoming messages against defined rules. Messages can be filtered, transformed, or enriched with additional context (e.g., timestamps, device metadata) before being stored.
  3. Writing Data to Azure SQL Database: Based on the rule configuration, processed messages are inserted or updated into specific tables and columns in Azure SQL Database. SQL templates allow precise mapping of message fields to database columns, ensuring structured and consistent storage.
  4. Data Storage and Utilization: Once stored, IoT data in Azure SQL Database is immediately available for querying, reporting, or analytics. It can also integrate with other Azure services such as Power BI, Synapse Analytics, and Logic Apps for visualization, automated workflows, and advanced processing.

By leveraging this integration, organizations can build a fully managed IoT data pipeline that combines real-time data ingestion, structured storage, and seamless analytics, enabling rapid insights and operational intelligence from device telemetry.

Features and Advantages ​

The data integration between EMQX Platform and Azure SQL Database offers the following functionalities and benefits for your business:

Real-Time IoT Data Ingestion: EMQX can process and forward high-frequency MQTT messages directly into Azure SQL Database, enabling immediate storage and real-time availability for monitoring, analytics, or operational workflows.

Structured and Queryable Storage: Azure SQL Database stores incoming IoT data in relational tables with full support for schemas, constraints, and indexing. This structure allows for precise querying, reporting, and historical analysis, making it easier to derive actionable insights from device data.

Preprocessing and Data Transformation: EMQX’s SQL-based Rule Engine allows messages to be filtered, enriched, aggregated, or reformatted before they reach Azure SQL Database. This ensures that only relevant and well-structured data is stored, reducing downstream processing complexity.

Scalable and High-Performance: Both EMQX and Azure SQL Database support horizontal and vertical scaling. EMQX can handle millions of MQTT connections, while Azure SQL Database can automatically scale compute and storage resources to accommodate growing data volumes without compromising performance.

Before You Start ​

This section describes the preparations you need to complete before you start to create the Azure SQL Database data integration. You will need to create an Azure SQL Database instance (if you haven’t already), configure the SQL server and network access rules, and prepare the required database and tables for storing MQTT messages. In addition, make sure you have the connection information ready for later use in EMQX Cloud.

Prerequisites ​

Set Up Network ​

Before you start, you need to create a deployment (EMQX cluster) on the EMQX Platform and configure the network.

  • For Dedicated Flex deployment users: First, create a VPC Peering Connection. After establishing the peering connection, you can log in to the Platform Console via the internal network IP to access the target connector. Alternatively, set up a NAT Gateway to access the target connector through a public IP.
  • For BYOC (Bring Your Own Cloud) deployment users: Establish a peering connection between the VPC where BYOC is deployed and the VPC where the target connector is located. After creating the peering connection, you can access the target connector via the internal network IP. If you need to access resources via a public IP address, configure a NAT gateway for the VPC where BYOC is deployed in the public cloud console.

Set Up Azure SQL Database ​

This section covers creating an Azure SQL Database instance for storing MQTT messages. If you already have a database, you can skip this step and proceed to creating a private endpoint.

  1. In the Azure portal, navigate to Azure SQL | SQL databases service page, and select Create.
  2. On the Basics tab of the Create SQL Database form:
    • Subscription: Select your desired Azure Subscription.
    • Resource group: Select an existing Azure Resource group or select Create new.
    • Database name: Enter a name that will be used later when creating the connector. We will use emqx in this guide.
    • Server: Select Create new, and fill out the form:
      • Server name: Enter a name for the server.
      • Location: Choose the same region as your EMQX deployment.
      • Authentication method: Select Use SQL authentication.
      • Server admin login / Password: Enter a username and password that meet Azure requirements. Keep a record for later use when connecting and creating the connector.
  3. Configure the remaining options according to your needs.
  4. Select OK to complete server creation.
  5. Back to the Create SQL Database form, select Next: Networking to proceed.

Create a Private Endpoint ​

To enable secure network access to your Azure SQL Database, you need to create a private endpoint.

  • If you already have a database, go to Overview → Properties → Networking → Private → + Add private endpoint.
  • If you just created a database in the previous section, the + Add private endpoint button is available directly on the Networking tab.
  1. In the Create a private endpoint form, select the desired subscription, resource group, virtual network, and subnet for the private endpoint.
  2. In the DNS configuration, keep Integrate with private DNS zone set to Yes and leave the default Private DNS zone.
  3. Select OK. Once the private endpoint is created, its status will show as Succeeded.
  4. Record the IP address and FQDN from Private Endpoint → Settings → DNS configuration.

Create Database and Data Tables ​

This section describes how to connect to your Azure SQL Database and create a table for storing MQTT data. There are several ways to connect to the database (refer to this Azure official documentation for details). In this guide, we will demonstrate the process using the Query Editor in Azure portal.

  1. Follow this documentation to create a server-level IP firewall rule.
  2. On the SQL database Overview page, select Query editor (preview) from the left menu.
  3. On the sign-in screen, provide credentials to connect to the database.
  4. Run the following SQL command to create temp_hum table, and this table will be used for storing the temperature and humidity data reported by devices.
sql
 CREATE TABLE temp_hum(
   client_id VARCHAR(64) NULL,
   temp NVARCHAR(100) NULL,
   hum NVARCHAR(100) NULL,
   up_timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
 GO;

Create a Microsoft SQL Server Connector ​

Before creating data integration rules, you need to first create a Microsoft SQL Server connector to access the Azure SQL server.

  1. Navigate to your deployment and select Data Integration from the left-navigation menu.
  2. If it is the first time for you to create a connector, select Microsoft SQL Server under the Data Persistence category. If you have already created connectors, select New Connector and then select Microsoft SQL server under the Data Persistence category.
  3. Connector name: The system will automatically generate a connector name.
  4. Enter the connection information:
    • Server Host: IP address and port of the server (default port is 1433).
    • Database Name: Enter the database name you created (for example, emqx).
    • Username: Enter the username in the format username@FQDN. For example, if the username is sa and the FQDN is emqx-test.database.windows.net, enter [email protected].
    • Password: Enter the password you set for the SQL Server account.
    • SQL Server Driver Name: Enter ODBC Driver 17 for SQL Server, which is installed in EMQX Platform by default.
  5. Click the Test button. If the Microsoft SQL server service is accessible, a success prompt will be returned.
  6. Click the New button to complete the creation.

Create a Rule ​

Next, you need to create a rule to specify the data to be written and add corresponding actions in the rule to forward the processed data to Azure SQL Database.

  1. Click New Rule in Rules area or click the New Rule icon in the Actions column of the connector you just created.

  2. Enter the rule matching SQL statement in the SQL editor. The following SQL example reads the message reporting time up_timestamp, client ID, and message body (Payload) from messages sent to the temp_hum/emqx topic, extracting temperature and humidity.

    sql
     SELECT 
   timestamp as up_timestamp, 
   clientid as client_id, 
   payload.temp as temp,
   payload.hum as hum
 FROM
   "temp_hum/emqx"

    TIP

    If you are a beginner user, click SQL Examples and Try It Out to learn and test the SQL rule.

  3. Click Next to add an action.

  4. Select the connector you just created from the Connector dropdown box.

  5. Configure the SQL Template based on the feature to use. Note: This is a preprocessed SQL, so the fields should not be enclosed in quotation marks, and do not write a semicolon at the end of the statements:

    sql
     INSERT INTO temp_hum(client_id, temp, hum)
 VALUES (
   ${client_id},
   ${temp},
   ${hum}
 )

    If a placeholder variable is undefined in the SQL template, you can toggle the Undefined Vars as Null switch above the SQL template to define the rule engine behavior:

    • Disabled (default): The rule engine can insert the string undefined into the database.

    • Enabled: Allow the rule engine to insert NULL into the database when a variable is undefined.

      TIP

      If possible, this option should always be enabled; disabling the option is only used to ensure backward compatibility.

  6. Advanced Settings (Optional).

  7. Click the Confirm button to complete the rule creation.

  8. In the Successful new rule pop-up, click Back to Rules, thus completing the entire data integration configuration chain.

Test the Rule ​

You are recommended to use MQTTX to simulate temperature and humidity data reporting, but you can also use any other client.

  1. Use MQTTX to connect to the deployment and send messages to the following Topic.

    • topic: temp_hum/emqx

    • payload:

      json
      {
  "temp": "27.5",
  "hum": "41.8"
}
  2. Check if the message has been forwarded to Azure SQL Database.
  3. View operational data in the console. Click the rule ID in the rule list, and you can see the statistics of the rule and the statistics of all actions under this rule.