Striim 3.10.3 documentation

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.

In this section: