Skip to main content

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
}
}