x

How to restrict particular operations in Change data capture in sql server 2008

Hi All,

We are using CDC (change Data capture) in sql server 2008. In the "cdc.dbo_table_ct" system generated table , Separate record for every operation ( __$operation = 1 for Delete Record, __$operation = 2 for Insert New Record and __$operation = 3/4 for Before/After Update) is getting maintained. we want to maintain only records for __$operation = 1,3 and 4.

I don't need new record insertions in the CDC table. I'm enabling the CDC for the table like this

Exec sys.sp_cdc_enable_table
@Source_schema=N'dbo',
@Source_name = N'tablename',
@Role_name= NULL,
@captured_column_list = 'column1,column2'

  1. Is there any option to restrict the operations, so that only Deletes and Updates will be captured.

  2. Is there any way to caature the CDC data in more than one table, depending on a particular column For example , if "section" column have distinct values "section1" and "section2" then, Is there a way to move all the "section1" valued updates/inserts/delete in one CDC table and "section2" updates/inserts/delete in another CDC table?

Please suggest.

-Sriman
more ▼

asked Dec 22, 2010 at 01:16 AM in Default

sriman gravatar image

sriman
21 2 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

CDC is meant for capturing INSERT, UPDATE, DELETE operations, that means you cannot customize the operations.

To your second question, the better option will be creating views. i.e something like

Edit : only if your columns values are few.

 CREATE VIEW vw_CDC_For_Section1 SELECT Column FROM CDC.yourCDCTable WHERE Column = 'section1'

CREATE VIEW vw_CDC_For_Section2 SELECT Column FROM CDC.yourCDCTable WHERE Column = 'section2'

more ▼

answered Dec 22, 2010 at 02:26 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

Thanks for the reponse Cyborg, for the second question, In my case our table contains few properties with respect to section1 and section2. it is a denormalized table, where both sections details we are including in the same table. In case of section1 value all the section1 related columns will have corresponding values and section2 related columns will have null values. Similarly in case of inserting a new record of section2 type all the section1 related columns will have null values and section2 related columns will have values.

In that case if we can manage separate table depending upon the section type, the @captured_column_list will be reduced. only section1 related columns will be captured in @captured_column_list of first CDc table and section2 related columns will captured separately in second cdc table. so that we can reduce the space.

Please suggest whether this is possible.

thanks Sriman
Dec 22, 2010 at 02:40 AM sriman

Yes you can configure more than one CDC for your table so that Section1 Related columns in one CDC table and Section2 Related tables to the other CDC table

E.g. Assuming Column1,Column2 Belongs to Section1 Type and Column3, column4 belongs to Section2 type

you can configure 2 cdc table so that data changes on these columns will be logged in 2 separate tables

-- for section1

Exec sys.sp_cdc_enable_table @Source_schema=N'dbo', @Source_name = N'tablename', @Role_name= NULL, @captured_column_list = 'column1,column2', @capture_instance = 'Section1'

-- for Section2

Exec sys.sp_cdc_enable_table @Source_schema=N'dbo', @Source_name = N'tablename', @Role_name= NULL, @captured_column_list = 'column2,column3', @capture_instance = 'Section2'
Dec 22, 2010 at 03:07 AM Cyborg

Thanks Cyborg, one of my column name is "Type" which holds either "Prl" or "Med" in this case what will be the capture instance ?

@capture_instance = 'Type'

Or

@capture_instance = 'PRB' or @capture_instance = 'Med'
Dec 23, 2010 at 04:26 AM sriman

Sriman i suggest you to have a look at the sys.sp_cdc_enable_table procedure.

@capture_instance is "Is the name of the capture instance used to name instance-specific change data capture objects. capture_instance is sysname and cannot be NULL.

If not specified, the name is derived from the source schema name plus the source table name in the format schemaname_sourcename. capture_instance cannot exceed 100 characters and must be unique within the database. Whether specified or derived, capture_instance is trimmed of any white space to the right of the string."

Ref : books online.

You cannot configure CDC based on the values of the columns. The One option is creating view against the CDC table.
Dec 23, 2010 at 04:51 AM Cyborg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x18

asked: Dec 22, 2010 at 01:16 AM

Seen: 2107 times

Last Updated: Dec 23, 2010 at 04:53 AM