Skip to main content

Striim for Snowflake Documentation

Set up your SQL Server source

You must perform all setup tasks appropriate for your source environment before you can create a pipeline. If any of these tasks are not complete, the corresponding prerequisite checks will fail.

Striim reads SQL Server change data using the native SQL Server Agent utility. For more information, see Learn > SQL > SQL Server > About Change Data Capture (SQL Server).

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 it is not running already, start SQL Server Agent (see Start, Stop, or Pause the SQL Server Agent Service; if the agent is disabled, see Agent XPs Server Configuration Option).

  2. Enable change data capture on each database to be read using the following commands (for more information, see Learn / SQL / SQL Server / Enable and disable change data capture):

    • 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

  3. Create a SQL Server user for use by Striim. This user must use the SQL Server authentication mode, which must be enabled in SQL Server. (If only Windows authentication mode is enabled, Striim will not be able to connect to SQL Server.)

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

In this section: