Microsoft SQL Server Setup
Microsoft SQL Server is a relational database management system. It can be used as an upstream or downstream resource in your Turbine data applications using the records or write functions to a select table in a databse.
Meroxa supports self-hosted and Azure instances of Microsoft SQL Server versions 2012 - 2019.
Setup
Networking
To add a Microsoft SQL Server database as a resource, it must be accessible by Meroxa. If not publicly accessible, here are some ways to give Meroxa access.
Resource Configuration
Use the meroxa resource create command to configure your Microsoft SQL Server resource.
The following example depicts how this command is used to create a Microsoft SQL Server resource named mysqlserver with the minimum configuration required.
meroxa resource create mysqlserver \
--type sqlserver \
--url "sqlserver://$MSSQL_USER:$MSSQL_PASS@$MSSQL_URL:$MSSQL_PORT/$MSSQL_DB"
In the example above, replace following variables with valid credentials from your Microsoft SQL Server environment:
$MSSQL_USER- Microsoft SQL Server Username$MSSQL_PASS- Microsoft SQL Server Password$MSSQL_URL- Microsoft SQL Server URL$MSSQL_DB- Microsoft SQL Server Database Name$MSSQL_PORT- Microsoft SQL Server Port (e.g., 1433).
Connections
Change Data Capture (CDC)
For changes to be captured, CDC support must be enabled for the Microsoft SQL Server database and every table. To enable Change Data Capture (CDC) on Azure see: Enable and disable change data capture .
Database
To enable CDC to a database named meroxa, run the following SQL commands:
-- ====
-- Enable Database for CDC template
-- ====
USE meroxa
GO
EXEC sys.sp_cdc_enable_db
GO
Table
To enable CDC to a table named dbo.User, run the following SQL commands:
-- ====
-- Enable CDC on table 'User' in 'dbo' schema
-- ====
exec sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'User',
@role_name = NULL
GO
Verify
You may verify if you have CDC enabled for a given table or database using the following commands:
-- ====
-- Check if CDC is enabled for databases
-- ====
SELECT name, is_cdc_enabled FROM sys.databases;
GO
-- ====
-- Check if CDC is enabled for tables
-- ====
SELECT name, is_tracked_by_cdc FROM sys.tables;
GO
Data Record Output
Data records from Microsoft SQL Server using Change Data Capture (CDC) events will take on the following format:
{
"schema": {
"type": "struct",
"fields": [
{
"type": "struct",
"fields": [
{
"type": "int32",
"optional": false,
"field": "id"
},
{
"type": "string",
"optional": false,
"field": "email"
},
{
"type": "string",
"optional": true,
"field": "category"
},
{
"type": "string",
"optional": true,
"field": "product_name"
},
{
"type": "boolean",
"optional": true,
"field": "stock"
},
{
"type": "int32",
"optional": true,
"field": "product_id"
},
{
"type": "string",
"optional": true,
"field": "shipping_address"
},
{
"type": "string",
"optional": true,
"field": "product_type"
}
],
"optional": true,
"name": "resource.schema.orders.Value",
"field": "before"
},
{
"type": "struct",
"fields": [
{
"type": "int32",
"optional": false,
"field": "id"
},
{
"type": "string",
"optional": false,
"field": "email"
},
{
"type": "string",
"optional": true,
"field": "category"
},
{
"type": "string",
"optional": true,
"field": "product_name"
},
{
"type": "boolean",
"optional": true,
"field": "stock"
},
{
"type": "int32",
"optional": true,
"field": "product_id"
},
{
"type": "string",
"optional": true,
"field": "shipping_address"
},
{
"type": "string",
"optional": true,
"field": "product_type"
}
],
"optional": true,
"name": "resource.schema.orders.Value",
"field": "after"
},
{
"type": "struct",
"fields": [
{
"type": "string",
"optional": false,
"field": "version"
},
{
"type": "string",
"optional": false,
"field": "connector"
},
{
"type": "string",
"optional": false,
"field": "name"
},
{
"type": "int64",
"optional": false,
"field": "ts_ms"
},
{
"type": "string",
"optional": true,
"name": "io.debezium.data.Enum",
"version": 1,
"parameters": {
"allowed": "true,last,false"
},
"default": "false",
"field": "snapshot"
},
{
"type": "string",
"optional": false,
"field": "db"
},
{
"type": "string",
"optional": false,
"field": "schema"
},
{
"type": "string",
"optional": false,
"field": "table"
},
{
"type": "string",
"optional": true,
"field": "change_lsn"
},
{
"type": "string",
"optional": true,
"field": "commit_lsn"
},
{
"type": "int64",
"optional": true,
"field": "event_serial_no"
}
],
"optional": false,
"name": "io.debezium.connector.sqlserver.Source",
"field": "source"
},
{
"type": "string",
"optional": false,
"field": "op"
},
{
"type": "int64",
"optional": true,
"field": "ts_ms"
},
{
"type": "struct",
"fields": [
{
"type": "string",
"optional": false,
"field": "id"
},
{
"type": "int64",
"optional": false,
"field": "total_order"
},
{
"type": "int64",
"optional": false,
"field": "data_collection_order"
}
],
"optional": true,
"field": "transaction"
}
],
"optional": false,
"name": "resource.schema.orders.Envelope"
},
"payload": {
"before": null,
"after": {
"id": 1,
"email": "[email protected]",
"category": "camping",
"product_name": "Forte 35 Sleeping Bag - Womens",
"stock": true,
"product_id": 361632,
"shipping_address": "9718 East Virginia Avenue Silver Spring, MD 20901",
"product_type": "sleeping-bag"
},
"source": {
"version": "1.2.5.Final",
"connector": "sqlserver",
"name": "resource",
"ts_ms": 1650418202572,
"snapshot": "true",
"db": "database",
"schema": "schema",
"table": "orders",
"change_lsn": null,
"commit_lsn": "0000050c:000006d5:0001",
"event_serial_no": null
},
"op": "r",
"ts_ms": 1650418202572,
"transaction": null
}
}