Report an issue
Back to plugin list
mssql
Official

Microsoft SQL Server

This plugin is in preview.

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

Repositorygithub.com
Latest version

v4.4.1

Type

Destination

Platforms
Date Published

Oct 27, 2023

Price

Free

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 >= 2017
  • Azure SQL Database >= 2017


Configuration

Microsoft SQL Server destination plugin configuration reference

Example Configuration

kind: destination
spec:
  name: "mssql"
  path: "cloudquery/mssql"
  registry: "cloudquery"
  version: "v4.3.15"
  spec:
    connection_string: "server=localhost;user id=SA;password=yourStrongP@ssword;port=1433;database=cloudquery;"
    # Optional parameters:
    # auth_mode: ms
    # schema: dbo
    # batch_size: 1000 # 1K entries
    # batch_size_bytes: 5242880 # 5 MiB
    # batch_timeout: 20s
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 more details.
  • 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)
    This parameter controls the maximum amount of items may be grouped together to be written as a single write.
  • batch_size_bytes (integer) (optional) (default: 5242880 (= 5 MiB))
    This parameter controls the maximum size of items that may be grouped together to be written as a single write.
  • batch_timeout (duration) (optional) (default: 20s (= 20 seconds))
    This parameter controls the timeout for writing a single batch.

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:  "v4.3.15"

  spec:
    connection_string: "${MS_SQL_CONN_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: "v4.3.15"

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


Subscribe to product updates

Be the first to know about new features.