Microsoft SQL Server Plugin documentation

Official

Microsoft SQL Server

This destination plugin lets you sync data from a CloudQuery source to a Microsoft SQL Server compatible database. This includes both Microsoft SQL Server and Azure SQL Server.

Publisher

cloudquery

Repository

github.com

Latest version

v5.1.11

Type

Destination

Platforms

Date Published

Documentation Changelog

Overview Configuration Example Licenses

Overview #

Microsoft SQL Server destination plugin

This destination plugin lets you sync data from a CloudQuery source to a Microsoft SQL Server compatible database. This includes both Microsoft SQL Server and Azure SQL Server.

Supported database versions:

Microsoft SQL Server >= 2019
Azure SQL Database >= 2019

Configuration #

Microsoft SQL Server destination plugin configuration reference

Example Configuration #

kind: destination
spec:
  name: "mssql"
  path: "cloudquery/mssql"
  registry: "cloudquery"
  version: "v5.1.11"
  spec:
    # Connection string in the format `server=localhost;user id=SA;password=yourStrongP@ssword;port=1433;database=cloudquery;`
    connection_string: "${MSSQL_CONNECTION_STRING}"
    # Optional parameters:
    # auth_mode: ms
    # schema: dbo
    # batch_size: 1000 # 1K entries
    # batch_size_bytes: 5242880 # 5 MiB
    # batch_timeout: 20s

Make sure you use environment variable expansion in production instead of committing the credentials to the configuration file directly.

The Microsoft SQL Server destination utilizes batching, and supports batch_size and batch_size_bytes.

Microsoft SQL Server spec #

This is the (nested) spec used by the Microsoft SQL Server destination plugin.

connection_string (string) (required)
Connection string to connect to the database. See SDK documentation for details.
Example connection strings:
- "sqlserver://username:password@hostname/instance" basic connection using a named instance
- "sqlserver://username:password@localhost?database=master&connection+timeout=30" select "master" database and set connection timeout (default instance)

auth_mode (string) (optional) (default: ms)

If you need to authenticate via Azure Active Directory ensure you specify azure value. See SDK documentation for more information. Supported values:

- `ms` _connect to Microsoft SQL Server instance_
- `azure` _connect to Azure SQL Server instance_

schema (string) (optional) (default: dbo)
Schema name to be used. By default, Microsoft SQL Server destination plugin will use the default schema named dbo.
batch_size (integer) (optional) (default: 1000)
Maximum amount of items that may be grouped together to be written in a single write.
batch_size_bytes (integer) (optional) (default: 5242880 (= 5 MiB))
Maximum size of items that may be grouped together to be written in a single write.
batch_timeout (duration) (optional) (default: 20s (= 20 seconds))
Maximum interval between batch writes.

Verbose logging for debug #

The Microsoft SQL Server destination can be run in debug mode. To achieve this pass the log option to connection_string. See SDK documentation for more details.

Note: This will use SDK built-in logging and might output data and sensitive information to logs. Make sure not to use it in production environment.

kind: destination
spec:
  name:     "mssql"
  path:     "cloudquery/mssql"
  registry:   "cloudquery"
  version:  "v5.1.11"

  spec:
    connection_string: "${MSSQL_CONNECTION_STRING};log=255"

Example #

Microsoft SQL Server destination plugin example

In this article we will show you a simple example of configuring Microsoft SQL Server destination plugin.

Prerequisites #

In order to be able to sync to Microsoft SQL Server you will need a running installation. We will be using the quickstart guide for running Microsoft SQL Server locally using Docker.

Create admin password for Microsoft SQL Server #

Microsoft SQL Server enforces password complexity. In order to successfully run the database you must specify a password that adheres the policy described here.

For this example we will be using yourStrongP@ssword as a password.

Start Microsoft SQL Server locally #

docker run \
  -e "ACCEPT_EULA=Y" \
  -e "MSSQL_SA_PASSWORD=yourStrongP@ssword" \
  -p 1433:1433 \
  -d \
  mcr.microsoft.com/mssql/server:2022-latest

Create database to sync to #

We will be using cloudquery for database name in this example.

docker exec $(docker ps -alq) \
  /opt/mssql-tools/bin/sqlcmd \
  -U "SA" \
  -P "yourStrongP@ssword" \
  -Q "CREATE DATABASE cloudquery;"

Note: docker ps -alq returns container ID for the latest started container. You can use container ID discovered manually via docker ps output instead.

Configure Microsoft SQL Server destination plugin #

Once you've completed the steps from Prerequisites section you should be able to connect to the local cloudquery Microsoft SQL Server database via the following connection string:

server=localhost;user id=SA;password=yourStrongP@ssword;port=1433;database=cloudquery;

The (top level) spec section is described in the Destination Spec Reference. The full configuration for Microsoft SQL Server destination plugin will look like this:

kind: destination
spec:
  name: "mssql"
  path: "cloudquery/mssql"
  registry: "cloudquery"
  version: "v5.1.11"

  spec:
    connection_string: "server=localhost;user id=SA;password=yourStrongP@ssword;port=1433;database=cloudquery;"