Over the weekend, a company's transaction database was moved to an upgraded server. All validations performed after the migration indicated that the database was functioning as expected. However, on Monday morning, multiple users reported that the corporate reporting application was not working.
Which of the following are the most likely causes? (Choose two.)
A.The access permissions for the service account used by the reporting application were not changed.Correct
B.The new database server has its own reporting system, so the old one is not needed.
C.The reporting jobs that could not process during the database migration have locked the application.
D.The reporting application's mapping to the database location was not updated.Correct
E.The database server is not permitted to fulfill requests from a reporting application.
F.The reporting application cannot keep up with the new, faster response from the database.
Explanation
When a database moves to a new server, every client application must be pointed at the new location (connection string, DSN or host mapping), and the service account the application uses must be granted the same logins and permissions on the new server. The database itself validated correctly, so a stale connection mapping or missing service-account permissions are the most likely reasons only the reporting application fails. A new server does not refuse reporting clients by design, does not replace the existing reporting tool, and faster responses never break a client; jobs missed during the outage would not lock the whole application.
A DBA is reviewing the following logs to determine the current data backup plan for a primary data server:
| Timestamp | Activity | Size | Duration |
|---|---|---|---|
| 2023-Jan-23 23:59:00 | Back up to disk | 7.35GB | 03:14:55 |
| 2023-Jan-24 23:59:00 | Back up to disk | 0.12GB | 00:14:22 |
| 2023-Jan-25 23:59:00 | Back up to disk | 1.11GB | 01:11:55 |
| 2023-Jan-26 23:59:00 | Back up to disk | 1.23GB | 01:22:12 |
| 2023-Jan-27 23:59:00 | Back up to disk | 1.22GB | 01:19:56 |
| 2023-Jan-28 23:59:00 | Back up to disk | 1.21GB | 01:17:19 |
| 2023-Jan-29 23:59:00 | Back up to disk | 0.94GB | 01:01:29 |
| 2023-Jan-30 23:59:00 | Back up to disk | 8.1GB | 03:45:66 |
Which of the following best describes this backup plan?
A.Monthly full, daily differential
B.Daily differential
C.Daily full
D.Weekly full, daily incrementalCorrect
Explanation
A large backup runs on January 23 and again seven days later on January 30, which marks a weekly full backup. The daily backups in between are small and do not keep growing (1.23GB, 1.22GB, 1.21GB, 0.94GB), so each one captures only the changes since the previous backup: that is incremental. Differential backups would grow every day because each contains all changes since the last full backup, and daily full backups would all be about the size of the full ones.
A database administrator needs to ensure that a newly installed corporate business intelligence application can access the company's transactional data. Which of the following tasks should the administrator perform first?
A.Create a new service account exclusively for the business intelligence application.Correct
B.Build a separate data warehouse customized to the business intelligence application's specifications.
C.Set up a nightly FTP data transfer from the database server to the business intelligence application server.
D.Send the business intelligence administrator the approved TNS names file to configure the data mapping.
E.Open a new port on the database server exclusively for the business intelligence application.
Explanation
Before an application can read the transactional data, it needs its own identity on the database, so the first step is a dedicated service account that can be granted least-privilege, read access and audited separately. Connection details such as a TNS names file only help once there is an account to connect with. Building a separate warehouse or a nightly FTP export is unnecessary to give access, and a dedicated port adds no access control.
A database administrator is conducting a stress test and providing feedback to a team that is developing an application that uses the Entity Framework. Which of the following explains the approach the administrator should use when conducting the stress test?
A.Capture business logic, check the performance of codes, and report findings.Correct
B.Check the clustered and non-clustered indexes, and report findings.
C.Review application tables and columns, and report findings.
D.Write queries directly into the database and report findings.
Explanation
With Entity Framework the application generates its SQL from code, so a stress test should exercise the real business logic under load, capture the queries the code produces, measure their performance and report the findings back to the developers. Reviewing indexes or tables alone is a static review rather than a stress test, and writing queries directly into the database bypasses the ORM-generated code that is actually under test.
A deployment starts by confirming the prerequisites (hardware, operating system, dependencies and licensing), because the installation can fail or be unsupported without them. The database software is then installed, configured, and connected to the application and network, after which it is tested, validated against the requirements, and finally released to production. The other orders install or connect before the prerequisites are checked, or configure before anything is installed, which cannot work.
A company wants to deploy a new application that will distribute the workload to five different database instances. The database administrator needs to ensure that, for each copy of the database, users are able to read and write data that will be synchronized across all of the instances.
Which of the following should the administrator use to achieve this objective?
A.Peer-to-peer replicationCorrect
B.Failover clustering
C.Log shipping
D.Availability groups
Explanation
Peer-to-peer replication keeps a full copy of the data on every node, and each node can accept reads and writes that are propagated in near real time to all the other nodes, which matches five read/write instances kept in sync. Failover clustering runs one active instance on shared storage, log shipping keeps warm standby copies that are not writable, and availability groups allow writes only on the primary replica with read-only secondaries.
A database administrator manages a database server that is running low on disk space. A lot of backup files are stored on the server's disks.
Which of the following is the best action for the administrator to take?
A.Move all the backup files to external disks.
B.Delete all the backup files containing data that is rated as classified.
C.Delete all the backup files that are not required by the backup retention policy.Correct
D.Delete all the backup files except for the most recent one.
Explanation
The backup retention policy defines which backups must be kept, so removing only the backups that fall outside it frees disk space without breaking recovery or compliance requirements. Moving every backup to external disks just relocates the problem and may move backups that should stay on managed storage, deleting by data classification ignores retention requirements, and keeping only the latest backup can make point-in-time recovery or required retention impossible.
A business analyst is using a client table and an invoice table to create a database view that shows clients who have not made purchases yet. Which of the following joins is most appropriate for the analyst to use to create this database view?
A.INNER JOIN ON Client.Key = Invoice.Key
B.RIGHT JOIN ON Client.Key = Invoice.Key WHERE Client.Key IS NULL
C.LEFT JOIN ON Client.Key = Invoice.Key
D.LEFT JOIN ON Client.Key = Invoice.Key WHERE Invoice.Key IS NULLCorrect
Explanation
A LEFT JOIN from Client to Invoice returns every client, with NULL in the invoice columns when there is no matching invoice, so filtering on Invoice.Key IS NULL leaves only clients without purchases (an anti-join). An INNER JOIN returns only clients that have invoices, a LEFT JOIN without the filter returns all clients, and the RIGHT JOIN keeps all invoices, so checking Client.Key IS NULL would find orphaned invoices rather than clients without purchases.
A database administrator needs to provide access to data from two different tables to multiple group users in order to facilitate ongoing reporting. However, some columns in each table are restricted, and users should not be able to see the values in these columns.
Which of the following is the best action for the administrator to take?
A.Create a stored procedure.
B.Create a view.Correct
C.Create a .csv export.
D.Create a trigger.
Explanation
A view can join the two tables and expose only the permitted columns, and users can be granted access to the view without access to the underlying tables, so it supports ongoing reporting while hiding restricted columns. A stored procedure is suited to parameterized operations rather than reusable reporting access, a .csv export is a static copy that goes stale and is hard to secure, and a trigger runs in response to data changes and does not control what users can read.
Which of the following describes a scenario in which a database administrator would use a relational database rather than a non-relational database?
A.An organization wants to maintain consistency among the data in the database.Correct
B.An organization requires data encryption.
C.An organization wants to process complex data sets.
D.An organization wants to store a large number of videos, photos, and documents.
Explanation
Relational databases enforce a schema, primary and foreign key constraints and ACID transactions, which keep data consistent across related tables. Encryption is available in both relational and non-relational systems, many non-relational systems handle complex or large data sets well, and large numbers of videos, photos and documents are better suited to object or document storage than to relational tables.
A database administrator set up a connection for a SQL Server instance for a new user, but the administrator is unable to connect using the user's workstation. Which of the following is the most likely cause of the issue?
A.The SQL Server codes are performing badly.
B.The SQL Server has not been tested properly.
C.The SQL Server ports to the main machine are closed.Correct
D.The SQL Server has many concurrent users.
Explanation
When a connection works on the server but fails from a client workstation, the most likely cause is that the firewall blocks the SQL Server port, by default TCP 1433 for the default instance (and UDP 1434 for the SQL Server Browser with named instances). Poorly performing code, insufficient testing or many concurrent users would slow the server down but would not stop a new client from connecting at all.
A group of developers needs access to a database in a development environment, but the database contains sensitive data. Which of the following should the database administrator do before giving the developers access to the environment?
A.Audit access to tables with sensitive data.
B.Remove sensitive data from tables.
C.Mask the sensitive data.Correct
D.Encrypt connections to the development environment.
Explanation
Masking replaces sensitive values with realistic but fictitious or obscured values, so developers can work with data that keeps its format and structure without seeing the real values. Removing the sensitive data would break the realistic test data developers need, auditing only records access after the fact, and encrypting connections protects data in transit but still shows the real values to the developers.
A database administrator needs to aggregate data from multiple tables in a way that does not impact the original tables, and then provide this information to a department. Which of the following is the best way for the administrator to accomplish this task?
A.Create a materialized view.Correct
B.Create indexes on those tables.
C.Create a new database.
D.Create a function.
Explanation
A materialized view runs the aggregation query once and stores its result as a separate object that the department can query, without changing or locking the original tables for each read; it can be refreshed on a schedule. Indexes speed up queries but do not aggregate anything, a new database is unnecessary overhead, and a function returns computed values but does not provide a stored, queryable data set.
A database administrator is migrating the information in a legacy table to a newer table. Both tables contain the same columns, and some of the data may overlap. Which of the following SQL commands should the administrator use to ensure that records from the two tables are not duplicated?
A.UNIONCorrect
B.JOIN
C.INTERSECT
D.CROSS JOIN
Explanation
UNION combines the result sets of two queries with the same columns and removes duplicate rows, so overlapping records appear only once (UNION ALL would keep them). INTERSECT returns only the rows present in both tables, losing the non-overlapping records, while JOIN and CROSS JOIN combine columns side by side rather than stacking rows.
A company is launching a proof-of-concept, cloud-based application. One of the requirements is to select a database engine that will allow administrators to perform quick and simple queries on unstructured data. Which of the following would be best suited for this task?
A.MongoDBCorrect
B.MS SQL
C.Oracle
D.Graph database
Explanation
MongoDB is a document (NoSQL) database that stores schema-flexible JSON-like documents, which makes it well suited to storing and quickly querying unstructured or semi-structured data in a cloud proof of concept. MS SQL and Oracle are relational databases that need a predefined schema, and a graph database is optimized for traversing relationships between entities rather than simple queries on unstructured data.
A database administrator needs to ensure database backups are occurring on a daily basis and at scheduled times. Which of the following actions should the administrator take?
A.Query the database to observe entries.
B.Check the database schema.
C.Review the backup media.
D.Review the server logs for entries.Correct
Explanation
The database server and backup job logs record when each backup job started, finished and whether it succeeded, so reviewing them confirms that backups ran daily at the scheduled times. Querying the database or checking the schema says nothing about backup jobs, and looking at the backup media shows that files exist but not reliably when or whether each scheduled job completed.
Which of the following is a result of an on-path attack on a system?
A.A Wi-Fi network that redirects to clones of legitimate websitesCorrect
B.A website that has crashed and is no longer accessible
C.An email from an unknown source requesting bank account details
D.A web application that returns the addresses of its customers
Explanation
In an on-path (man-in-the-middle) attack the attacker sits between the victim and the legitimate service, for example through a rogue Wi-Fi access point that intercepts traffic and redirects users to cloned websites. A crashed website points to a denial-of-service attack, an email asking for bank details is phishing, and a web application leaking customer addresses is a data exposure or injection issue.
Which of the following is the best way to migrate a large data load from one table to another, considering total time and blocking?
A.Split the load size into many transactions.Correct
B.Split the load size in half and run simultaneously.
C.Batch into small loads and run in parallel.
D.Batch large loads into one transaction.
Explanation
Splitting a large load into many smaller transactions keeps each transaction short, so locks are held briefly, lock escalation and log growth are limited, and other sessions are not blocked for long, while a failure only rolls back one small batch. One large transaction holds locks for the whole load and blocks other users, and running halves or small batches in parallel against the same table makes the loads compete for locks and block each other.
Following a security breach, a database administrator needs to ensure users cannot change data unless a request is approved by the management team. Which of the following principles addresses this issue?
A.Open access
B.Least resistance
C.Elevated privilege
D.Least privilegeCorrect
Explanation
Least privilege grants users only the permissions they need for their job, so ordinary users get read access and write rights are granted only when a request has been approved by management. Elevated privilege is the opposite of what is needed, and open access and least resistance are not security principles that restrict changes.
A programmer wants to configure a database to only allow read or write access when requests are coming from specific IP addresses. Which of the following can be used to configure IP addresses to allow access to the database?
A.Static IP address
B.FirewallCorrect
C.Dynamic IP address
D.DNS
Explanation
A firewall (network or host-based, or the firewall rules built into a database service) filters traffic by source IP address, so it can allow only the listed addresses to reach the database port. Static and dynamic IP addresses are ways hosts receive addresses, not access controls. DNS resolves names to addresses and does not restrict who may connect.
Verifying access...
Access Required
You need to purchase this exam to access practice mode
Unlock a few more questions every day for 300 points you earn free on
Godlike Academy. Everything you unlock with the Daily Climb stays open for 7 days after your last unlock.