CDC in Sql Developer Enterprise Edition

Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.


CREATE DATABASE TestDB;
Use TestDB
GO

CREATE TABLE Employee(
EID INT IDENTITY(1,1)PRIMARY KEY,
ENAME VARCHAR(50),
DEPT VARCHAR(20)
);

INSERT INTO Employee
Values ('Rambo','IT'),
('Jason','Finance'),
('Brad','HR');
SELECT * FROM Employee;

Use TestDB
GO
EXEC sys.sp_cdc_enable_db

SELECT name,is_cdc_enabled FROM sys.databases WHERE name='TestDB'

EXEC sys.sp_cdc_enable_table
    @source_schema ='dbo',
    @source_name ='Employee',
    @role_name ='EmpCDCRole',
    @supports_net_changes = 1
  SELECT name,is_tracked_by_cdc FROM sys.tables WHERE name='Employee'

//DDL And DML Operation
DELETE FROM Employee WHERE EID=3
INSERT INTO Employee VALUES('Mary','HR')
UPDATE Employee SET ENAME='Nichole' WHERE EID=2
UPDATE Employee SET ENAME='EMMA' WHERE EID=2

SELECT * FROM Employee

DECLARE @Begin_LSN binary(10), @End_LSN binary(10)
-- get the first LSN
SELECT @Begin_LSN =sys.fn_cdc_get_min_lsn('dbo_Employee')
-- get the last LSN
SELECT @End_LSN =sys.fn_cdc_get_max_lsn()
-- returns net changes
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Employee(
 @Begin_LSN, @End_LSN,'ALL');

select * from cdc.ddl_history

Ref: http://www.databasejournal.com/features/mssql/article.php/3720361/Microsoft-SQL-Server-2008----Change-Data-Capture-150-Part-I.htm

Example 2:
use mssqltips
go

declare
 @is_cdc_enabled bit
,@rc int


--
-- turn on change data capture at the database level
--

select @is_cdc_enabled = is_cdc_enabled
from sys.databases
where [name] = N'mssqltips'

if @is_cdc_enabled = 0
    exec sys.sp_cdc_enable_db
else
    print 'CDC is already enabled on the database'


--
-- turn on cdc for the customer table
--
if not exists (select 1 from sys.tables
               where [name] = N'customer'
               and is_tracked_by_cdc = 1)
    exec @rc = sys.sp_cdc_enable_table
    @source_schema = N'dbo'
    ,@source_name = N'customer'
    ,@role_name = N'cdc_admin'
    ,@capture_instance = N'customer_all'
    ,@supports_net_changes = 1
    ,@index_name = NULL
    ,@captured_column_list = NULL
    ,@filegroup_name = NULL
else
    print 'CDC is already enabled on customer'


--
-- turn off change tracking
--
/*
exec sys.sp_cdc_disable_table
  @source_schema = 'dbo' ,
  @source_name = 'customer',
  @capture_instance = 'customer_all'

 */


--
-- Track the history of processing CDC by capture instance
--
create table dbo.cdc_capture_log (
    cdc_capture_log_id    int identity not null
,    capture_instance    nvarchar(50) not null   
,    start_time            datetime not null   
,    min_lsn                binary(10) not null
,    max_lsn                binary(10) not null
,    end_time            datetime null
,    insert_count        int not null default 0
,    update_count        int not null default 0
,    delete_count        int not null default 0
,    status_code            int not null default 0
)

Browse More Articles

Tweet

Written by Test

We are Creative Blogger Theme Wavers which provides user friendly, effective and easy to use themes. Each support has free and providing HD support screen casting.