SQL Server Extension

The SQL Server extension enables reading from MS SQL Server CDC tables in Grainite applications.

Setup

In order to be able to use the SQL Server extension, first include it as a dependency in your application's pom.xml file.

...
<dependency>
  <groupId>ext.grainite</groupId>
  <artifactId>grainite-sqlserver</artifactId>
  <version>{GRAINITE-VERSION}</version>
</dependency>
...

Replace {GRAINITE-VERSION} with the version of Grainite you are also using for libgrainite (the Grainite Client library for Java).

Contents

The SQL Server Extension includes:

• SQLServerReaderTask: Task to continuously read from a given SQLServer’s CDC tables. The task supports combining the reads in a way that all updates across the capture set within the same transaction may be grouped in one event, or a sharded approach where each table’s changes are captured independently and streamed in parallel.

SQLServerReaderTask

Prerequisite: Some pre-configuration needs to be done to enable Change Data Capture on the MS SQL Server side.

Note: When the transaction is too large to fit in a single message, the message is split into multiple messages. The last record for each message will contain a property __$partial which is set to true when this is one of the partial messages, and false when this is the final message of a series of parts. For applications that need to perform some actions on completion of a transaction, they should perform those actions after receiving a message with __$partial set to false.

Usage

To include this task in your application, you must specify the taskClass ext.grainite.tasks.sqlserver.SQLServerCDCReaderTask and taskInstanceClass ext.grainite.tasks.sqlserver.SQLServerCDCReaderInstance in your application's configuration YAML file.

app.yaml

...
tasks:
  - task_name: my_sqlserver_task
    taskClass: ext.grainite.tasks.sqlserver.SQLServerCDCReaderTask
    taskInstanceClass: ext.grainite.tasks.sqlserver.SQLServerCDCReaderInstance
    config:
...

Below are the configuration options that can be passed in under config:

Property	Required?	Value	Description
connectionURL	REQUIRED	Example: jdbc:sqlserver://localhost:1433;databaseName=TestDB;encrypt=true;trustServerCertificate=true	JDBC Connection URL for the SQL Server instance, must include the database.
database	REQUIRED	Example: TestDB	Database name
capture_instances	REQUIRED	Example: TestTableA; TestTableB	Capture instance names separated with semi-colon
combine_txn	Optional	true or false Default: false	When set to true, there is only one TaskInstance shard created, which polls all the capture instances and sends one message per transaction. When false, there are as many instance shards as capture instances - all operating in parallel.
max_poll_interval	Optional	Example: 30 Default: 60	Maximum poll interval in number of seconds. When no changes are found, the instance backs off for the next poll (by doubling the delay). The max threshold is the value in max_poll_interval
output.topic	Optional	Example: my_events	Grainite topic to emit output of this task to
output.table	Optional	Example: my_table	Grainite table to emit output of this task to via Grain message (output.table_action must also be provided)
output.table_action	Optional	Example: my_table_action	Given action to invoke via Grain Message to the table specified in output.table
singleUpdateMaxSize	Optional	Example: 10240 Default: 200000	Maximum payload size (in bytes) of either single topic append message or single g2g message. min_size: 10240 max_size: 200000 If the config value is less than min_size then it is updated to min_size by default.
totalUpdateMaxSize	Optional	Example: 20480 Default: 400000	Maximum payload size (in bytes) across all the topic append messages and g2g messages in one commit. min_size: 20480 max_size: 400000 If the config value is less than min_size then it is updated to min_size by default.
start_from_max_lsn	Optional	true or false Default: false	When set to true, task polls CDC events starting from max lsn.

SQL Server is a trademark of Microsoft Corporation