Back to plugin list
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.4
Type
Destination
Platforms
Date Published
Mar 19, 2024
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.4.4"
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 details.auth_mode
(string
) (optional) (default:ms
)If you need to authenticate via Azure Active Directory ensure you specifyazure
value. See SDK documentation for more information. Supported values:ms
connect to Microsoft SQL Server instanceazure
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 nameddbo
.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: "v4.4.4"
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.4.4"
spec:
connection_string: "server=localhost;user id=SA;password=yourStrongP@ssword;port=1433;database=cloudquery;"