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 reads logical changes directly from SQL Server's transaction logs.

Differences between MSJet and MS SQL Reader:

MSJet does not require SQL Server's CDC change tables.
MSJet automatically enables CDC on a per-table basis.
MSJet supports TLS.
MSJet supports reading from replication logs (enabling CDC is not required if replication publisher is enabled).
MS SQL Reader supports reading from a secondary database in an Always On availability group.

MSJet supports Microsoft SQL Server 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.

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).

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:

If SQL Server is running in a virtual machine in Azure, follow the instructions in Configuring an Azure virtual machine running SQL Server.
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   
```
Enable change data capture on each database to be read using the following commands:
- 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
```
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.)
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 .

Go to the virtual machine's Overview tab.
If there is no public IP address, enable it.
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.
Go to the virtual machine's SQL Server configuration tab.
Set SQL connectivity to Public (Internet).
Enable SQL Authentication and and specify the login name and password MSSQLReader will use to connect to SQL Server.
Make note of the Port setting, as you will need it to configure MSSQLReader.
Go to the Overview tab and click Connect.
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).
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
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:

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).
Grant that user local Administrator privileges on the SQL Server host.
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.
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.
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
```
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.
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.
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>
```
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)).

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.

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).
Grant that user local Administrator privileges on the SQL Server host.
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.
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.
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.
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>
```
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`. 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		If the `Tables` string contains wildcards, any tables specified here will be excluded.
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	time to wait between fetches; in seconds
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) or view(s) in 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 and views 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. 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		If the `Tables` string contains wildcards, any tables specified here will be excluded.
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	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) or view(s) in 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 and views 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. Tables with any server-side compression (such as DATA_COMPRESSION = PAGE) cannot be read.
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	string
decimal	string	string
float	double	string
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
ntext	string	string	not included in `before` array for UPDATE or `data` array for DELETE operations: see cautionary note below
numeric	string	string
nvarchar	string	string
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	unsupported	unsupported	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	unsupported	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.
Page compression is supported but not row compression.
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 backups is not supported. These backups 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.

In this section:

Striim Cloud 4.1.2 documentation

SQL Server

SQL Server setup

SQL Server setup for MS SQL Reader

Configuring an Azure virtual machine running SQL Server

SQL Server setup for MSJet

Note

SQL Server setup for MSJet when transactional replication is already running

Creating the QUIESCEMARKER table for MSJet

MS SQL Reader properties

Note

MSJet properties

Note

SQL Server readers WAEvent fields

MS SQL Reader simple application

MSSQLReader example output

INSERT

UPDATE

DELETE

MSJet example output

SQL Server readers data type support and correspondence

Caution

MSJet limitations

Search results