Striim 4.0.4 documentation

SQL Server setup for MS SQL Reader

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

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 This service must be running for MS SQL Reader 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 
    ( 2522 : 
    Could not position at EOF, its equivalent LSN is NULL   
  3. Enable change data capture on each database to be read using the following commands:

    • for Amazon RDS for SQL Server:

      EXEC msdb.dbo.rds_cdc_enable_db '<database name>';
    • for all others:

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

  5. Grant the MS SQL Reader 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
EXEC sp_addrolemember @rolename=db_owner, @membername=striim

MS SQL Reader 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.