SQL Server 2008 Administration Flashcards

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?

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.

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?

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.

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?

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.

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?

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.

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?

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.

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?

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.