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

Question 1

A database administrator is planning the deployment of a new Microsoft SQL Server 2022 instance for a financial application. The application requires that committed transactions survive a power loss and that the storage subsystem provide the lowest possible write latency. The administrator must choose the appropriate recovery model and disk configuration. Which combination best meets these requirements?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Set the database to the full recovery model and place data files on RAID 10 and transaction log files on a separate RAID 10 array.. The full recovery model preserves the transaction log for point-in-time recovery, which is required for financial data. RAID 10 provides low-latency writes through mirroring and striping without parity overhead. Separating data and log files onto distinct RAID 10 arrays isolates I/O patterns and further reduces contention, meeting both durability and write latency requirements.

Answer

Set the database to the full recovery model and place data and log files on the same RAID 6 array to maximize fault tolerance.

Answer

Set the database to the simple recovery model and place data and log files on the same RAID 5 array.

Answer

Set the database to the bulk-logged recovery model and place all files on a single RAID 0 array for maximum throughput.

Question 2

A database administrator is deploying a new online transaction processing (OLTP) database on a four-socket server with 512 GB of RAM and a storage area network (SAN) that uses solid-state drives. The application performs many small, random reads and writes. The administrator needs to configure the database instance to minimize physical I/O and improve response time. Which configuration should the administrator implement?

Accepted Answer

Increase the database buffer pool size to cache frequently accessed data pages in memory.. The buffer pool caches data pages in memory, so frequently accessed rows are served without touching the SAN. With 512 GB of RAM and a random-access OLTP workload, a large buffer pool directly reduces physical I/O and response time. Other options address write throughput, sorting, or storage size, none of which target the primary random-read bottleneck.

Answer

Enable table compression on all tables to reduce the storage footprint on the SAN.

Answer

Configure a larger sort area to allow all ORDER BY operations to complete in memory.

Answer

Set the database to use asynchronous I/O and increase the number of writer processes.

Question 3

A database administrator is deploying a new database that will store sensitive customer data. The organization requires that all data at rest be encrypted using AES-256. The database supports Transparent Data Encryption (TDE). After enabling TDE, what additional step must be performed to ensure that the encryption keys are protected and can be recovered in case of a disaster?

Accepted Answer

Back up the TDE certificate and its private key to a secure location.. When using Transparent Data Encryption, the database encryption key is protected by a certificate. To ensure recoverability, you must back up the certificate and its private key. This backup allows you to restore the encrypted database on another server or after a disaster. Without it, the data is irrecoverable.

Answer

Configure a Database Master Key (DMK) for the master database.

Answer

Enable encryption of the tempdb system database.

Answer

Back up the database master key and the service master key to a secure location.

Question 4

A database administrator is deploying a new PostgreSQL 15 cluster on Linux and wants to protect against data loss if a single disk in the data volume fails. The server has four identical 1 TB SSDs available. Which RAID level should the administrator configure for the PostgreSQL data directory?

Accepted Answer

RAID 10. RAID 10 mirrors data across pairs of disks and then stripes across those pairs, delivering redundancy plus the high random I/O throughput a PostgreSQL data directory requires. It tolerates at least one disk failure and often more, depending on which disks fail, while avoiding the parity write penalty of RAID 5 and the total lack of protection in RAID 0.

Answer

RAID 0

Answer

RAID 5

Answer

RAID 1

Question 5

A database administrator is deploying a new PostgreSQL database cluster that must support a read-intensive reporting workload. The reporting queries are long-running and scan large tables. The administrator wants to avoid blocking write transactions on the primary server. Which deployment approach should the administrator use?

Accepted Answer

Set up streaming replication with hot standby and route reporting queries to the standby server.. Streaming replication with hot standby allows a PostgreSQL standby to serve read-only queries, so long-running reports run on a separate server. This keeps the primary free to handle write transactions without competition from reporting queries. Synchronous replication would add write latency, while parallel query and partitioning keep the load on the primary.

Answer

Enable parallel query execution on the primary server to speed up reporting queries.

Answer

Configure synchronous replication to a standby server and direct reporting queries to the standby.

Answer

Implement table partitioning on the large tables and run reports against the primary server.

Question 6

A database administrator is planning the deployment of a new database server. The organization requires that the database be able to recover from a failure with minimal data loss, and the Recovery Point Objective (RPO) is 5 minutes. Which backup strategy should the administrator implement?

Accepted Answer

Full backups taken once per day plus transaction log backups every 5 minutes.. To achieve an RPO of 5 minutes, transaction log backups must be taken at intervals no greater than 5 minutes, combined with periodic full backups. This allows recovery to a point in time just before the failure, minimizing data loss. Daily full backups alone are insufficient, and differential or snapshot backups at longer intervals do not meet the requirement.

Answer

Differential backups taken every 6 hours.

Answer

Full backups taken once per day.

Answer

Snapshot backups taken every hour.

Question 7

A data engineer is deploying a new MySQL 8.0 instance that will store customer records for users in the European Union. The compliance team requires that all data at rest be encrypted and that encryption keys be managed separately from the database server. Which action should the engineer take?

Accepted Answer

Enable InnoDB tablespace encryption using a keyring plugin backed by a remote key management service.. InnoDB tablespace encryption with a keyring plugin connected to a remote key management service encrypts the tablespaces, redo logs, and undo logs while keeping the master key outside the database host. This directly meets both the at-rest encryption mandate and the separation-of-key-custody requirement, unlike transport encryption or filesystem-level encryption.

Answer

Enable binary log encryption and rotate the binary log encryption key every 30 days.

Answer

Configure SSL/TLS for all client connections and require X.509 certificates for authentication.

Answer

Enable Transparent Data Encryption at the operating system level using eCryptfs on the data directory.

Question 8

A database administrator is deploying a new PostgreSQL 15 instance that will store customer PII. The security policy requires that all data at rest be encrypted, and the platform team wants encryption tied to the host's Trusted Platform Module without managing passphrases. Which deployment approach best satisfies this requirement?

Accepted Answer

Rely on the operating system's full-disk encryption configured with TPM-backed key sealing, and leave PostgreSQL's own storage unencrypted.. Storing PII at rest requires confidentiality for every byte the database writes, including WAL, temporary files, and indexes. TPM-sealed full-disk encryption delivers that transparently and removes the operational burden of passphrase entry, because the key is unsealed only by the specific host. Application-layer column encryption and TLS protect narrower scopes, and checksums address integrity rather than secrecy.

Answer

Set the ssl=on and ssl_cert_file parameters in postgresql.conf so that client connections and stored data are both encrypted.

Answer

Enable the pgcrypto extension and encrypt individual PII columns with pgp_sym_encrypt using a key stored in the application config.

Answer

Configure data_checksums=on during initdb so that every page is checksummed and therefore protected against unauthorized reads.

Question 9

A database administrator is deploying a new database instance that will store sensitive customer information. The organization must comply with PCI DSS requirements for encryption of data at rest and in transit. Which TWO configurations should the administrator implement to meet these requirements? (Choose two.)

Accepted Answer

Configure SSL/TLS for all client connections to the database.. TDE encrypts data files at rest, and SSL/TLS encrypts data in transit, directly addressing PCI DSS encryption requirements. Column-level encryption for all columns is impractical, auditing does not encrypt data, and password hashing only protects credentials, not the data itself.

Answer

Enable database auditing to track all SELECT statements on customer tables.

Answer

Configure the database to use only strong password hashing for authentication.

Answer

Implement column-level encryption for all columns in the database.

Question 10

A database administrator is planning a new SQL Server 2022 deployment that must support a read-only reporting workload without impacting the primary OLTP workload. The reporting queries are long-running and scan large tables. Which two features should the administrator implement to meet this requirement? (Choose two.)

Accepted Answer

Configure an Always On availability group with a readable secondary replica.. A readable secondary replica in an Always On availability group moves reporting queries off the primary instance, and a columnstore index makes the large scans those queries perform far more efficient through columnar storage and compression. Together they isolate the reporting workload while accelerating it, which is exactly what the scenario demands.

Answer

Enable snapshot isolation on the primary database to reduce blocking.

Answer

Configure transactional replication to push data to a separate reporting server.

Answer

Enable Resource Governor to cap CPU usage for reporting sessions.

Question 11

An organization is deploying a new MySQL 8.0 database for an order-entry application. The team needs the database to enforce referential integrity so that an order row cannot reference a customer that does not exist. Which deployment step accomplishes this?

Accepted Answer

Create the orders table with an InnoDB FOREIGN KEY constraint referencing the customers table primary key.. Referential integrity is enforced when the child table carries a foreign key that names the parent table and column. The engine then validates every write against the parent and applies the configured action on parent deletion or update. Uniqueness, privileges, and range checks each constrain a different aspect of the data and none of them can detect a reference to a missing customer.

Answer

Add a CHECK constraint on orders.customer_id that verifies the value is greater than zero.

Answer

Grant the application account only SELECT and INSERT privileges on the orders table.

Answer

Create a unique index on the customer_id column of the orders table so duplicate references are impossible.

Question 12

A database administrator is deploying a new database on a cloud platform. The organization requires that the database automatically scale storage capacity as data grows, without manual intervention, and that it maintain high availability across multiple availability zones. Which cloud database deployment option should the administrator choose?

Accepted Answer

A managed relational database service configured with a multi-AZ deployment and storage auto-scaling enabled.. A managed relational database service with multi-AZ deployment and storage auto-scaling automatically increases storage and maintains a standby replica in another availability zone for failover. This satisfies both automatic scaling and high availability. Self-managed, NoSQL with manual sharding, and data warehouse options do not meet these specific requirements.

Answer

A NoSQL database deployed on a cluster of VMs across multiple zones with manual sharding.

Answer

A data warehouse service with columnar storage and automatic scaling of compute nodes.

Answer

A single virtual machine running a self-managed database instance with attached SSD storage.

Question 13

A data warehouse team is deploying a partitioned fact table in SQL Server 2022 that will hold seven years of sales data. Most queries filter on the order date, and the team wants the oldest year moved to cheaper storage while remaining queryable. Which deployment design best meets these goals?

Accepted Answer

Create a partitioned table with a partition function on the order date, map each year to its own filegroup, and place the oldest filegroup on cheaper storage with a read-only filegroup setting.. Native partitioning on the date column provides two things at once: the optimizer eliminates partitions that cannot match a date predicate, and each partition lives in its own filegroup so it can be placed on a different storage tier. Marking the oldest filegroup read-only prevents accidental modification while keeping the data available to the same queries.

Answer

Create seven separate tables, one per year, and build a UNION ALL view across them for reporting queries.

Answer

Enable table compression on the fact table and archive the oldest year to a separate database that is restored on demand.

Answer

Create a single unpartitioned table with a clustered columnstore index and rely on column elimination to skip the oldest year.

Question 14

A database administrator is deploying a new Oracle 19c database and needs to ensure that if the production server's power fails, no committed transactions are lost. The storage array has a write-back cache with a battery-backed unit. Which configuration should the administrator verify?

Accepted Answer

Verify that the storage array's write-back cache is protected by the battery and that the operating system does not disable write caching flush.. Commit durability requires that redo records reach nonvolatile storage before the commit is acknowledged. A battery-backed write-back cache keeps cached writes safe across a power failure, and the operating system must pass flush commands to the array so commits are not stranded in volatile memory. Verifying both conditions directly addresses the requirement.

Answer

Increase the size of the redo log buffer to reduce the frequency of log writer flushes.

Answer

Set the database to ARCHIVELOG mode and enable automatic archival.

Answer

Enable FORCE LOGGING at the tablespace level to guarantee redo is generated for all changes.

Question 15

A database administrator is preparing to deploy a new Oracle Database 19c instance that will store regulated financial data. The compliance team requires that all data at rest be encrypted and that encryption keys be managed outside the database to satisfy separation-of-duties controls. Which deployment approach should the administrator use?

Accepted Answer

Enable Transparent Data Encryption with an external key management system using PKCS#11. Transparent Data Encryption with an external key management system encrypts data at rest while delegating master key custody to a separate appliance through PKCS#11. This satisfies both the encryption requirement and the separation-of-duties control, because the database server never holds the master key locally.

Answer

Enable Transparent Data Encryption with a local wallet stored on the database server

Answer

Enable Data Redaction policies on all columns containing regulated financial data

Answer

Enable database auditing and configure unified audit policies on all sensitive tables