# Stream MQTT Data into Azure SQL Database [Azure SQL Database](https://azure.microsoft.com/en-us/products/azure-sql/database) is a fully managed relational database service by Microsoft that provides high availability, security, and scalability for structured data. EMQX Cloud’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 Cloud 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 Cloud, 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](https://docs.emqx.com/en/cloud/latest/data_integration/rules.html), 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 Cloud 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 - Understand [rules](https://docs.emqx.com/en/cloud/latest/data_integration/rules.html). - Understand [data integration](https://docs.emqx.com/en/cloud/latest/data_integration/introduction.html). ### Set Up Network ### 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. 4. 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](https://learn.microsoft.com/en-us/azure/azure-sql/database/design-first-database-tutorial?view=azuresql&tabs=queryeditor) for details). In this guide, we will demonstrate the process using the **Query Editor** in Azure portal. 1. Follow [this documentation](https://learn.microsoft.com/en-us/azure/azure-sql/database/design-first-database-tutorial?view=azuresql&tabs=queryeditor#create-a-server-level-ip-firewall-rule) 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 `sa@emqx-test.database.windows.net`. - **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 Cloud 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](https://mqttx.app/) 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.