Seamlessly Store MQTT Data in Microsoft SQL Server and Azure SQL
Microsoft SQL Server and cloud-based Azure SQL are trusted database management systems designed to handle high volumes of data with enterprise-grade reliability. By forwarding MQTT messages directly to Microsoft SQL Server or Azure SQL, organizations can leverage SQL Server’s powerful querying capabilities to extract valuable insights from their IoT data in real-time.
HiveMQ just released the Enterprise Extension for Microsoft SQL Server, providing a powerful integration that allows users to forward MQTT data from IoT devices directly to Microsoft SQL Server and Azure SQL databases, eliminating the need for additional middleware. This post explores the features and benefits of the extension, setup requirements, and a sample configuration to get you started.
Why Integrate HiveMQ with Microsoft SQL Server?
With the HiveMQ Enterprise Extension for Microsoft SQL Server, IoT data becomes available in SQL Server in real-time, enabling businesses to leverage existing database infrastructure for streamlined data analytics and reporting.
Key benefits of the HiveMQ Enterprise Extension for Microsoft SQL Server include:
Real-time Analytics: Stream MQTT data directly into SQL Server or Azure SQL in real time, with data automatically ingested and stored in the database as it’s published to the HiveMQ broker, ready for immediate analysis. This integration empowers organizations to drive faster decision-making by making IoT and IIoT data instantly accessible for reporting and analytics.
Custom Data Mapping: Gain complete control over how MQTT data is mapped to SQL Server tables. Customize data structures and insert statements to match your unique requirements, ensuring that the MQTT messages fit seamlessly into your existing SQL schema and are ready for your use case.
Reliable and Scalable: Designed for enterprise-grade reliability, the extension scales effortlessly to accommodate high volumes of messages. It ensures data integrity even at scale, making it ideal for organizations that depend on SQL Server and Azure SQL for large-scale IoT deployments and need a solution that grows with their data.
Secure Data Transfer: End-to-end encryption guarantees that IoT data is securely transmitted from HiveMQ to SQL Server or Azure SQL. With support for SSL/TLS, the HiveMQ Enterprise Extension ensures that data flows remain secure from the source device all the way to the database, protecting sensitive information at every step.
This extension builds on HiveMQ’s plug-in architecture, which we discuss in more detail in our recent post, "Connector Framework vs. Plug-in Architecture in MQTT-Based IoT Architectures". Unlike connector frameworks, which can introduce latency and complexity, HiveMQ’s plug-in-based approach ensures that data is processed within the broker environment, minimizing delays and maximizing scalability.
Mapping MQTT Data to SQL Server Tables
The HiveMQ Enterprise Extension for Microsoft SQL Server allows you to map MQTT data directly to SQL Server tables using customizable insert statements. These statements define how incoming MQTT messages are transformed into SQL rows, making it easy to tailor data storage to your unique requirements.
With the extension, you can specify table names, enable bulk inserts, and map MQTT message properties—such as topics, payloads, and timestamps—to corresponding table columns. This flexibility is ideal for ensuring that your database schema aligns with your business needs and for maintaining data structure consistency across your IoT applications.
Here’s an example insert statement configuration:
<insert-statement>
<table>mqtt_to_mssql_table</table>
<use-bulk-copy>true</use-bulk-copy>
<columns>
<column>
<name>topic</name>
<value>mqtt-topic</value>
</column>
<column>
<name>payload_utf8</name>
<value>mqtt-payload-utf8</value>
</column>
</columns>
</insert-statement>In this example:
The
<table>tag specifies the target SQL table,mqtt_data_table.The
<use-bulk-copy>option is set totruefor efficient batch inserts, enabling the extension to handle high data volumes with optimized performance.Each
<column>tag maps a SQL table column (such astopic,payload_utf8, andtimestamp_ms) to an MQTT message property. The extension replaces placeholders with the actual MQTT message data, ensuring that each message is stored according to your configuration.
These insert statements make it easy to transform and load MQTT data directly into your Microsoft SQL Server database, enabling structured storage that’s ready for analysis and reporting. Whether you’re monitoring IoT sensors, collecting data from industrial devices, or tracking smart city infrastructure, the extension’s insert statement capabilities offer a flexible, powerful way to manage your MQTT data within SQL Server or Azure SQL.
Getting Started with the HiveMQ Enterprise Extension for Microsoft SQL Server
To get started, you’ll need:
A running HiveMQ Platform (version 4.33 or higher)
A Microsoft SQL Server or Azure SQL database (version 2019 or higher recommended)
A valid extension license (contact us for production use or you can start with a 5-hour trial version)
Once you have the required software, follow the steps in our documentation to install and configure the extension.
From leveraging Microsoft SQL Server’s robust analytics capabilities to track sensor data across industrial environments or smart city applications, to accessing real-time IoT metrics with Azure SQL integrations in Power BI, the extension empowers faster, data-driven decision-making.
Contact HiveMQ for a demo or download HiveMQ and try the HiveMQ Enterprise Extension.
HiveMQ Team
The HiveMQ team loves writing about MQTT, Sparkplug, Industrial IoT, protocols, how to deploy our platform, and more. We focus on industries ranging from energy, to transportation and logistics, to automotive manufacturing. Our experts are here to help, contact us with any questions.
