70-465 · Question #93
You work as a Database Administrator (DBA) for a company named ABC.com. The company uses a Microsoft SQL Server 2012 infrastructure. You have a database named CorpDB. CorpDB contains a table named…
The correct answer is C. You should configure change data capture. To monitor future deletions from a SQL Server table with minimal development effort, Change Data Capture (CDC) is the most suitable built-in feature. CDC tracks all DML operations, including deletes, on specified tables.
Question
Options
- AYou should configure table permissions.
- BYou should configure a user role.
- CYou should configure change data capture.
- DYou should configure a trigger.
How the community answered
(27 responses)- A15% (4)
- B4% (1)
- C74% (20)
- D7% (2)
Why each option
To monitor future deletions from a SQL Server table with minimal development effort, Change Data Capture (CDC) is the most suitable built-in feature. CDC tracks all DML operations, including deletes, on specified tables.
Configuring table permissions controls who has the ability to delete data but does not provide a logging or monitoring mechanism to track when a deletion actually occurs or by whom if they possess the permission.
Configuring a user role groups permissions for easier management, but like individual permissions, it does not inherently log or monitor DML operations performed by users assigned to that role.
Change Data Capture (CDC) is a native SQL Server feature designed to capture insert, update, and delete activity applied to tables. Enabling CDC on the `SalesInfo` table will automatically record all deletion events, providing details on what was deleted and when, thus fulfilling the monitoring requirement with minimal custom development.
While a trigger could be custom-coded to log delete operations, it requires significant development and ongoing maintenance effort, which violates the requirement to minimize development.
Concept tested: SQL Server Change Data Capture for DML auditing
Source: https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server
Topics
Community Discussion
No community discussion yet for this question.