Enable CDC in Microsoft SQL Server
1 min readFeb 2, 2023
Prerequisites:
- Let’s assume we have a SQL Server instance named
MSSQL
running SQL Server 2012 or later. - Let’s also assume that we have a database named
SalesDB
in the Full recovery model.
Enabling CDC:
- Open SQL Server Management Studio and connect to the
MSSQL
instance. - Right-click the
SalesDB
database, and select "Properties." - Select the “Options” page and set the “CDC” option to “True.”
- Close the database properties dialog.
Creating a CDC Capture Instance:
- In Object Explorer, right-click the “Replication” folder, and select “New Capture Instance.”
- In the “New Capture Instance Wizard,” select the
SalesDB
database and provide a name for the capture instance, such asSalesDB_CaptureInstance
. - Follow the remaining steps in the wizard to complete the creation of the CDC capture instance.
Creating a CDC Enabled Table:
- Open a new query window and run the following script to enable CDC for a table named
Orders
in theSalesDB
database:
EXEC sys.sp_cdc_enable_table
@source_schema = ‘dbo’,
@source_name = ‘Orders’,
@role_name = NULL;
Once CDC is enabled, you can use the cdc.fn_cdc_get_all_changes_Orders
function to retrieve changes made to the Orders
table. This function returns a result set that can be used to track changes made to the data in real-time.
For example, you can use the following script to retrieve all changes made to the Orders
table in the last hour:
SELECT *
FROM cdc.fn_cdc_get_all_changes_Orders (
GETDATE() — 1,
GETDATE(),
‘all’
)