MCTS - Microsoft Certified Technology Specialist SQL Server 2008 Administration Questions and Answers

Question 1

A database administrator is managing a critical OLTP database in SQL Server 2008 that requires the ability to restore to a specific point in time to minimize data loss.
Transaction log backups must be performed.
Which recovery model should be configured for this database?

Accepted Answer

Full

Answer

Simple

Answer

Bulk-Logged

Answer

Snapshot

Question 2

You are tasked with implementing a high-availability solution for a SQL Server 2008 database. The requirements specify that there should be no data loss upon failover and that failover should be automatic. Which database mirroring configuration should you implement?

Accepted Answer

High-safety mode with a witness

Answer

High-performance mode without a witness

Answer

High-safety mode without a witness

Answer

High-protection mode with a witness

Question 3

A company wants to enforce a standard that all user databases on a SQL Server 2008 instance must have the 'Auto Shrink' property set to 'False'. Which SQL Server 2008 feature should be used to automatically check for and log violations of this standard?

Accepted Answer

Policy-Based Management

Answer

Resource Governor

Answer

SQL Server Profiler

Answer

Database Mail

Question 4

You need to implement Transparent Data Encryption (TDE) on a user database in SQL Server 2008. Which of the following is a necessary prerequisite that must be created in the master database before you can create the Database Encryption Key (DEK) in the user database?

Accepted Answer

A Database Master Key (DMK) and a server certificate

Answer

A linked server to an encryption provider

Answer

A SQL Server Agent job to manage the keys

Answer

An extended stored procedure for encryption

Question 5

A DBA is concerned that ad-hoc reporting queries are consuming excessive CPU resources on a production SQL Server 2008 instance, negatively impacting the performance of the primary OLTP application. Which feature can be used to limit the CPU usage for the connections running these reporting queries?

Accepted Answer

Resource Governor

Answer

SQL Server Audit

Answer

Database Tuning Advisor

Answer

Activity Monitor

Question 6

Which of the following components in Policy-Based Management defines a set of logical properties that model the behavior or characteristics of a target, such as a database or a table?

Accepted Answer

Facet

Answer

Condition

Answer

Policy

Answer

A database administrator is managing a critical OLTP database in SQL Server 2008 that requires the ability to restore to a specific point in time to minimize data loss.Transaction log backups must be performed.Which recovery model should be configured for this database?

A database administrator is managing a critical OLTP database in SQL Server 2008 that requires the ability to restore to a specific point in time to minimize data loss.
Transaction log backups must be performed.
Which recovery model should be configured for this database?