This page describes how to enablechange data capture (CDC) inCloud SQL for SQL Server. This feature is available for the databases of yourinstances.

CDC enables you to capture many types of changes. For information about enabling and disabling CDC, see theMicrosoft documentation.

After youconnect to aninstance, thesqlserveruser can do manyCDC operations.

Also seeWork with Change Data.

Before you begin

Before you implement CDC on an instance, review all of the information on thispage.

Confirm feature availability

CDC is available for the following Cloud SQL for SQL Server database versions:

• SQL Server 2022 Standard
• SQL Server 2022 Enterprise
• SQL Server 2019 Standard
• SQL Server 2019 Enterprise
• SQL Server 2017 Standard
• SQL Server 2017 Enterprise

Enabling CDC and starting CDC capture jobs

Your database has the following stored procedures, for use by thesqlserveruser:

• msdb.dbo.gcloudsql_cdc_enable_db
• msdb.dbo.gcloudsql_cdc_disable_db

Turn CDC on

To turn this feature on for a database, execute the necessary stored procedureand pass in the database name. For example:

EXECmsdb.dbo.gcloudsql_cdc_enable_db'DATABASE_NAME'

Turn CDC off

To turn this feature off for a database, run a command such as the following:

EXECmsdb.dbo.gcloudsql_cdc_disable_db'DATABASE_NAME'

Start CDC capture jobs

After CDC is enabled, jobs are created for capture and cleanup. The jobs areinvisible to thesqlserver user in SQL Server Management Studio (SSMS).However, you can modify the jobs using built-in stored procedures. Additionally,the jobs are viewable via the following stored procedure:

• sys.sp_cdc_help_jobs

To start a cleanup job, you could use the following command:

EXECsys.sp_cdc_start_job@job_type=N'cleanup'

To change the job parameters, you could use a command similar to the following,for example:

EXECsys.sp_cdc_change_job@job_type=N'capture',@maxtrans=20,@pollinginterval=NULL,@maxscans=NULL,@continuous=NULL

For more information about starting and changing jobs, see the following:

• sys.sp_cdc_start_job
• sys.sp_cdc_change_job

Also seesys.sp_cdc_add_job.

Enabling CDC for a table

After youturn on CDC for a database, any user with dbo(database owner) access can set up tracking for tables in the database.

For information about the standard CDC commands and options, seeEnable and Disable Change Data Capture.

Track changes in a table

To track a table, use thesys.sp_cdc_enable_table stored procedure.

For example, you could specify a command similar to the following:

EXECsys.sp_cdc_enable_table@source_schema=N'dbo',@source_name=N'customer',@role_name=N'CDC'

Check if CDC is enabled on a table

To check if CDC is enabled on a table, use thesys.sp_cdc_help_change_data_capture stored procedure.

For example, you could specify a command similar to the following:

EXECUTEsys.sp_cdc_help_change_data_capture@source_schema=N'dbo',@source_name=N'customer'

Query changes via a CDC change table

To view CDC changes made on a table, use aSELECT query on the tablethat automatically is created when CDC is enabled on that table.

The table is named as follows:

<schema>_<table_name>_CT

For example, you could specify a command similar to the following:

SELECT*FROMcdc.dbo_customer_CT

Enable CDC on a table with a capture instance specified

To track a table with a "capture instance", use thesys.sp_cdc_enable_tablestored procedure.

For example, you could specify a command similar to the following:

EXECsys.sp_cdc_enable_table@source_schema=N'dbo',@source_name=N'customer',@role_name=N'CDC',@capture_instance=N'customer_cdc',@supports_net_changes=1

Query all changes within a capture instance

To view CDC changes made on a table within a "capture instance", use thecdc.fn_cdc_get_all_changes_<capture_instance> stored procedure.

For example, you could specify a SQL statement similar to the following:

DECLARE@from_lsnbinary(10),@to_lsnbinary(10)SET@from_lsn=sys.fn_cdc_get_min_lsn(N'customer_cdc')SET@to_lsn=sys.fn_cdc_get_max_lsn()SELECT*FROMcdc.fn_cdc_get_all_changes_customer_cdc(@from_lsn,@to_lsn,N'all');

Disabling CDC for a table

To disable CDC tracking for a table, use thesys.sp_cdc_disable_table storedprocedure. Specify a capture instance to disable it. Alternatively, specify acapture instance as'all'.

For example, you could specify a command similar to the following to disable CDCfor the table:

EXECsys.sp_cdc_disable_table@source_schema=N'dbo',@source_name=N'customer',@capture_instance=N'all'

Deleting a CDC-enabled database

If CDC is enabled for a database and you try to delete it, then you may encountererrors. If this occurs, disable CDC for the database, and then delete the database.

For example, you can specify a command similar to the following to disable anddelete a database:

EXECmsdb.dbo.gcloudsql_cdc_disable_db'DATABASE_NAME'DROPDATABASE'DATABASE_NAME'

If you can't delete the database because it has open connections, thenuse the following query to see those connections:

selectdb_name(dbid),*fromsys.sysprocesseswheredb_name(dbid)='DATABASE_NAME'

Close all open connections. Disable CDC for the database, and then delete thedatabase.

Importing a CDC-enabled database

When importing a CDC-enabled database, Cloud SQL for SQL Server keeps theKEEP_CDCflag enabled and automatically creates capture and cleanup jobs with default parameters.