Home » Microsoft » MB6-886 » What should you do?
You are a database developer. You plan to design a database solution by using SQL Server 2008.
A database contains two tables named Orders and OrderDetails. There is also a data warehouse containing a table named factSales. The factSales tale has a denormalized structure and contains columns from Orders and OrderDetails.
You plan to design a solution that will extract all data modifications from Orders and OrderDetails and load them into factSales.
You have the following requirements:
• The load operation is incremental and runs daily.
• The schema of the tables cannot be modified.
• The history of each modification is maintained for one month.
You need to implement the solution by using the least amount of coding and administrative effort.
What should you do?
A. Use the SQL Server Change Data Capture feature.
B. Use the SQL Server Change Tracking feature.
C. Use Microsoft Sync Services.
D. Partition the Orders and OrderDetails tables based on date.
Correct Answer: A
Explanation/Reference:
Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system.
Change tracking captures the fact that rows in a table were changed, but does not capture the data that was changed. This enables applications to determine the rows that have changed with the latest row data being obtained directly from the user tables. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that do not require the historical information, there is far less storage overhead because of the changed data not being captured. A synchronous tracking mechanism is used to track the changes. This has been designed to have minimal overhead to the DML operations.
In the current scenario,:
• we need to design a solution that will extract all data modifications from Orders and OrderDetails and load them into factSales
• we have a requirement: The history of each modification is maintained for …
That means we should use SQL Server Change Data Capture feature.
