[SQL Server Data Warehousing] Change Data Capture - Pros/Cons/Gotchas

Change Data Capture - Pros/Cons/Gotchas

My company is thinking of implementing Change Data Capture. I find many online articles, which cover the basics of Change Data Capture, but no real-life examples of using it, the pros and cons, limitations or "gotchas" encountered during actual implementation and usage, etc. I could use such info as we determine whether Change Data Capture is the right solution for our needs.

Some details about our scenario:

1. Source data: We have currently about 200 entities that use one of our applications. The entities involved are all functioning units of a larger, single business entity, but are spread out geographically. Each entity and its copy of the application stores its data in a separate database, although all copies of the database have identical data structures and tables. They post summarative data on a routine basis to a couple of main servers, however, in each case this data is still stored in separate database on the servers - thus, still about 200 databases.
2. What we would like to do: Bring together this summarative data into a single relational data store, and from there into a dimensional database. We do not want to, nor can we, eliminate the source databases, on either the geographically-distributed sites or the main servers, b/c we don't "own" them.

Is this a feasible scenario for Change Data Capture? We would need to include data column(s) to indicate data sources b/c the data, although placed in a single database, must still be identifiable by data source and data from one source never overwrites data from a different source. As I understand it, Change Data Capture would need to be enabled on the 200+ databases on the main servers, from which we would then likely use SSIS to process the data to post it to the new database we would develop.

I would appreciate any advice, based on understanding and experience, of important things to consider, things to avoid, "gotchas", best practices, etc. We have worked quite a bit with SSIS, developing dimensional data structures, etc., so although any major comments there are welcomed, our primary "need to know" is how/if Change Data Capture can be used for our scenario, as we have no experience with it.

Thanks for your assistance,
Randy

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Change Data Capture - Pros/Cons/Gotchas

Change Data Capture - Pros/Cons/Gotchas

My company is thinking of implementing Change Data Capture. I find many online articles, which cover the basics of Change Data Capture, but no real-life examples of using it, the pros and cons, limitations or "gotchas" encountered during actual implementation and usage, etc. I could use such info as we determine whether Change Data Capture is the right solution for our needs.

Some details about our scenario:

1. Source data: We have currently about 200 entities that use one of our applications. The entities involved are all functioning units of a larger, single business entity, but are spread out geographically. Each entity and its copy of the application stores its data in a separate database, although all copies of the database have identical data structures and tables. They post summarative data on a routine basis to a couple of main servers, however, in each case this data is still stored in separate database on the servers - thus, still about 200 databases.
2. What we would like to do: Bring together this summarative data into a single relational data store, and from there into a dimensional database. We do not want to, nor can we, eliminate the source databases, on either the geographically-distributed sites or the main servers, b/c we don't "own" them.

Is this a feasible scenario for Change Data Capture? We would need to include data column(s) to indicate data sources b/c the data, although placed in a single database, must still be identifiable by data source and data from one source never overwrites data from a different source. As I understand it, Change Data Capture would need to be enabled on the 200+ databases on the main servers, from which we would then likely use SSIS to process the data to post it to the new database we would develop.

I would appreciate any advice, based on understanding and experience, of important things to consider, things to avoid, "gotchas", best practices, etc. We have worked quite a bit with SSIS, developing dimensional data structures, etc., so although any major comments there are welcomed, our primary "need to know" is how/if Change Data Capture can be used for our scenario, as we have no experience with it.

Thanks for your assistance,
Randy

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Change Data Capture - Pros/Cons/Gotchas

Change Data Capture - Pros/Cons/Gotchas

My company is thinking of implementing Change Data Capture. I find many online articles, which cover the basics of Change Data Capture, but no real-life examples of using it, the pros and cons, limitations or "gotchas" encountered during actual implementation and usage, etc. I could use such info as we determine whether Change Data Capture is the right solution for our needs.

Some details about our scenario:

1. Source data: We have currently about 200 entities that use one of our applications. The entities involved are all functioning units of a larger, single business entity, but are spread out geographically. Each entity and its copy of the application stores its data in a separate database, although all copies of the database have identical data structures and tables. They post summarative data on a routine basis to a couple of main servers, however, in each case this data is still stored in separate database on the servers - thus, still about 200 databases.
2. What we would like to do: Bring together this summarative data into a single relational data store, and from there into a dimensional database. We do not want to, nor can we, eliminate the source databases, on either the geographically-distributed sites or the main servers, b/c we don't "own" them.

Is this a feasible scenario for Change Data Capture? We would need to include data column(s) to indicate data sources b/c the data, although placed in a single database, must still be identifiable by data source and data from one source never overwrites data from a different source. As I understand it, Change Data Capture would need to be enabled on the 200+ databases on the main servers, from which we would then likely use SSIS to process the data to post it to the new database we would develop.

I would appreciate any advice, based on understanding and experience, of important things to consider, things to avoid, "gotchas", best practices, etc. We have worked quite a bit with SSIS, developing dimensional data structures, etc., so although any major comments there are welcomed, our primary "need to know" is how/if Change Data Capture can be used for our scenario, as we have no experience with it.

Thanks for your assistance,
Randy

.

social.technet.microsoft.com/Forums