Skip to main content

​
SQL Server Overview

The SQLSERVER route stores MQTT messages in Microsoft SQL Server databases. It supports Windows and SQL authentication, SSL connections, and enterprise features.
SQL Server is ideal for enterprise environments, especially those integrated with Microsoft ecosystems. It offers advanced features like Always On availability and native JSON support.

​
Basic Syntax

DEFINE ROUTE SensorDB WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "sqlserver.example.com"
        WITH PORT '1433'
        WITH DATABASE "IoTData"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreSensorReading
        WITH SOURCE_TOPIC "sensors/+/reading"
        WITH QUERY "INSERT INTO Readings (RecordedAt, SensorId, Value) VALUES (GETDATE(), '{sensor_id}', '{value.json}')"

​
Connection Configuration

​
SQL_CONFIG Parameters

​
SERVER
string
required
SQL Server hostname or IP address. Can include instance name (e.g., server\instance).
​
PORT
integer
SQL Server port. Default: 1433.
​
DATABASE
string
required
Target database name.
​
USERNAME
string
required
SQL Server username.
​
PASSWORD
string
required
SQL Server password.
​
USE_SSL
boolean
Enable SSL connection. Default: false.
​
TRUST_SERVER_CERTIFICATE
boolean
Trust server certificate without validation. Default: false.

​
Complete Examples

Store sensor data in SQL Server:
DEFINE ROUTE SensorStorage WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "sqlserver.example.com"
        WITH PORT '1433'
        WITH DATABASE "IoTData"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreReading
        WITH SOURCE_TOPIC "sensors/+/reading"
        WITH QUERY "INSERT INTO SensorReadings (Timestamp, SensorId, Value) VALUES (GETDATE(), '{sensor_id}', '{value.json}')"

​
Table Schema Examples

-- Basic sensor readings
CREATE TABLE SensorReadings (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Timestamp DATETIME2 DEFAULT GETDATE(),
    SensorId NVARCHAR(50),
    Value FLOAT
);

-- JSON storage (SQL Server 2016+)
CREATE TABLE Events (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Timestamp DATETIME2 DEFAULT GETDATE(),
    Topic NVARCHAR(255),
    Payload NVARCHAR(MAX)
);

​
Troubleshooting

  • Verify SERVER and PORT are correct
  • Check SQL Server is running and accepting TCP connections
  • Enable TCP/IP in SQL Server Configuration Manager
  • Verify firewall allows connections on port 1433
  • Verify USERNAME and PASSWORD are correct
  • Ensure SQL Server authentication is enabled (mixed mode)
  • Check user has permissions on the DATABASE
  • Set TRUST_SERVER_CERTIFICATE “true” for self-signed certs
  • Verify SSL is configured on SQL Server

​
Next Steps

MongoDB Route

Configure MongoDB document storage.

Storage Overview

Compare all storage options.