Microsoft SQL Server

9 min

The Microsoft SQL Server connector writes Litmus Edge (LE) DeviceHub messages into a table in a Microsoft SQL Server database, including Azure SQL. The connector writes rows in batches, either as multi-row INSERT statements or with TDS bulk copy.

The connector also supports Execute Command for ad-hoc SQL queries.

Supported communication

Outbound (write data from LE to the database): Yes

Inbound (read data from the database to LE): No

Execute Command (ad-hoc SQL query): Yes

Compatibility

The connector supports the following products. Other products and versions might work.

Product	Version
Microsoft SQL Server	2017 and later. Validated on 2019
Azure SQL Database	-
Azure SQL Managed Instance	-

Connector configuration

Field	Type	Description	Required	Default
Name	String	Name of this connector instance.	Yes	-
Hostname	String	DNS name or IP address of the SQL Server host. The host must be reachable from the LE system. Length is 3 to 255 characters.	Yes	-
Port	Integer	Port of the SQL Server host. Range is 1 to 65535.	Yes	1433
Encrypt	Dropdown	TLS encryption mode: disable, false, true, or strict.	No	false
CA Certificate	String (Secret)	CA certificate that the connector uses to validate the server. You can also add it to the system-wide certificate list. Applies only when Encrypt is true or strict.	No	-
Username	String	Database user that the connector authenticates as.	Yes	-
Password	String (Secret)	Password for the database user.	No	-
Database	String	Name of the target database.	Yes	-
Schema	String	Schema that holds the target table.	No	dbo
Table	String	Target table that the connector writes into. The table must exist unless you enable Create table.	Yes	-
Mapping	Object	Key-value pairs that map payload data to the columns of your table. The key is a column in your table. The value is a payload field name such as deviceId, the matching default-schema column name such as device_id, or static text with an @ prefix, such as @plant-a. Leave it empty to use the default table schema. Not available with Bulk insert mode set to bulkcopy.	No	-
Enable JSONPath	Boolean	Read Mapping values that start with $ as JSONPath expressions, such as $.factory.line.machine.sensors.temperature. Values that don't start with $ keep their usual meaning, and an expression that matches nothing writes NULL. Not available with Bulk insert mode set to bulkcopy.	No	Disabled
Create table	Boolean	Create the target table with the default table schema if it doesn't exist. The database user needs permission to create a table.	Yes	Disabled
Commit timeout	Integer	Maximum time in milliseconds that one batch insert transaction can take before the connector treats it as failed and retries it. Range is 10 to 65535.	No	30000
Batch timeout	Integer	Maximum time in milliseconds after the previous commit attempt before the connector writes a batch that isn't full. Range is 10 to 65535.	No	500
Max transaction size	Integer	Maximum number of messages in one batch insert. The connector writes a full batch without waiting for Batch timeout. Range is 1 to 65535.	No	500
Bulk insert count	Integer	Number of messages the connector buffers before it writes a batch. A value of 0 uses Max transaction size. Range is 0 to 65535.	No	0
Bulk insert mode	Dropdown	How the connector inserts rows. values uses a multi-row INSERT statement. bulkcopy uses TDS bulk copy (BCP) for higher throughput, and requires the default table schema with no Mapping.	No	values
Write mode	Dropdown	When a message counts as delivered: sync or async.	No	sync
Parallel transactions	Integer	Maximum number of batch inserts that run at the same time, each on its own database connection. Values above 1 increase throughput, but the connector can then write rows out of publish order. Range is 1 to 64.	No	1
Parallel Publish Count	Integer	Number of messages the connector publishes at the same time. In sync write mode, this value limits how many rows a batch can collect. Range is 1 to 4000.	No	1000

Additional attributes

Field	Type	Description	Required	Default
Throttling limit	Integer	Maximum outbound messages per second. A value of 0 disables throttling. Minimum is 0.	No	0
Persistent storage	Boolean	Store outbound queue messages on disk. If the connection drops or the system loses power, the messages are retained and sent once the connection is restored.	No	Enabled
Persistent storage size limit	Integer	Maximum disk space in GB used for persisted data. Applies only when Persistent storage is enabled. Minimum is 1.	Conditional	1
Queue Mode	Dropdown	Order in which the connector sends buffered messages. fifo sends the earliest message first. lifo sends the latest message first. Applies only when Persistent storage is enabled.	No	lifo
Enable Logging	Boolean	Write connection and publish error messages to the connector log. Logging can increase CPU usage, so enable it only for debugging.	No	Disabled

Encrypt modes

Value	Behavior
disable	No TLS. The connector sends the login and data in cleartext. The server must allow unencrypted connections.
false	The connector uses TLS for the login handshake only, to protect credentials. Data after the login is unencrypted. This is the historical SQL Server default.
true	The connector uses TLS for the login and all data, and validates the server certificate. If the server uses a CA that isn't public, enter it in CA Certificate.
strict	TDS 8.0 strict encryption, for SQL Server 2022 and later. The connector negotiates TLS before the TDS handshake, and the server certificate must validate, with no fallback.

Caution: With Encrypt set to disable or the default false, the connector sends data in cleartext. Use true or strict in production.

Default table schema

When you enable Create table and the target table doesn't exist, the connector creates it with the following schema.

IF OBJECT_ID(N'[SCHEMA_NAME].[TABLE_NAME]', N'U') IS NULL
BEGIN
    CREATE TABLE [SCHEMA_NAME].[TABLE_NAME]
    (
        id          BIGINT IDENTITY(1,1) NOT NULL,
        record_uuid UNIQUEIDENTIFIER     NOT NULL,
        arrived_at  DATETIME2(3)         NOT NULL,
        device_id   NVARCHAR(64)         NULL,
        register_id NVARCHAR(64)         NULL,
        tag_name    NVARCHAR(64)         NULL,
        datatype    NVARCHAR(32)         NULL,
        value       NVARCHAR(MAX)        NULL,
        success     BIT                  NULL,
        PRIMARY KEY (id)
    );
END

To write into a table with different columns, create the table yourself, use Mapping, and leave Bulk insert mode set to values. For mapping and JSONPath examples, see the integration guide.

Write modes

Write mode	Behavior
sync	The connector acknowledges a message only after it commits the batch that holds the message. If the database is unreachable, the message stays in persistent storage, and the connector writes it after it reconnects.
async	The connector acknowledges a message once it buffers it. If a batch fails to write, the connector drops it and counts the messages as dropped.

Caution: In async write mode, or with Persistent storage disabled, you can lose data during a database outage. Use async only when throughput matters more than delivery.

IT Hub

Use the IT Hub ProcessorIT Hub Processor to run an ad-hoc SQL query through the connector. The connector streams the result back one message per row, on the response topic supplied in the request.

Add a ConnectorAdd a Connector

Manage ConnectorsManage Connectors

IT Hub ProcessorIT Hub Processor