DS0-001 Database Planning And Deployment Practice Test 3 — 15 Questions

Question 1

During a database deployment, the administrator needs to ensure that audit logs are captured for all privileged user actions. Which component should be configured?

Accepted Answer

Database Audit Specification. To ensure audit logs are captured for all privileged user actions, the administrator should configure a Database Audit Specification. This feature in SQL Server allows auditing of specific database-level actions, such as SELECT, INSERT, UPDATE, DELETE, and EXECUTE by privileged users. It captures these actions to the audit log for compliance and monitoring.

Answer

Row-Level Security

Answer

Data Masking

Answer

Query Store

Question 2

Which database feature is used to automatically maintain data integrity by ensuring that a value in a child table exists as a primary key in a parent table?

Accepted Answer

Foreign key constraint. A foreign key constraint enforces referential integrity by requiring that values in a child table's foreign key column match existing primary key (or unique key) values in the parent table. This automatically prevents orphaned records and maintains data consistency across related tables. It is the standard mechanism for parent-child integrity.

Answer

Check constraint

Answer

Unique constraint

Answer

Default constraint

Question 3

When planning database capacity, you calculate the projected growth of a table based on transaction volume and row size. What does 'database bloat' refer to in this context?

Accepted Answer

The accumulation of unused space in data pages. Database bloat refers to the accumulation of unused or dead space inside data pages — space that was allocated for rows which have since been deleted or updated but not reclaimed. In MVCC engines like PostgreSQL and InnoDB, UPDATEs create new row versions and DELETE marks rows dead, leaving holes in pages that inflate the physical file size without adding usable data. This directly affects capacity planning because the on-disk footprint grows faster than the logical row count.

Answer

The rapid growth of transaction logs

Answer

The degradation of query performance over time

Answer

The increase in the number of concurrent connections

Question 4

An organization is deploying a database that requires a Recovery Time Objective (RTO) of less than one minute. Which backup and recovery strategy is most appropriate?

Accepted Answer

High-availability clustering. An RTO under one minute demands near-instantaneous failover with minimal or zero data loss, which high-availability clustering provides by keeping a synchronized standby node ready to take over automatically. Clustering solutions such as SQL Server Always On availability groups or Oracle RAC detect failure and redirect workloads within seconds, satisfying sub-minute recovery. Log shipping, tape, and daily incrementals all involve longer recovery windows.

Answer

Transaction log shipping to a standby server

Answer

Tape-based full backups

Answer

Daily incremental backups

Question 5

A database administrator is deploying a production environment and needs to restrict network access to the database server. Which tool or setting is the first line of defense at the network layer?

Accepted Answer

Firewall access control lists. Firewall access control lists (ACLs) operate at the network layer and are the first line of defense for restricting which hosts or subnets can reach the database server's listening port. They filter traffic before it reaches the database engine, providing the outermost boundary of protection.

Answer

SSL/TLS certificates

Answer

Database user permissions

Answer

Database proxy authentication

Question 6

What is the primary role of a Data Dictionary in a database management system?

Accepted Answer

To store metadata about the database structure. A Data Dictionary is the repository of metadata — the 'data about data' — describing the database's structure: table names, column names, data types, constraints, relationships, indexes, and often business definitions. It does not hold user data itself; it describes how that data is organized. This is why the correct answer is 'to store metadata about the database structure.'

Answer

To manage physical storage allocation

Answer

To execute complex queries

Answer

To store the actual user data

Question 7

A database administrator is evaluating storage subsystems for a mission-critical database. The database uses random I/O patterns for most operations. Which storage performance metric is most critical for this workload?

Accepted Answer

IOPS. IOPS (Input/Output Operations Per Second) measures the number of read/write operations a storage subsystem can handle per second. For a database with random I/O patterns, each operation targets a different block, so the ability to service many small, scattered I/Os concurrently is the limiting factor. IOPS is therefore the most critical metric for random I/O workloads, directly determining transaction throughput and query responsiveness.

Answer

Cache hit ratio

Answer

Sequential throughput

Answer

Latency variation

Question 8

You are deploying a database that requires compliance with FIPS 140-2. What must be configured to ensure the database meets this security standard?

Accepted Answer

FIPS-mode encryption. FIPS 140-2 is a U.S. government standard for cryptographic modules, so compliance requires that the database use FIPS-validated cryptographic modules — typically enabled via a FIPS mode setting that forces all encryption operations through validated algorithms and libraries. Without FIPS-mode encryption, the database may use non-validated ciphers even if encryption is otherwise enabled.

Answer

Database hardening scripts

Answer

User password rotation policies

Answer

Multi-factor authentication (MFA)

Question 9

Which of the following activities is performed during the 'Testing' phase of a database deployment to ensure the system meets its performance targets?

Accepted Answer

Load testing. Load testing is performed during the Testing phase to ensure the database system meets its performance targets under expected and peak loads. It simulates concurrent user activity and measures response times, throughput, and resource utilization, directly validating performance requirements. This makes it the correct activity for verifying performance targets.

Answer

User acceptance testing (UAT)

Answer

Unit testing

Answer

Security patching

Question 10

A database is configured with an 'Asynchronous Commit' replication mode. What is the potential risk during a primary server failure?

Accepted Answer

Potential data loss. In asynchronous commit replication, the primary acknowledges a transaction to the client before the standby has confirmed receipt or applied it. If the primary fails before the WAL/binlog is shipped and replayed on the standby, those acknowledged transactions are lost when failover occurs — this is the classic RPO > 0 trade-off. The risk is data loss, not latency or locking.

Answer

Increased latency on the primary server

Answer

Deadlocks on the primary

Answer

Inability to perform reads on the secondary

Question 11

Which THREE factors should be considered when choosing between a traditional RDBMS and a NoSQL database for a new project?

Accepted Answer

Consistency requirements (ACID vs BASE). Option A is correct because consistency requirements directly drive the choice: a traditional RDBMS enforces ACID transactions (atomicity, consistency, isolation, durability) with strong consistency, whereas many NoSQL databases follow BASE (basically available, soft state, eventual consistency), so the project's tolerance for stale reads and need for multi-row transactions must be evaluated. Option C is correct because data structure determines fit: RDBMSs use fixed schemas with tables, rows, and joins suited to structured data, while NoSQL stores (document, key-value, column-family, graph) handle semi-structured or unstructured data with flexible, evolving schemas. Option D is correct because scalability requirements differ fundamentally: RDBMSs typically scale vertically by adding CPU/RAM to a single node and are harder to shard, while NoSQL systems are designed for horizontal scaling via partitioning/replication across commodity nodes. Option B does not belong because the developer's preferred operating system is irrelevant—both RDBMS and NoSQL databases run on Windows, Linux, and macOS, and the choice is driven by data and workload needs, not the developer's desktop OS. Option E does not belong because the color of the database console UI is a cosmetic preference with no bearing on data modeling, consistency, scalability, or performance.

Answer

The developer's preferred operating system

Answer

The color of the database console UI

Question 12

Which TWO tasks are critical before performing a major database schema upgrade?

Accepted Answer

Full database backup. Option A (Full database backup) is correct because a complete, restorable backup (e.g., a full backup plus any needed transaction log backups, or a snapshot) is the only reliable rollback path if the schema upgrade fails or corrupts data, so it must be taken immediately before the change. Option C (Migration script validation in a staging environment) is correct because rehearsing the DDL/DML migration scripts against a staging copy that mirrors production schema, data volume, and dependencies exposes syntax errors, lock contention, and constraint or index conflicts before touching production. Option B (Adding more memory) is not a prerequisite for a schema upgrade; memory sizing is a capacity-tuning activity and does not protect data or validate the migration. Option D (Deleting all stored procedures) is destructive and unnecessary, since procedures should be reviewed for dependency compatibility, not removed. Option E (Updating the OS kernel version) is unrelated to schema migration readiness and introduces additional change risk rather than mitigating it.

Answer

Adding more memory to the server

Answer

Deleting all stored procedures

Answer

Updating the OS kernel version

Question 13

Which TWO settings should be configured in a database deployment to improve performance for heavy read-intensive reporting workloads?

Accepted Answer

Columnstore indexes. B is correct because columnstore indexes store data in a column-oriented, compressed format with batch-mode execution, which dramatically speeds up the large scans and aggregations typical of read-intensive reporting workloads. E is correct because read-only secondary replicas offload reporting queries from the primary, letting the primary handle transactional traffic while secondaries serve reads, improving overall read throughput and scalability. A is wrong because adding transaction logs does not improve read performance and mainly affects write durability/recovery. C is wrong because dropping primary key constraints harms data integrity and removes useful indexes rather than helping reads. D is wrong because asynchronous commit mode is a write-side durability trade-off and does not accelerate read-intensive reporting.

Answer

Increasing the number of transaction logs

Answer

Dropping primary key constraints

Answer

Asynchronous commit mode

Question 14

Which THREE database configuration settings help ensure data security and regulatory compliance?

Accepted Answer

Role-Based Access Control (RBAC). Role-Based Access Control (RBAC) is correct because it enforces least-privilege access by granting permissions to roles rather than individual users, which is a core requirement of security frameworks and regulatory mandates such as SOX, HIPAA, and PCI DSS. Transparent Data Encryption (TDE) is correct because it encrypts data at rest (data files, log files, and backups) using a database encryption key protected by a certificate or asymmetric key, satisfying compliance requirements for protecting stored sensitive data. Audit logging of DDL and DML operations is correct because it creates a tamper-evident trail of who changed what schema objects and data and when, which is explicitly required for regulatory audits and forensic investigation. Disabling transaction log backups is not correct because it breaks the log chain, eliminates point-in-time recovery, and increases data-loss risk, which harms rather than helps compliance. Automatic plan cache clearing is not correct because it is a performance-tuning behavior that has no bearing on access control, encryption, or auditability.

Answer

Disabling transaction log backups

Answer

Automatic plan cache clearing

Question 15

Which TWO indicators should be monitored when testing a database deployment to ensure it is correctly sized for performance?

Accepted Answer

Disk I/O latency. Disk I/O latency (B) is a correct indicator because database performance is heavily bound by storage throughput and response time; high read/write latency directly signals that the deployment's disk subsystem is undersized for the workload. CPU utilization (C) is also correct because sustained high processor usage indicates the database engine is CPU-bound, meaning the instance is not correctly sized in terms of compute capacity. Together these two metrics reveal whether the storage and compute tiers meet the performance demands of the database. The total number of network users (A) is not a direct measure of database sizing, since user count alone says nothing about query load or resource consumption. The number of open database connections (D) reflects concurrency but not whether the underlying resources are adequately provisioned. Server uptime (E) only indicates availability over time and has no bearing on performance sizing.

Answer

Total number of users on the network

Answer

Number of open database connections

Answer

Server uptime