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.
Best Answer
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.