SQL Server programmer's reference

MS SQL Reader properties

Note

Before using this adapter, you must complete the tasks described in Configuring SQL Server to use MS SQL Reader.

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

Striim provides wizards for creating applications that read from SQL Server and write to various targets. SeeCreating an application using a wizard 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
CDC Role Name	String	STRIIM_READER	The name of the role Striim will use when it enables CDC at the table level. If this role does not exist it will be created automatically. See Learn / SQL / SQL Server / sys.sp_cdc_enable_table (Transact-SQL) for more information.
Compression	Boolean	False	Not supported by Striim for ClickHouse.
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 Profile Name	String		Appears in Flow Designer only when Use Connection Profile is True. See Connection profiles.
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. The `encrypt=strict` parameter is supported only when the source is SQL Server 2022 in Windows 11 or Windows Server 2022 and Cumulative Update 1 for SQL Server 2022 has been applied. Known issue DEV-46366: The connection URL does not support connecting to a specific instance. Striim will always connect to the default instance.
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. This must be set to False to enable Preserve Source Transaction Boundary in a downstream writer.
Ignorable Exception	String		Set to DELAYED_DURABILITY_ALLOWED_OR_FORCED_EXCEPTION if reading from SQL Server sources with delayed transaction durability enabled (see Learn > SQL > SQL Server > Control Transaction Durability).
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.
Partial Record Policy	String		This property enables enable fetching column values from the database tables when the values are partially available or not available in the database transaction log. It allows the adapter to query and fetch supported/unsupported columns from the source database as needed. For example: `PARTIALRECORDPOLICY : '{ "Tables" : "TPCC.Orders (orderid, image);" }'` `PARTIALRECORDPOLICY : '{"Tables":"TPCC.Orders (orderid, image);", "LookupUsing" : "PKEY"}'` The PKEY should be used if you need to fetch the record based on the primary key. If you do not specify a `LookupUsing` parameter, the Partial Record Policy automatically takes the PKEY into account. See Using the Partial Record Policy with CDC Readers for more information.
Password	encrypted password		the password specified for the username
Polling Interval	Integer	5	This property sets the maximum number of seconds that will elapse between checks for new data in SQL Server's change tables. Each table has its own separate interval. So long as there is new data, the adapter will keep reading continuously. When there is no new data, to avoid too many empty cycles the adapter will introduce a progressive delay between read attempts. The delay will increase on each retry with no new data until the polling interval is reached.
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 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 of SQL Server sources.
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.) Do not modify this property when CDDL Capture is True or recovery is enabled for the application. You may specify multiple tables as a list separated by semicolons or with the `%` wildcard. 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. Specifying a table with more than 1018 columns will cause the application to halt with a SQL Server error that a column "exceeds the maximum of 1024 columns" (because additional columns are added to SQL Server's corresponding change table). Specifying a table with fixed-sized columns that total more than 8022 bytes will cause the application to terminate with a "Could not update the metadata ..." error. This is due to a limitation in SQL Server's change tables. Fixed-size column types include bigint, binary, bit, char, date, datetime, datetime2, datetimeoffset, decimal, float, int, money, nchar, numeric, real, smalldatetime, smallint, smallmoney, time, and tinyint.
Transaction Support	Boolean	False	If set to True, MSSQLReader will preserve the order of operations within a transaction. This is required to support Preserve Source Transaction Boundary in Database Writer. If you set Transaction Support to True, set Filter Transaction Boundaries to False. 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.
Use Connection Profile	Boolean	False	Set to True to use a connection profile instead of specifying the connection properties in the adapter properties. See Connection profiles.
Username	String		the login name for the user created as described in Configuring SQL Server to use MS SQL Reader

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
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 Configuring SQL Server to use MS SQL Reader), 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.

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
geography	not supported	not supported
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
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	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
vector	java.lang.Byte[]	java.lang.Byte[]	Supported in MSJet only with explicit PRP (PartialRecordPolicy (`PartialRecordPolicy: '{ \"Tables\" : \"<schema name>.<table name> (vec);\" }'`).
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.

Target data type support & mapping for SQL Server sources

The table below details how Striim maps the data types of a SQL Server source to ClickHouse data types when you create an application using a wizard with Auto Schema Creation, perform an initial load using Database Reader with Create Schema enabled, or run the schema conversion utility, or when Striim schema evolution creates or alters target tables.

SQL Server source data types rowversion and udt are not supported.

For fixed-length data types, Striim interprets the length parameter as one character = one byte, which can result in errors if the data uses multi-byte characters. To avoid this issue, manually increase the size of the data type in the target, or change the target data type to blob or clob.

SQL Server Data Type	ClickHouse
BIGINT	Int64
BIGINT IDENTITY(p,s)	Int64, if 10 <= (p) <= 19
BINARY(p)	String
BIT	Bool
CHAR	String
CHAR(p)	String
DATE	Date32
DATETIME	DateTime64(s)
DATETIME2	DateTime64(s)
DATETIME2(p)	DateTime64(s), if (s) <= 9
DATETIMEOFFSET	DateTime64(s)
DATETIMEOFFSET(p)	DateTime64(s), if (s) <= 9
DECIMAL	Decimal(p, s)
DECIMAL(p,0)	Decimal(p, s), if (p) <= 76, if (s) <= 76
DECIMAL(p,s)	Decimal(p, s), if (p) <= 76, if (s) <= 76 String, if (s) > 76* String, if (p,s) > 76*
FLOAT	Float64
FLOAT(p)	String, if (p) > 53* Float64, if 24 <= (p) <= 53
GEOGRAPHY	Not supported
GEOMETRY	Not supported
HIERARCHYID	Not supported
IMAGE	String
INT	Int32
INT IDENTITY(p,s)	Int64, if 10 <= (p) <= 19 Int32, if 5 <= (p) <= 10
MONEY	String
NCHAR	String
NCHAR(p)	String
NTEXT	String
NUMERIC	Decimal(p, s)
NUMERIC IDENTITY(p,s)	Decimal(p, s), if (p) <= 76, if (s) <= 76 String, if (s) > 76* String, if (p,s) > 76*
NUMERIC(p,0)	Decimal(p, s), if (p) <= 76, if (s) <= 76
NUMERIC(p,s)	Decimal(p, s), if (p) <= 76, if (s) <= 76 String, if (s) > 76* String, if (p,s) > 76*
NVARCHAR	String
NVARCHAR(max)	String
NVARCHAR(p)	String
REAL	Float32
REAL(p)	Float32, if (p) <= 24
SMALLDATETIME	DateTime64(s)
SMALLINT	Int16
SMALLINT IDENTITY(p,s)	Int16, if 3 <= (p) <= 5 Int32, if 5 <= (p) <= 10
SMALLMONEY	String
SQL_VARIANT	Not supported
TEXT	String
TIME	Time64(s)
TIME(p)	Time64(s), if (s) <= 9
TIMESTAMP	String
TINYINT	UInt8
TINYINT IDENTITY(p,s)	Int8, if (p) <= 3 Int16, if 3 <= (p) <= 5
UNIQUEIDENTIFIER	String
VARBINARY	String
VARBINARY(max)	String
VARBINARY(p)	String
VARCHAR	String
VARCHAR(max)	String
VARCHAR(p)	String
XML	String

*When using the schema conversion utility, these mappings appear in converted_tables_with_striim_intelligence.sql.