Change Data Capture - database schema change

Question

Hi,

Could any one tell me how to change database schema when CDC ia enabled.

Thanks.
Eva

Answer 1

Hello Eva

Click this link for your solution, this article is submitted by Pinal Dave. It should be more useful

http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/

Regards
RM Thirunavukkarasu MCP, MCITP

Answer 2

Hi,

Though I am not a technical person but found your post to be very informative.

I would like to share a link where a Software Testing Engineer of my company (Mindfire Solutions) has done a very good analysis on Database Applications.

Here is the link on: Database application.

Hope you find it useful.

---

Cheers, Eliza Mindfire: India's Only Company to be both Apple Premier & Microsoft Gold certified. www.mindfiresolutions.com

Answer 3

Hi RM,

Thanks for your reply. Unfortunately Pine Dave didn't explain about schema change after enabling CDC to table.


Any other thoughts?

Thanks.
Eva

Answer 4

Here you go
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=525198#tabs

Thanks.

Answer 5

If you are looking for info on schema changes made to a table on which CDC has been enabled, thankfully CDC will track the DDLs that have been issued on a CDC-enabled table. There are specific functions that get created every time CDC is enabled on a table and you can query them to get a list of DDLs which were fired on that table.

For a very detailed and excellent video, watch the video by Paul Randal at below link, also accompanied by textual documentation.

http://technet.microsoft.com/en-us/magazine/2008.11.sql.aspx

Hope that helps.



Cheers!!
Muqadder.

Answer 6

Ohh, and I just had a look at the Connect item you have submitted and that made clearer on what excatly were you looking for (your question above doesn't entail much unfortunately).

If you added a column to a CDC enabled table, that won't get reflected automatically to the corresponding _CDC table as well (this is by design and Paul in the above video does mention this.). However, as already suggested on Connect, you can create another instance of the _cdc table (a maximum of two _cdc tables are allowed for any given relational table). and also retain the existing one as well. Now to retain the history data, you have multiple options:-

1. Load the existing data from the old _cdc table into the new one (i.e the 2nd instance, with NULL in the newly added fileld) and drop the older cdc table.

2. Retain both the older as well as the new instances of _cdc table. For historical cdc data, you'll refer to the old cdc table and for the newer changes, new instance of cdc table can be used. Note, however, in this case that the CDC process will continue to write the changes to both the cdc instance tables if you choose to retain both instances and your databse will grow twice as fast, and will consume twice as much disk space as well!!



Hope that helps!!




Cheers!!
Muqadder.

Answer 7

In my testing, I found that you could not backup the history and copy it into a new capture instance.  Other CDC meta data such as cdc.change_tables.start_lsn are out of sync with the data.   Below is the script I used to try and preserve history.

I'm not quite sure how the SQL Server team thought this feature could be useful without providing the ability to preserve history.

 

/*Change Data Capture Test - Alter table definition test */

/*Enter restricted mode so we don't lose data changes during this process*/
alter database ChangeDataCaptureTest set AUTO_UPDATE_STATISTICS_ASYNC OFF
alter database ChangeDataCaptureTest set RESTRICTED_USER with ROLLBACK IMMEDIATE
go

/*Add a column to the table*/
alter table dbo.Table1 add value3 varchar(20) DEFAULT '' not null

/*Copy the existing change tracking into a temp table*/
select * into cdc.dbo_Table1_temp from cdc.dbo_Table1_CT

/*Add the new column to the temp table so that we don't have to map
all columns when we copy back, note that we use NULL as the default*/
alter table cdc.dbo_Table1_temp add value3 varchar(20) DEFAULT NULL

/*Disable CDC on the source table, this will drop the associated cdc table*/
exec sys.sp_cdc_disable_table 
@source_schema='dbo',
@source_name='Table1', 
@capture_instance='dbo_Table1'

/*Enable CDC for the table which recreates the CDC table*/
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name  = N'Table1',
@role_name   = NULL,
@supports_net_changes = 1,
@filegroup_name = N'ChangeDataCapture'
GO

/*Insert values from the temp table back into the new CDC Table*/
Insert into cdc.dbo_Table1_CT 
SELECT * 
From cdc.dbo_Table1_temp
go

/*Drop the temp table*/
drop table cdc.dbo_Table1_temp

/*The problem at this point is that the CDC meta data tables like 
cdc.change_tables.start_lsn are no longer valid because rows were
added to the cdc table outside the normal process.*/

/*Go back into multi-user mode*/
alter database ChangeDataCaptureTest set AUTO_UPDATE_STATISTICS_ASYNC ON
alter database ChangeDataCaptureTest set MULTI_USER
go

/*Add a new row to the table*/
insert into table1
values(12,'zz','g') 

 

Answer 8

Can I raise the profile of this post, I know its ancient, but ive still not found a decent article on CDC that details how to make changes to a production DB with CDC enabled by implementing a change script.

Its normal practice to compare a staging database that has got to release status to a production DB and generate a change script that can be applied to production or indeed be rolled back succesfully with no ill afects.

It seems to be to be able to do a release, you need to make the DB Read Only, run any Warehouse Loads etc that use the cdc tables, disable the cdc on the tables, make your changes re-apply CDC,   But this doesnt allow for rollback and the systems that depend on cdc have already been changed meaning Relying on Backups is not helpful either.

as pointed out by "rich Collete" years ago Someone in MS should cleary Point out how CDC Fits cleanly into the Life cycle of DB development i feel this still hasnt been addressed

ColinR

Answer 9

It is disappointing that they have done nothing to resolve this issue in SQL Server 2012.   Likewise on Connect they closed out the issue as "Won't Fix".   They have a habit of closing every issue in one way or another just prior to a new version release so you wind up having to recreate the issue all over again.   For me, CDC is useless.  I suspect they will just drop the feature which is a shame.  The concept is great in a static environment but there is no such thing as a static environment in the real world.

Answer 10

i agree,

It has Issues with Log Shipping and Replication Also, just not a well thought out solution, yet a simple fix to put the CDC stuff in a user definded secondary Database instead of CDC Schema would address all of the issues, it would allow performance, logshipping and replication segmentation. In addition to being able to control DB Growth.  

Answer 11

I am feeling the pain, I just added a new column to my table and I need the CDC schema updated.

Answer 12

This only received four votes up on https://connect.microsoft.com/SQLServer/feedback/details/525198/change-table-schema-after-enabling-cdc .  I gave it number 5.

I wonder if this works: http://blogs.claritycon.com/blog/2011/06/enabling-change-data-capture-cdc-on-newly-added-columns-on-underlying-table/

Answer 13

This technique seems accurate from my testing: http://blogs.claritycon.com/blog/2011/06/enabling-change-data-capture-cdc-on-newly-added-columns-on-underlying-table/, but there was one thing I had to do that is not described: put time between each call of the enable/disable SPs.  This seems to have something to do with job execution.  I delayed for 5 seconds, which didn't work.  Ten seconds seemed to work.  The consequences of not doing this correctly appeared to be log processing stopped working, which seemed to break everything.  I would have liked to maintain column ordering for changes, which I would do by creating a new table and transferring the data to the new table.  However, the Clarity blog says not to drop the old table and this may be a good idea, from my testing where I originally was in the habit of building a new table.  I don't quite understand this process, but I got the following pattern to work:

alter table staffsessions add test varchar(10) null

select * into dbo_staffsessions_ct_temp from cdc.dbo_staffsessions_ct

EXEC sys.sp_cdc_disable_table @source_schema='dbo', @source_name='StaffSessions', @capture_instance='dbo_StaffSessions'

waitfor delay '00:00:10'

EXEC sys.sp_cdc_enable_table @source_schema='dbo', @source_name='StaffSessions', @role_name     = null,	@supports_net_changes = 1

waitfor delay '00:00:10'

insert into cdc.dbo_staffsessions_ct
select *,null from dbo_staffsessions_ct_temp 

update cdc.change_tables set start_lsn=(select min(__$start_lsn) from dbo_staffsessions_ct_temp)
where cdc.change_tables.capture_instance = 'dbo_staffsessions'