SQL Server

Databases Analytics

Synopsis

Creates a SQL Server target that writes records into one table of an existing SQL Server or Azure SQL Database. Each field of a record fills the column with the same name, or the whole record goes into a single JSON column.

Schema

- name: <string>
  description: <string>
  type: mssql
  pipelines: <pipeline[]>
  status: <boolean>
  properties:
    address: <string>
    port: <integer>
    database: <string>
    schema: <string>
    table: <string>
    username: <string>
    password: <string>
    json_column: <string>
    batch_size: <integer>
    max_bytes: <integer>
    timeout: <integer>
    field_format: <string>
    tls:
      status: <boolean>
      verify: <boolean>
      ca_name: <string>
      cert_name: <string>
      key_name: <string>
      passphrase: <string>
      server_name: <string>
      min_tls_version: <string>
      max_tls_version: <string>
    debug:
      status: <boolean>
      dont_send_logs: <boolean>

Configuration

The following fields are used to define the target:

Field	Required	Default	Description
name	Y		Target name
description	N	-	Optional description
type	Y		Must be mssql
pipelines	N	-	Optional post-processor pipelines
status	N	true	Enable/disable the target

Connection

Field	Required	Default	Description
address	Y	-	Host name or IP address of the SQL Server, or of the Azure SQL logical server
port	N	1433	Port of the server
database	Y	-	The database that holds the table
username	N	-	The user that writes to the table. It needs permission to insert into it
password	N	-	Password for that user
timeout	N	30	Seconds one database call may take. At least 1

Table

Field	Required	Default	Description
schema	N	dbo	The table's schema
table	Y	-	The table to write to. It must already exist
json_column	N	-	The column that receives the whole record. It must be an nvarchar or varchar column: a json column is refused. Leave it out to fill columns by name. See Columns by Name or Whole Record as JSON

The database, schema, table and JSON column names must start with a letter or an underscore, and use only letters, digits and underscores. Any other name is refused and the target does not start.

Delivery

Field	Required	Default	Description
batch_size	N	1000	The most records in one write. The target's own value first, then the service-level batch_size, then 1000
max_bytes	N	16777216 (16 MiB)	The most bytes of JSON in one write
field_format	N	-	Converts each record to a format such as ECS or OCSF before it is written. See applicable Normalization section

note

max_bytes caps the size of a single request body or batch. It behaves differently from max_size on the file-writing targets, and the difference is easy to get wrong:

• An explicit max_bytes: 0 IS honoured and removes the ceiling on targets that have no ceiling of their own. The value is read with Int64, which returns what you wrote — unlike max_size, which is read with MustInt64 and silently substitutes its default for a zero. Where a target has a fixed ceiling of its own, its page says so and a zero is clamped to that ceiling instead.
• A negative value is rejected at configuration time with max_bytes must not be negative.

The ceiling is validated when the target is built and enforced again on every batch, so a value the receiving service will refuse fails early rather than per request.

TLS

Field	Required	Default	Description
tls.status	N	false	Connect over TLS. TLS stays off unless this is true

TLS material is resolved through the shared client builder, so these keys mean the same thing on every target that uses it. They are nested under a tls: block.

Field	Required	Default	Description
tls.verify	N	true	Verify the server certificate. On by default — set it to false only to accept an untrusted certificate, and only where you control the network path
tls.server_name	N	-	SNI hostname override for the handshake. Use it when the certificate's name does not match the address you connect to
tls.ca_name	N	-	CA bundle used to verify the server certificate. When unset the host trust store is used; when set it replaces the host trust store rather than adding to it
tls.cert_name	N*	-	Client certificate, for mutual TLS
tls.key_name	N*	-	Client private key, for mutual TLS
tls.passphrase	N	-	Passphrase for an encrypted client private key
tls.min_tls_version	N	tls1.2	Lowest protocol version accepted (tls1.0, tls1.1, tls1.2, tls1.3)
tls.max_tls_version	N	tls1.3	Highest protocol version accepted (tls1.0, tls1.1, tls1.2, tls1.3)

* cert_name and key_name are individually optional but must be supplied together — a certificate without its key, or a key without its certificate, is a configuration error.

Scheduling

See Scheduling and Pool Behavior for interval and cron fields shared by all targets.

Debug Options

Field	Required	Default	Description
debug.status	N	false	Enable debug logging
debug.dont_send_logs	N	false	Process logs but don't send to target (testing)

Details

The SQL Server target writes records into one table that already exists, on SQL Server or on Azure SQL Database. It never creates the table or changes it, and it only adds rows. Each batch is written in one go, and each worker uses its own connection to the database.

Columns by Name or Whole Record as JSON

The target works in one of two modes:

• Columns by name, the default: each top-level field of a record fills the column with the same name, with the same upper and lower case. A field with no matching column is skipped. A record that is not a JSON object, or that fills no column, is skipped and counted as dropped.
• Whole record as JSON, when json_column is set: the whole record goes into that one column, and every other column keeps its default. A record that is not valid JSON is written as a JSON string.

The target leaves out computed columns, identity columns and rowversion columns, because SQL Server refuses a value written to them.

warning

A field with no matching column is skipped without an error. If no field of a record matches any column, the record is dropped, and when a whole batch keeps nothing the Director logs a warning. Check the column names against the fields your pipeline emits before relying on the target.

Tables with a json Column

The target refuses a table that has a column of SQL Server's json type, in both modes, even when no record would fill that column. The target does not start. Store JSON in an nvarchar or varchar column instead, such as nvarchar(max), and point json_column at it to write the whole record there.

Prerequisites

1. A running SQL Server, or an Azure SQL Database, reachable from the Director host
2. The database and the table, already created, with no json column
3. A user with permission to insert into the table

Examples

Basic

Writing each record's fields into the columns of the same name:

targets:
  - name: mssql_events
    type: mssql
    properties:
      address: "sqlserver.example.com"
      database: "analytics"
      table: "events"
      username: "datastream"
      password: "secret"

port and schema are left out, so they default to 1433 and dbo.

Whole Record as JSON on Azure SQL Database

Writing each whole record into an nvarchar(max) column of an Azure SQL Database, over TLS:

targets:
  - name: azuresql_raw
    type: mssql
    properties:
      address: "myserver.database.windows.net"
      database: "analytics"
      schema: "logs"
      table: "raw_events"
      json_column: "record"
      username: "datastream"
      password: "secret"
      tls:
        status: true

Every other column of logs.raw_events keeps its default.

Troubleshooting

Situation	Result
The database refuses a value, for example one that does not fit its column	The batch is sent again one record at a time. Only the records that still fail are dropped, and they are counted as dropped
The connection or the server has a temporary problem	The batch is retried
A column was removed from the table	The target reads the table's columns again once and retries the batch
The table does not exist when the target starts	The target stops with an error. Create the table first and give the user access to it

The table was not found

mssql table analytics.dbo.events was not found, or it has no columns this target can write. Create the table first, and give the user access to it

Cause: the table named in schema and table does not exist, the user cannot see it, or none of its columns can take a written value. The target reads the table's columns when it starts, so this shows up at startup rather than on the first batch.

Fix: create the table, or correct schema and table, and grant the user access to it.

The table has a json column

could not read the columns of mssql table analytics.dbo.events: column "payload" has type json, and the SQL Server driver cannot bulk copy into a table that has a json column. Use an nvarchar(max) column instead

Cause: a column of the table has SQL Server's json type. The target refuses such a table in both modes.

Fix: change the column to nvarchar(max), or write to a table that has no json column.

Records are dropped although nothing fails

Cause: the fields of the records match no column of the table. Names match exactly, including case.

Fix: rename the columns after the fields your pipeline emits, or set json_column to store the whole record in one column.

Configuration errors

These stop the target from starting:

Reason text	Fix
address is required for mssql target	Set address
database is required for mssql target	Set database
table is required for mssql target	Set table
invalid database name "..." for mssql target	Use a name of letters, digits and underscores that starts with a letter or an underscore. The same rule applies to schema, table and json_column
timeout must be at least 1 second for mssql target, got ...	Set timeout to 1 or more
batch_size must be a whole number greater than 0 for mssql target, got ...	Use a whole number of at least 1, or leave batch_size out to use the service-level value
failed to build TLS config: ...	The rest of the line names the TLS setting at fault