DS0-001 Data Systems Management Practice Test 3 — 15 Questions

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Move-ClusterGroup. 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.

Answer

SHUTDOWN WITH NOWAIT

Answer

ALTER SERVER ROLE

Answer

RESTORE DATABASE

Question 2

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?

Accepted Answer

The certificate and its private key. Transparent Data Encryption (TDE) encrypts the database using a Database Encryption Key (DEK), which is itself protected by a certificate stored in the master database. To restore a TDE-encrypted backup on a different server, you must supply that certificate and its private key, otherwise the DEK cannot be decrypted and the restore fails.

Answer

The master data file

Answer

The SQL Server configuration file

Answer

The transaction log file

Question 3

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

Accepted Answer

DBCC SHRINKFILE. DBCC SHRINKFILE is the targeted command that reduces the size of a specific database file — including a transaction log file — by moving the file's end marker inward and releasing unused pages back to the OS. It is the correct tool when a DBA needs to reclaim space from one named log file without affecting other files in the database.

Answer

DBCC SHRINKDATABASE

Answer

BACKUP LOG ... WITH TRUNCATE

Answer

ALTER DATABASE ... SET LOGFILE

Question 4

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

Accepted Answer

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

Answer

Index

Answer

Primary Key

Answer

Unique Constraint

Question 5

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?

Accepted Answer

Data type mismatch between column and parameter. Implicit conversion occurs when SQL Server must convert a value from one data type to another to compare it, such as when a varchar parameter is compared to an int column. Because the conversion is applied to the column (the higher-precedence type), the optimizer cannot seek the index and must scan, which is why the execution plan shows the warning. Matching parameter data types to column data types eliminates the conversion and restores index seeks.

Answer

The database collation is incorrect

Answer

The table is not partitioned

Answer

The statistics are outdated

Question 6

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

Accepted Answer

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

Answer

Performance Monitor

Answer

SQL Server Profiler

Answer

SQL Server Configuration Manager

Question 7

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?

Accepted Answer

Copy-only backup. A copy-only backup is a special type of backup that does not affect the normal backup sequence. It is independent of the regular full and differential backup chain, so it does not interfere with the differential base or log sequence. This makes it ideal for ad-hoc backups in a 24/7 environment without disrupting the existing backup strategy.

Answer

Full backup

Answer

Differential backup

Answer

Transaction log backup

Question 8

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

Accepted Answer

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

Answer

To enforce data encryption

Answer

To reduce disk space usage

Answer

To manage user permissions

Question 9

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?

Accepted Answer

max server memory. The 'max server memory' setting in SQL Server configures the upper limit of memory that the SQL Server instance can allocate from the operating system. Setting this value prevents SQL Server from consuming all available memory and starving the OS or other applications, which is critical on a production server.

Answer

Query memory grant

Answer

Working set size

Answer

min server memory

Question 10

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

Accepted Answer

It requires rebuilding the database or recreating tables. Changing a database's collation is not a simple metadata update; it affects how data is stored, compared, and sorted. In most database systems (e.g., SQL Server, MySQL), altering collation requires rebuilding the database or recreating tables and indexes, because existing data must be converted to the new collation's encoding and comparison rules.

Answer

It can be done instantly with an ALTER command

Answer

It is a metadata-only change

Answer

It only impacts system tables

Question 11

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

Accepted Answer

Create read-only replicas. Option A (Create read-only replicas) is correct because read replicas offload SELECT traffic from the primary database, allowing reads to be distributed across multiple nodes and improving throughput for read-heavy workloads. Option B (Create covering indexes) is correct because a covering index contains all columns required by a query, letting the database satisfy the query entirely from the index without touching the base table, drastically reducing I/O for frequent read queries. Option E (Implement query caching) is correct because caching the results of frequently executed read queries avoids repeated database work and returns results faster, directly benefiting read-heavy patterns. Option C (Increase the number of write threads) does not belong because it targets write throughput, not read performance, and can even contend with reads. Option D (Enable synchronous replication) does not belong because synchronous replication waits for replicas to acknowledge writes, adding latency and reducing write performance rather than improving read-heavy workloads.

Answer

Increase the number of write threads

Answer

Enable synchronous replication

Question 12

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

Accepted Answer

Excessive transaction log growth. Option C is correct because dropping a large table is a fully logged DDL operation: SQL Server records the page deallocations and extent changes in the transaction log, so on a big table this can cause excessive transaction log growth and potentially fill the log drive if the log isn't sized or backed up appropriately. Option D is correct because a DROP TABLE removes the object immediately, so any application, view, stored procedure, or query referencing that table will fail with errors such as 'Invalid object name' as soon as it executes. Option A is wrong because DROP TABLE does not touch backup files; backups are separate media and are unaffected by dropping a table. Option B is wrong because dropping a user table does not corrupt the master database, which stores server-level metadata rather than user table data. Option E is wrong because dropping a table releases its extents back for reuse, so the database file does not permanently increase in size; if anything, space is freed within the file.

Answer

Automatic loss of database backup files

Answer

Corruption of the master database

Answer

Permanent increase in database file size

Question 13

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

Accepted Answer

Off-site or cloud storage of backups. Option C is correct because storing backups off-site or in the cloud protects against site-wide disasters such as fire, flood, or ransomware that could destroy locally held copies, ensuring a survivable copy exists in a separate failure domain. Option D is correct because backups are only trustworthy if restoration has been verified; regularly testing restores validates backup integrity, media readability, and RTO/RPO assumptions before an actual outage. Option E is correct because documented and tested failover procedures ensure that database replicas, clustering, or standby systems can be promoted quickly and consistently, minimizing downtime and human error during a real incident. Option A is not appropriate because RAID 0 offers striping with no redundancy or parity, so a single disk failure destroys the array and it is never a disaster recovery safeguard. Option B is not appropriate because keeping all data in memory is volatile, non-persistent, and lost on power failure or crash, providing no durable recovery capability.

Answer

Using only RAID 0 for storage

Answer

Keeping all data in memory

Question 14

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

Accepted Answer

Database must be in Full recovery model. Option A is correct because database mirroring requires the principal database to use the Full recovery model so that the transaction log can support the log stream sent to the mirror; Simple or Bulk-Logged recovery models are not supported for mirroring. Option B is correct because before mirroring can start, you must restore a full backup of the principal database on the mirror server WITH NORECOVERY, followed by any necessary transaction log backups WITH NORECOVERY, to bring the mirror database to a recovering state. Option E is correct because the SQL Server service accounts on both the principal and mirror instances must have the appropriate permissions to connect and communicate, typically via domain accounts or certificates with endpoints configured for database mirroring. Option C is not required — a domain controller is not necessary, and in fact running SQL Server on a domain controller has its own restrictions. Option D is not required — the mirror database stays in a restoring/recovering state (not read-only accessible) until a failover occurs, and read-only access is only available with a snapshot, not as a mirroring prerequisite.

Answer

The server must be a domain controller

Answer

Must be running in Read-only mode

Question 15

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

Accepted Answer

Transaction log full errors. An undersized transaction log file commonly manifests as transaction log full errors (Option C), because the log cannot grow fast enough or is capped, so SQL Server raises error 9002 ("The transaction log for database is full") when it cannot accommodate new log records. It also causes frequent auto-growth events (Option D), since the log repeatedly hits its size limit and must expand in small increments, producing many VLFs and I/O stalls. Options A, B, and E are not typical symptoms of an undersized log: memory fragmentation relates to buffer pool or OS memory allocation, excessive CPU usage stems from query or workload inefficiency rather than log sizing, and slow index reading is an I/O or indexing issue unrelated to transaction log capacity.

Answer

Memory fragmentation

Answer

Excessive CPU usage

Answer

Slow index reading