Skip to main content

SQL Server

MS SQL Reader supports:

  • SQL Server Enterprise versions 2008, 2012, 2014, 2016, 2017, and 2019

  • SQL Server Standard versions 2016, 2017, and 2019

  • Azure SQL Database, S3 tier and above (Standard and Premium tiers; CDC is not supported for Basic tier)

  • Azure SQL Database Managed Instance

Striim provides templates for creating applications that read from SQL Server and write to various targets. See Creating an application using a template for details.

MSJet supports Microsoft SQL Server Standard and Enterprise versions 2016 (SP2), 2017, and 2019 running on 64-bit Windows 10 or Windows Server 2012 or later. It is not compatible with SQL Server running on other operating systems or on Windows on ARM.

Key differences between MSJet and MS SQL Reader

MSJet

MS SQL Reader

reads changes from SQL Server's transaction logs (CDC change tables not required)

reads changes from SQL Server's change data capture tables

automatically enables CDC on a per-table basis

supports compressed tables (see Learn / SQL / SQL Server / Enable Compression on a Table or Index)

supports TLS

supports reading from replication logs (enabling CDC is not required if replication publisher is enabled)

supports reading from a secondary database in an Always On availability group

SQL Server setup

Server-side setup steps vary depending on whether you are using MS SQL Reader or MSJet.

SQL Server setup for MS SQL Reader

MS SQL Reader reads SQL Server change data using the native SQL Server Agent utility. For more information, see About Change Data Capture (SQL Server) on msdn.microsoft.com.

If a table uses a SQL Server feature that prevents change data capture, MS SQL Reader can not read it. For examples, see the "SQL Server 2014 (12.x) specific limitations" section of CREATE COLUMNSTORE INDEX (Transact-SQL) and Unsupported SQL Server Features for In-Memory OLTP.

In Azure SQL Database managed instances, change data capture requires collation to be set to the default SQL_Latin1_General_CP1_CI_AS at the server, database, and table level. If you need a different collation, it must be set at the column level.

Before Striim applications can use the MS SQL Reader adapter, a SQL Server administrator with the necessary privileges must do the following:

  1. If SQL Server is running in a virtual machine in Azure, follow the instructions in Configuring an Azure virtual machine running SQL Server.

  2. If it is not running already, start SQL Server Agent (see Start, Stop, or Pause the SQL Server Agent Service; if the agent is disabled, see Agent XPs Server Configuration Option). This service must be running for MS SQL Reader to work. If it is not running, you will see an error similar to the following in striim.server.log:

    2017-01-08 15:40:24,596 @ -ERROR cached5 
    com.webaction.source.tm.MSSqlTransactionManager.getStartPosition 
    (MSSqlTransactionManager.java:389) 2522 : 
    Could not position at EOF, its equivalent LSN is NULL   
    
  3. Enable change data capture on each database to be read using the following commands (for more information, see Learn / SQL / SQL Server / Enable and disable change data capture):

    • for Amazon RDS for SQL Server:

      EXEC msdb.dbo.rds_cdc_enable_db '<database name>';
    • for all others:

      USE <database name>
      EXEC sys.sp_cdc_enable_db
  4. Create a SQL Server user for use by Striim. This user must use the SQL Server authentication mode, which must be enabled in SQL Server. (If only Windows authentication mode is enabled, Striim will not be able to connect to SQL Server.)

  5. Grant the MS SQL Reader user the db_owner role for each database to be read using the following commands:

    USE <database name>
    EXEC sp_addrolemember @rolename=db_owner, @membername=<user name>

For example, to enable change data capture on the database mydb, create a user striim, and give that user the db_owner role on mydb:

USE mydb
EXEC sys.sp_cdc_enable_db
CREATE LOGIN striim WITH PASSWORD = 'passwd' 
CREATE USER striim FOR LOGIN striim
EXEC sp_addrolemember @rolename=db_owner, @membername=striim

To confirm that change data capture is set up correctly, run the following command and verify that all tables to read are included in the output:

EXEC sys.sp_cdc_help_change_data_capture

Striim can capture change data from a secondary database in an Always On availability group. In that case, change data capture must be enabled on the primary database.

Configuring an Azure virtual machine running SQL Server

When SQL Server is running in an Azure virtual machine as described in How to provision a Windows SQL Server virtual machine in the Azure portal, do the following before following the steps in SQL Server setup .

  1. Go to the virtual machine's Overview tab.

  2. If there is no public IP address, enable it.

  3. If there is no DNS name, specify one, and make a note of the full name (<DNS name>.<Azure region>.cloudapp.azure.com), as you will need it to configure MSSQLReader.

  4. Go to the virtual machine's SQL Server configuration tab.

  5. Set SQL connectivity to Public (Internet).

  6. Enable SQL Authentication and and specify the login name and password MSSQLReader will use to connect to SQL Server.

  7. Make note of the Port setting, as you will need it to configure MSSQLReader.

  8. Go to the Overview tab and click Connect.

  9. When prompted, download the .rdb file, open it in Remote Desktop Connection, and connect to the virtual machine using the resource group's user name and password (not the user name and password you specified for SQL Server authentication).

  10. Open the SQL Server Configuration Manager and set the following as necessary:

    • Protocols: Shared Memory enabled, Named Pipes disabled, TCP/IP enabled

    • TCP/IP Properties IP Addresses tab: TCP Dynamic Ports empty, TCP Port matches the SQL Authentication setting

  11. Log out of Remote Desktop Connection and continue with the instructions in SQL Server setup for MS SQL Reader.

SQL Server setup for MSJet

Note

If transactional replication is already running, follow the instructions in SQL Server setup for MSJet when transactional replication is already running.

Before Striim applications can use MSJet, a SQL Server administrator with local administrator privileges on the SQL Server host must do the following:

  1. Create a Windows user for use by Striim on the SQL Server host (the Windows system that hosts the SQL Server instance containing the databases to be read).

  2. Grant that user local Administrator privileges on the SQL Server host and make it a member of the sysadmin role in SQL Server. (The sysadmin role includes db_owner privileges on all databases.)

  3. Log in as that user and install a Forwarding Agent or Striim Server on the SQL Server host (see Striim Forwarding Agent installation and configuration). Your Striim Forwarding Agent or Striim Server must be installed and run as a user with local administrative privileges.

  4. If Microsoft Visual C++ 2015-2019 Redistributable (x64) version 14.28.29914 or later (see Visual Studio 2015, 2017, 2019, and 2022) and Microsoft OLE DB Driver for SQL Server version 18.3-18.5 (see Release notes for the Microsoft OLE DB Driver for SQL Server) are not already available on the SQL Server host, install or upgrade them.

  5. If Replication subscribers are enabled on each database to be read, skip this step.

    If Replication is not enabled, you must enable CDC logging. In SQL Server, enable change data capture on each database to be read using the following commands, which require the sysadmin role:

    USE <database name>
    EXEC sys.sp_cdc_enable_db
  6. If Replication subscribers are enabled on each database to be read, skip this step.

    If Replication is not enabled, stop the Capture and Cleanup jobs on each of those databases (see Administer and Monitor Change Data Capture (SQL Server)). This will stop SQL Server from writing to its CDC change tables, which MSJet does not require.

  7. If using Windows authentication, skip this step.

    If using SQL Server authentication, create a SQL Server user for use by MSJet.

    For more information, see Microsoft's Choose an Authentication Mode and the notes for MSJet's Integrated Security property in MSJet properties.MSJet properties

  8. Grant the SQL Server user (if using SQL Server authentication) or the Windows user (if using Windows authentication) the db_owner role for each database to be read using the following commands, which require the sysadmin role:

    USE <database name>
    EXEC sp_addrolemember @rolename=db_owner, @membername=<user name>
  9. If you have not previously performed a full backup on each of the databases to be read, do so now (Full Database Backups (SQL Server)).

  10. If Replication subscribers are enabled on each database to be read, skip this step.

    If Replication is not enabled, configure the following stored procedure to run every five minutes on each database that will be read. This will retain the logs read by this adapter for three days. If that is more than necessary or not enough, you may increase the retentionminutes variable. Note that the longer you retain the logs, the more disk space will be required by SQL Server.

    declare @retentionminutes int = (3 * 24 * 60) --3 days in minute granularity
    
    declare @trans table (begt binary(10), endt binary(10))
    insert into @trans exec sp_repltrans
    
    select dateadd(minute, -@retentionminutes, getdate())
    
    declare @firstlsn binary(10) = null
    declare @lastlsn binary(10) = null
    declare @firstTime datetime
    declare @lasttime datetime
    
    select top (1) @lastTime = (select top(1) [begin time] 
      from fn_dblog(stuff(stuff(convert(char(24), begt, 1), 19, 0, ':'), 11, 0, ':'), default)),
        @lastlsn = begt
     from @trans
    order by begt desc     
    
    --All transactions are older than the retention, no further processing required,
    --everything can be discarded
    if (@lasttime < dateadd(minute,-@retentionminutes, getdate()))
    begin
      EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1  
    end
    else
    begin
      --see if anything can be discarded
      select top (1) @firstTime = (select top(1) [begin time] 
        from fn_dblog(stuff(stuff(convert(char(24), begt, 1), 19, 0, ':'), 11, 0, ':'), default)),
          @firstlsn = isnull(@firstlsn, begt)
      from @trans
      order by begt asc
    
      if (@firsttime < dateadd(minute, -@retentionminutes, getdate()))
      begin
        --Since only full VLogs can be truncated we really only need to check the earliest LSN 
        --for every Vlog's date
        select @firstlsn = substring(max(t.lsns), 1, 10), 
               @lastlsn = substring(max(t.lsns), 11, 10)
        from (select min(begt + endt) as lsns 
        from @trans group by substring(begt, 1, 4)) as t
        where (select top(1) [begin time] 
        from fn_dblog(stuff(stuff(convert(char(24), t.lsns, 1), 19, 0, ':'), 11, 0, ':'), default)
        where Operation = 'LOP_BEGIN_XACT') < dateadd(minute, -@retentionminutes, getdate())
    
        exec sp_repldone @xactid = @firstlsn, @xact_seqno = @lastlsn, @numtrans = 0, @time = 0,
          @reset = 0  
      end
    end

SQL Server setup for MSJet when transactional replication is already running

If transactional replication is already running, install MSJet on the publisher.

  1. Create a Windows user for use by Striim on the SQL Server host (the Windows system that hosts the SQL Server instance containing the databases to be read).

  2. Grant that user local Administrator privileges on the SQL Server host and make it a member of the sysadmin role in SQL Server. (The sysadmin role includes db_owner privileges on all databases.)

  3. Log in as that user and install a Forwarding Agent or Striim Server on the SQL Server host (see Striim Forwarding Agent installation and configuration). Your Striim Forwarding Agent or Striim Server must be installed and run as a user with local administrative privileges.

  4. If Microsoft Visual C++ 2015-2019 Redistributable (x64) version 14.28.29914 or later (see Visual Studio 2015, 2017, 2019, and 2022) and Microsoft OLE DB Driver for SQL Server version 18.3-18.5 (see Release notes for the Microsoft OLE DB Driver for SQL Server) are not already available on the SQL Server host, install or upgrade them.

  5. If using Windows authentication, skip this step.

    If using SQL Server authentication, create a SQL Server user for use by MSJet.

    For more information, see Microsoft's Choose an Authentication Mode and the notes for MSJet's Integrated Security property in MSJet properties.MSJet properties

  6. Grant the SQL Server user (if using SQL Server authentication) or the Windows user (if using Windows authentication) the db_owner role for each database to be read using the following commands, which require the sysadmin role:

    USE <database name>
    EXEC sp_addrolemember @rolename=db_owner, @membername=<user name>
  7. If you have not previously performed a full backup on each of the databases to be read, do so now (Full Database Backups (SQL Server)).

Creating the QUIESCEMARKER table for MSJet

To allow Striim to quiesce (see QUIESCE) an application that uses MSJet, you must create a QUIESCEMARKER table in SQL Server.

The DDL for creating the table is: 

CREATE TABLE QUIESCEMARKER (
  source varchar(100), 
  status varchar(100), 
  sequence int, 
  inittime datetime2, 
  updatetime datetime2 default CURRENT_TIMESTAMP, 
  approvedtime datetime2, 
  reason varchar(100), 
constraint quiesce_marker_pk primary key (source, sequence));

The user created as described in SQL Server setup for MSJet must have SELECT, INSERT, and  UPDATE privileges on this table.

MS SQL Reader properties

Note

Before using this adapter, you must complete the tasks described in SQL Server setup for MS SQL Reader.

Before reading from SQL Server with an application deployed to a Forwarding Agent, you must install the required driver as described in Install the Microsoft JDBC Driver in a Forwarding Agent.

By default, SQL Server retains three days of change capture data.

Striim provides templates for creating applications that read from SQL Server and write to various targets. See Creating an application using a template for details.

The adapter properties are:

property

type

default value

notes

Auto Disable Table CDC

Boolean

False

SQL Server starts capturing change data when the Striim application is started. With the default setting of False, SQL Server will continue capturing change data after the application is undeployed. If set to True, when the application is undeployed, SQL Server will stop capturing change data and delete all previously captured data from its change tables.

Bidirectional Marker Table

String

When performing bidirectional replication, the fully qualified name of the marker table (see Bidirectional replication). This setting is case-sensitive.

Compression

Boolean

False

Set to True when the output of the source is the input of a DatabaseWriter target that writes to Cassandra {see Cassandra Writer).

Connection Pool Size

Integer

10

typically should be set to the number of tables, with a large number of tables can set lower to reduce impact on MSSQL host

Connection Retry Policy

String

timeOut=30, retryInterval=30, maxRetries=3

With the default setting:

  • Striim will wait for the database to respond to a connection request for 30 seconds (timeOut=30).

  • If the request times out, Striim will try again in 30 seconds (retryInterval=30).

  • If the request times out on the third retry (maxRetries=3), a ConnectionException will be logged and the application will stop.

Negative values are not supported.

Connection URL

String

<ip address or server name>:<port> or <ip address>\\<instance name>:<port>, for example, 92.168.1.10:1433. If reading from a secondary database in an Always On availability group, use <IP address>:<port>;applicationIntent=ReadOnly.

When connecting through an SSH tunnel (see Using an SSH tunnel to connect to a source or target), specify the IP address of the tunnel.

If the connection requires SSL, see Set up connection to MSSQLReader with SSL in Striim's knowledge base.

Database Name

String

the SQL Server database name

Excluded Tables

String

Data for any tables specified here will not be returned. For example, if Tables uses a wildcard, data from any tables specified here will be omitted. Multiple table names (separated by semicolons) and wildcards may be used exactly as for Tables.

Fetch Size

Integer

0

The fetch size is the number of rows that MSSQLReader will fetch at a time. With the default value of 0, this is controlled by SQL Server. You may set this manually: lower values will reduce memory usage, higher values will increase performance.

Fetch Transaction Metadata

Boolean

False

With the default value of False, the metadata array will not include TimeStamp or TxnID fields. If set to True, the metadata array will include TimeStamp and TxnID values (note that this will reduce performance). This must be set to True for Monitoring end-to-end lag (LEE) to produce accurate results.

Filter Transaction Boundaries

Boolean

True

With the default value of True, begin and commit transactions are filtered out. Set to False to include begin and commit transactions.

Integrated Security

Boolean

False

When set to the default value of False, the adapter will use SQL Server Authentication. Set to True to use Windows Authentication, in which case the adapter will authenticate as the user running the Forwarding Agent or Striim server on which it is deployed, and any settings in Username or Password will be ignored. See Choose an Authentication Mode for more information.

Password

encrypted password

the password specified for the username (see Encrypted passwords)

Polling Interval

Integer

5

This property controls how often the adapter reads from the source. The value is in seconds. By default, it checks the source for new data every five seconds. If there is new data, the adapter reads it and sends it to the adapter's output stream.

Start Position

String

EOF

With the default value EOF, reading starts at the end of the log file (that is, only new data is read). Alternatively, you may specify a specific time (in the Transact-SQL format TIME: YYYY-MM-DD hh:mm:ss:nnn, for example, TIME:2014-10-03 13:32:32.917) or SQL Server log sequence number (for example, LSN:0x00000A85000001B8002D) for the Begin operation of the transaction from which to start reading.

See also Switching from initial load to continuous replication.

Tables

String

The table(s) for which to return change data. Names must be specified as <schema name>.<table name> and are case-sensitive. (The server is specified by the IP address in connectionURL and the database by databaseName.)

You may specify multiple tables as a list separated by semicolons or with the following wildcards:

  • %: any series of characters

  • _: any single character

For example, my.% would read all tables in the my schema. The % wildcard is allowed only at the end of the string. For example, mydb.prefix% is valid, but mydb.%suffix is not.

At least one table must match the wildcard or start will fail with a "Could not find tables specified in the database" error. Temporary tables (which start with #) are ignored.

Transaction Support

Boolean

False

If set to True, MSSQLReader will preserve the order of operations within a transaction. This is required for Bidirectional replication.

Transaction support requires one of the cumulative SQL Server updates listed in FIX: The change table is ordered incorrectly for updated rows after you enable change data capture for a Microsoft SQL Server database. If you have not applied one of those updates, or are reading from SQL Server 2008, leave this at its default value of False.

Username

String

the login name for the user created as described in Microsoft SQL Server setup

MSJet properties

Note

Before you can use this adapter, the tasks described in SQL Server setup for MSJet must be completed.

MSJet must be deployed to a Forwarding Agent (or Striim server) running on the same Windows system as the SQL Server instance that hosts the databases to be read.

If the adapter is deployed to a Forwarding Agent, the Microsoft JDBC driver must be installed as described in Install the Microsoft JDBC Driver in a Forwarding Agent.

This adapter has the following properties:

property

type

default value

notes

Bidirectional Marker Table

String

When performing bidirectional replication, the fully qualified name of the marker table (see Bidirectional replication). This setting is case-sensitive.

CDDL Action

enum

Process

See Handling schema evolution.

CDDL Capture

Boolean

False

See Handling schema evolution.

Committed Transactions

String

True

By default, only committed transactions are read. Set to False to read both committed and uncommitted transactions.

Compression

Boolean

False

Set to True when the output of the source is the input of a DatabaseWriter target that writes to Cassandra {see Cassandra Writer).

Connection Retry Policy

String

timeOut=30, retryInterval=30, maxRetries=3

With the default setting:

  • Striim will wait for the database to respond to a connection request for 30 seconds (timeOut=30).

  • If the request times out, Striim will try again in 30 seconds (retryInterval=30).

  • If the request times out on the third retry (maxRetries=3), a ConnectionException will be logged and the application will stop.

Negative values are not supported.

Connection URL

String

IP address and port of Microsoft SQL server, separated by a colon: for example, 192.168.1.10:1433. Reading from a secondary database is not supported.

When connecting through an SSH tunnel (see Using an SSH tunnel to connect to a source or target), specify the IP address of the tunnel.

MSJet supports TLS 1.2 (see Transport Layer Security (TLS)). No configuration is required on Striim's side.

If the connection requires SSL, see Set up connection to MSSQLReader with SSL in Striim's knowledge base.

Database Name

String

the SQL Server database name

Excluded Tables

String

Data for any tables specified here will not be returned. For example, if Tables uses a wildcard, data from any tables specified here will be omitted. Multiple table names (separated by semicolons) and wildcards may be used exactly as for Tables.

Filter Transaction Boundaries

Boolean

True

With the default value of True, begin and commit transactions are filtered out. Set to False to include begin and commit transactions.

Integrated Security

Boolean

False

When set to the default value of False, the adapter will use SQL Server Authentication. Set to True to use Windows Authentication, in which case the adapter will authenticate as the user running the Forwarding Agent or Striim server on which it is deployed, and any settings in Username or Password will be ignored. See Choose an Authentication Mode for more information.

Password

encrypted password

The password specified for the username (see Encrypted passwords).

Quiesce Marker Table

String

QUIESCEMARKER

See Creating the QUIESCEMARKER table for MSJet. Modify the default value if the quiesce marker table is not in the schema associated with the user specified in the Username.

Send Before Image

Boolean

True

set to False to omit before data from output

Start Position

String

NOW

With the default value NOW, reading starts at the end of the log file (that is, only new data is read). Alternatively, you may specify a SQL Server log sequence number (for example, LSN:0x00000A85000001B8002D) for the Begin operation of the transaction from which to start reading.

If you are using schema evolution (see Handling schema evolution, set a Start Position only if you are sure that there have been no DDL changes after that point.Handling schema evolution

See also Switching from initial load to continuous replication.

Tables

String

The table(s) for which to return change data. Names must be specified as <schema name>.<table name> and are case-sensitive. (The server is specified by the IP address in connectionURL and the database by databaseName.)

You may specify multiple tables as a list separated by semicolons or with the following wildcards:

  • %: any series of characters

  • _: any single character

For example, my.% would read all tables in the my schema. The % wildcard is allowed only at the end of the string. For example, mydb.prefix% is valid, but mydb.%suffix is not.

At least one table must match the wildcard or start will fail with a "Could not find tables specified in the database" error. Temporary tables (which start with #) are ignored.

MSJet supports compressed tables and indexes (see Learn / SQL / SQL Server / Enable Compression on a Table or Index).

Transaction Buffer Spillover Size

String

1MB

When Transaction Buffer Type is Memory, this setting has no effect.

When Transaction Buffer Type is Disk, the amount of memory that Striim will use to hold each in-process transactions before buffering it to disk. You may specify the size in MB or GB.

Transaction Buffer Type

String

Disk

When Striim runs out of available Java heap space, the application will terminate. Typically this will happen when a transaction includes millions of INSERT, UPDATE, or DELETE events with a single COMMIT.

To avoid this problem, with the default setting of Disk, when a transaction exceeds the Transaction Buffer Spillover Size, Striim will buffer it to disk at the location specified by the Transaction Buffer Disk Location property, then process it when memory is available.

When the setting is Disk and recovery is enabled (see Recovering applications), after the application terminates or is stopped the buffer will be reset, and during recovery any previously buffered transactions will restart from the beginning.Recovering applications

To disable transaction buffering, set Transaction Buffer Type to Memory.

Username

String

If Integrated Security is True, leave blank. If Integrated Security is False, specify the login name for the SQL Server user.

SQL Server readers WAEvent fields

The output data type for MS SQL Reader and MSJet is WAEvent. The elements are:

metadata: a map including:

  • BeginLsn (MSJet only): LSN of Begin operation for the transaction

  • BeginTimestamp (MSJet only): timestamp of Begin operation for the transaction

  • CommitLsn (MSJet only): LSN of Commit operation for the transaction

  • CommitTimestamp (MSJet only): timestamp of Commit operation for the transaction

  • OperationName: INSERT, UPDATE, or DELETE

    MSJet only: When schema evolution is enabled, OperationName for DDL events will be Alter, AlterColumns, Create, or Drop. This metadata is reserved for internal use by Striim and subject to change, so should not be used in CQs, open processors, or custom Java functions.

  • PartitionId (MSJet only): the partition from which the data was read

  • PK_UPDATE:

    • MS SQL Reader: for UPDATE only, true if the primary key value was changed, otherwise false

    • MSJet: field not included (see limitations in SQL Server)SQL Server

  • SEQUENCE: LSN of the operation

  • TableName: fully qualified name of the table . It is present but null for key-sequenced files and key-sequenced tables that have a user-defined primary key.

  • TimeStamp (MS SQL Reader only): timestamp from the CDC log. By default, values are included only for the first record of a new transaction (for more details, see FetchTransactionMetadata in MSSQLReader properties).

  • TransactionName: name of the transaction

  • TxnID: transaction ID. When using MS SQL Reader, by default, values are included only for the first record of a new transaction (for more details, see FetchTransactionMetadata in MSSQLReader properties).

To retrieve the values for these fields, use the META() function. See Parsing the fields of WAEvent for CDC readers.

data: an array of fields, numbered from 0, containing:

  • for an INSERT or DELETE operation, the values that were inserted or deleted

  • for an UPDATE, the values after the operation was completed

To retrieve the values for these fields, use SELECT ... (DATA[]). See Parsing the fields of WAEvent for CDC readers.

before (for UPDATE operations only): the same format as data, but containing the values as they were prior to the UPDATE operation

dataPresenceBitMap, beforePresenceBitMap, and typeUUID are reserved and should be ignored.

MS SQL Reader simple application

The following application will write change data for the specified table to SysOut. Replace the Username and Password values with the credentials for the account you created for Striim (see Microsoft SQL Server setup), dbo.mytable with the name of the table to be read, and watestdb with the name of the database containing the table.

CREATE APPLICATION SQLServerTest;
CREATE SOURCE SQLServerCDCIn USING MSSqlReader (
  Username:'wauser',
  Password:'password',
  DatabaseName:'watestdb',
  ConnectionURL:'192.168.1.10:1433',
  Tables:'dbo.mytable'
) 
OUTPUT TO SQLServerCDCStream;
CREATE TARGET SQLServerCDCOut
  USING SysOut(name:SQLServerCDC)
  INPUT FROM SQLServerCDCStream;
END APPLICATION SQLServerTest;

MSSQLReader example output

MSSQLReader's output type is WAEvent. See WAEvent contents for change data and SQL Server readers WAEvent fields.

The following are examples of WAEvents emitted by MSSQLReader for various operation types. They all use the following table:

CREATE TABLE POSAUTHORIZATIONS (BUSINESS_NAME varchar(30),
  MERCHANT_ID varchar(100),
  PRIMARY_ACCOUNT bigint,
  POS bigint,
  CODE varchar(20),
  EXP char(4),
  CURRENCY_CODE char(3),
  AUTH_AMOUNT decimal(10,3),
  TERMINAL_ID bigint,
  ZIP integer,
  CITY varchar(20));
GO

INSERT

If you performed the following INSERT on the table:

INSERT INTO POSAUTHORIZATIONS VALUES(
  'COMPANY 1',
  'D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu',
  6705362103919221351,
  0,
  '20130309113025',
  '0916',
  'USD',
  2.20,
  5150279519809946,
  41363,
  'Quicksand');
GO

The WAEvent for that INSERT would be:

data: ["COMPANY 1","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD","2.200",5150279519809946,41363,"Quicksand"]
metadata: {"TimeStamp":0,"TxnID":"","SEQUENCE":"0000002800000171001C","PK_UPDATE":"false",
"TableName":"dbo.POSAUTHORIZATIONS","OperationName":"INSERT"}
before: null

UPDATE

If you performed the following UPDATE on the table:

UPDATE POSAUTHORIZATIONS SET BUSINESS_NAME = 'COMPANY 5A' where pos=0;
GO

The WAEvent for that UPDATE for the row created by the INSERT above would be:

data: ["COMPANY 5A","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD","2.200",5150279519809946,41363,"Quicksand"]
metadata: {"TimeStamp":0,"TxnID":"","SEQUENCE":"00000028000001BC0002","PK_UPDATE":"false",
"TableName":"dbo.POSAUTHORIZATIONS","OperationName":"UPDATE"}
before: ["COMPANY 1","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD","2.200",5150279519809946,41363,"Quicksand"]

DELETE

If you performed the following DELETE on the table:

DELETE from POSAUTHORIZATIONS where pos=0;
GO

The WAEvent for that DELETE for the row affected by the INSERT above would be:

data: ["COMPANY 5A","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu",6705362103919221351,0,"20130309113025",
"0916","USD","2.200",5150279519809946,41363,"Quicksand"]
metadata: {"TimeStamp":0,"TxnID":"","SEQUENCE":"00000028000001DE0002","PK_UPDATE":"false",
"TableName":"dbo.POSAUTHORIZATIONS","OperationName":"DELETE"}
before: null

Note that the contents of data and before are reversed from what you might expect for a DELETE operation. This simplifies programming since you can get data for INSERT, UPDATE, and DELETE operations using only the data field.

MSJet example output

MSJet's output type is WAEvent. See WAEvent contents for change data and SQL Server readers WAEvent fields.

The following commands are examples of various common operation types.

create table employee(id int,name char(40),address char(40));

begin transaction t1;

--insert
insert into dbo.employee values('1','Maha 5','Chennai');

--update
update dbo.employee set name='Maha' where ID=1;

--primary key update
update dbo.employee set ID=10 where ID=1;

--delete
delete from dbo.employee where id=10;

commit transaction t1;

The WAEvent output resulting from those commands will be similar to:

Data: WAEvent{
data: [ ] 
  metadata: {"CommitLsn":"0x00000028:00000450:0040","TableName":null,"TxnID":"0000.000003ea",
    "OperationName":"BEGIN","SEQUENCE":"0x00000028:00000450:0020","CommitTimestamp":1633414675857,
    "BeginTimestamp":1633414675823,"BeginLsn":"0x00000028:00000450:0020","TransactionName":"t1"}
  userdata: null
  before: null
  dataPresenceBitMap: "AA=="
  beforePresenceBitMap: "AA=="
  typeUUID: null
};
Data: WAEvent{
  data: ["1","Maha 5                                  ","Chennai                                 "]
  metadata: {"CommitLsn":"0x00000028:00000450:0040","TableName":"dbo.employee","TxnID":"0000.000003ea",
    "OperationName":"INSERT","SEQUENCE":"0x00000028:00000450:003a","CommitTimestamp":1633414675857,"
    PartitionId":72057594043170816,"BeginTimestamp":1633414675823,"BeginLsn":"0x00000028:00000450:0020",
    "TransactionName":"t1"}
  userdata: null
  before: null
  dataPresenceBitMap: "Bw=="
  beforePresenceBitMap: "AA=="
  typeUUID: {"uuidstring":"01ec25a3-fb2f-70d1-901a-001c42ca1a64"}
};
Data: WAEvent{
  data: ["1","Maha                                    ","Chennai                                 "]
  metadata: {"CommitLsn":"0x00000028:00000450:0040","TableName":"dbo.employee","TxnID":"0000.000003ea",
    "OperationName":"UPDATE","SEQUENCE":"0x00000028:00000450:003c","CommitTimestamp":1633414675857,
    "PartitionId":72057594043170816,"BeginTimestamp":1633414675823,"BeginLsn":"0x00000028:00000450:0020",
    "TransactionName":"t1"}
  userdata: null
  before: [1,"Maha 5                                  ","Chennai                                 "]
  dataPresenceBitMap: "Bw=="
  beforePresenceBitMap: "Bw=="
  typeUUID: {"uuidstring":"01ec25a3-fb2f-70d1-901a-001c42ca1a64"}
};
Data: WAEvent{
  data: ["10","Maha                                    ","Chennai                                 "]
  metadata: {"CommitLsn":"0x00000028:00000450:0040","TableName":"dbo.employee","TxnID":"0000.000003ea",
    "OperationName":"UPDATE","SEQUENCE":"0x00000028:00000450:003d","CommitTimestamp":1633414675857,
    "PartitionId":72057594043170816,"BeginTimestamp":1633414675823,"BeginLsn":"0x00000028:00000450:0020",
    "TransactionName":"t1"}
  userdata: null
  before: [1,"Maha                                    ","Chennai                                 "]
  dataPresenceBitMap: "Bw=="
  beforePresenceBitMap: "Bw=="
  typeUUID: {"uuidstring":"01ec25a3-fb2f-70d1-901a-001c42ca1a64"}
};
Data: WAEvent{
  data: ["10","Maha                                    ","Chennai                                 "]
  metadata: {"CommitLsn":"0x00000028:00000450:0040","TableName":"dbo.employee","TxnID":"0000.000003ea",
    "OperationName":"DELETE","SEQUENCE":"0x00000028:00000450:003e","CommitTimestamp":1633414675857,"
    PartitionId":72057594043170816,"BeginTimestamp":1633414675823,"BeginLsn":"0x00000028:00000450:0020","
    TransactionName":"t1"}
  userdata: null
  before: null
  dataPresenceBitMap: "Bw=="
  beforePresenceBitMap: "AA=="
  typeUUID: {"uuidstring":"01ec25a3-fb2f-70d1-901a-001c42ca1a64"}
};
Data: WAEvent{
data: [ ] 
  metadata: {"CommitLsn":"0x00000028:00000450:0040","TableName":null,"TxnID":"0000.000003ea",
    "OperationName":"COMMIT","SEQUENCE":"0x00000028:00000450:0040","CommitTimestamp":1633414675857,
    "BeginTimestamp":1633414675823,"BeginLsn":"0x00000028:00000450:0020","TransactionName":"t1"}
  userdata: null
  before: null
  dataPresenceBitMap: "AA=="
  beforePresenceBitMap: "AA=="
  typeUUID: null
};

SQL Server readers data type support and correspondence

SQL Server type

MS SQL Reader TQL type

MSJet TQL type

notes

bigint

long

integer

binary

byte[]

byte[]

not included in before array for UPDATE or data array for DELETE operations: see cautionary note below

when reading from Azure SQL Database, supported only when values are less than 64 kb

bit

string

boolean

char

string

string

date

string

string

datetime

string

string

datetime2

string

string

datetimeoffset

string

not supported in this release (known issue DEV-35885)

decimal

string

string

float

double

string

geometry

not supported

not supported

image

byte[]

byte[]

not included in before array for UPDATE or data array for DELETE operations: see cautionary note below

int

integer

integer

money

string

string

nchar

string

string

Known issue DEV-36005: data may be incorrect when reading from a compressed table.

ntext

string

string

not included in before array for UPDATE or data array for DELETE operations: see cautionary note below

Known issue DEV-36005: data may be incorrect when reading from a compressed table.

numeric

string

string

nvarchar

string

string

Known issue DEV-36005: data may be incorrect when reading from a compressed table.

nvarchar(max)

string

string

included in before array for UPDATE operations only if value is changed by the update

real

float

string

rowversion

byte[]

byte[]

 

smalldatetime

string

string

smallint

short

short

smallmoney

string

string

sqlvariant

not supported

not supported

Columns of this type will have value null in WAEvent

text

string

string

not included in before array for UPDATE or data array for DELETE operations: see cautionary note below

time

string

string

timestamp

byte[]

byte[]

tinyint

short

short

udt

string

string

uniqueidentifier

string

string

varbinary

byte[]

byte

not included in before array for UPDATE or data array for DELETE operations: see cautionary note below

when reading from Azure SQL Database, supported only when values are less than 64 kb

varbinary(max)

byte[]

byte[]

not included in before array for UPDATE or data array for DELETE operations: see cautionary note below

when reading from Azure SQL Database, supported only when values are less than 64 kb

varchar

string

string

varchar(max)

string

string

included in before array for UPDATE operations only if value is changed by the update

xml

string

not supported

MS SQL Reader: included in before array for UPDATE operations only if value is changed by the update

MSJet: columns of this type type will have value null in in WAEvent

Caution

When all tables being read have primary keys and none of those primary key columns is of type binary, image, ntext, text, varbinary, or varbinary(max), you will not encounter the following issue.

When replicating MSSQLReader or MSJet output using DatabaseWriter, if one or more of a table's primary key columns is of type binary, image, ntext, text, varbinary, or varbinary(max), or if a table has no primary key and one more columns of those types, UPDATE or DELETE operations may erroneously be replicated to more than one row. This may result in additional errors when subsequent operations try to update or delete the missing or incorrectly updated rows.

MSJet limitations

  • The Forwarding Agent (or Striim server) on which MSJet is deployed must be running on the same Windows system as the SQL Server instance that hosts the databases to be read.

  • Each Striim server or Forwarding Agent can run only a single MSJet source. If you need multiple MSJet sources, deploy each on a different server or Forwarding Agent.

  • Tables with XML columns are not supported.

  • Reading from secondary databases is not supported.

  • Reading from AG listeners is not supported.

  • Reading from backups is supported only if they are accessible only in the location where they were taken.

  • Reading from encrypted or compressed databases or backups is not supported. These would have to be uncompressed and unencrypted for Striim to read.

  • Debug messages for the Windows-native portion of the adapter may appear in striim/logs/striim_mssqlnativereader.log rather than in cstriim.server.log.

  • If utilizing both replication and CDC, you must continue to keep CDC jobs enabled.