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 |
---|---|---|---|---|
|
String |
Username to use when connecting to the database. |
Yes |
|
|
String |
Password to use when connecting to the database. |
Yes |
|
|
String |
Host name or IP address to the database server. Must be DNS resolvable if non-numeric. |
Yes |
|
|
Integer |
Database IP port. |
1433 |
|
|
String |
Name/id of database to connect to. |
Yes |
|
|
String |
Indicates if the server is a normal SQL server or a Synapse server. The allowed values are |
|
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.