Configure an Azure SQL database for CDC

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

To configure an Azure SQL database:

1. Enable change data capture (CDC) for your source Azure SQL database. To do it, connect to the database using Azure Data Studio or SQL Server Management Studio and run the following command:

   EXECsys.sp_cdc_enable_db;
   GO
   

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

   EXECsys.sp_cdc_enable_table
   @source_schema=N'SCHEMA_NAME',
   @source_name=N'TABLE_NAME',
   @role_name=NULL
   GO
   

   Replace the following:

   • 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

3. 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:

   ALTERDATABASEDATABASE_NAMESETALLOW_SNAPSHOT_ISOLATIONON;
   

   Replace DATABASE_NAME with the name of you database.

4. Create a Datastream user:

   1. Connect to the master database and create a login:

      USEmaster;
      CREATELOGINYOUR_LOGINWITHPASSWORD='PASSWORD';
      

   2. Connect to the source database and create a user for your login:

      USEDATABASE_NAME
      CREATEUSERUSER_NAMEFORLOGINYOUR_LOGIN;
      

   3. Assign the db_owner and db_denydatawriter roles to your user:

      EXECsp_addrolemember'db_owner','USER_NAME';
      EXECsp_addrolemember'db_denydatawriter','USER_NAME';
      

   4. Grant the VIEW DATABASE STATE permission to your user:

      GRANTVIEWDATABASESTATETOUSER_NAME;
      

What's next

• Learn more about how Datastream works with SQL Server sources.