Configure a self-managed SQL Server database for CDC

This page describes how to configure change data capture (CDC) to stream data from a self-managed SQL Server database to a supported destination, such as BigQuery or Cloud Storage.

1. Ensure your database is using the full recovery model. To check and set the recovery model, connect to the database and run the following command at a SQL prompt or in a terminal:

   USE master
   GO
   ALTER DATABASE [DATABASE_NAME] SET RECOVERY FULL
   GO
   

   Replace DATABASE_NAME with the name of your source database.

2. Enable CDC for your source database. To do it, connect to the database and run the following command at a SQL prompt or in a terminal:

   USE [DATABASE_NAME]
   GO
   EXEC sys.sp_cdc_enable_db
   GO
   

   Replace DATABASE_NAME with the name of your source database.

3. Enable CDC on the tables for which you need to capture changes:

   USE [DATABASE_NAME]
   EXEC sys.sp_cdc_enable_table
   @source_schema = N'SCHEMA_NAME',
   @source_name = N'TABLE_NAME',
   @role_name = NULL
   GO
   

   Replace the following:

   • DATABASE_NAME: the name of your source database
   • SCHEMA_NAME: the name of the schema to which the tables belong
   • TABLE_NAME: the name of the table for which you want to enable CDC

4. Start the SQL Server Agent and make sure it's running at all times. If the SQL Server Agent remains down for an extended period, the logs might get truncated, leading to a permanent loss of the change data that wasn't read by Datastream.

   For information about running the SQL Server Agent, see Start, stop, or restart an instance of the SQL Server Agent.

5. Enable snapshot isolation.

   When you backfill data from your SQL Server database, it's important to ensure consistent snapshots. If you don't apply the settings described in this section, changes made to the database during the backfill process might lead to duplicates or incorrect results. Applying snapshot isolation settings is required if your stream includes tables without primary keys.

   Enabling snapshot isolation creates a temporary view of your database at the start of the backfill process. This ensures that the data being copied remains consistent, even if other users are making changes to the live tables at the same time. Enabling snapshot isolation might have a slight performance impact, but it's essential for reliable data extraction.

   To enable snapshot isolation:

   1. Connect to your database using a SQL Server client.
   2. Run the following command:

   ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
   

   Replace DATABASE_NAME with the name of you database.

6. Create a Datastream user:

   1. Connect to the source database and enter the following command:

      USE DATABASE_NAME;
      

   2. Create a login to use while setting up the connection profile in Datastream.

      CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
      

   3. Create a user:

      CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
      

   4. Assign the db_datareader role to them:

      EXEC sp_addrolemember 'db_datareader', 'USER_NAME';
      

   5. Grant the VIEW DATABASE STATE permission to them:

      GRANT VIEW DATABASE STATE TO USER_NAME;
      

   6. Add this user to the master database:

      USE master;
      CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
      

Additional steps required for the transaction logs CDC method

The steps described in this section are only required when you configure your source SQL Server database for use with the transaction logs CDC method.

1. Connect to the source database and assign the db_owner and db_denydatawriter roles to your user:

   USE DATABASE_NAME;
   EXEC sp_addrolemember 'db_owner', 'USER_NAME';
   EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';
   

2. Grant SELECT permissions for the sys.fn_dblog function.

   USE master;
   GRANT SELECT ON sys.fn_dblog TO USER_NAME;
   

3. Add your user to the msdb database and assign the following permissions to them:

   USE msdb;
   CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
   GRANT SELECT ON dbo.sysjobs TO USER_NAME;
   

4. Assign the following permissions to your user in the master database:

     USE master;
     GRANT VIEW SERVER STATE TO