HiveMQ Enterprise Extension for Microsoft SQL Server
Microsoft SQL Server (MSSQL) is a relational database management system from Microsoft that is used across on-premise and cloud environments to store, manage, and analyze data.
Features
-
Forward MQTT messages from IoT devices that are connected to your HiveMQ MQTT broker to one or more Microsoft SQL Server databases.
-
Convert MQTT messages into Microsoft SQL Server rows with convenient insert statements.
Requirements
-
A running HiveMQ Professional or Enterprise Edition installation, version 4.33 or higher.
-
A running Microsoft SQL Server database system. Version 2019 or higher is recommended.
-
For production use, a valid HiveMQ Enterprise Extension for Microsoft SQL Server license.
If you do not provide a valid license, HiveMQ automatically uses a free trial license. Trial licenses for HiveMQ Enterprise Extensions are valid for 5 hours. For more license information or to request an extended evaluation license, contact HiveMQ sales. |
Installation
-
Place your HiveMQ Enterprise Extension for Microsoft SQL Server license file (.elic) in the license folder of your HiveMQ installation. (Skip this step if you are using a trial version of the extension).
└─ <HiveMQ folder> ├─ bin ├─ conf ├─ data ├─ extensions │ ├─ hivemq-microsoft-sql-server-extension │ └─ ... ├─ license ├─ log └─ ...
-
Before you enable the extension, you need to configure the extension to match your individual Microsoft SQL Server setup.
For your convenience, we provide an example configurationconf/examples/config.xml
that you can copy and modify as desired.
The includedconfig.xsd
file outlines the schema and elements that can be used in the XML configuration.
Your completed configuration file must be namedconfig.xml
and located inHIVEMQ_HOME/extensions/hivemq-microsoft-sql-server-extension/conf/config.xml
.
For detailed information on configuration options, see Configuration. -
To enable the HiveMQ Enterprise Extension for Microsoft SQL Server, locate the
hivemq-microsoft-sql-server-extension
folder in theextensions
directory of your HiveMQ installation and remove theDISABLED
file (if present).
To function properly, the HiveMQ Enterprise Extension for Microsoft SQL Server must be installed on all HiveMQ broker nodes in your HiveMQ cluster and the configuration file on each node must be identical. |
Configuration
The extension configuration is divided into two sections:
-
MSSQLs: Provides information about the MSSQL databases to which your HiveMQ broker connects.
-
MQTT to MSSQL Routes: Defines how MQTT messages are sent from your HiveMQ broker to the configured MSSQL databases.
Extension Configuration File
The config.xml
file for your Microsoft SQL Server extension must be located in the hivemq-microsoft-sql-server-extension/conf/
folder within the extensions
folder of your HiveMQ installation.
The extension uses a simple but powerful XML-based configuration.
The conf/examples/config.xml
file is a configuration example that has all the parameters you need to send MQTT messages from your HiveMQ MQTT broker to Microsoft SQL Server.
If you copy and reuse the conf/examples/config.xml file, be sure to move the file to /conf/config.xml before you enable your extension.
For more information, see Installation.
|
<hivemq-microsoft-sql-server-extension xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="config.xsd">
<mssqls>
<mssql>
<id>my-mssql-id</id>
<server-name>my.host.com</server-name>
<port>1433</port>
<database>my-database</database>
<authentication>
<basic>
<username>my-user</username>
<password>my-password</password>
</basic>
</authentication>
</mssql>
</mssqls>
<mqtt-to-mssql-routes>
<mqtt-to-mssql-route>
<id>my-mqtt-to-mssql-route-insert</id>
<mssql-id>my-mssql-id</mssql-id>
<mqtt-topic-filters>
<mqtt-topic-filter>#</mqtt-topic-filter>
</mqtt-topic-filters>
<processor>
<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>
</processor>
</mqtt-to-mssql-route>
</mqtt-to-mssql-routes>
</hivemq-microsoft-sql-server-extension>
MSSQL Databases Configuration
The <mssqls>
section of your configuration lists the MSSQL databases to which configured MQTT messages are routed.
You can define as many <mssql>
tags as your use case requires.
<mssqls>
<mssql>
<id>my-mssql-id</id>
<server-name>my.host.com</server-name>
<port>1433</port>
<database>my-database</database>
<authentication>
<basic>
<username>my-user</username>
<password>my-password</password>
</basic>
</authentication>
</mssql>
</mssqls>
<mssqls>
<mssql>
<id>my-mssql-id</id>
<server-name>my.host.com</server-name>
<port>1433</port>
<database>my-database</database>
<authentication>
<basic>
<username>my-user</username>
<password>my-password</password>
</basic>
</authentication>
<tls>
<trust-server-certificate>false</trust-server-certificate>
<truststore>
<path>/truststore.jks</path>
<password>changeme</password>
</truststore>
</tls>
</mssql>
</mssqls>
You can use Environment variables to map properties such as the MSSQL password. |
Parameter | Required | Type | Description |
---|---|---|---|
|
ID |
The ID of the MSSQL connection. This string can only contain lowercase alphanumeric characters, dashes, and underscores. |
|
|
String |
The server name of the MSSQL database. |
|
|
Integer |
The port of the MSSQL database. |
|
|
String |
The name of the MSSQL database. |
|
|
Complex |
Defines the type of authentication the extension uses for the MSSQL connection.
|
|
|
- |
Complex |
Optional TLS configuration to establish a secure connection to MSSQL. Currently, only TLS 1.2 is supported.
|
MQTT to MSSQL Routes
The <mqtt-to-mssql-routes>
section of your extension configuration defines how MQTT messages are sent from the HiveMQ broker to Microsoft SQL Server.
You can define as many <mqtt-to-mssql-route>
tags as your use case requires.
<mqtt-to-mssql-route>
<id>my-mqtt-to-mssql-route-insert</id>
<mssql-id>my-mssql-id</mssql-id>
<mqtt-topic-filters>
<mqtt-topic-filter>#</mqtt-topic-filter>
</mqtt-topic-filters>
<processor>
<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>
</processor>
</mqtt-to-mssql-route>
Parameter | Required | Type | Description |
---|---|---|---|
|
ID |
The ID of the |
|
|
IDREF |
The ID of the MSSQL database. |
|
|
- |
Boolean |
Optional setting that defines whether the selected |
|
String |
A list of one or more MQTT topic filters.
|
|
|
Complex |
Defines how HiveMQ converts MQTT messages into MSSQL rows.
|
Bulk copy does not support all data types and has additional known limitations. Although most of the limitations do not apply to the extension, verify that you are not using any data types listed as not supported. If an unsupported data type is detected, bulk copy is automatically disabled, resulting in lower performance. |
Insert Statements
Insert statements help you convert MQTT messages into MSSQL rows.
The insert statements provide the table name, target columns, and respective value placeholders. The extension fills the placeholders with MQTT PUBLISH properties and appends those to the batch of values to insert. Batching for insert statements is always enabled to optimize performance and throughput.
<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>
The following table lists all placeholders the HiveMQ MSSQL extension recognizes:
Name | Type | MSSQL recommend data type | Description |
---|---|---|---|
|
String |
NVARCHAR |
The topic of the MQTT PUBLISH. |
|
String |
NVARCHAR |
The payload of the MQTT PUBLISH as a UTF-8 string. |
|
String |
NVARCHAR |
The payload of the MQTT PUBLISH as a Base64 string. |
|
String |
NVARCHAR |
The QoS level of the MQTT PUBLISH (AT_MOST_ONCE, AT_LEAST_ONCE, EXACTLY_ONCE). |
|
boolean |
BIT |
The retain flag of the MQTT PUBLISH. |
|
int |
INT |
The packet ID of the MQTT PUBLISH. |
|
String |
NVARCHAR |
The payload format indicator of the MQTT PUBLISH. |
|
String |
NVARCHAR |
The response topic of the MQTT PUBLISH. |
|
String |
NVARCHAR |
The correlation data of the MQTT PUBLISH as a UTF-8 string. |
|
String |
NVARCHAR |
The correlation data of the MQTT PUBLISH as a Base64 string. |
|
List |
NVARCHAR |
The user properties of the MQTT PUBLISH as a JSON array string. |
|
String |
NVARCHAR |
The value of the user property of the MQTT PUBLISH with the matching property name. Example: |
|
long |
BIGINT |
The arrival timestamp of the PUBLISH message represented as a UNIX timestamp value in milliseconds.
|
|
The same information as |
Some properties in an MQTT PUBLISH message are optional. The number of placeholders the MSSQL extension fills varies based on the properties that are present in the MQTT PUBLISH message. |
Environment variables
HiveMQ offers placeholders that can be replaced with the content of environment variables when the configuration file is read. For many use cases, it can be beneficial or necessary to use environment variables to configure items such as ports and bind addresses on the system on which you run HiveMQ. For example, when you run HiveMQ in a containerized environment.
You can use ${YOUR_ENVVAR_NAME}
in the config.xml
file.
HiveMQ replaces the placeholder with the value of the specified environment variable during startup.
export MY_MSSQL_PASSWORD=my-password
<mssql>
<id>my-mssql-id</id>
<server-name>my.host.com</server-name>
<port>1433</port>
<database>my-database</database>
<authentication>
<basic>
<username>my-user</username>
<password>${MY_MSSQL_PASSWORD}</password>
</basic>
</authentication>
</mssql>
<mssql>
<id>my-mssql-id</id>
<server-name>my.host.com</server-name>
<port>1433</port>
<database>my-database</database>
<authentication>
<basic>
<username>my-user</username>
<password>my-password</password>
</basic>
</authentication>
</mssql>
Make sure that HiveMQ is started in the same context as your environment variables are set, otherwise HiveMQ will not be able to access them. |