CCNA Database Planning And Deployment Questions

Always On availability groups support synchronous-commit mode, which ensures zero data loss, and can be configured for automatic failover. They do not require shared storage, and they support replicas on different subnets within the same data center. This solution meets the zero data loss and automatic failover requirements.

A running service that refuses external connections typically means the port is filtered before reaching it. The host firewall dropping inbound traffic on the database port satisfies the verified-running-service constraint, since the listener itself is healthy but packets never arrive.

Document-oriented databases store each record as a self-describing JSON-like document, so fields can vary between records without schema migrations. This directly satisfies the stem's requirement for highly variable, semi-structured data, unlike relational tables with fixed columns or key-value stores that lack queryable nested structure.

TDE encrypts the database encryption key (DEK) with a certificate stored in the master database. To recover an encrypted database on a different server or after a disaster, you must have a backup of the certificate and its private key. Without it, the database cannot be decrypted, resulting in permanent data loss.

One copy must be stored off-site, physically separate from the primary and secondary copies. This satisfies the geographic separation constraint of the 3-2-1 rule, protecting backups against site-level disasters such as fire, flood or theft that would otherwise destroy every local copy simultaneously.

Restricting connections in pg_hba.conf to specific IP addresses enforces network-level access control at the database layer, satisfying the hardening requirement by rejecting connections from unauthorised hosts before authentication. This limits the attack surface of the newly deployed PostgreSQL instance, complementing credential and encryption controls.

systemctl is the command-line utility for controlling the systemd system and service manager. It is used to start, stop, enable, and manage services, including database services. To ensure the database starts at boot, the administrator would use systemctl enable <service>. This is the standard method on modern Linux distributions that use systemd.

PostgreSQL streaming replication with hot standby allows a standby server to accept read-only queries while continuously applying WAL changes from the primary. This offloads long-running reporting queries to the standby, preventing them from consuming resources on the primary and blocking write transactions. It is the standard PostgreSQL approach for read scaling and reporting isolation.

Consistency requirements determine the appropriate data model: ACID transactions suit relational integrity, while BASE prioritises availability and partition tolerance. This directly addresses the stem's demand for selection factors, since the required consistency guarantee dictates whether a traditional RDBMS or a NoSQL database fits the project's distributed, scalability and correctness needs.

Peak transactional throughput in transactions per second defines the maximum concurrent workload the database must sustain. This satisfies the stem's capacity planning requirement by sizing CPU, memory and IOPS against worst-case demand rather than average load.

Random I/O patterns issue many small, non-sequential reads and writes, so the storage subsystem's throughput is bounded by how many discrete operations it can complete each second. IOPS directly measures that operation rate, making it the decisive metric for this workload rather than sequential transfer speed.

Purging permanently removes records that have passed their retention period and hold no regulatory obligation, satisfying the policy's terminal stage. Unlike archiving, which retains data in secondary storage, or anonymisation, which preserves the dataset minus identifiers, purging eliminates the data entirely, ensuring compliance and reclaiming storage.

Patroni uses a distributed consensus store such as etcd to hold the cluster leader lock. With three nodes, a quorum can elect a new leader and promote a standby automatically when the primary fails. The consensus store prevents split-brain by ensuring only one node holds the leader lock. This directly satisfies the automatic failover and split-brain minimization requirements.

RBAC enforces least-privilege access by assigning permissions through roles rather than to individuals, directly satisfying the stem's data security and regulatory compliance requirement. It restricts which users and service principals can read or modify data, providing the access-governance control auditors expect.

Oracle GoldenGate performs continuous logical replication, applying ongoing changes from source to target so both stay synchronised. This keeps downtime to a brief cutover rather than a full export/import, directly meeting the minimal-downtime, in-sync constraint.

Standardised service account permissions define the exact database principals and privileges the application uses. Documenting them on the checklist guarantees the same least-privilege grants are recreated in each environment, directly satisfying the consistency requirement across deployments.

Columnstore indexes are highly optimized for large-scale analytical and reporting queries.

Firewall access control lists filter traffic by source IP, port and protocol before any connection reaches the database engine, satisfying the requirement for network-layer restriction. Unlike authentication controls, which operate after a session is established, ACLs drop unauthorised packets at the perimeter, making them the first line of defence.

Committed transactions are durable only when redo records reach nonvolatile storage. A battery-backed write-back cache preserves cached writes across a power failure, and the operating system must honor flush commands so that commits are not left in volatile cache. This combination ensures that a commit acknowledged to the application survives a power loss.

Transaction log writes are sequential and latency-sensitive, so placing log files on high-speed, low-latency storage (such as NVMe SSD) minimises write-ahead logging delay and commit time. This satisfies the stem's storage-performance constraint, since slow log I/O throttles every transaction regardless of data file speed.

Physical streaming replication ships WAL to replicas that continuously apply it, so each region holds a full copy of the data. A connection router such as Pgpool-II or a proxy directs read queries to the closest replica, cutting read latency for each region. Writes still go to the single primary, satisfying the single-write-region requirement.

Transparent Data Encryption encrypts tablespaces and credentials at rest, and integrating it with an external key management system via PKCS#11 moves master key custody to a separate hardware or software appliance. This satisfies both the encryption-at-rest requirement and the separation-of-duties control by keeping key management independent of the database server.

Disk I/O latency reveals whether storage can sustain the database's read and write workload; rising latency indicates the deployment is undersized for its transaction volume. It satisfies the stem's requirement for an indicator proving correct sizing for performance.

Storing each migration script in a repository gives schema changes the same versioning as application code, so any environment can be rebuilt at a known revision. This directly satisfies the stem's benefit of traceable, repeatable schema evolution rather than ad-hoc manual alterations.

Database bloat is wasted space within data pages left by deleted or updated rows that autovacuum has not yet reclaimed. It inflates table size beyond live row data, so capacity projections must account for it alongside transaction growth and row size.

Geographic separation determines whether a single regional outage can affect both sites simultaneously. Placing the DR replica far enough away avoids correlated failures from the same power grid, flood plain or network backbone, directly satisfying the DR requirement for site-independent resilience rather than merely duplicating data locally.

Asynchronous streaming replication allows the primary to commit transactions without waiting for standby acknowledgment, minimizing write latency. Hot standby servers can serve read-only queries, and a load balancer can distribute the read workload across them, improving read performance. This setup also provides high availability because a standby can be promoted if the primary fails. Given the 10:1 read-to-write ratio, this is the most efficient configuration.

Updating statistics refreshes the optimizer's metadata about column value distribution and cardinality, letting it choose a more efficient plan. Stale or missing statistics cause poor cardinality estimates, so refreshing them directly addresses the poor query performance.

For an OLTP workload dominated by small random reads, the buffer pool is the primary mechanism for reducing physical I/O. With 512 GB of RAM, allocating a large portion to the buffer pool allows frequently accessed pages to remain in memory, dramatically lowering disk reads and improving response time. This is the standard tuning step for this scenario.

Asynchronous commit acknowledges transactions before the standby confirms receipt, so a primary failure can lose recently committed transactions not yet replicated. The risk is data loss, not corruption or split-brain, because the standby simply lacks those final writes.

An Always On availability group can host a secondary replica configured to allow read-only connections, offloading reporting queries from the primary. Long-running scans then execute on the secondary, preserving primary CPU and I/O for OLTP transactions. This directly satisfies the requirement to isolate reporting from the transactional workload without additional licensing for a separate reporting server.

A data dictionary stores metadata — table names, column definitions, data types, constraints and relationships — describing the database structure. This metadata repository lets DBAs and applications understand schema layout, supporting consistent design, documentation and change management across the DBMS.

Load testing subjects the deployed database to concurrent user volumes and transaction rates, revealing throughput, latency and resource bottlenecks. It directly verifies whether the system meets its stated performance targets, which is the Testing phase objective named in the stem.

The `shared_buffers` parameter in `postgresql.conf` explicitly sets the memory PostgreSQL dedicates to caching data pages, directly satisfying the stem's requirement for the shared buffer allocation. Other parameters govern different memory areas, such as per-connection work memory or the OS page cache, so none controls this specific allocation.

Combining daily full backups with transaction log backups every 5 minutes allows point-in-time recovery to within 5 minutes of a failure. This strategy meets the RPO by minimizing data loss, as the log backups capture all transactions since the last backup. It is a standard approach for databases requiring low RPO.

The full recovery model preserves the transaction log and supports point-in-time recovery, which is essential for financial data. RAID 10 provides both mirroring and striping without parity overhead, giving low write latency and redundancy. Separating data and log files on distinct RAID 10 arrays isolates I/O and improves performance. This combination satisfies durability and low write latency requirements.

Oracle Database Replay captures a production workload and replays it against a test system, reproducing realistic concurrency and transaction mix. This satisfies the pre-deployment requirement to simulate workload and forecast hardware consumption, unlike capacity-planning spreadsheets or static benchmarks that cannot replicate actual Oracle session behaviour.

Multiple tempdb data files of equal size let SQL Server spread allocation and I/O across several files, each served by separate threads. This reduces allocation latch and page contention, preventing tempdb becoming a bottleneck under high concurrency.

A full database backup captures a restorable copy of all data and schema objects before any structural change. If the upgrade fails or corrupts objects, this backup provides the rollback point, satisfying the pre-upgrade requirement for recoverability.

A Database Audit Specification defines which auditable events, such as privileged user actions, are captured at the database level. Configuring it satisfies the requirement to log all privileged activity, since it binds specific actions to an audit target and records them for later review.

Archive logging preserves the sequence of log files after they are filled, so a base backup plus the archived logs can be replayed to any moment before the failure. Without archived logs, only the most recent backup can be restored and all transactions since then are lost. Copying the archives to a separate volume protects them from the same storage failure that destroys the data files.

Synchronous-commit Availability Group commits on the secondary before acknowledging the primary, so failover to the standby loses no committed transactions. This satisfies the stem's zero-data-loss requirement, unlike asynchronous-commit mode, which permits potential data loss during failover.

Partitioning allows for 'partition elimination', which significantly speeds up queries on large datasets.

A database compatibility assessment evaluates schema objects, data types, stored procedures and vendor-specific SQL against the target managed service's engine version, directly satisfying the stem's requirement to confirm application compatibility before migration. It exposes unsupported features and deprecated syntax that would otherwise break the application once cutover occurs.

FIPS-mode encryption enforces FIPS 140-2 validated cryptographic modules and algorithms for data at rest and in transit, satisfying the stem's compliance requirement. Enabling this mode ensures the database's cryptographic operations meet the mandated federal standard.

Normalising the data schema is a logical design task because it restructures relations and attributes—resolving partial and transitive dependencies—without altering physical storage. This satisfies the stem's requirement for logical, not physical, design work, since normalisation operates on the conceptual schema independently of indexes, filegroups or disk layout.

Transparent Data Encryption encrypts database files and backups at the page level, with the Database Encryption Key protected by a certificate stored in the master database. This satisfies the policy for data-at-rest encryption without application changes, since SQL Server decrypts data transparently during queries.

SSL/TLS encrypts data in transit between clients and the database server, preventing eavesdropping and man-in-the-middle attacks. PCI DSS requires strong cryptography for data transmission over open networks. Enabling SSL/TLS on the database listener ensures that sensitive customer information is protected while moving across the network.

Sealing the volume encryption key to the host TPM means the disk is transparently decrypted at boot without an operator-supplied passphrase, and PostgreSQL's data directory, WAL, and temporary files all reside inside that encrypted volume. This satisfies the blanket data-at-rest requirement while removing passphrase management, which is exactly what the platform team requested.

Partitioning on the order date gives the optimizer partition elimination for date-filtered queries and places each year's rows in a separate filegroup. Because the oldest year occupies its own read-only filegroup on lower-cost media, it stays queryable while consuming cheaper storage. This design satisfies both the query-performance and storage-tiering goals without rewriting the fact table.

A MongoDB replica set with at least three nodes provides automatic failover: members hold an election and promote a secondary to primary when the current primary becomes unreachable. Three nodes allow a majority quorum to elect a new primary, satisfying the high availability and automatic failover requirements.

Integer-based surrogate keys are highly efficient for indexing and foreign key joins.

A staging environment mirrors production so the deployment and migration scripts can be rehearsed against realistic data and schema before release. This validates the process itself, catching failures in migration order, dependencies and rollback, satisfying the stem's requirement to test deployment and migration safely.

Managed relational database services, such as Amazon RDS or Azure SQL Database, offer multi-AZ deployments for high availability and storage auto-scaling that automatically increases storage as data grows. These features meet the requirements without manual intervention. The service handles replication and failover, ensuring high availability across availability zones.

InnoDB tablespace encryption encrypts data files, redo logs, and undo logs at rest. Using a keyring plugin such as keyring_okv or a component that integrates with a remote key management service keeps the master encryption key off the database host, satisfying the requirement that keys be managed separately. This is the standard MySQL approach for encryption at rest with external key custody.

A FOREIGN KEY constraint on the orders table referencing the customers primary key makes the storage engine reject any insert or update whose customer identifier has no matching parent row, and it also blocks deletion of a parent that still has children. This is declarative referential integrity enforced by the database itself, which is precisely what the order-entry deployment requires.

High-availability clustering satisfies the sub-one-minute RTO through automatic failover to a synchronised standby node, typically completing in seconds without data restoration. Unlike backup-and-restore approaches, which require replaying data and cannot meet such tight recovery windows, clustering keeps a live replica continuously ready, ensuring near-instant service resumption.

Range partitioning divides rows by contiguous key values such as date, so records older than five years occupy distinct partitions that can be archived or dropped as a unit. This directly satisfies the efficient archival requirement, unlike hash or list partitioning, which scatter chronological data across partitions.

RAID 10 combines mirroring and striping, providing both fault tolerance and high performance. With four 1 TB SSDs, it yields a 2 TB usable volume that survives one disk failure per mirrored pair. This matches the requirement to protect against a single disk failure while maintaining the I/O throughput PostgreSQL needs for transaction processing.

A five-member replica set spread across three data centers can tolerate the loss of any single data center while still maintaining a majority of three members to elect a primary. The w:majority write concern ensures each write is acknowledged by a majority of voting members before success is returned, meeting both availability and durability requirements.

datadir defines the filesystem path where MySQL stores its data files, letting you place them on separate storage from system logs. That directly satisfies the isolation-for-performance constraint; other variables govern logging, connections or caching, not data file location.

A foreign key constraint enforces referential integrity by rejecting any child-row insert or update whose value lacks a matching primary key in the parent table, satisfying the stem's requirement for automatic integrity maintenance. Unlike triggers or application checks, enforcement occurs at the database engine level, so no application code is needed.

The Database Requirements Specification captures functional and non-functional requirements, constraints and business rules before design begins, giving developers and stakeholders an agreed baseline. It directly satisfies the planning-phase need to define the proposed system's specific requirements, constraints and business rules.