MSSQL - How to, Step by Step Change Data Capture (CDC) Tutorial

REFERENCES
Change data capture cannot function properly when the Database Engine service or the SQL Server Agent service is running under the NETWORK SERVICE account. This can result in error 22832.

0) CDC Can not be enabled when Transactional Replication is on, must turn off, enable CDC then reapply Transactional Replication
1) Source is the SQL Server Transaction Log
2) Log file serves as the Input to the Capture Process
3) Commands
a. EXEC sp_changedbowner 'dbo' or 'sa'
4) To SELECT a table you must use the cdc schema such as cdc.SCHEMANAME_TABLENAME_CT iand its suffixed with CT

5) Columns
a. _$start_lsn -- commit log sequence number (LSN) within the same Transaction
b. _$end_lsn -
c. _$seqval -- order changes within a transaction
d. _$operation -- 1=delete, 2=insert,3=updatebefore,4=updateafter
e. _$update_mask -- for insert,delete all bits are set, for update bits set correspond to columns changed

6) Note CDC creates SQL Agent Jobs to move log entries to the CDC tables, there is a latency
7) There is a moving window of data kept, I believe the default is 3 days.
8) At most 2 capture instances per table

USE AdventureWorks2008R2
GO

EXEC sp_changedbowner 'sa'

SELECT * FROM cdc.Address_CT
SELECT * FROM cdc.Person_Address_CT ORDER BY __$start_lsn DESC

@source_schema = N'Person'
, @source_name = N'Address'
, @capture_instance = N'Address'

@source_schema = N'Person'
, @source_name = N'Address'
, @role_name = NULL
-- , @capture_instance = N'Address'
, @capture_instance = NULL
, @supports_net_changes = 1
, @captured_column_list = N'AddressID, AddressLine1, City'
, @filegroup_name = N'PRIMARY';
GO

INSERT INTO AdventureWorks2008R2.Person.Address
(AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,SpatialLocation,rowguid,ModifiedDate)
VALUES
('188 Football Avenue', 'Suite 188', 'Seattle', 10, '80230', NULL, NEWID(), GETDATE());

SELECT TOP 1 * FROM Person.Address ORDER BY AddressID DESC
UPDATE Person.Address SET AddressLine1 = '199 Football Ave' WHERE AddressID = 32524
DELETE FROM Person.Address WHERE AddressID = 32524


GO

[kolhapure]

Nice Video. Straight to the point. One question, is it advised to use CDC for large production databases?

[alexitosrv]

Man, I liked your explanation. It absolutely made sense, and now that CDC thing is much more clear. Thank you!

[theinttheintthu]

if cdc enable table in the same database, capture job is work?

[alexitosrv]

I wonder why there is so much mysticism around that tech? It's like too many vendors are trying to take their cut by providing its own solution, but really, it's a big deal because the integration point with multiple technologies (which is not small feat, being honest), but feel like is rebrandred ETLs

[GURUKHAN_OG]

Really put me to sleep with this video pace, but you were quick to cut the real SQL statements and steps needed to get this CDC set up in first place.

[sshinde]

Good Video Thanks and also the code attached is handy. I will try this soon to give me a better understanding.

[rogerbreton]

Thank you. I understand Change Data Capture does not work in the SQL Express version...

[wRatte]

Insanely good demo - even showing some pitfalls like he Network Service account! (Which is something I've encountered before.)

[krunaljagani]

Hello Sir, Is there any way if we are using SQL server business intelligence instance ? I am not able to perform this operation.

[ChaimMosheStern]

Does it show the date and time modified?

[ImUsmanWaraich]

How to read the column name from operation_mask column!!!

[charlykjoseph]

how to clean up or what time it cleans up automatically

[laylow]

I assume there is a way to do this through a terminal window as well, correct? I am trying to run the enable DB command over putty and it does not want to work.

[cunay]

One more thing, you have to check that sql AGENT has been already started

[dpw]

Would it not be easier to have a trigger on the table which would write to another table of the tasks you want it to capture, this would mean you wouldn't need a sql job running all the time?

[snmailist]

thx's, it helps me understanding cdc