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

Question 1

Which THREE items should be included in a standard database deployment checklist to ensure consistency across environments?

Accepted Answer

Standardized service account permissions. A standardized database deployment checklist must capture the security and configuration elements that make environments behave identically. Option A (Standardized service account permissions) is correct because the database service account must have the same OS-level and DB-level privileges in every environment, otherwise the instance may fail to start or access files differently. Option C (Database instance configuration parameters) is correct because settings such as memory allocation, max_connections, and compatibility level directly affect performance and behavior, so they must be documented and applied consistently. Option E (Security access policies) is correct because authentication rules, roles, grants, and network access controls must be replicated to avoid privilege drift or exposure between environments. Option B (Application codebase version) is not a database deployment checklist item—it belongs to application release management, even though the DB may support it. Option D (The name of the database lead developer) is irrelevant operational metadata and has no bearing on environment consistency.

Answer

Application codebase version

Answer

The name of the database lead developer

Question 2

Which TWO database features are best used to handle massive volumes of data while maintaining fast query speeds for historical analytics?

Accepted Answer

Table partitioning. Table partitioning (B) is correct because splitting a large table into smaller, manageable partitions—typically by date or range—lets the query engine scan only the relevant partitions (partition elimination), dramatically reducing I/O for historical analytics workloads. Columnstore indexes (C) are correct because they store data column-wise with compression and batch-mode processing, which is purpose-built for large-scale analytical scans and aggregations, delivering fast query speeds over massive data volumes. Clustered indexes on random UUIDs (A) are wrong because random values cause page splits and fragmentation, hurting write and read performance. Row-level triggers (D) add per-row procedural overhead and do nothing to accelerate analytical queries. Database mirroring (E) is a high-availability/DR feature that duplicates a database but provides no query-performance or analytical-scan benefit.

Answer

Clustered indexes on random UUIDs

Answer

Row-level triggers

Answer

Database mirroring

Question 3

Which TWO of the following are examples of logical database design tasks?

Accepted Answer

Normalizing the data schema. Normalizing the data schema (C) is a logical design task because it organizes tables and relationships to eliminate redundancy and anomalies without regard to physical storage. Defining entity-relationship models (E) is also logical design, as ER modeling captures entities, attributes, and relationships at a conceptual/logical level before implementation. The other options are physical design or operational tasks: choosing a RAID level (A) and selecting a database file path (B) concern storage layout, while configuring the buffer pool size (D) tunes memory allocation for performance.

Answer

Choosing the RAID level for storage

Answer

Selecting the database file path

Answer

Configuring the database buffer pool size

Question 4

Which THREE of the following are benefits of implementing a standardized database schema migration process?

Accepted Answer

Version control for schema changes. Option A (Version control for schema changes) is correct because a standardized migration process stores each schema change as a versioned, ordered script (e.g., in Git alongside application code), so the database's evolution can be tracked, reviewed, and rolled back to a known revision. Option B (Audit trails for all schema modifications) is correct because migrations are applied through a controlled, logged mechanism that records who ran which migration, when, and in what order, providing a reliable change history for compliance and troubleshooting. Option E (Consistency of database structure across environments) is correct because the same migration scripts are replayed identically on development, test, staging, and production, eliminating schema drift between environments. Option C is incorrect because hardware startup time is a function of the OS, firmware, and services, not of how database schema changes are managed. Option D is incorrect because password conversion is an application-level authentication concern (e.g., rehashing credentials), not something a schema migration process performs automatically.

Answer

Faster hardware startup times

Answer

Automatic conversion of user passwords

Question 5

Which THREE factors are critical to consider when configuring a database for disaster recovery (DR)?

Accepted Answer

Geographic distance between primary and DR sites. Geographic distance between primary and DR sites (B) is critical because it determines the latency and bandwidth available for replication and the site's exposure to the same regional failure; too close and a single disaster can take out both sites, too far and synchronous replication may become impractical. Recovery Time Objective (C) is essential because it defines the maximum acceptable downtime and therefore dictates the DR architecture — for example, whether you need hot standby with automatic failover or can tolerate restoring from backups. Recovery Point Objective (D) is equally essential because it defines the maximum tolerable data loss and drives the replication mode and frequency, such as synchronous vs. asynchronous replication or backup intervals. The brand of the server hardware (A) is not a DR-critical factor since DR design depends on capabilities and compatibility, not vendor branding, and the database developer's IDE preference (E) is irrelevant to recovery architecture and objectives.

Answer

The brand of the server hardware

Answer

The database developer's IDE preference

Question 6

A database administrator is planning a new SQL Server deployment and needs to ensure that the tempdb system database does not become a bottleneck during high-concurrency operations. Which configuration step should be performed during the initial setup?

Accepted Answer

Create multiple tempdb data files of equal size to distribute I/O contention across multiple threads.. Creating multiple tempdb data files of equal size is the standard SQL Server best practice for high-concurrency workloads. SQL Server uses proportional fill across data files in a filegroup, and multiple tempdb files allow allocation pages (PFS, GAM, SGAM) and I/O to be spread across files, reducing latch contention on allocation structures and distributing I/O across CPUs and storage queues. Equal sizing is essential so proportional fill distributes writes evenly rather than favouring the largest file.

Answer

Move the tempdb database to a network attached storage (NAS) share for high availability.

Answer

Set the recovery model of tempdb to Full to ensure point-in-time recovery.

Answer

Configure the tempdb database to use a single large file on a compressed volume.

Question 7

A company is designing a distributed NoSQL database deployment using MongoDB. They need to ensure high availability and automatic failover in the event that a primary node becomes unreachable. What must the architect configure to achieve this?

Accepted Answer

Deploy a MongoDB Replica Set with at least three nodes.. MongoDB achieves high availability and automatic failover through replica sets, which require a minimum of three nodes to maintain a majority for elections. Deploying a replica set with at least three nodes (option D) ensures that if the primary becomes unreachable, the remaining nodes can elect a new primary automatically, providing both redundancy and failover. This is the standard MongoDB architecture for HA.

Answer

Implement database mirroring using the primary-secondary synchronous replication setting.

Answer

Increase the write concern to 'all' to force nodes to write simultaneously.

Answer

Configure a sharded cluster with a single mongos instance.

Question 8

You are migrating an on-premises Oracle database to Oracle Cloud Infrastructure (OCI) using a tool that requires the least amount of downtime while keeping the source and destination databases in sync. Which method should you select?

Accepted Answer

Implement Oracle GoldenGate for continuous logical replication.. Oracle GoldenGate performs continuous logical replication by mining redo/archive logs on the source and applying changes to the target, keeping both databases in sync with minimal downtime. This makes it the standard choice for near-zero-downtime migrations where the source must remain available until cutover.

Answer

Perform a cold backup and restore to an OCI block storage volume.

Answer

Use Data Pump Export/Import with manual application of incremental backups.

Answer

Use SQL Developer to manually generate and execute insert scripts for all tables.

Question 9

You are deploying a MySQL database on Linux and want to ensure that the database files are isolated from system logs for performance reasons. Which MySQL configuration variable should you modify to specify the data directory location?

Accepted Answer

datadir. The datadir variable in MySQL specifies the directory where the database files (such as .frm, .ibd, and ibdata files) are stored. By modifying datadir, you can isolate these files from system logs, improving I/O performance by placing them on separate storage. This is the correct variable for changing the data directory location.

Answer

tmpdir

Answer

log_error

Answer

basedir

Question 10

During the testing phase of a database deployment, you discover that the database is not accepting external connections. You have verified that the database service is running. What is the most likely culprit?

Accepted Answer

The host firewall is blocking the database port.. If the database service is confirmed running but external connections are refused, the most likely cause is a host firewall blocking the database's listening port (e.g., 1433 for SQL Server, 3306 for MySQL, 5432 for PostgreSQL). The firewall operates at the network layer and silently drops or rejects inbound traffic before it ever reaches the database engine, producing exactly this symptom.

Answer

The backup agent is running.

Answer

The database schema is not fully migrated.

Answer

The database is in Single User mode.

Question 11

An administrator is verifying the storage requirements for a new PostgreSQL database installation. Which configuration parameter in the postgresql.conf file directly impacts the amount of memory allocated for shared buffers?

Accepted Answer

shared_buffers. In PostgreSQL, the shared_buffers parameter in postgresql.conf defines the amount of memory the database server uses for shared memory buffers — the cache of data pages shared across all connections. It directly controls how much RAM PostgreSQL allocates for caching table and index data, and tuning it is one of the most impactful performance settings.

Answer

maintenance_work_mem

Answer

max_connections

Answer

work_mem

Question 12

You are planning the disk layout for a SQL Server production database. Which THREE best practices should you follow to optimize storage performance?

Accepted Answer

Place transaction log files on high-speed, low-latency storage.. Option B is correct because transaction log files (.ldf) are written sequentially and are highly latency-sensitive, so placing them on high-speed, low-latency storage (e.g., SSD/NVMe or a dedicated RAID 10 array) minimizes log write waits and improves transaction throughput. Option D is correct because keeping backups on separate physical volumes from the data files prevents a single disk failure from destroying both the database and its backups, and it avoids backup I/O competing with production data I/O. Option E is correct because isolating data files from the OS volume prevents OS paging, updates, and system I/O from contending with database reads/writes, and it also protects the database from OS volume corruption or reinstallation. Option A is incorrect because co-locating .mdf and .ldf on the same physical drive creates I/O contention between random data access and sequential log writes and removes fault isolation. Option C is incorrect because storing backups on the same drive as the data files means a single drive failure can destroy both the live database and its backups, and backup writes will degrade production performance.

Answer

Place data files (.mdf) and transaction log files (.ldf) on the same physical drive.

Answer

Place backup files on the same drive as the database data files.

Question 13

An administrator needs to improve the security posture of a newly deployed PostgreSQL database. Which TWO actions should be performed to harden the environment?

Accepted Answer

Modify pg_hba.conf to allow only specific IP addresses to connect.. Option B is correct because editing pg_hba.conf to restrict connections to specific IP addresses enforces host-based access control, ensuring only trusted hosts can reach the PostgreSQL instance and reducing the attack surface. Option E is correct because setting password_encryption to 'scram-sha-256' ensures stored passwords use SCRAM-SHA-256, a salted challenge-response mechanism that is far stronger than md5 and protects credentials against replay and offline cracking. Option A is wrong because the 'trust' method allows connections without any password, completely undermining authentication. Option C is wrong because granting superuser to all application service accounts violates least privilege and magnifies the impact of any compromise. Option D is wrong because changing the port to a random high number is only security through obscurity and does not provide real protection against determined attackers.

Answer

Enable the 'trust' authentication method for all users.

Answer

Grant 'superuser' privileges to all application service accounts.

Answer

Change the default database port to a random high number for security through obscurity.

Question 14

When preparing a database deployment plan, what THREE factors must be considered to ensure successful capacity planning?

Accepted Answer

Peak transactional throughput (transactions per second).. Option A (peak transactional throughput in transactions per second) is correct because capacity planning must size CPU, memory, and I/O to handle the busiest workload period, not just average load, since peak TPS drives the required IOPS and connection limits. Option C (projected data growth rates over the next 12 to 24 months) is correct because storage, backup windows, and index maintenance scale with data volume, so forecasting growth prevents premature exhaustion of disk and memory resources. Option D (data retention and archival policies) is correct because retention periods determine how long data stays in primary storage versus being archived or purged, directly affecting total storage requirements, backup size, and query performance. Option B is incorrect because dashboard color scheme is a cosmetic UI preference with no bearing on resource sizing or performance. Option E is incorrect because the total count of registered Active Directory users does not indicate concurrent database connections or workload; only active, concurrent sessions and their query patterns matter for capacity planning.

Answer

The chosen color scheme for the database management dashboard.

Answer

The total number of users currently registered in the company's Active Directory.

Question 15

A database administrator is deploying a new PostgreSQL 16 cluster for an online transaction processing (OLTP) application. The application must survive the loss of any single server in the primary data center without manual intervention, and the administrator wants the cluster to automatically promote a standby if the primary fails. The administrator has three servers available and wants to minimize the risk of split-brain. Which deployment approach should the administrator use?

Accepted Answer

Deploy a three-node cluster managed by Patroni with etcd as the distributed configuration store to elect a leader and promote a standby automatically.. Patroni with a three-node etcd cluster provides distributed consensus for leader election, automatic promotion of a standby, and protection against split-brain. A single synchronous standby with manual scripts does not automate failover. Logical replication and WAL shipping without a cluster manager do not promote a standby or elect a leader, so they cannot meet the automatic failover requirement.

Answer

Configure streaming replication with a single synchronous standby and a manual failover script triggered by a monitoring alert.

Answer

Use pg_basebackup to create a physical standby, configure WAL shipping, and rely on the operating system cluster software to restart PostgreSQL on the standby.

Answer

Configure logical replication from the primary to three subscribers and use a load balancer to route writes to the first subscriber that responds.