Back to destination list
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.
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 >= 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.0.4"
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
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 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: "v5.0.4"
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.0.4"
spec:
connection_string: "server=localhost;user id=SA;password=yourStrongP@ssword;port=1433;database=cloudquery;"