2

In my product we need to create a solution for the reporting team to extract and process the data. We need to send them incremental data changes from our DB. I am evaluating various solutions. Looked into CDC and Replication and not sure which one to use or to build a custom one.

While CDC is easy to configure and need lesser infrastructure than replication, but the change tables are in the same DB the performance may slowdown depending on how frequently the reporting team queries the db.

Replication seems more salable but it needs lot of infrastructure. Also looks like there has to be separate instance of the DB in the same server or use another server.

Will a custom solution help here? Is there a way to create a copy of the database and read the logs from source DB and push incremental data to a destination DB through a job.

  • 1
    Your last sentence sounds like log shipping. Have you considered this? – James Anderson Jul 13 '16 at 14:36
  • 1
    What DBMS do you have in mind? Any? – mustaccio Jul 13 '16 at 15:44
1

CDC isn't really designed as a way to keep a reporting database up to date, it's more for tracking changes.

What exactly is the end goal here, are you just going to be taking some select tables\data off to another server to report against or are you looking for a way to take an entire database?

You've got a couple of options, if you're just interested in a handful of tables then you probably want to look at replication. Replication doesn't really need much infrastructure, just another SQL Server to replicate to.

If you want to have a complete copy of the database for reporting purposes, the easiest option that you've got is log shipping although be aware that the reporting database will be unavailable while the log shipping process plays in the logs.

Another option that you've got is to use an availability group with a secondary node acting as a read only reporting copy.

0

First off, don't try to do it manually. It's harder than you think. Honest, just don't.

CDC is really useful for data capture, for event handling etc. However, you really should use Replication here. I have a replicated database to over 150 warehouses and it works well.

One thing I would strongly recommend is you use a VIEW for your replication filtering. That way, if you need to modify what gets replicated, you can change it easily, without having to drop and re-init replication.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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