Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to apply Change Data Capture(CDC) to database DB1's tables and want to log everything in DB2.

So, could it be possible to capture table DB1's changes to DB2?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

No, it will only work within the same database. After all, what do you expect to happen if the second database goes offline, gets dropped, becomes corrupt, has permissions changes, etc.?

Now, once the CDC tables are created, I guess one potential workaround would be to place INSTEAD OF INSERT triggers on them, and divert the data to identical tables in another database that you manually create. But I haven't tested this, it would hardly be supported, and you would also lost a lot of the inherent CDC functionality (think about all the LSN-based functions that would now obviously not work). And again, I don't know what would happen when the other database goes offline, gets dropped, etc...

share|improve this answer
    
then it's very costly functionality because it's create table for every table in database.Then, what is reason to use CDC ? any points you know then please share.......... –  KuldipMCA Apr 25 '13 at 13:51
    
Yep, and CDC creates a table for every table you enable too. So I'm not sure how much more costly it is if you're trying to avoid occupying space in DB1 by keeping those tables empty and making copies of them elsewhere. –  Aaron Bertrand Apr 25 '13 at 14:04

While it is not possible with SQLserver CDC directly, there are commercial tools out there which can readily perform this task.

I work for Attunity, which has a full featured 'Replicate' product. Define a source DB; define a target; include or exclude desired tables (wildcards); optionally define some per-table or global transformations; and 'click' to replicate. There is also an SSIS / BIDS based option for more complete control and more ETL options.

Disclaimer: I work for Attunity.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.