Microsoft SQL Server system¶

This system type represents a Microsoft SQL Server running on premise or in the cloud (Azure). It can also be used to connect to a Microsoft Azure Synapse Analytics service. For the latter, you will need to set the dialect property to synapse.

This system uses the official Microsoft (ODBC) drivers.

See the supported column types list for a overview of which SQL Server column types are supported and how they are mapped to Sesam types.

Prototype¶

{
    "_id": "sql_system_id",
    "type": "system:sqlserver",
    "name": "A Microsoft SQL server",
    "username":"$ENV(username-variable)",
    "password":"$SECRET(password-variable)",
    "host":"fqdn-or-ip-address-here",
    "dialect": "sql-server",
    "port": 1433,
    "database": "database-name"
}

Properties¶

Property	Type	Description	Default	Req
`username`	String	Username to use when connecting to the database.		Yes
`password`	String	Password to use when connecting to the database.		Yes
`host`	String	Host name or IP address to the database server. Must be DNS resolvable if non-numeric.		Yes
`port`	Integer	Database IP port.	1433
`database`	String	Name/id of database to connect to.		Yes
`dialect`	String	Indicates if the server is a normal SQL server or a Synapse server. The allowed values are `sql-server` or `synapse`.	`sql-server`

Example configuration¶

Example MS SQL Server configuration:

{
    "_id": "sqlserver_db",
    "name": "MS SQL Server test database",
    "type": "system:sqlserver",
    "username": "$ENV(username-variable)",
    "password": "$SECRET(password-variable)",
    "host": "myserver.database.windows.net",
    "port": 1433,
    "database": "testdb"
}

Note that for backwards compatibility reasons the type “system:mssql-azure-dw” is still accepted as an alternative alias.

Bulk operations in Microsoft SQL server and Azure Synapse systems¶

Both Microsoft SQL Server and Azure Synapse support bulk operations for uploading data. Sesam uses the bcp utility for bulk uploading.

When a pipe has been configured with a SQL sink that has the use_bulk_operations parameter set to true, this happens when the pipe runs:

Sesam creates a temporary database table named “SESAM_BULK_TMP_<table>” (where <table> is the name of the table the sink writes to).
Sesam writes a temporary file to the local disk that is formatted in a way that the bcp utility understands.
Sesam runs the bcp utility, which will upload the content of the file to the temporary table.
Sesam runs a MERGE sql statement that updates the target table with the contents of the temporary table (inserting and updating rows as required).
Sesam drops the temporary database table.

For this method to work, Sesam must have permissions to create and drop tables in the database. If for some reason that is not possible, the use_bulk_operations parameter in the sql sink can be set to false to make the sink use the (slower) INSERT and UPDATE sql statements to upload data.

A note on writable views in MS SQL server¶

If you need to filter out identity columns or skip certain columns in a table you can try to use the skip_identity_columns property on the SQL sink. However, for MS SQL based servers there is another option/workaround for this usecase: instead of writing to the table directly, you can define a “writable view” of the table that omits the identity columns (or other columns you want to skip) and write to that instead. See more information here: https://docs.microsoft.com/en-us/sql/relational-databases/views/modify-data-through-a-view

Note that this does not work for primary key columns - i.e. you still need to have one or more unique columns for each row defined in the primary_key property. Autogenerated primary keys are not supported since Sesam then have no way to uniquely identify which rows to update or delete.

Microservice system

MySQL system