Striim 4.1.0 documentation

MS SQL Reader properties

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

The JDBC driver for SQL Server 2012 and later is bundled with the Striim server. When reading from SQL Server 2008 or when running an MS SQL Reader source with the Forwarding Agent, the driver must be installed as described in Install the Microsoft JDBC driver.

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

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

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.

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

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

Username

String

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

In this section: