CompTIA DataSys+ DS0-002: Database Deployment

Topic snapshot

Sample questions

These original IT Mastery practice questions are aligned to this topic area. Use them for self-assessment, scope review, and deciding what to drill next.

Question 1

Topic: Database Deployment

A team is stress testing a checkout database before a new release. The requirement is 600 concurrent users with p95 checkout latency under 700 ms and an error rate below 1%.

Exhibit: Stress test results

Which interpretation and next action are best supported by the exhibit?

Options:

• A. Investigate database contention before retesting

• B. Add application servers before reviewing the database

• C. Approve the release because app CPU is stable

• D. Increase backup frequency to reduce checkout errors

Best answer: A

Explanation: Stress testing evaluates whether the database and application continue to meet requirements under expected or peak load. In this exhibit, the 600-user test fails both service targets: p95 latency is 2,900 ms instead of under 700 ms, and the error rate is 6.4% instead of below 1%. The strongest bottleneck signal is on the database side because DB CPU reaches 94% and deadlocks appear only under load, while application CPU remains moderate. The next action should focus on database contention, such as transaction scope, locking behavior, indexing, or query patterns, followed by another stress test to validate improvement.

• App CPU focus fails because stable app CPU does not offset missed latency and error requirements.
• App scaling first is premature because the exhibit points to DB saturation and deadlocks, not app-tier resource exhaustion.
• Backup frequency does not address the observed checkout latency, deadlocks, or error spike during load.

Question 2

Topic: Database Deployment

A company is deploying a customer database for an internet-facing application. The web tier must accept public HTTPS traffic, but the database contains PII and must not be reachable directly from the internet. DBAs also need occasional administrative access from corporate laptops. Which connectivity design is the BEST professional decision?

Options:

• A. Place the database on a private network and allow only app-tier and VPN-admin traffic on required database ports

• B. Expose the database publicly and allow corporate IP addresses through the firewall

• C. Use public DNS round-robin records to distribute database connections

• D. Place the database in the perimeter network and restrict access with strong passwords

Best answer: A

Explanation: The core concept is secure database connectivity. An internet-facing application should normally separate public web access from private database access. The web tier can receive HTTPS traffic, but the database should remain on a private network segment with firewall rules that permit only required database ports from approved sources, such as the application tier. DBA access should use a controlled administrative path, such as VPN or a bastion/jump host, rather than direct public exposure. This design supports database access while reducing attack surface and helping protect PII. Public DNS or public database endpoints may improve reachability, but they conflict with the stated security constraint.

• Perimeter database increases exposure because the database would sit closer to public traffic than necessary.
• Public endpoint allowlisting still makes the database internet-reachable and depends heavily on source IP control.
• DNS round-robin addresses distribution, not secure network segmentation or administrative access control.

Question 3

Topic: Database Deployment

A company runs an order-entry database in its own data center. The database contains regulated customer data that must remain under company-controlled storage, and the application requires low-latency access to on-site warehouse systems. Leadership also wants scalable reporting during seasonal peaks without adding permanent on-premises hardware. Which placement is the best professional decision?

Options:

• A. Replace the database with a SaaS reporting application

• B. Use a hybrid deployment with on-premises OLTP and cloud reporting

• C. Keep all databases on premises and buy peak-capacity hardware

• D. Move the entire database to a cloud DBaaS platform

Best answer: B

Explanation: A hybrid placement best matches mixed control and scalability requirements. The regulated operational database needs company-controlled storage and low-latency access to on-site warehouse systems, which supports keeping the OLTP workload on premises. Reporting has seasonal peaks and can benefit from elastic cloud storage and compute, especially if only appropriate replicated, transformed, or governed data is sent to the cloud reporting environment. This separates operational persistence requirements from analytical scalability needs without forcing one deployment model to handle both. Moving everything to cloud ignores the stated control and latency constraints, while keeping everything on premises ignores the elasticity requirement.

• Cloud-only placement fails because the stem requires regulated data to remain under company-controlled storage with low-latency on-site access.
• On-premises-only expansion meets control needs but adds permanent hardware for a temporary reporting peak.
• SaaS reporting replacement does not address the transactional database placement or the required control over regulated data.

Question 4

Topic: Database Deployment

A retailer is moving an order-processing database to the cloud. The database stores persistent transactional data, must enforce relational integrity, and must remain in an approved geographic region for compliance. The DBA team wants to reduce operating system and database patching work while still allowing storage and compute to scale as demand grows. Which hosting model is the BEST professional decision?

Options:

• A. Managed relational DBaaS in the approved region

• B. Ephemeral container database in a shared region

• C. Self-managed database on IaaS virtual machines

• D. SaaS reporting platform with imported order files

Best answer: A

Explanation: A managed relational DBaaS aligns with the stated constraints because the workload needs persistent transactional storage and relational integrity, but the team also wants to reduce administrative burden. In DBaaS, the provider commonly handles infrastructure and routine database platform operations such as patching, while the DBA retains control over schema, access, performance settings, and data governance. Region selection helps meet data residency requirements, and managed scaling options support growth without requiring the team to operate the full server stack. A self-managed IaaS database can meet persistence and compliance needs, but it leaves more patching and administration with the DBA team.

• IaaS self-management adds operating system and database maintenance work, which conflicts with the stated administration goal.
• SaaS reporting is not appropriate for the primary order-processing transactional database and may not preserve required control.
• Ephemeral containers are a poor fit for persistent regulated order data, especially when the region is not approved.

Question 5

Topic: Database Deployment

During a production database deployment, the DBA team discovers that the new database instance cannot meet the agreed response-time KPI after standard troubleshooting and rollback testing. The deployment window ends in 30 minutes, and the SLA requires formal notification for any expected service-impacting delay. Which action should the deployment lead take next?

Options:

• A. Escalate through the documented incident path

• B. Continue tuning until the window closes

• C. Disable KPI monitoring for the release

• D. Open a low-priority maintenance ticket

Best answer: A

Explanation: Escalation procedures define when and how to involve higher-level support, management, vendors, or incident response roles during deployment. In this scenario, normal troubleshooting has not resolved the issue, the deployment window is nearly over, and the SLA requires notification for service-impacting delays. That combination means the deployment lead should stop treating the problem as routine implementation work and follow the documented escalation path, including required reporting and stakeholder communication. The key takeaway is that SLA or KPI risk changes the response from local troubleshooting to formal escalation.

• Continuing to tune risks missing the SLA notification requirement and delays formal support involvement.
• A low-priority maintenance ticket understates a production deployment issue with potential service impact.
• Disabling KPI monitoring hides the deployment problem instead of resolving or reporting it.

Question 6

Topic: Database Deployment

A DBA is preparing documentation for a new reporting database before handing it over to analysts. The analysts need a reference that lists each table and column, the data type, allowed values, and the business meaning of fields such as customer_status and net_amount. Which documentation artifact should the DBA create?

Options:

• A. Physical schema diagram

• B. Entity relationship diagram

• C. Data dictionary

• D. Standard operating procedure

Best answer: C

Explanation: A data dictionary is the right documentation artifact when users or administrators need precise definitions for database objects and fields. It commonly lists table names, column names, data types, constraints, valid values, default values, and business meanings. In this scenario, the key need is not just to show relationships or deployment structure, but to define what each field means and how it should be interpreted by analysts.

An ERD is useful for showing entities and relationships, but it usually does not provide full column-level meaning and allowed-value documentation. The key takeaway is to choose a data dictionary when the requirement centers on column, table, type, and meaning definitions.

• ERD focus fails because an entity relationship diagram primarily shows relationships and cardinality, not full field definitions.
• SOP focus fails because a standard operating procedure describes repeatable tasks, not table and column metadata.
• Physical schema focus fails because physical design emphasizes implementation details such as storage and indexes, not business meanings.

Question 7

Topic: Database Deployment

A regional retailer is deploying a new customer loyalty database. The workload is standard relational OLTP with predictable growth. The team needs automated backups, built-in high availability, routine patching, and encryption at rest. The application uses standard SQL drivers, and there is no requirement for custom OS tuning, specialized storage drivers, or direct server-level access. Which deployment choice is the BEST professional decision?

Options:

• A. Use a managed DBaaS relational database

• B. Build an on-premises database cluster

• C. Deploy the database on self-managed IaaS virtual machines

• D. Replace the database with a SaaS CRM platform

Best answer: A

Explanation: A managed database service is favored when the database requirements are common, the application can use standard connectivity, and the organization values operational features such as automated backups, patching, encryption, monitoring, and high availability more than direct infrastructure control. In this scenario, the workload is standard relational OLTP and there is no stated need for OS-level access, custom storage, or specialized server tuning. That makes DBaaS a good fit because it reduces administrative burden while meeting availability, security, and backup objectives. Self-managed infrastructure becomes more appropriate when low-level control is a real requirement, not just a preference.

• Self-managed IaaS adds OS, patching, backup, and HA responsibilities without a stated need for server-level control.
• On-premises clustering increases infrastructure ownership and operational complexity beyond the stated requirements.
• SaaS replacement changes the application and data model decision rather than selecting an appropriate database deployment model.

Question 8

Topic: Database Deployment

A team is deploying an OLTP customer database on a managed DBaaS platform. The SLA requires 99.9% monthly availability, the KPI is p95 login-query latency under 500 ms, service desk escalation must occur if a production incident remains unresolved for 10 minutes, and operations must receive a monthly SLA report. Which deployment decision best satisfies these requirements?

Options:

• A. Implement cross-region active-active replication for all transactions

• B. Use a larger database instance and review performance manually each month

• C. Enable multizone deployment, KPI monitoring, escalation alerts, and monthly reporting

• D. Schedule nightly full backups and test restores quarterly

Best answer: C

Explanation: SLA planning must translate service commitments into deployable controls and operational processes. In this scenario, the database needs availability support, measurable performance monitoring, an escalation path tied to the 10-minute incident threshold, and recurring SLA reporting. A multizone DBaaS deployment helps reduce availability risk, while monitoring p95 login-query latency validates the KPI. Alerting and escalation rules ensure incidents are routed on time, and monthly reports provide evidence for operations review.

Backups are important, but they do not prove availability or latency compliance. A larger instance may help performance but does not address escalation or reporting. Cross-region active-active replication may exceed the stated need and adds complexity not justified by the 99.9% monthly availability requirement.

• Backup focus misses the stated performance KPI, incident escalation, and SLA reporting obligations.
• Manual review is too slow for a 10-minute escalation requirement and does not ensure availability.
• Active-active replication may be excessive for the stated SLA and introduces unnecessary operational complexity.

Question 9

Topic: Database Deployment

A DBA is validating a deployment that imports staged customer and order data into a relational production database. The release checklist requires verification of column data types, nullability, allowed status values, table row counts, primary keys, and customer-to-order relationships. The release window is short, and no schema redesign is approved. Which validation approach is the BEST professional decision before release?

Options:

• A. Add new indexes and update statistics

• B. Run only a workload stress test

• C. Run column profiling and table integrity checks

• D. Perform a full backup restore test

Best answer: C

Explanation: Pre-release database quality validation should match the stated data rules. Column checks confirm that staged values conform to expected data types, required fields are not null, and domain values such as order status are valid. Table checks confirm completeness and integrity, including row counts, primary key uniqueness, and foreign key relationships between customers and orders. Because the schema is not being redesigned and the release window is short, the best approach is targeted validation of data quality and relational integrity rather than unrelated operational testing or tuning. Performance and recovery tests can be important, but they do not prove that the imported columns and tables meet the deployment acceptance criteria.

• Stress testing only misses column validity, key uniqueness, row-count reconciliation, and referential integrity checks.
• Index changes are a tuning activity and could introduce unapproved schema changes during a short release window.
• Restore testing supports backup readiness, but it does not validate the imported data against column and table quality rules.

Question 10

Topic: Database Deployment

A DBA is preparing deployment documentation for a schema change to a production OLTP database. The compliance team will not approve the change until the required SOP evidence is attached.

Exhibit: Compliance note

Deployment: Add customer_status column
Required evidence:
- Approved maintenance window
- Pre-change backup verification
- Step-by-step implementation procedure
- Rollback procedure if validation fails
- Post-change validation sign-off

Which SOP documentation best satisfies the requirement?

Options:

• A. Change management and maintenance SOP

• B. Entity relationship diagram update

• C. Data dictionary field definition

• D. Third-party service onboarding SOP

Best answer: A

Explanation: SOP documentation should match the operational control being audited. The exhibit is not asking whether the new column is logically valid or how it relates to other entities. It requires evidence that the production change will be performed under an approved process: scheduled maintenance, backup verification, implementation steps, rollback planning, and post-change sign-off. Those items belong in a change management and maintenance SOP because they define repeatable procedures for safely modifying a production database while meeting organizational compliance expectations. Schema artifacts can support the change, but they do not replace the required operational SOP.

• ERD update helps document relationships, but it does not prove maintenance approval, rollback planning, or validation sign-off.
• Data dictionary defines the new column, but it does not document the controlled production-change procedure.
• Vendor onboarding may support third-party compliance, but the exhibit is about an internal database deployment change.

Continue with full practice

Use the CompTIA DataSys+ DS0-002 Practice Test page for the full IT Mastery practice bank, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try CompTIA DataSys+ DS0-002 on Web View CompTIA DataSys+ DS0-002 Practice Test

Related focused pages

• Free CompTIA DataSys+ DS0-002 Full-Length Practice Exam
• Database Fundamentals
• Database Management and Maintenance
• Data and Database Security
• Business Continuity
• Data Integration

Free review resource

Use the full IT Mastery practice page above for the latest review links and practice page.