CCNA Data Systems Management Questions

MCQmedium

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

A.It can be done instantly with an ALTER command

B.It is a metadata-only change

C.It requires rebuilding the database or recreating tables

D.It only impacts system tables

AnswerC

Existing data must be migrated to be stored with the new collation.

Why this answer

Changing a database collation is a destructive operation. It requires re-creating the database or recreating all tables, as column-level collations are not automatically updated for existing data.

Practice this question →

MCQmedium

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.pg_stat_database

B.pg_locks

C.pg_stat_user_tables

D.pg_stat_activity

AnswerD

This system view displays information about current backend processes and their active queries.

Why this answer

pg_stat_activity provides information about the current state of each backend process, including the query being executed.

Practice this question →

Multi-Selectmedium

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

Select 3 answers

A.Create read-only replicas

B.Create covering indexes

C.Increase the number of write threads

D.Enable synchronous replication

E.Implement query caching

AnswersA, B, E

Offloads read traffic from the primary node.

Why this answer

Adding read-replicas, implementing caching, and creating appropriate indexes are all standard methods to offload and accelerate read operations.

Practice this question →

MCQmedium

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?

A.Full backup

B.Copy-only backup

C.Differential backup

D.Transaction log backup

AnswerB

Copy-only backups are designed to not affect the standard backup and restore sequence.

Why this answer

A 'Copy-only' backup creates a copy of the database or log without affecting the differential or log backup sequence.

Practice this question →

Multi-Selectmedium

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

Select 2 answers

A.Reorganize or rebuild fragmented indexes

B.Execute DBCC SHRINKFILE

C.Create a new database user

D.Perform a full database backup

E.Clear the SQL Server error log

AnswersA, B

This can help defragment space, allowing for shrinkage.

Why this answer

Shrinking the database and reorganizing/rebuilding tables to reclaim space are valid ways to reduce the footprint of a data file.

Practice this question →

Multi-Selecthard

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

Select 3 answers

A.Low page density

B.High scan latency

C.Decreased query performance

D.Increased memory usage

E.Increased server uptime

AnswersA, B, C

Indicates wasted space within the data pages.

Why this answer

High scan latency, decreased query performance, and low page density are all common signs that indexes need maintenance.

Practice this question →

MCQmedium

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.Use the Move-Item cmdlet

B.Shrink the database

C.DROP and RECREATE index on a new filegroup

D.Disable the index

AnswerC

Moving the clustered index to a different filegroup physically moves the base table data.

Why this answer

Moving the clustered index to a new filegroup moves the data pages of the table to that filegroup, effectively changing the physical storage location.

Practice this question →

MCQeasy

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?

A.Disable antivirus

B.Use striped backups

C.Run in single-user mode

D.Increase the recovery interval

AnswerB

Restoring from multiple backup files concurrently increases IO throughput and reduces restore time.

Why this answer

Using the 'WITH MOVE' and 'FILE=' options is standard, but using multiple backup files ('striped backups') allows for parallel reading and writing during the restore process, significantly increasing speed.

Practice this question →

MCQeasy

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?

A.Transaction Log Backups every 10 minutes

B.Daily Full Backups

C.Weekly Full and Daily Differential Backups

D.Hourly Transaction Log Backups

AnswerA

Backing up every 10 minutes ensures the data loss is limited to 10 minutes, satisfying the 15-minute RPO.

Why this answer

An RPO of 15 minutes implies that the maximum amount of data loss acceptable is 15 minutes. Therefore, transaction log backups must occur at least every 15 minutes.

Practice this question →

MCQmedium

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.Innodb_buffer_pool_reads

B.Seconds_Behind_Master

C.Slave_IO_Running

D.Threads_connected

AnswerB

This indicates the number of seconds the replica is lagging behind the primary.

Why this answer

Seconds_Behind_Master is the standard variable to check the replication delay in a traditional MariaDB replication setup.

Practice this question →

Multi-Selectmedium

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

Select 2 answers

A.Memory fragmentation

B.Excessive CPU usage

C.Transaction log full errors

D.Frequent auto-growth events

E.Slow index reading

AnswersC, D

The database cannot log more changes if the space is exhausted.

Why this answer

Frequent auto-growth events and failed transactions (due to log full errors) are the primary indicators of an undersized log.

Practice this question →

MCQmedium

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.Performance Monitor

B.SQL Server Configuration Manager

C.Extended Events

D.Windows Event Viewer

AnswerC

Extended Events is the preferred, lightweight way to capture query execution details and plans.

Why this answer

SQL Server Profiler or Extended Events are the standard tools for tracing and capturing execution plans, especially for identifying expensive queries.

Practice this question →

MCQhard

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?

A.The database collation is incorrect

B.The table is not partitioned

C.Data type mismatch between column and parameter

D.The statistics are outdated

AnswerC

When comparing a VARCHAR column to an NVARCHAR parameter, SQL Server must convert the column values to match, preventing index usage.

Why this answer

An implicit conversion happens when the data type of a column does not match the data type of the constant or parameter being compared to it, forcing the engine to convert every row.

Practice this question →

MCQeasy

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

A.To speed up data retrieval

B.To enforce data encryption

C.To reduce disk space usage

D.To manage user permissions

AnswerA

Indexes allow the database engine to find data without performing full table scans.

Why this answer

Indexes are designed to speed up data retrieval by providing a faster lookup mechanism than scanning every row in a table.

Practice this question →

MCQhard

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.ALTER TABLE ... SHRINK SPACE

B.TRUNCATE TABLE

C.ANALYZE TABLE ... COMPUTE STATISTICS

D.DROP AND RECREATE

AnswerA

This command compacts the table segments and lowers the high water mark to reclaim space.

Why this answer

The ALTER TABLE ... SHRINK SPACE command is the standard way to defragment and reclaim space in an Oracle table while maintaining the table's structure and indexes.

Practice this question →

MCQhard

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?

A.CHECK TABLE

B.SHOW ENGINE INNODB STATUS

C.EXPLAIN

D.SHOW PROCESSLIST

AnswerB

This command outputs the InnoDB monitor output, which includes the latest deadlock detection information.

Why this answer

The command 'SHOW ENGINE INNODB STATUS' provides a section specifically detailing the last detected deadlock, including the locks held by the involved transactions.

Practice this question →

Multi-Selecteasy

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

Select 3 answers

A.LDAP Authentication

B.SQL Server Authentication

C.Windows Authentication

D.OAuth Authentication

E.Mixed Mode Authentication

AnswersB, C, E

Native database-managed authentication.

Why this answer

SQL Server supports Windows Authentication, SQL Server Authentication, and Mixed Mode (which enables both).

Practice this question →

Multi-Selecteasy

Which THREE of the following are standard database maintenance tasks?

Select 3 answers

A.Creating new database servers

B.Updating statistics

C.Changing the server IP address

D.Rebuilding indexes

E.Full database backups

AnswersB, D, E

Keeps the query optimizer informed for better performance.

Why this answer

Updating statistics, backing up the database, and rebuilding indexes are foundational maintenance tasks for performance and data safety.

Practice this question →

Multi-Selecthard

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

Select 3 answers

A.Database must be in Full recovery model

B.Full backup and log backup restored on the mirror

C.The server must be a domain controller

D.Must be running in Read-only mode

E.Correct service account permissions

AnswersA, B, E

Mirroring requires the log chain.

Why this answer

Mirroring requires the database to be in Full recovery mode, a full backup/restore for seeding, and the correct account permissions for network communication.

Practice this question →

MCQeasy

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

A.Differential backups only

B.Full and Transaction Log backups

C.Full backups only

D.Copy-only backups

AnswerB

Transaction logs capture all changes, enabling recovery to any specific point in time between backups.

Why this answer

Transaction log backups, when used in conjunction with full backups and the Full recovery model, allow for point-in-time recovery.

Practice this question →

MCQhard

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?

A.Active Directory group

B.Service Master Key

C.SSL/TLS certificate

D.Certificate

AnswerD

TDE uses a DEK, which must be protected by a certificate or an asymmetric key.

Why this answer

The Certificate or Asymmetric Key is required to protect the DEK, which in turn is protected by the Master Key in the master database.

Practice this question →

Multi-Selecteasy

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

Select 3 answers

A.Using only RAID 0 for storage

B.Keeping all data in memory

C.Off-site or cloud storage of backups

D.Regularly tested backup restoration

E.Documented and tested failover procedures

AnswersC, D, E

Protects data if the primary data center is destroyed.

Why this answer

A complete DR plan requires regular backups, a documented restoration procedure, and off-site storage to protect against site-wide disasters.

Practice this question →

MCQhard

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.Move the log to a high-performance dedicated drive

B.Use data compression

C.Increase the buffer pool size

D.Reduce the database recovery interval

AnswerA

Transaction logs are write-intensive; moving them to a fast, dedicated drive reduces latency.

Why this answer

Moving the transaction log to a dedicated physical drive with high write performance (low latency) is the most effective way to resolve I/O bottlenecks related to transaction logging.

Practice this question →

MCQmedium

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

A.DBCC SHRINKDATABASE

B.BACKUP LOG ... WITH TRUNCATE

C.ALTER DATABASE ... SET LOGFILE

D.DBCC SHRINKFILE

AnswerD

This command is used to shrink a specific file, such as a transaction log.

Why this answer

DBCC SHRINKFILE is the command used to shrink the size of data or log files in SQL Server.

Practice this question →

Multi-Selecthard

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

Select 3 answers

A.Disk latency (I/O wait)

B.Batch requests per second

C.The database name length

D.Server room temperature

E.CPU utilization

AnswersA, B, E

Often the root cause of slow performance.

Why this answer

Disk latency, CPU utilization, and batch requests per second are classic metrics for diagnosing performance issues.

Practice this question →

MCQeasy

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

A.Index

B.Primary Key

C.Unique Constraint

D.Foreign Key

AnswerD

Foreign keys maintain the relationship and referential integrity between tables.

Why this answer

A Foreign Key constraint is designed to ensure that data in one table matches valid entries in another table, maintaining referential integrity.

Practice this question →

Multi-Selecteasy

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

Select 3 answers

A.Full

B.Differential

C.Snapshot

D.Transaction Log

E.Hardware copy

AnswersA, B, D

Captures the entire database.

Why this answer

Full, differential, and transaction log backups are the standard backup types used in professional database environments.

Practice this question →

Multi-Selectmedium

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

Select 2 answers

A.The color of the server chassis

B.Number of database users

C.Network latency between nodes

D.The database name

E.Storage subsystem performance

AnswersC, E

Affects the sync speed and potential for data loss.

Why this answer

Network latency between nodes and the speed of the storage subsystem are critical to ensure synchronization is efficient and failover is reliable.

Practice this question →

Multi-Selectmedium

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

Select 2 answers

A.Row-level security

B.Add more CPU cores

C.Role-based access control

D.Increase the backup frequency

E.Use the sa account

AnswersA, C

Limits access to specific rows based on user criteria.

Why this answer

Implementing role-based access control (RBAC) and row-level security (RLS) are effective ways to secure access to data.

Practice this question →

MCQeasy

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

A.Performance Monitor

B.SQL Server Profiler

C.SQL Server Management Studio

D.SQL Server Configuration Manager

AnswerC

SSMS is the primary GUI-based management tool for SQL Server.

Why this answer

SQL Server Management Studio (SSMS) is the standard GUI tool for SQL Server management.

Practice this question →

MCQmedium

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.The master data file

B.The SQL Server configuration file

C.The transaction log file

D.The certificate and its private key

AnswerD

Without the certificate used to protect the encryption key, the restored database cannot be decrypted.

Why this answer

To restore a TDE-encrypted database, the certificate used to protect the DEK must be backed up and moved to the new server.

Practice this question →

MCQmedium

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?

A.Differential

B.Bulk-Logged

C.Full

D.Simple

AnswerB

Bulk-Logged recovery minimizes log space usage during bulk operations while still allowing for log backups.

Why this answer

The Full recovery model requires log backups to truncate the transaction log. The Simple recovery model automatically truncates the log, but the Bulk-Logged recovery model is specifically designed to minimize log space usage during large-scale bulk-load operations.

Practice this question →

MCQmedium

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.Active Directory Users and Computers

B.SQL Server Configuration Manager

C.SQL Server Agent Maintenance Plans

D.Windows Task Scheduler

AnswerC

Maintenance Plans provide a GUI to schedule and automate statistics updates.

Why this answer

Maintenance Plans allow for the automation of tasks like updating statistics, reorganizing indexes, and backing up databases.

Practice this question →

Multi-Selecthard

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

Select 2 answers

A.Automatic loss of database backup files

B.Corruption of the master database

C.Excessive transaction log growth

D.Immediate application errors due to missing objects

E.Permanent increase in database file size

AnswersC, D

The operation needs to be recorded, potentially filling the log.

Why this answer

Dropping a large table can cause massive transaction log growth if the operation is logged, and it can cause immediate application failures if the application expects the table to exist.

Practice this question →

MCQhard

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?

A.Create an index on join columns

B.Set the isolation level to Read Uncommitted

C.Force the join order

D.Update statistics

AnswerA

Indexing join keys provides the optimizer with alternative, potentially more efficient, join strategies.

Why this answer

Adding an index on the join columns allows the optimizer to consider a 'Merge Join' or 'Nested Loop' join, which may be more memory-efficient than a Hash Match.

Practice this question →

MCQmedium

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.Readable Secondary

B.Automatic Failover

C.Synchronous Commit

D.Primary Role

AnswerA

This setting enables the secondary replica to handle read-only workloads.

Why this answer

The 'Readable Secondary' property must be set to 'Read-intent only' or 'Yes' to allow the secondary node to accept connections for reporting tasks.

Practice this question →

MCQhard

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?

A.SHUTDOWN WITH NOWAIT

B.ALTER SERVER ROLE

C.Move-ClusterGroup

D.RESTORE DATABASE

AnswerC

This PowerShell command or the Failover Cluster Manager GUI triggers the manual transition.

Why this answer

In a WSFC environment, the 'Move' command in the Failover Cluster Manager is used to manually move the role (including the database resource) to another node.

Practice this question →

MCQeasy

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.--quick

B.--add-drop-table

C.--all-databases

D.--no-data

AnswerD

This option excludes data rows and exports only the schema definitions.

Why this answer

The '--no-data' flag instructs mysqldump to export the schema (CREATE statements) without including the table content.

Practice this question →

MCQhard

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?

A.max server memory

B.Query memory grant

C.Working set size

D.min server memory

AnswerA

This setting limits the instance to a specified maximum amount of memory.

Why this answer

The 'max server memory' setting in SQL Server configuration determines the ceiling for the buffer pool, preventing the instance from starving the OS.

Practice this question →