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

Download example from my Google Drive - https://goo.gl/3HYQcH

REFERENCES
http://technet.microsoft.com/en-us/li...
http://technet.microsoft.com/en-us/li...
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'
b. EXEC sys.sp_cdc_enable_db / EXEC sys.sp_cdc_disable_db
c. EXEC sys.sp_cdc_enable_table /
d. EXEC sys.sp_cdc_help_change_data_capture -- view the cdc tables
e. SELECT name, is_cdc_enabled FROM sys.databases
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'

EXEC sys.sp_cdc_help_change_data_capture

EXEC sys.sp_cdc_enable_db
EXEC sys.sp_cdc_disable_db

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



EXEC sys.sp_cdc_disable_table
@source_schema = N'Person'
, @source_name = N'Address'
, @capture_instance = N'Address'

EXEC sys.sp_cdc_enable_table
@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