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

Question 1

Which document is essential to create during the database planning phase to define the specific requirements, constraints, and business rules of the proposed system?

Accepted Answer

Database Requirements Specification. A Database Requirements Specification (DRS) serves as the roadmap for the database build, ensuring all stakeholders agree on the system scope.

Answer

User Access Policy

Answer

Disaster Recovery Plan

Answer

Database Schema Diagram

Question 2

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. A SQL Server Audit or similar database-level auditing feature allows the tracking of specific actions (like ALTER or DROP) performed by privileged accounts.

Answer

Row-Level Security

Answer

Data Masking

Answer

Query Store

Question 3

A database is experiencing high lock contention on a specific table during batch update processes. What is the most effective way to resolve this issue without changing the application code?

Accepted Answer

Enabling snapshot isolation. Implementing snapshot isolation (or read-committed snapshot isolation) allows readers to access the versioned data without blocking writers, significantly reducing contention.

Answer

Switching to an asynchronous commit model

Answer

Increasing the memory allocation for the buffer pool

Answer

Dropping all non-clustered indexes

Question 4

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 is the standard relational database mechanism for enforcing referential integrity between tables.

Answer

Check constraint

Answer

Unique constraint

Answer

Default constraint

Question 5

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 is the accumulation of unused space within data files, often caused by frequent updates and deletes that leave empty pages that aren't automatically reclaimed.

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 6

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. Always-On availability groups or high-availability clusters allow for near-instant failover, meeting very aggressive RTOs.

Answer

Transaction log shipping to a standby server

Answer

Tape-based full backups

Answer

Daily incremental backups

Question 7

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 rules (either host-based or network-based) are the primary way to restrict database access to only authorized application server IPs.

Answer

SSL/TLS certificates

Answer

Database user permissions

Answer

Database proxy authentication

Question 8

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

Accepted Answer

To store metadata about the database structure. The data dictionary contains metadata about the database, such as table definitions, column types, and constraints, acting as the central repository for database structure.

Answer

To manage physical storage allocation

Answer

To execute complex queries

Answer

To store the actual user data

Question 9

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) is the primary metric for random I/O performance, while throughput is for sequential operations.

Answer

Cache hit ratio

Answer

Sequential throughput

Answer

Latency variation

Question 10

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. Enabling FIPS-compliant encryption modules ensures that all cryptographic operations performed by the database meet the federal security standard.

Answer

Database hardening scripts

Answer

User password rotation policies

Answer

Multi-factor authentication (MFA)

Question 11

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 simulates expected user traffic to determine if the database and hardware can handle the production workload without bottlenecking.

Answer

User acceptance testing (UAT)

Answer

Unit testing

Answer

Security patching

Question 12

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. Asynchronous replication acknowledges transactions on the primary before they reach the secondary, meaning some committed data might not have been replicated, leading to data loss upon failover.

Answer

Increased latency on the primary server

Answer

Deadlocks on the primary

Answer

Inability to perform reads on the secondary

Question 13

Which TWO of the following are essential components of a robust database backup plan?

Accepted Answer

Periodic full and incremental backups. A complete backup plan must account for both the schedule (frequency) and the location (off-site storage for disaster recovery).

Answer

Testing of backup restoration procedures

Answer

Automatic database schema updates

Answer

Installation of the latest OS patches

Question 14

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). Data structure, scalability requirements, and ACID transaction needs are the primary drivers in this architectural decision.

Answer

The developer's preferred operating system

Answer

The color of the database console UI

Question 15

Which TWO database deployment configurations help mitigate the impact of a hardware failure on the primary storage controller?

Accepted Answer

Multi-path I/O (MPIO) configuration. Redundancy at the hardware/storage layer is achieved through RAID configurations and multi-path I/O (MPIO).

Answer

Full recovery model enabled

Answer

SQL Server Always On

Answer

Database partitioning