Striim

Welcome to the Striim Help Center and Community Site

3.10.3 SQL Server

Follow

Striim's MS SQL Reader supports Microsoft SQL Server versions 2008, 2012, 2014, 2016, and 2017 and Azure SQL Database managed instances. Since Azure SQL Database single databases and elastic pools do not support change data capture (see Azure SQL Database Features), use Incremental Batch Reader or Database Reader to read from them.

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.

SQL Server setup

Striim 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:

  1. If SQL Server is running 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" on msdn.microsoft.com). This service must be running for MSSQLReader 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 the databases using the following commands:

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

  5. Grant the MSSQLReader 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

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.

MS SQL Reader properties

Before you can use this adapter, the tasks described in Microsoft SQL Server setup must be completed. If reading from SQL Server 2008, see also Installing 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 a new 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 an MSSQLReader 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 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

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

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

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

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

String

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

MS SQL Reader WAEvent fields

The output data type for MSSQLReader is WAEvent. The elements are:

metadata: a map including:

  • OperationName: INSERT, UPDATE, or DELETE

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

  • TxnID: transaction ID

  • TimeStamp: timestamp from the CDC log

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

By default, values for TxnID and TimeStamp 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 for general information.

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.

MS SQL Reader data type support and correspondence

MSSQL type

TQL type

notes

bigint

long

binary

byte[]

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

bit

string

char

string

date

string

datetime

string

datetime2

string

datetimeoffset

string

decimal

string

float

double

image

byte[]

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

int

int

money

string

nchar

string

ntext

string

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

numeric

string

nvarchar

string

nvarchar(max)

string

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

real

float

smalldatetime

string

smallint

short

smallmoney

string

text

string

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

time

string

timestamp

byte[]

tinyint

short

udt

string

uniqueidentifier

string

varbinary

byte[]

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

varbinary(max)

byte[]

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

varchar

string

varchar(max)

string

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

xml

string

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

Caution

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

When replicating MSSQLReader 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.

3.10.3
Was this article helpful?
0 out of 0 found this helpful

Comments