Implementing Change Data Capture in MS Databases

Overview of Change Data

SQL Server Change Tracking

Change Data Capture (CDC)

Figure 1.0, Source:
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017

SQL Server Edition with Change Data Capture

Enabling the Change Data Capture for SQL Database

sqlcmd -S localhost -U SA -P 'abcd@1234'
CREATE DATABASE TestCDC
GO
USE TestCDC
GO
EXEC sys.sp_cdc_enable_db
GO
Figure 1.1
Figure 1.2
Figure 1.3
Figure 1.4
Figure 1.5
SELECT is_cdc_enabled, * FROM sys.databases WHERE name='TestCDC'
Figure 1.6
Figure 1.7
Figure 1.8
Figure 1.9

Enabling the Change Data Capture for SQL Database Tables

USE TestCDC
GO
CREATE TABLE dbo.Employee (
EmployeeID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(100),
LastName VARCHAR(100),
Position VARCHAR(100),
PayScale DECIMAL
)
GO
INSERT INTO dbo.Employee(FirstName, LastName, Position, PayScale)
VALUES
('User1', 'Test1', 'Software Engineer', 150000),
('User2', 'Test2', 'Quality Assuarance Engineer', 120000),
('USer3', 'Test3', 'Business Analyst', 250000),
('USer4', 'Test4', 'Systems Engineer', 150000),
('USer5', 'Test5', 'Project Manager', 200000)
GO

Enable Change Data Capture for a specified table in the current database

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Employee',
@role_name = N'UserRole',
@supports_net_changes = 1
GO
Figure 2.0
Figure 2.1
INSERT INTO dbo.Employee(FirstName, LastName, Position, PayScale)
VALUES('user6', 'Test6', 'Senior Software Engineer', 235000)
GO
DELETE FROM dbo.Employee
WHERE EmployeeID = 2
GO
UPDATE dbo.Employee
SET PayScale = 275000, FirstName = 'MUser3'
WHERE EmployeeID = 3
GO
UPDATE dbo.Employee
SET PayScale = 300000
WHERE EmployeeID = 3
GO
Figure 2.2
Figure 2.3
ECLARE @MinimumLSN binary(10), @MaximumLSN binary(10)
SET
@MinimumLSN = sys.fn_cdc_get_min_lsn('dbo_Employee')
SET
@MaximumLSN = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Employee (
@MinimumLSN, @MaximumLSN, N'all');
GO
Figure 2.4
DECLARE @MinimumLSN binary(10), @MaximumLSN binary(10)
SET
@MinimumLSN = sys.fn_cdc_get_min_lsn('dbo_Employee')
SET
@MaximumLSN = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Employee (
@MinimumLSN, @MaximumLSN, N'all');
GO
Figure 2.5

CDC Jobs and Cleanup Process

Senior Software Engineer @WSO2, B.Sc.(Hons).Computer Engineering