Data Systems Management

A DBA is configuring an Always On Availability Group in SQL Server. To ensure that the secondary node can be used for read-only reporting tasks, which setting must be correctly configured in the Availability Group replica settings?

A database administrator wants to move a heavily accessed table to a different physical storage tier to improve performance. In SQL Server, what is the best way to move the table data while retaining the clustered index?

A database administrator needs to ensure that a SQL Server transaction log does not grow uncontrollably during a high-volume ETL process. Which recovery model should be configured to allow for periodic log truncation while maintaining a balance between performance and recovery needs?

In PostgreSQL, you need to monitor the performance of your long-running queries. Which view is the most appropriate to inspect the query execution statistics for all active sessions?

A DBA needs to implement transparent data encryption (TDE) for an entire database. Which component is required to protect the Database Encryption Key (DEK) in a SQL Server environment?

When planning a backup strategy, a company requires that the Recovery Point Objective (RPO) be set to 15 minutes. Which backup schedule meets this requirement?

In an Oracle Database environment, a DBA identifies that specific queries are performing poorly due to fragmented tables. Which command should the DBA execute to reclaim unused space and defragment the table without dropping the table structure?

A MySQL DBA needs to diagnose a deadlock issue. Which tool or command provides the most detailed information regarding the specific InnoDB internal locks held by transactions at the time of a deadlock?

During a disaster recovery test, the DBA realizes the database restoration takes too long. Which action is the most effective way to improve the restore speed of a large database?

You are managing a MariaDB cluster. You notice that nodes are falling out of sync. Which status variable should you check to verify the replication lag between the primary and the replica?

A DBA is tuning a query that performs a large join. The execution plan shows a 'Hash Match' operation. What can the DBA do to potentially change the join algorithm if the Hash Match is causing high memory pressure?

Which database backup strategy is recommended to ensure that you can restore a database to a specific point in time?

A DBA needs to automate the maintenance of database statistics in SQL Server. Which tool is the standard approach to manage this across all user databases?

A database is experiencing high I/O wait times on the log drive. What is the most effective configuration change to alleviate this bottleneck?

A database administrator needs to perform a schema-only export of a database. Which tool parameter is typically used for this purpose in mysqldump?

A database server is consistently hitting high CPU usage. You suspect an unoptimized query is causing a full table scan. Which tool should you use to capture the execution plan of currently running queries?

A database is configured with a high-availability cluster. During a planned maintenance window, you need to fail over to the secondary node manually. Which command or action is used in a Windows Server Failover Cluster (WSFC) environment?

You are managing a database that uses transparent data encryption. If you need to back up the database, what additional component must be included to ensure the backup can be restored on a different server?

A database administrator notices that the transaction log file is nearly full. Which operation will shrink the file to free up space?

Which database object is specifically designed to enforce referential integrity between two tables?

A DBA is troubleshooting a slow query. The execution plan indicates an 'Implicit Conversion' is occurring, which prevents the use of an index seek. What is the most likely cause?

Which tool provides a graphical interface for managing SQL Server instances, including configuring security and creating maintenance plans?

You are performing a backup for a database that is in a 24/7 environment. Which backup type should you use if you want to avoid interfering with the existing backup sequence of full and differential backups?

What is the primary function of an index in a database?

A production database server is running out of memory. Which setting in SQL Server limits the amount of memory the instance can consume from the OS?

You need to change the collation of a database. What is the impact of performing this operation?

Which THREE of the following are valid methods to improve database performance for read-heavy workloads?

Which TWO of the following are common risks associated with performing a direct 'DROP' operation on a large production table?

Which THREE of the following are essential components of a robust disaster recovery plan for a mission-critical database?

Which TWO of the following actions can a DBA take to reduce the size of a database data file?

Which THREE of the following are necessary prerequisites for implementing database mirroring in a SQL Server environment?

Which TWO of the following are common symptoms of an undersized transaction log file?

Which THREE of the following are valid SQL Server authentication modes?

Which THREE of the following are standard database maintenance tasks?

Which THREE of the following are common types of database backups?

Which THREE of the following are indicators of significant index fragmentation in a database?

Which TWO of the following are key considerations when configuring a database for high availability?

Which THREE of the following items should be monitored to identify performance bottlenecks in a database?

Which TWO of the following can be used to prevent unauthorized access to sensitive database tables?