Database Deployment

DS0-002 · 51 questions

• A company plans to replace a fifteen-year-old database platform. Before selecting a product, the project lead wants a structured comparison of what the current system does today against what the business now requires, so missing capabilities can be listed. Which requirements-gathering activity is this?
• A production database is 2 TB today and has grown at a steady 4 percent per month for the past two years. The new server must run for three years without a storage expansion. Which sizing approach should the planner use?
• For a new system, an analyst estimates storage by multiplying the expected row count by average row size, arriving at 500 GB, and proposes provisioning exactly that amount. What should the DBA point out about this estimate?
• A team plans to host a new OLTP order database, which performs thousands of small random writes per second, on a few high-capacity 7,200 RPM spinning disks because they offer the lowest cost per terabyte. What is the main concern with this storage specification?
• Company policy requires that customer records remain in the company's own data center. Separately, the analytics team runs large quarterly models on anonymized aggregates and needs a burst of compute for a few days each quarter. Which hosting approach best fits both requirements?
• Requirements for a new system list 50 internal analysts running ad hoc reports during office hours and up to 20,000 concurrent web customers placing orders through an application. What planning conclusion follows from these numbers and user types?
• A DBA is drafting a service level agreement for an internal database service and needs indicators that both parties can measure objectively each month. Which pair of KPIs is most appropriate?
• A production database outage has run past the response time agreed in the SLA. The on-call engineer is working the problem but is unsure which manager, vendor contact or business owner must be informed, and at what point. Which SLA element should have answered this?
• The business owner of a hosted database service asks the provider for evidence that the agreed availability commitment was actually met during each month of the last quarter. Which SLA component provides this evidence?
• An installation of a database engine on a new Linux server fails partway through. The log shows that a required operating system library is missing and that the service account the engine runs under does not exist. Which deployment step should the team add before the next attempt?
• A team must build database instances for development, test and production, and past incidents were caused by small configuration differences between environments that were set up by hand. Which provisioning approach best prevents this?
• A DBA is planning a major version upgrade of a production database engine that supports an order-processing application. Which sequence follows sound upgrade practice?
• The initial load of 400 million rows into a new database is running far slower than planned. Every target table already has all of its secondary indexes built and its constraints enabled. Which approach is most appropriate for the load?
• A team needs to install a custom-compiled extension into its database engine, which requires changing operating system packages and kernel settings on the host. It wants to run this in a public cloud. Which cloud service model fits?
• A three-person development team with no dedicated DBA is launching a new application. It wants the cloud provider to handle engine patching, automated backups and high availability so it can focus on its own schema and queries. Which deployment option fits best?
• A sales department uses a hosted SaaS customer relationship management application, and the IT team wonders how its database responsibilities differ from those of a DBaaS deployment. Which statement accurately describes the SaaS case?
• An analytics service runs on cloud compute nodes that are automatically destroyed and recreated during scale-in and scale-out events. After each event, data the service had written to the nodes' local disks is gone. Which design change keeps autoscaling while preventing the loss?
• Before go-live, a team must confirm two things: that the database sustains the projected holiday peak of 5,000 transactions per second, and that a recent schema change did not break any of the existing finance reports. Which testing combination addresses both?
• A release of a billing application failed in production because the new database schema renamed a column that a nightly export job still referenced by its old name. Which testing practice should be added before future schema deployments?
• After migrating an order system to a new database, users notice orders that reference customer IDs missing from the customer table, and some product descriptions are cut off midway. Which validation approach would have caught both issues before cutover?
• Early in a project, an architect is modeling customers, orders and products along with their attributes and relationships, but has not yet chosen data types, indexes, partitions or storage locations. At which level of schema design is the architect working?
• With the logical model approved, a DBA is now deciding how to partition the largest table by month, which file groups or tablespaces hold each partition, and on which storage volumes the indexes will live. Which design activity is this?
• A newly hired developer cannot tell what the codes 1 through 6 in an order table's status column mean, or whether the weight column stores kilograms or pounds. Which design documentation should the team maintain to answer these questions?
• An ERD for a training system shows crow's-foot symbols at both ends of the line between Student and Course: a student can enroll in many courses, and a course can have many students. How should this relationship be implemented in a relational database?
• External auditors want to understand, at a glance, how the tables in a payment system relate to one another, including which tables reference which and with what cardinality. Which design document is best suited to communicate this?
• A retailer runs a point-of-sale system that records thousands of short sales transactions per minute, and a separate warehouse where analysts aggregate years of sales by region and product. Which description matches the two systems' designs?
• Executives run large ad hoc sales reports against the production order database during business hours, and online checkout slows noticeably whenever they do. Which change best addresses the conflict?
• A BI team is designing a reporting model with one central sales fact table holding measures such as quantity and revenue, surrounded by date, store, product and customer tables. Each surrounding table is a single denormalized table joined directly to the fact table. Which design is this?
• Each of a company's four DBAs performs the weekly index maintenance differently: some rebuild everything, some reorganize selectively, and only some update statistics or check the results. Which action best makes this task consistent and repeatable?
• An internal security policy requires that every request for access to production databases be approved by the data owner and by the security team, with the approval recorded before any account is created. How should the DBA team operationalize this requirement?
• A qualified assessor evaluating the company's compliance with the Payment Card Industry Data Security Standard asks the DBA team how cardholder data in the database is protected, accessed and retained, and wants documented evidence. What best satisfies this request?
• An e-commerce application wraps two statements in one transaction: insert the order row, then decrement the product's inventory. The inventory update fails on a constraint violation, and the business requires that the order row must then not remain either. Which ACID property guarantees this behavior?
• During testing, a report transaction reads account balances that another transaction has changed but not yet committed. The other transaction then rolls back, so the report has shown values that never officially existed. Which ACID property is not being adequately enforced?
• A database server loses power without warning seconds after an application received a successful commit for a funds transfer. When the server restarts, the transfer is still present and complete. Which ACID property does this demonstrate, and what mechanism typically provides it?
• A user updates their profile on a globally distributed NoSQL store. For a few seconds, users reading from another region still see the old profile, and then everyone sees the new version. The system accepts writes even if a region is temporarily unreachable. Which consistency model does this behavior illustrate?
• An architect is choosing data stores for two new services: a banking ledger that records account debits and credits, and a social media activity feed that must stay available worldwide even during network partitions. Which pairing of consistency models is most appropriate?
• A new team is taking over a fifteen-year-old database that has no current documentation. The original developers have left, and the team must make schema changes next quarter. What should the team do first?
• An application performs mostly reads, and its single primary database server is saturated during peak hours. Three read replicas were built months ago but receive almost no traffic because every application connection points at the primary. Which change best uses the existing infrastructure?
• After a load balancer was placed in front of a primary database and two read-only replicas, the application intermittently fails with errors stating that the database is read-only. The balancer distributes all connections round-robin across the three servers. What is the correct fix?
• During a failover test, the standby database became the new primary within a minute, but the applications kept trying to reach the old server for over an hour until their configuration files were edited. Each application's connection string contained the old server's IP address. How should this be prevented?
• Clients receive a 'host not found' error when connecting to a newly built database server by its name, sdb-orders01. The database service is running and listening, and connections to the server's IP address on the database port succeed. What should the DBA check next?
• A company is designing a three-tier web application with browser clients, an application tier and a database tier. A developer proposes letting the browser-side code connect straight to the database to save a hop. Why does the client/server design keep the database separate behind the application tier?
• Public-facing web servers sit in a perimeter network (screened subnet) and must query the company's customer database. A network engineer asks where the database should live and what traffic should be permitted. Which design is most appropriate?
• An application server can ping the database server by host name, and the DBA confirms the database service is running and accepts local connections on the server itself. Connections from the application server to TCP port 5432, however, hang and then time out without any error message from the database. What is the most likely cause?
• After a weekend reboot, a database server came back with a different IP address from the DHCP pool. Several applications that use the server's IP address in their connection strings could no longer connect until their configuration was edited by hand. What should the DBA do to prevent a repeat?
• A company runs a production database with a cloud provider. The database must stay available if one data center in the region fails, and data residency rules require that all copies remain within that same region. Which deployment meets both requirements?
• A security team is writing firewall rules for two newly installed database servers: a Microsoft SQL Server default instance and a PostgreSQL server. Neither installation was customized. Which inbound TCP ports should the request list for the default configuration of each?
• A DBA is documenting a hybrid environment for the network team. It includes a MySQL server and an Oracle Database server, both installed with default network settings. Which default TCP ports should the documentation list for the MySQL server and the Oracle listener?
• To reduce exposure, management proposes moving every production database service from its default port to a nonstandard port and treating that change as the main network protection for the databases. How should the DBA characterize this proposal?
• An application connects to a database through a network load balancer. After periods of inactivity longer than about ten minutes, the first query on a pooled connection fails with a connection-reset error, and a retry succeeds. Direct connections that bypass the load balancer never show the problem. What is the most likely cause?
• A company adds a database replica in a distant cloud region and configures it for synchronous replication so that no committed transaction can be lost. Immediately afterward, write transactions on the primary take noticeably longer to commit, although read queries are unaffected. What best explains the slowdown?