Ingest MQTT Data into Microsoft SQL Server

SQL Server is one of the leading relational commercial database solutions, widely used in enterprises and organizations of various sizes and types. EMQX supports integration with SQL Server, enabling you to save MQTT messages and client events to SQL Server. This facilitates the construction of complex data pipelines and analytical processes for data management and analysis, or for managing device connections and integrating with other enterprise systems such as ERP, CRM, and BI.

This page provides a detailed overview of the data integration between EMQX and Microsoft SQL Server with practical instructions on creating and validating the data integration.

TIP

The data integration with Microsoft SQL Server is supported in EMQX Enterprise 5.0.3 and above.

How It Works

Microsoft SQL Server data integration is an out-of-the-box feature in EMQX, combining EMQX's device connectivity and message transmission capabilities with the powerful data storage capabilities of Microsoft SQL Server. Through the built-in rule engine component and Sink, you can store MQTT messages and client events in Microsoft SQL Server. Additionally, events can trigger updates or deletions of data within Microsoft SQL Server, enabling the recording of information such as device online status and connection history. This integration simplifies the process of ingesting data from EMQX to SQL Server for storage and management, eliminating the need for complex coding.

The diagram below illustrates a typical architecture of data integration between EMQX and SQL Server:

EMQX Integration SQL Server

Ingesting MQTT data into Microsoft SQL Server works as follows:

Message publication and reception: Industrial IoT devices establish successful connections to EMQX through the MQTT protocol and publish real-time MQTT data from machines, sensors, and product lines based on their operational states, readings, or triggered events to EMQX. When EMQX receives these messages, it initiates the matching process within its rules engine.
Message data processing: When a message arrives, it passes through the rule engine and is then processed by the rule defined in EMQX. The rules, based on predefined criteria, determine which messages need to be routed to Microsoft SQL Server. If any rules specify payload transformations, those transformations are applied, such as converting data formats, filtering out specific information, or enriching the payload with additional context.
Data ingestion into SQL Server: The rule triggers the writing of messages to Microsoft SQL Server. With the help of SQL templates, users can extract data from the rule processing results to construct SQL and send it to SQL Server for execution, so that specific fields of the message can be written or updated into the corresponding tables and columns of the database.
Data Storage and Utilization: With the data now stored in Microsoft SQL Server, businesses can harness its querying power for various use cases.

Features and Benefits

The data integration with Microsoft SQL Server offers a range of features and benefits tailored to ensure efficient data transmission, storage, and utilization:

Real-time Data Streaming: EMQX is built for handling real-time data streams, ensuring efficient and reliable data transmission from source systems to Microsoft SQL Server. It enables organizations to capture and analyze data in real-time, making it ideal for use cases requiring immediate insights and actions.
High Performance and Scalability: Both EMQX and Microsoft SQL Server feature expandability and reliability, suitable for handling large-scale IoT data. They can undergo uninterrupted horizontal and vertical expansion as demands grow, ensuring the continuity and reliability of IoT applications.
Flexibility in Data Transformation: EMQX provides a powerful SQL-based Rule Engine, allowing organizations to pre-process data before storing it in Microsoft SQL Server. It supports various data transformation mechanisms, such as filtering, routing, aggregation, and enrichment, enabling organizations to shape the data according to their needs.
Advanced Analytics: Microsoft SQL Server offers powerful analytical capabilities, such as building multi-dimensional data models through Analysis Services to support complex data analysis and data mining. It also enables the creation and publication of reports through Reporting Services, presenting insights and analysis results of IoT data to stakeholders.

Before You Start

This section describes the preparations you need to complete before you start to create the Microsoft SQL Server data integration, including how to install and connect to the Microsoft SQL Server, create database and data tables, and install and configure the ODBC driver.

Prerequisites

Knowledge about EMQX data integration rules
Knowledge about data integration

Install and Connect to Microsoft SQL Server

This section describes how to start Microsoft SQL Server 2019 on Linux/MacOS using Docker images and use sqlcmd to connect to Microsoft SQL Server. For other installation methods of Microsoft SQL Server, please refer to Microsoft SQL Server Installation Guide.

Install Microsoft SQL Server via Docker, and then start the docker image with the command below. Use mqtt_public1 as the password. For the password policy of Microsoft SQL Server, see Password Complexity.
Note: By starting a Docker container with the environment variable ACCEPT_EULA=Y you agree to the terms of Microsoft EULA, see also MICROSOFT SOFTWARE LICENSE TERMS MICROSOFT SQL SERVER 2019 STANDARD(EN_US).
bash
```
# To start the Microsoft SQL Server docker image and set the password as `mqtt_public1`
$ docker run --name sqlserver -p 1433:1433 -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=mqtt_public1 -d mcr.microsoft.com/mssql/server:2019-CU19-ubuntu-20.04
```
Access the container.
bash
```
docker exec -it sqlserver bash
```
Enter the preset password to connect to the server in the container. The characters are not echoed when entering the password. Click Enter directly after entering the password.
bash
```
$ /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa
$ Password:
1>
```
TIP
The mssql-tools have been installed in the Microsoft SQL Server container provided by Microsoft, but the executable file is not in $PATH. Therefore, you need to specify the executable file path for mssql-tools before proceeding. As for the Docker deployment in this example, the file path should be opt.
For more information on how to use mssql-tools, see sqlcmd-utility.

So far, the Microsoft SQL Server 2019 instance has been deployed and can be connected.

Create Database and Data Tables

This section describes how to create a database and data table in Microsoft SQL Server.

Create a database mqtt in Microsoft SQL Server using the connection created from the previous section.

bash

...
Password:
1> USE master
2> GO
Changed database context to 'master'.
1> IF NOT EXISTS(SELECT name FROM sys.databases WHERE name = 'mqtt') BEGIN CREATE DATABASE mqtt END
2> GO

Use the following SQL statements to create a data table.

Create the following data table for storing the MQTT message, including the message ID, topic, QoS, payload, and publish time of each message.

sql

CREATE TABLE mqtt.dbo.t_mqtt_msg (id int PRIMARY KEY IDENTITY(1000000001,1) NOT NULL,
                                  msgid   VARCHAR(64) NULL,
                                  topic   VARCHAR(100) NULL,
                                  qos     tinyint NOT NULL DEFAULT 0,
                                  payload VARCHAR(100) NULL,
                                  arrived DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
GO

Create the following data table for recording the online/offline status of clients.

sql

CREATE TABLE mqtt.dbo.t_mqtt_events (id int PRIMARY KEY IDENTITY(1000000001,1) NOT NULL,
                                     clientid VARCHAR(255) NULL,
                                     event_type VARCHAR(255) NULL,
                                     event_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
GO

Install and Configure ODBC Driver

You need to configure the ODBC driver to be able to access the Microsoft SQL Server database. You can use either FreeTDS or the msodbcsql17 driver provided by Microsoft as the ODBC driver (The connection properties for msodbcsql18 have not been adapted yet).

EMQX uses the DSN Name specified in the odbcinst.ini configuration to determine the path to the driver dynamic library. In the examples below, the DSN Name is ms-sql. For more information, refer to Connection Properties.

Note:

You can choose your own DSN name according to your preference, but it is recommended to use only English letters. Additionally, the DSN Name is case-sensitive.

Install and Configure msodbcsql17 Driver as ODBC Driver

If you need to use msodbcsql17 driver as the ODBC driver, refer to the Microsoft instructions:

Restricted by Microsoft EULA terms, the Docker image provided by EMQX does not include the msodbcsql17 driver. To use it in Docker or Kubernetes, you need to create a new image installed with ODBC driver based on the image provided by EMQX-Enterprise to access the Microsoft SQL Server database. Using the new image means that you agree to the Microsoft SQL Server EULA.

Follow the instructions below to build a new image:

Get the corresponding EMQX Dockerfile. You can save the file locally.

The image version in this example is emqx/emqx-enterprise:5.0.3-alpha.2. You can build the image based on the EMQX-Enterprise version you need, or use the latest version image emqx/emqx-enterprise:latest.

docker

# FROM emqx/emqx-enterprise:latest
FROM emqx/emqx-enterprise:5.0.3-alpha.2

USER root

RUN apt-get update \
    && apt-get install -y gnupg2 curl apt-utils \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update \
    && ACCEPT_EULA=Y apt-get install -y msodbcsql17 unixodbc-dev \
    && sed -i 's/ODBC Driver 17 for SQL Server/ms-sql/g' /etc/odbcinst.ini \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/*

USER emqx

Build a new image using the command docker build -f=Dockerfile.msodbc -t emqx-enterprise-with-msodbc:5.0.3-alpha.2 .
After building, you can use docker image ls to obtain a list of local images. You can also upload or save the image for later use.

TIP

Check that the DSN Name in odbcinst.ini should be ms-sql if you install the msodbcsql17 driver using this example. You can change the DSN Name according to your needs.

Install and Configure FreeTDS as ODBC driver

This section introduces how to install and configure FreeTDS as an ODBC driver on some of the mainstream distributions.

Install and configure FreeTDS ODBC driver on MacOS:

bash

$ brew install unixodbc freetds
$ vim /usr/local/etc/odbcinst.ini
# add the following lines
[ms-sql]
Description = ODBC for FreeTDS
Driver      = /usr/local/lib/libtdsodbc.so
Setup       = /usr/local/lib/libtdsodbc.so
FileUsage   = 1

Install and configure FreeTDS ODBC driver on Centos:

bash

$ yum install unixODBC unixODBC-devel freetds freetds-devel perl-DBD-ODBC perl-local-lib
$ vim /etc/odbcinst.ini
# add the following lines
[ms-sql]
Description = ODBC for FreeTDS
Driver      = /usr/lib64/libtdsodbc.so
Setup       = /usr/lib64/libtdsS.so.2
Driver64    = /usr/lib64/libtdsodbc.so
Setup64     = /usr/lib64/libtdsS.so.2
FileUsage   = 1

Install and configure FreeTDS ODBC driver on Ubuntu (Take Ubuntu20.04 as an example, for other versions, please refer to the official ODBC documentation):

bash

$ apt-get install unixodbc unixodbc-dev tdsodbc freetds-bin freetds-common freetds-dev libdbd-odbc-perl liblocal-lib-perl
$ vim /etc/odbcinst.ini
# add the following lines
[ms-sql]
Description = ODBC for FreeTDS
Driver      = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup       = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage   = 1

Create a Rule for Microsoft SQL Server Sink

This section demonstrates how to create rules to specify the data to be saved into Microsoft SQL Server and to record client's online/offline status. It assumes that you run both EMQX and Microsoft SQL Server on the local machine. If you have Microsoft SQL Server and EMQX running remotely, adjust the settings accordingly.

Go to EMQX Dashboard, click Integration -> Rules.
Click Create on the top right corner of the page.
Enter my_rule as the rule ID, and set the rules in the SQL Editor based on the feature to use:
- To create a rule for message storage, input the following statement, which means the MQTT messages under topic t/# will be saved to Microsoft SQL Server.
  Note: If you want to specify your own SQL syntax, make sure that you have included all fields required by the Sink in the SELECT part.
  sql
```
SELECT
  *
FROM
  "t/#"
```
- To create a rule for online/offline status recording, input the following statement:
  sql
```
SELECT
  *,
  floor(timestamp / 1000) as s_shift,
  timestamp div 1000 as ms_shift
FROM
  "$events/client_connected", "$events/client_disconnected"
```
TIP
If you are a beginner user, click SQL Examples and Enable Test to learn and test the SQL rule.
Click the + Add Action button to define an action that will be triggered by the rule. With this action, EMQX sends the data processed by the rule to Microsoft SQL Server.
Select Microsoft SQL Server from the Type of Action dropdown list. Keep the Action dropdown with the default Create Action value. You can also select a Microsoft SQL Server Sink if you have created one. This demonstration will create a new Sink.
Enter a name for the Sink. The name should be a combination of upper/lower case letters and numbers.
Enter the connection information:
- Server Host: Enter 127.0.0.1:1433, or the URL if the Microsoft SQL Server is running remotely.
- Database Name: Enter mqtt.
- Username: Enter sa.
- Password: Enter the preset password mqtt_public1, or use the actual password.
- SQL Server Driver Name: Enter ms-sql, as the DSN Name configured in odbcinst.ini
Configure the SQL Template based on the feature to use:
- To configure the SQL template for message storage, use the following SQL statement:
  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 t_mqtt_msg(msgid, topic, qos, payload) values ( ${id}, ${topic}, ${qos}, ${payload} )
```
- To configure the SQL template for online/offline status recording, use the following SQL statement:
  sql
```
insert into t_mqtt_events(clientid, event_type, event_time) values ( ${clientid}, ${event}, DATEADD(MS, ${ms_shift}, DATEADD(S, ${s_shift}, '19700101 00:00:00:000') ) )
```
Advanced settings (optional): Choose whether to use sync or async query mode as needed. For details, see Features of Sink.
Before clicking Create, you can click Test Connectivity to test that the Sink can be connected to the Microsoft SQL Server.
Click the Create button to complete the Sink configuration. A new Sink will be added to the Action Outputs.
Back on the Create Rule page, verify the configured information. Click the Create button to generate the rule.

You have now successfully created the rule for the Microsoft SQL Server Sink. You can see the newly created rule on the Integration -> Rules page. Click the Actions(Sink) tab and you can see the new Microsoft SQL Server Sink.

You can also click Integration -> Flow Designer to view the topology and you can see that the messages under topic t/# are sent and saved to Microsoft SQL Server after parsing by rule my_rule.

Test the Rule

Use MQTT X to send a message to topic t/1 to trigger an online/offline event.

bash

mqttx pub -i emqx_c -t t/1 -m '{ "msg": "hello SQL Server" }'

Check the running statistics of the Microsoft SQL Server Sink.

For the Sink used to store messages, there should be one new matching and one new outgoing message. Check whether the data is written into the mqtt.dbo.t_mqtt_msg data table.

bash

1> SELECT * from mqtt.dbo.t_mqtt_msg
2> GO
id          msgid                                                            topic                                                                                                qos payload                                                                                              arrived
----------- ---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --- ---------------------------------------------------------------------------------------------------- -----------------------
 1000000001 0005F995096D9466F442000010520002                                 t/1                                                                                                    0 { "msg": "Hello SQL Server" }                                                                        2023-04-18 04:49:47.170

(1 rows affected)
1>

For the Sink used to record online/offline status, there should be two new events recorded: client connected and client disconnected. Check whether the status recording is written into the mqtt.dbo.t_mqtt_events data table.

bash

1> SELECT * from mqtt.dbo.t_mqtt_events
2> GO
id          clientid                                                         event_type                                                                                                                                                                                                    event_time
----------- ---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
 1000000001 emqx_c                                                           client.connected                                                                                                                                                                                              2023-04-18 04:49:47.140
 1000000002 emqx_c                                                           client.disconnected                                                                                                                                                                                           2023-04-18 04:49:47.180

(2 rows affected)
1>

Publish/Subscribe

Client SDK

REST API

Configure Load Balancer

Performance and Tuning

Network and TLS

Authentication

Password-Based Authentication

Authorization

Schema Registry

Configuration

EMQX Dashboard

Topics and subscription

Access Control

Data Integration

Single Sign-On (SSO)

Logs and Observability

Integrate with OpenTlelemetry

Plugin and Extension

MQTT over QUIC

File Transfer over MQTT

Multi-Protocol Gateway

JT/T 808 Gateway

Design and Implementation

Ingest MQTT Data into Microsoft SQL Server

How It Works

Features and Benefits

Before You Start

Prerequisites

Install and Connect to Microsoft SQL Server

Create Database and Data Tables

Install and Configure ODBC Driver

Install and Configure msodbcsql17 Driver as ODBC Driver

Install and Configure FreeTDS as ODBC driver

Create a Rule for Microsoft SQL Server Sink

Test the Rule

Password-Based Authentication

Topics and subscription

Access Control

Data Integration

Integrate with OpenTlelemetry

JT/T 808 Gateway

Ingest MQTT Data into Microsoft SQL Server ​

How It Works ​

Features and Benefits ​

Before You Start ​

Prerequisites ​

Install and Connect to Microsoft SQL Server ​

Create Database and Data Tables ​

Install and Configure ODBC Driver ​

Install and Configure msodbcsql17 Driver as ODBC Driver ​

Install and Configure FreeTDS as ODBC driver ​

Create a Rule for Microsoft SQL Server Sink ​

Test the Rule ​

Ingest MQTT Data into Microsoft SQL Server

How It Works

Features and Benefits

Before You Start

Prerequisites

Install and Connect to Microsoft SQL Server

Create Database and Data Tables

Install and Configure ODBC Driver

Install and Configure msodbcsql17 Driver as ODBC Driver

Install and Configure FreeTDS as ODBC driver

Create a Rule for Microsoft SQL Server Sink

Test the Rule