Note that CDC isn't supported on system databases.
The change data capture (CDC) feature introduced in SQL Server 2008 provides an efficient framework for tracking inserts, updates, and deletes in tables in a SQL Server database.
CDC has a performance cost, but it's less than other change-tracking solutions such as triggers.
When CDC is enabled, the changes are written to tables in the database and can be accessed using CDC system stored procedures.
The data can be obtained from your order tracking application, but this system needs to be highly available and shouldn't be hit with large queries during the day when orders are being processed.
The solution that makes the most sense is to create a copy of the database that can be used for reporting, but there are millions of records on these tables and even a nightly load won't complete in the available maintenance window.
Related: Transforming Data Using SSIS and SQL One challenge in feeding data from an OLTP database to an operational data store (ODS) is keeping the data in sync.The target tables in the ODS can be entirely reloaded from the source on a regular schedule, but this has performance implications on the source and target databases.The Source object is, of course, connected to the Source database's table/view.The other objects beneath it (the Lookup, Destination and Update objects) are all connected to the Destination database's table/view. " will be automatically mapped by the system based on the order you defined it.All you have to do next is to specify the Column Mappings.As the DBA at the XYZ Company, you've been assigned the task of creating a report that shows sales order data.