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:

    EXEC  
   sys.sp_cdc_enable_db ; 
   GO 
   

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

    EXEC  
   sys.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, especially for 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.

4. Create a Datastream user:

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

        USE 
        
       master 
       ; 
       CREATE 
        
       LOGIN 
        
        YOUR_LOGIN 
       
        
       WITH 
        
       PASSWORD 
        
       = 
        
       ' PASSWORD 
      ' 
       ; 
       
      

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

        USE 
        
        DATABASE_NAME 
       
       CREATE 
        
       USER 
        
        USER_NAME 
       
        
       FOR 
        
       LOGIN 
        
        YOUR_LOGIN 
       
       ; 
       
      

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

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

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

        GRANT 
        
       VIEW 
        
       DATABASE 
        
       STATE 
        
       TO 
        
        USER_NAME 
       
       ; 
       
      

What's next

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