This page describes how to configure change data capture (CDC) tostream data from a Cloud SQL for SQL Server database to asupported destination,such as BigQuery or Cloud Storage.

1. Connect to your Cloud SQL instance. You can do it using thegcloud sql connect command in theCloud Shell prompt.

2. Enable CDC on the database by running the following command:

   EXECmsdb.dbo.gcloudsql_cdc_enable_db'DATABASE_NAME'

   ReplaceDATABASE_NAME with the name of your source database.

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

   USE[DATABASE_NAME]EXECsys.sp_cdc_enable_table@source_schema=N'SCHEMA_NAME',@source_name=N'TABLE_NAME',@role_name=NULLGO

   Note: You need to run the command for each table for which you want to enable CDC.

4. Enable snapshot isolation.

   When you backfill data from your SQL Server database, it's important to ensureconsistent snapshots. If you don't apply the settings described in thissection, changes made to the database during the backfill process might lead toduplicates or incorrect results, especially for tables without primary keys.

   Enabling snapshot isolation creates a temporary view of your database at the startof 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'sessential 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;

   ReplaceDATABASE_NAME with the name of you database.

5. Create a Datastream user:

   1. In the Google Cloud console, go to theCloud SQL Instances page.

      Go to Cloud SQL Instances

   2. Create a user and assignthedb_owner anddb_denydatawriter roles to them:

   CREATEUSERUSER_NAMEFORLOGINYOUR_LOGIN;

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

Additional steps required for the transaction logs CDC method

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

1. Set the polling interval for which you want the changes to be available onyour source.

   USE[DATABASE_NAME]EXECsys.sp_cdc_change_job@job_type='capture',@pollinginterval=86399EXECsp_cdc_stop_job'capture'EXECsp_cdc_start_job'capture'

   The@pollinginterval parameter is measured in seconds with a recommended valueset to86399. This means that the transaction log retains changes for 86,399seconds (one day). Executing thesp_cdc_start_job 'capture procedure initiatesthe settings.

2. Set up a log truncation safeguard.

   To make sure that the CDC reader has enough time to read the logs while allowinglog truncation to prevent using up the storage space, you can set up a log truncationsafeguard:

   1. Connect to the database using a SQL Server client.

   2. Create a dummy table in the database:

      USE[DATABASE_NAME];CREATETABLEdbo.gcp_datastream_truncation_safeguard([id]INTIDENTITY(1,1)PRIMARYKEY,CreatedDateDATETIMEDEFAULTGETDATE(),[char_column]CHAR(8));

   3. Create a stored procedure that runs an active transaction for a period thatyou specify to prevent log truncation:

      CREATEPROCEDURE[dbo].[DatastreamLogTruncationSafeguard]@transaction_logs_retention_timeINTASBEGIN-- Start a new transactionBEGINTRANSACTION;INSERTINTOdbo.gcp_datastream_truncation_safeguard(char_column)VALUES('a')DECLARE@formatted_timeVARCHAR(5)SET@formatted_time=CONVERT(VARCHAR(5),DATEADD(MINUTE,@transaction_logs_retention_time,0),108);-- Wait for X minutes before ending the transactionWAITFORDELAY@formatted_time;-- Commit the transactionCOMMITTRANSACTION;END;

   4. Create another stored procedure. This time, you create a job that runs thestored procedure that you created in the previous step according to a specifiedcadence:

      CREATEPROCEDURE[dbo].[SetUpDatastreamJob]@transaction_logs_retention_timeINTASBEGINDECLARE@database_nameVARCHAR(MAX)SET@database_name=(SELECTDB_NAME());;DECLARE@command_strVARCHAR(MAX);SET@command_str=CONCAT('Use ',@database_name,'; exec dbo.DatastreamLogTruncationSafeguard @transaction_logs_retention_time = '+CAST(@transaction_logs_retention_timeASVARCHAR(10)));DECLARE@job_nameVARCHAR(MAX);SET@job_name=CONCAT(@database_name,'_','DatastreamLogTruncationSafeguardJob1')-- Add 3 schedules to the job to run again after specified time.IFNOTEXISTS(SELECT*FROMmsdb.dbo.sysjobsWHEREname=@job_name)BEGINEXECmsdb.dbo.sp_add_job@job_name=@job_name,@enabled=1,@description=N'Execute the procedure to run an active transaction for x minutes.';EXECmsdb.dbo.sp_add_jobstep@job_name=@job_name,@step_name=N'Execute_DatastreamLogTruncationSafeguard',@subsystem=N'TSQL',@command=@command_str;-- Add a schedule that runs the stored procedure every given minutes starting now.DECLARE@schedule_name_1VARCHAR(MAX);SET@schedule_name_1=CONCAT(@database_name,'_','DatastreamEveryGivenMinutesFromNow')DECLARE@start_time_1time;SET@start_time_1=DATEADD(SECOND,1,GETDATE());DECLARE@formatted_start_time_1INT;SET@formatted_start_time_1=CONVERT(INT,REPLACE(CONVERT(VARCHAR(8),@start_time_1,114),':',''));EXECmsdb.dbo.sp_add_schedule@schedule_name=@schedule_name_1,@freq_type=4,-- daily start@freq_subday_type=4,-- every X minutes daily@freq_interval=1,@freq_subday_interval=@transaction_logs_retention_time,@active_start_time=@formatted_start_time_1;EXECmsdb.dbo.sp_attach_schedule@job_name=@job_name,@schedule_name=@schedule_name_1;-- Add a schedule that runs the stored procedure after every given minutes starting after some delay.DECLARE@schedule_name_2VARCHAR(MAX);Set@schedule_name_2=CONCAT(@database_name,'_','DatastreamEveryGivenMinutesAfterDelay');DECLARE@start_time_2time;SET@start_time_2=DATEADD(MINUTE,@transaction_logs_retention_time/2,GETDATE());DECLARE@formatted_start_time_2INT;SET@formatted_start_time_2=CONVERT(INT,REPLACE(CONVERT(VARCHAR(8),@start_time_2,114),':',''));EXECmsdb.dbo.sp_add_schedule@schedule_name=@schedule_name_2,@freq_type=4,-- daily start@freq_subday_type=4,-- every x minutes daily@freq_interval=1,@freq_subday_interval=@transaction_logs_retention_time,@active_start_time=@formatted_start_time_2;EXECmsdb.dbo.sp_attach_schedule@job_name=@job_name,@schedule_name=@schedule_name_2;-- Add a schedule that runs the stored procedure on the SQL Server Agent startup.DECLARE@schedule_name_agent_startupVARCHAR(MAX);Set@schedule_name_agent_startup=CONCAT(@database_name,'_','DatastreamSqlServerAgentStartupSchedule')EXECmsdb.dbo.sp_add_schedule@schedule_name=@schedule_name_agent_startup,@freq_type=64,-- start on SQL Server Agent startup@active_start_time=@formatted_start_time_1;EXECmsdb.dbo.sp_attach_schedule@job_name=@job_name,@schedule_name=@schedule_name_agent_startup;EXECmsdb.dbo.sp_add_jobserver@job_name=@job_name,@server_name=@@servername;ENDEND;

   5. Execute the stored procedure that creates the Datastream job.

      DECLARE@transaction_logs_retention_timeINT=(INT)EXEC[dbo].[SetUpDatastreamJob]@transaction_logs_retention_time

      ReplaceINT with the number of minutes for which you want toretain the logs. For example:

      • The value of60 sets the retention time to 1 hour
      • The value of24 * 60 sets the retention time to 1 day
      • The value of3 * 24 * 60 sets the retention time to 3 days

3. Enablepoint-in-time recovery (PITR).

What's next

• Learn more about how Datastream works withSQL Server sources.