Microsoft DP-800: Secure, optimize, and deploy database solutions Practice Questions

The 'DropObjectsNotInSource' publish property controls whether SqlPackage/dacpac deployment drops database objects that exist in the target but are not defined in the source project. Setting it to 'False' prevents the deployment engine from generating DROP statements for objects not in the project, protecting objects owned by other teams.

Why the distractors are wrong:

• BlockOnPossibleDataLoss halts deployment when schema changes could cause data loss (e.g., column type change, column removal). It prevents destructive column/type changes but does NOT prevent objects outside the project from being dropped.
• IgnorePermissions instructs the deployment to skip comparing and deploying permission-related changes (GRANT/DENY/REVOKE). It has no effect on whether absent objects are dropped.
• CreateNewDatabase controls whether the target database is recreated from scratch (dropping everything), not whether individual objects absent from the project are dropped during an incremental publish. Setting it to False alone does not disable DropObjectsNotInSource behavior.

The system_health Extended Events session is enabled by default in Azure SQL Database and automatically captures deadlock XML graph events (xml_deadlock_report). The DBA can query the session ring buffer or the event file target to retrieve detailed deadlock information — victim transaction, participating sessions, resources, and wait chains — without configuring any additional trace.

Why the distractors are wrong:

• sys.dm_exec_requests shows currently executing and blocked requests in real time, but deadlocks are resolved instantly; by the time a DBA queries this DMV, the deadlock victim's session is gone. It is useful for detecting live blocking, not past deadlocks.
• sys.dm_os_wait_stats aggregates cumulative wait statistics across all sessions and does not record per-incident deadlock details or the objects involved.
• The 'blocked process threshold' option triggers blocked process reports (for blocking exceeding a time threshold), not deadlock events. sys.dm_exec_query_stats contains aggregated query execution statistics, not deadlock information.

In the DAB configuration file (dab-config.json), the per-entity REST pagination limit is set using the 'entities.<entity-name>.rest.max-page-size' property. This defines the maximum number of items the endpoint will return in a single page, and DAB automatically includes a 'nextLink' continuation token when more results exist.

Why the distractors are wrong:

• 'rest.max-response-size-mb' is not a valid DAB configuration property; DAB does not limit responses by raw byte size via this setting.
• 'pagination.cursor-enabled' does not exist as a standalone DAB configuration key; cursor behavior is implicit in the pagination model when max-page-size is set.
• 'runtime.pagination.default-page-size' is a plausible-sounding global setting, but DAB's actual global default is set under 'runtime.pagination' with the key 'default-page-size'; however, the question asks about the property that controls a specific entity's max page size, which is the entity-level setting. The global runtime default does not override the per-entity maximum.

Row-Level Security (RLS) uses security predicates defined in inline table-valued functions that are bound to a table via a SECURITY POLICY. The filter is applied automatically and transparently at the database engine level on every query against the table, requiring zero application changes.

Why the distractors are wrong:

• Dynamic Data Masking hides column values but does not restrict which rows are returned; a sales rep could still see all rows, just with some columns masked.
• Column-level DENY permissions restrict access to specific columns, not specific rows, and would prevent all users from reading SalesRepID entirely, not filter rows per user.
• Using a view with filtered queries would require the application to change its queries to target the view instead of the base table, violating the 'no application changes' requirement.

Azure Monitor supports metric alert rules for Azure SQL Database metrics including 'dtu_consumption_percent'. Configuring an alert rule with a 5-minute aggregation window and an 85% threshold directly satisfies the first requirement. Query Store automatically captures runtime statistics for every query and, when combined with Query Performance Insight in the Azure portal, provides a visual timeline of top queries by resource consumption and surfaces performance regressions by comparing query plans before and after deployments.

Why the distractors are wrong:

• SQL Auditing records security events (logins, schema changes, DML) to enable compliance tracking; it does not capture query CPU usage metrics or facilitate performance regression analysis.
• sys.dm_os_wait_stats provides server-wide cumulative wait statistics and is not granular enough for per-query analysis. SQL Agent jobs do not exist in Azure SQL Database (only SQL Managed Instance supports SQL Agent).
• Streaming execution plans to Event Hub via diagnostic settings is not a supported diagnostic log category for Azure SQL Database; execution plans are accessible through Query Store and DMVs, not as a streaming diagnostic log.