40 questions / 10 random questions
Random questions, instant feedback, and review for missed questions.
Which pair should be agreed with business stakeholders first when defining database disaster-recovery requirements?
Answer: RPO for tolerable data loss and RTO for tolerable downtime
RPO and RTO provide the basis for backup frequency, log retention, replication, recovery sites, and cost. Agree on them as measurable business requirements.
Daily backup jobs report success. What is the most reliable way to confirm recoverability?
Answer: Regularly restore into an isolated environment and validate integrity, application access, and elapsed time
Backup success does not guarantee restore success. A restore drill validates media, keys, permissions, procedures, and dependencies while measuring achieved RTO and recovery point.
Which statement correctly describes the relationship between high availability and backups?
Answer: HA reduces downtime, while backups recover from deletion, corruption, and prior points in time
Replication and clustering improve availability, but accidental deletes, ransomware, and logical corruption may propagate. Combine HA with independent backup and restore.
Which backup-storage design appropriately addresses ransomware and administrator mistakes?
Answer: Keep multiple generations in a separate failure domain with immutability and separated deletion privileges
Separate accounts, regions, or media so production credential compromise cannot immediately delete backups. Combine immutable retention with restore testing.
Which backup format is generally easier when moving selected schemas to a different database engine?
Answer: A logical export that represents DDL and data in a portable form
Logical exports support selective migration and transformation, though types, collations, sequences, privileges, and procedures still require mapping and validation.
What is important when backing up multiple files of a running database with storage snapshots?
Answer: Use the database engine's consistency procedure or an application-consistent snapshot
Verify engine support for crash consistency, atomic multi-volume capture, and required logs. Uncoordinated file copies can create an inconsistent backup.
A database must be restored to just before an accidental delete at 14:37. What does PITR require?
Answer: A base backup and an unbroken sequence of transaction logs through the target time
PITR replays WAL, binary logs, transaction logs, or archived redo from a base backup. A gap prevents recovery beyond that point.
Which key-management practice is appropriate for encrypted backups stored remotely?
Answer: Separate backup and key failure domains and manage access, rotation, old-key retention, and recovery procedures
Encryption becomes unrecoverable if keys are lost. Separate access with a KMS and include old-key availability and disaster key recovery in restore drills.
Which metrics should be prioritized for cross-platform monitoring of asynchronous replication health?
Answer: Send, receive, and apply position gaps, time lag, queue volume, and replication errors
Separate transport lag from apply lag. Monitor positions, queues, worker errors, and trends rather than relying on one seconds-behind value.
A standby is connected with low lag. What else should be checked before declaring it failover-ready?
Answer: Regularly exercise promotion, client routing, DNS or endpoints, writes, and isolation of the old primary
Synchronization alone does not guarantee service recovery. A failover drill must test role change, routing, credentials, application reconnection, and fencing to measure actual RTO.
A primary site is completely lost in an asynchronous replication setup. Which risk assessment is correct?
Answer: Unsent or unapplied transactions may be lost, requiring actual-lag assessment and a business decision
Asynchronous replication usually lowers latency in exchange for a possible data-loss window. Determine the last durable or applied position and communicate any gap from the declared RPO.
What is the core control for preventing split brain where both old and new primaries accept writes after a network partition?
Answer: Use quorum and fencing to reliably remove one node's write capability before changing roles
Fence the old primary from storage, network, or process access before promotion to preserve a single writer. Unreachable does not necessarily mean stopped.
Which connection design minimizes application changes after failover?
Answer: Use a role-aware service endpoint or proxy with bounded timeouts and reconnection
Abstract routing with a listener, cluster endpoint, proxy, or DNS, while applications safely recreate connections and retry transactions. Do not rely on DNS TTL alone.
Which preparation is appropriate before a planned primary-standby switchover?
Answer: Verify synchronization, capacity, client routing, backup jobs, and rollback criteria, then switch in a change window
A planned switchover verifies synchronization and dependent jobs, suppressing alerts only for the maintenance window. Validate writes, reads, jobs, backups, and monitoring afterward.
Which option can mitigate the risk of a large accidental delete immediately reaching ordinary replicas?
Answer: Maintain a delayed replica or PITR-capable log archive separate from normal HA
A delayed replica creates a detection window for logical mistakes but is not the freshest failover target. Combine it with PITR, immutable backups, and access control.
A query sent to a read replica immediately after a write returns stale data. What is the appropriate design response?
Answer: Route read-after-write operations to the primary or route only after confirming the required apply position
Asynchronous replicas provide eventual consistency. Define tolerated staleness per workflow and use session stickiness, position waiting, or primary reads as needed.
What is the best first approach for investigating performance problems across different database products?
Answer: Compare baselines for workload, latency, throughput, waits, top queries, and recent changes
Start with common signals showing what waited, when degradation began, and under which workload. Then drill into engine-specific wait events and query stores.
A query suddenly became slow. What should be checked when comparing execution plans?
Answer: Check estimated versus actual rows, access paths, join order, sorts or spills, parameters, and statistics or schema changes
Use runtime evidence and change history to find cardinality-estimation or plan changes. Plan forcing can mitigate an incident but needs scope and exit criteria.
Which policy is appropriate when standardizing statistics and index maintenance across database products?
Answer: Understand each product's automation and target maintenance using change volume, statistics freshness, and measured fragmentation impact
Excessive maintenance increases I/O, logging, replication lag, and locking. Understand engine automation and online features, then act based on measured benefits and side effects.
Blocking sessions increase and cause application timeouts. What is the appropriate first response?
Answer: Identify the blocking chain, transaction age, SQL, owner, and impact, then relieve the minimum necessary scope
Identify the root blocker and business operation before choosing cancellation, rollback, or traffic control. Preserve evidence and fix long transactions or access ordering afterward.
Increasing application instances exhausted the database connection limit. What is an appropriate response?
Answer: Define a total connection budget and tune pool limits, idle timeouts, queues, and backpressure
Pool size multiplies by instance count. Budget against database workers, memory, and workload, using queues and admission control to protect the database during spikes.
Long-running transactions prevent MVCC cleanup or transaction-log reuse. What is an appropriate long-term fix?
Answer: Shorten transaction scope and design idle-in-transaction timeouts and batch partitioning
Long transactions retain old versions, undo, WAL or logs, and locks. Redesign application boundaries and add attributable monitoring with safe timeouts.
Database latency rises while CPU remains low. What should be checked next?
Answer: Check storage IOPS, latency, queues, fsync or log flush, network, and lock waits
A database may consume little CPU while waiting on I/O, log flushes, network, or locks. Correlate host, storage, and engine waits over the same time window.
Non-data-file space is growing rapidly and disk exhaustion is near. Which cross-platform investigation is appropriate?
Answer: Break down growth from transaction logs, archives, temp, undo, replication slots or queues, and diagnostic logs
Space growth can signal backup failure, stopped replicas, long transactions, large sorts, or broken log rotation. Create emergency headroom while removing the cause.
Which privilege design is appropriate for an application database account?
Answer: Grant only required objects and operations per business role, separating schema-change and administrative privileges
Separate runtime, migration, monitoring, and backup roles, using secret management, short-lived credentials, and rotation. Review and audit privilege changes.
Which break-glass process is appropriate for powerful database privileges used only during emergencies?
Answer: Issue approved time-bounded access, record activity, then review and revoke it
Break-glass is an exception when normal paths are unavailable. Require identity, reason, approval, expiry, session recording, notification, and reliable revocation afterward.
Which policy is appropriate when designing database audit logs?
Answer: Define high-risk authentication, privilege, DDL, and sensitive-data events with tamper resistance, redaction, and retention
Select events from audit objectives and threat models while minimizing sensitive values. Centralize logs with time sync, access control, tamper detection, and searchable common fields.
What preparation supports rotating database TLS certificates without downtime?
Answer: Plan overlapping trust, client updates, staged rotation, connection tests, and a deadline to remove old trust
Servers and many clients cannot always change simultaneously, so use time-bounded overlap. Canary connections must preserve hostname validation and encryption before old keys or CAs are removed.
A NOT NULL column must be added to a large table with near-zero downtime. Which migration pattern is generally safer?
Answer: Expand-contract: add nullable, make the application dual-compatible, backfill in batches, then validate and enforce the constraint
Create a compatibility window between schema and application and throttle backfill. Test product-specific online DDL, locks, logging, and replication lag.
What is an appropriate change plan for adding an index to a multi-terabyte table?
Answer: Test online or concurrent features, extra space, log volume, replica lag, and cancellation or resume behavior, then stage the change
Even online DDL affects CPU, I/O, logs, and may take short locks. Measure with production-scale data and define traffic controls, stop conditions, and post-change plan checks.
Which compatibility areas deserve close review before migrating PostgreSQL to another engine?
Answer: Differences in data types, nulls and defaults, collation, time zones, identity or sequences, SQL, procedures, and isolation
Convertible syntax does not guarantee equivalent semantics. Test empty strings versus nulls, timestamps, decimals, sorting, booleans, generated keys, and transaction behavior.
Which general approach supports moving a large database to a different engine with short downtime?
Answer: Bulk-load first, synchronize changes with CDC, then cut over after lag convergence, a write freeze, and final validation
Separating bulk load from change data capture limits downtime to the final delta. Manage DDL, ordering, duplicates, deletes, retries, and CDC retention.
Which method appropriately validates data consistency before and after migration?
Answer: Compare per-table counts, key ranges, checksums or aggregates, samples, and business invariants in multiple stages
One metric can hide omissions, duplicates, or conversion errors. Prioritize critical tables and business rules, align snapshot points, control load, and record reproducible differences.
What should be predefined as rollback criteria for a database migration?
Answer: Thresholds for error rate, latency, data differences, CDC lag, and business failures, plus the last reversible point
Define go or no-go and rollback with metrics, owners, and deadlines. Once new writes reach the target, reverse synchronization may be required, so identify the point of no return.
Which preparation is appropriate before a major database-version upgrade?
Answer: Rehearse compatibility, upgrade duration, plan changes, rollback, and backup restore on a production-like copy
Major upgrades can change SQL, drivers, extensions, authentication, optimizers, and parameter defaults. Inventory deprecated features and run canary and performance-regression tests.
How should configuration drift be controlled across many database instances?
Answer: Version common policies and product-specific templates with approval, automated application, and drift detection
Define common intent for encryption, logging, backups, timeouts, and similar controls, then map it to each product. Exceptions need owners, reasons, and expiry.
Which information should be prioritized in an inventory for managing database-fleet risk?
Answer: Owner, business criticality, engine and version, support expiry, data classification, RPO and RTO, backup and HA status, and dependencies
Databases without clear ownership and lifecycle fall through patching, backup, and retirement processes. Reconcile inventory with discovery and prioritize EOL or unprotected instances.
Which design supports monitoring PostgreSQL, MySQL, SQL Server, and Oracle in one platform?
Answer: Use a common model for availability, latency, traffic, errors, saturation, replication, and backups, supplemented by product adapters
Compare fleet health through common service signals, then drill into product-specific waits, buffers, logs, and replication workers. Tune thresholds to SLOs and baselines.
A database restart is the leading temporary mitigation during an incident. What should be done before executing it?
Answer: Safely preserve session, wait, log, plan, and resource snapshots and communicate impact and recovery criteria
A restart may erase evidence and in-memory state. Runbooks should define quick evidence capture without delaying recovery, followed by symptom, integrity, and recurrence checks.
How should cross-platform database runbook quality be continuously improved?
Answer: Measure procedures, permissions, and timing in drills and incidents, then apply owned improvements from postmortems
Treat runbooks as executable products. Include prerequisites, stop conditions, escalation, vendor differences, validation, and rollback, and test whether another operator can complete them.