> For the complete documentation index, see [llms.txt](https://docs.cybus.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.cybus.io/2-4-2/guides/system-connectivity/databases-time-series-storage/microsoft-sql-server-integration.md).

# Microsoft SQL Server Integration

This guide describes how to integrate Microsoft SQL Server with Connectware. You configure a service commissioning file that writes shop floor data into a SQL Server table and polls query results back into an MQTT topic. A complete example file is available at the end of this guide.

## Objectives

* Establishing a connection between Connectware and Microsoft SQL Server.
* Writing shop floor data from an ISA-95-style topic hierarchy into a production events table.
* Polling recent rows from the database into an MQTT topic on a fixed interval.

## Prerequisites

To follow this guide, you will need the following:

* A running instance of Cybus Connectware.
* A Microsoft SQL Server instance that is reachable from Connectware. This can be an on-premises SQL Server or an [Azure SQL Database](https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview).
* A SQL login with `INSERT` and `SELECT` permissions on the target database.
* Access to the [Admin UI](/2-4-2/access/admin-ui.md) with sufficient [user permissions](/2-4-2/access/user-management.md).
* Basic knowledge of MQTT and the Connectware [services](/2-4-2/data-flows/services.md) concept (for example, [service commissioning files](/2-4-2/data-flows/service-commissioning-files.md), [connections](/2-4-2/data-flows/service-commissioning-files/resources/cybus-connection.md), and [endpoints](/2-4-2/data-flows/service-commissioning-files/resources/cybus-endpoint.md)).

## Connectware and Microsoft SQL Server Integration

Connectware communicates with Microsoft SQL Server through the [MSSQL connector](/2-4-2/connectors/enterprise-connectors/mssql.md). The connector works by defining SQL queries or query templates on endpoints:

* **Write endpoints** define a query template, typically an `INSERT` statement. The template contains placeholders in the form `@identifier`, which the connector replaces with the values from the JSON payload of each incoming MQTT message (see [Placeholder Syntax](/2-4-2/connectors/enterprise-connectors/mssql.md#placeholder-syntax)).
* **Subscribe endpoints** define a query and a polling interval. The connector executes the query on a regular basis and publishes the result rows as a JSON array to the MQTT broker.

The MQTT topics in this guide follow an ISA-95-style equipment hierarchy (`<enterprise>/<site>/<area>/<line>/<cell>`). The mapping subscribes with named wildcards across all levels, so any machine in the hierarchy is picked up without changing the integration, and the topic levels become column values in the database.

The connector encrypts the connection to the database server by default (the `useEncryption` connection property), so it also works with Azure SQL Database, which only accepts encrypted connections.

### Production Events Table

The integration writes into a single table that stores one row per shop floor event. Create it in your target database:

{% code lineNumbers="true" %}

```sql
CREATE TABLE production_events (
  id BIGINT IDENTITY(1,1) PRIMARY KEY,
  event_time DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
  site NVARCHAR(64) NOT NULL,
  area NVARCHAR(64) NOT NULL,
  line NVARCHAR(64) NOT NULL,
  cell NVARCHAR(64) NOT NULL,
  event_type NVARCHAR(64) NOT NULL,
  event_value FLOAT NOT NULL
);
```

{% endcode %}

The `event_time` column defaults to the insert time in UTC, so the messages do not need to carry a timestamp. If you want to store machine timestamps instead, add a column for them and extend the `INSERT` statement with another placeholder.

### Microsoft SQL Server Connection Properties

We add the connection values as parameters to the service commissioning file, so you can set them when you install the service.

Do not worry about copying the service commissioning file snippets together into one, the complete example file is available at the end of this guide.

* `mssqlHost` and `mssqlPort`: The hostname and port of your SQL Server instance. The default port is `1433`.
* `mssqlUsername` and `mssqlPassword`: The SQL login that Connectware uses to connect.
* `mssqlDatabase`: The database that contains the `production_events` table.
* `pollingInterval`: The interval in milliseconds between polling queries.
* `topicRoot`: The root of the MQTT topic hierarchy. Defaults to `enterprise`.

{% code lineNumbers="true" %}

```yaml
parameters:
  mssqlHost:
    description: Hostname or IP address of the Microsoft SQL Server instance
    type: string
    default: example.com

  mssqlPort:
    description: Port of the Microsoft SQL Server instance
    type: integer
    default: 1433

  mssqlUsername:
    description: SQL login that Connectware uses to connect
    type: string
    default: connectware

  mssqlPassword:
    description: Password of the SQL login
    type: string

  mssqlDatabase:
    description: Database that contains the production_events table
    type: string
    default: factory

  pollingInterval:
    description: Interval in milliseconds between polling queries
    type: integer
    default: 10000

  topicRoot:
    description: Root of the MQTT topic hierarchy
    type: string
    default: enterprise
```

{% endcode %}

### Microsoft SQL Server Connection

To connect to the database, we set up a `Cybus::Connection` resource that uses the MSSQL connector. All endpoints in this guide share this connection.

{% code lineNumbers="true" %}

```yaml
resources:
  mssqlConnection:
    type: Cybus::Connection
    properties:
      protocol: Mssql
      connection:
        host: !ref mssqlHost
        port: !ref mssqlPort
        username: !ref mssqlUsername
        password: !ref mssqlPassword
        database: !ref mssqlDatabase
```

{% endcode %}

For all available connection properties, including the TDS protocol version, timeouts, and the reconnection strategy, see [MSSQL Connection Properties](/2-4-2/connectors/enterprise-connectors/mssql/mssqlconnection.md).

### Writing Production Events

The write endpoint defines the `INSERT` statement as a query template. Each placeholder, for example `@site`, is replaced with the value of the matching key in the JSON payload of the incoming message.

The mapping feeds the endpoint from the topic hierarchy. It uses [named wildcards](/2-4-2/data-flows/service-commissioning-files/resources/cybus-mapping.md#wildcards), so the topic levels are available in the `$context.vars` object of the `transform` rule. The rule builds one flat object whose keys match the query placeholders, combining the topic levels with the `event_type` and `value` fields of the machine payload.

{% code lineNumbers="true" %}

```yaml
productionEventWrite:
  type: Cybus::Endpoint
  properties:
    protocol: Mssql
    connection: !ref mssqlConnection
    write:
      query: >-
        INSERT INTO production_events
        (site, area, line, cell, event_type, event_value)
        VALUES (@site, @area, @line, @cell, @event_type, @event_value)

productionEventMapping:
  type: Cybus::Mapping
  properties:
    mappings:
      - subscribe:
          topic: !sub '${topicRoot}/+site/+area/+line/+cell/production-events'
        publish:
          endpoint: !ref productionEventWrite
        rules:
          - transform:
              expression: >-
                {
                  "site": $context.vars.site,
                  "area": $context.vars.area,
                  "line": $context.vars.line,
                  "cell": $context.vars.cell,
                  "event_type": event_type,
                  "event_value": value
                }
```

{% endcode %}

Any message published to a matching topic, for example `enterprise/hamburg/assembly/line-1/press-01/production-events`, now inserts one row into the table. The machine payload only needs the event fields, the topic provides the rest:

{% code lineNumbers="true" %}

```json
{
  "event_type": "temperature",
  "value": 42.1
}
```

{% endcode %}

All placeholders defined in the query must exist in the resulting message payload. If one is missing, the connector logs an error and ignores the message (see [Writing Data](/2-4-2/connectors/enterprise-connectors/mssql.md#writing-data)).

{% hint style="warning" %}
The MSSQL connection on the Connectware side does not perform any data validation against the database schema. The senders of the MQTT messages must ensure that the values match the column types, for example a number for the `event_value` column.
{% endhint %}

### Polling Recent Production Events

For the opposite direction, a subscribe endpoint executes a query on a fixed interval and publishes the result rows to the MQTT broker. This example reads the production events of the last five minutes, so downstream consumers such as dashboards or MES systems receive a regularly updated snapshot.

{% code lineNumbers="true" %}

```yaml
recentEventsQuery:
  type: Cybus::Endpoint
  properties:
    protocol: Mssql
    connection: !ref mssqlConnection
    subscribe:
      query: >-
        SELECT TOP 50 id, event_time, site, area, line, cell,
        event_type, event_value
        FROM production_events
        WHERE event_time > DATEADD(minute, -5, SYSUTCDATETIME())
        ORDER BY event_time DESC
      interval: !ref pollingInterval

recentEventsMapping:
  type: Cybus::Mapping
  properties:
    mappings:
      - subscribe:
          endpoint: !ref recentEventsQuery
        publish:
          topic: !sub '${topicRoot}/production-events/recent'
```

{% endcode %}

Choose the polling interval carefully. A low value can overload the database. Instead of a fixed interval, you can also poll on a schedule with the `cronExpression` property (see [MSSQL Endpoint Properties](/2-4-2/connectors/enterprise-connectors/mssql/mssqlendpoint.md)).

Every query execution publishes a message with a `timestamp` property and a `value` property that contains the result rows. If the query returns no rows, `value` is an empty array (`[]`).

{% code lineNumbers="true" %}

```json
{
  "timestamp": 1768560000000,
  "value": [
    {
      "id": 1,
      "event_time": "2026-01-16T10:40:00.000Z",
      "site": "hamburg",
      "area": "assembly",
      "line": "line-1",
      "cell": "press-01",
      "event_type": "temperature",
      "event_value": 42.1
    }
  ]
}
```

{% endcode %}

## Verifying the Integration

1. Install the service and set the parameters with the values of your SQL Server instance.
2. Check that the connection is in the **Connected** state on the service details page in the Admin UI. If the credentials or the database name are wrong, the connection does not reach the connected state.
3. Publish a test message with the machine payload shown in this guide to `enterprise/hamburg/assembly/line-1/press-01/production-events`, for example with an MQTT client or the Admin UI.
4. Check that the row arrived in the database, for example with `SELECT TOP 10 * FROM production_events ORDER BY id DESC`. The result of every write is also published to the `/res` topic of the write endpoint, with `value` set to `true` on success.
5. Use the [Data Explorer](/2-4-2/monitoring/data-explorer.md) to inspect the polled result rows on the `enterprise/production-events/recent` topic.

## Service Commissioning File Example

{% file src="/files/Q8gp19CU8WAN27t6jyvq" %}

{% code title="microsoft-sql-server-example.yml" lineNumbers="true" expandable="true" %}

```yaml
---
# ----------------------------------------------------------------------------
# Service Commissioning File
# ----------------------------------------------------------------------------
# Copyright: Cybus GmbH
# Contact: support@cybus.io
# ----------------------------------------------------------------------------
# Microsoft SQL Server Integration (Example)
# ----------------------------------------------------------------------------

description: >
  Service commissioning file for the integration between Connectware and
  Microsoft SQL Server (Example)

metadata:
  name: Microsoft SQL Server Integration
  provider: cybus
  homepage: https://www.cybus.io
  version: 1.0.0

parameters:
  mssqlHost:
    description: Hostname or IP address of the Microsoft SQL Server instance
    type: string
    default: example.com

  mssqlPort:
    description: Port of the Microsoft SQL Server instance
    type: integer
    default: 1433

  mssqlUsername:
    description: SQL login that Connectware uses to connect
    type: string
    default: connectware

  mssqlPassword:
    description: Password of the SQL login
    type: string

  mssqlDatabase:
    description: Database that contains the production_events table
    type: string
    default: factory

  pollingInterval:
    description: Interval in milliseconds between polling queries
    type: integer
    default: 10000

  topicRoot:
    description: Root of the MQTT topic hierarchy
    type: string
    default: enterprise

resources:
  # Connection to the Microsoft SQL Server database
  mssqlConnection:
    type: Cybus::Connection
    properties:
      protocol: Mssql
      connection:
        host: !ref mssqlHost
        port: !ref mssqlPort
        username: !ref mssqlUsername
        password: !ref mssqlPassword
        database: !ref mssqlDatabase

  # Inserts one row into the production_events table per message
  productionEventWrite:
    type: Cybus::Endpoint
    properties:
      protocol: Mssql
      connection: !ref mssqlConnection
      write:
        query: >-
          INSERT INTO production_events
          (site, area, line, cell, event_type, event_value)
          VALUES (@site, @area, @line, @cell, @event_type, @event_value)

  # Feeds the write endpoint from the ISA-95 topic hierarchy. The named
  # wildcards provide the topic levels in $context.vars, so the transform
  # rule can build one flat object whose keys match the query placeholders.
  productionEventMapping:
    type: Cybus::Mapping
    properties:
      mappings:
        - subscribe:
            topic: !sub '${topicRoot}/+site/+area/+line/+cell/production-events'
          publish:
            endpoint: !ref productionEventWrite
          rules:
            - transform:
                expression: >-
                  {
                    "site": $context.vars.site,
                    "area": $context.vars.area,
                    "line": $context.vars.line,
                    "cell": $context.vars.cell,
                    "event_type": event_type,
                    "event_value": value
                  }

  # Polls the production events of the last five minutes
  recentEventsQuery:
    type: Cybus::Endpoint
    properties:
      protocol: Mssql
      connection: !ref mssqlConnection
      subscribe:
        query: >-
          SELECT TOP 50 id, event_time, site, area, line, cell,
          event_type, event_value
          FROM production_events
          WHERE event_time > DATEADD(minute, -5, SYSUTCDATETIME())
          ORDER BY event_time DESC
        # Be careful with low values, they can overload the database
        # (the unit is milliseconds).
        interval: !ref pollingInterval

  recentEventsMapping:
    type: Cybus::Mapping
    properties:
      mappings:
        - subscribe:
            endpoint: !ref recentEventsQuery
          publish:
            topic: !sub '${topicRoot}/production-events/recent'
```

{% endcode %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.cybus.io/2-4-2/guides/system-connectivity/databases-time-series-storage/microsoft-sql-server-integration.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.