Striim Platform 5.0 documentation

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) and Unsupported SQL Server Features for In-Memory OLTP.

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:

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.