MCTS - Microsoft Certified Technology Specialist SQL Server 2008 Administration Questions and Answers 1 — 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?
- Simple
- Full (Correct answer)
- Bulk-Logged
- Snapshot
Correct answer: Full
The Full recovery model logs all transactions and supports point-in-time restores. This model is required when the potential for data loss must be minimized and transaction log backups are necessary. The Simple recovery model does not allow for transaction log backups, and the Bulk-Logged model, while similar to Full, minimally logs certain bulk operations, which can complicate point-in-time recovery.
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?
- High-performance mode without a witness
- High-safety mode without a witness
- High-safety mode with a witness (Correct answer)
- High-protection mode with a witness
Correct answer: High-safety mode with a witness
High-safety mode with a witness is the only database mirroring configuration that supports automatic failover without data loss. This mode operates synchronously, ensuring that a transaction is committed on both the principal and mirror servers before being considered complete. The witness server is required to enable the automatic failover capability.
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?
- Resource Governor
- SQL Server Profiler
- Database Mail
- Policy-Based Management (Correct answer)
Correct answer: Policy-Based Management
Policy-Based Management is the feature designed for defining and enforcing policies for the configuration of SQL Server instances and databases. A policy can be created using the 'Database Options' facet to check the state of the 'AutoShrink' property and set an evaluation mode like 'On schedule' to periodically check and log any databases that are out of compliance.
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?
- A Database Master Key (DMK) and a server certificate (Correct answer)
- A linked server to an encryption provider
- A SQL Server Agent job to manage the keys
- An extended stored procedure for encryption
Correct answer: A Database Master Key (DMK) and a server certificate
To implement TDE, an encryption hierarchy must be established. This starts in the master database with the creation of a Database Master Key (DMK), which is used to protect a server certificate. This certificate is then used to protect the Database Encryption Key (DEK) in the user database that will be encrypted.
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?
- SQL Server Audit
- Database Tuning Advisor
- Resource Governor (Correct answer)
- Activity Monitor
Correct answer: Resource Governor
Resource Governor is a SQL Server 2008 feature designed to manage server workload and resources by specifying limits on CPU and memory usage for incoming requests. The DBA can create a resource pool with a maximum CPU percentage and a workload group for the reporting application, then use a classifier function to direct the reporting connections to that group.
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?
- Condition
- Policy
- Facet (Correct answer)
- Category
Correct answer: Facet
In Policy-Based Management, a Facet is a predefined set of logical properties that describe the configurable characteristics of a management target, like a database's 'Recovery Model' or a table's 'HasClusteredIndex' property. Conditions are then created to evaluate the state of these facet properties.
MCTS - Microsoft Certified Technology Specialist SQL Server 2008 Administration Questions and Answers 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?