Striim 3.9.8 documentation

MS SQL Reader properties

Before you can use this adapter, the tasks described in Microsoft SQL Server setup and in Installing the Microsoft JDBC driver must be completed.

Note

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 a new application using a template for details.

The adapter properties are:

property

type

default value

notes

Auto Disable Table CDC

java.lang. 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

java.lang. String

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

Compression

java.lang. Boolean

False

Set to True when the output of an MSSQLReader source is the input of a DatabaseWriter target that writes to Cassandra {see Notes on writing to Cassandra with DatabaseWriter).

Connection Pool Size

java.lang. 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

java.lang. 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

java.lang. String

IP address and port of Microsoft SQL server, separated by a colon: for example, 192.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.

You may use Active Directory authentication with Azure SQL Database (see Supporting Active Directory authentication for Azure) or, when Striim or a Forwarding Agent is running in Windows, with SQL Server (see Supporting Active Directory authentication for SQL Server).

Database Name

java. lang. String

the SQL Server database name

Excluded Tables

java.lang. String

If the Tables string contains wildcards, any tables specified here will be excluded.

Fetch Size

java.lang. 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

java.lang. Boolean

False

With the default falue of False, the metadata array will include TimeStamp and TxnID fields only when the TxnID changes. If set to True, the metadata array will include TimeStamp and TxnID values for every record (note that this will reduce performance).

Filter Transaction Boundaries

java.lang. Boolean

True

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

Password

com. webaction. security. Password

the password specified for the username (see Encrypted passwords)

Start Position

java.lang. 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 (for example, TIME:2014-10-03 12:32:32.917) or SQL Server log sequence number (for example, LSN:0x00000A85000001B8002D) from which to start reading.

Tables

java.lang. 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.) Tables must have a primary key.

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. At least one table must match the wildcard or start will fail with a "Could not find tables specifed in the database" error.

Transaction Support

java.lang. 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.

Transaction support also requires the Microsoft JDBC Driver 7.2 or later (see Installing the Microsoft JDBC driver).

Username

java.lang. String

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

In this section: