Microsoft 070-450 : PRO:MS SQL Serv 08,Design,Optimize, and Maintain DB Admin Solu

You administer a SQL Server 2008 instance that hosts a large financial database.
The database has the following backup strategies:
A full database backup is performed once a week.
A differential backup is performed every day.
A transaction log backup is performed every hour.
You plan to execute an end-of-year batch process that takes two hours to run. The batch process will modify about five percent of data within the database.
You need to ensure that if the Finance department does not approve the batch process, the batch operation can be rolled back in minimum possible time.
What should you do prior to starting the batch process?

• A. Perform a differential backup.
• B. Create a marked transaction. Perform a transaction log backup.
• C. Record the time before the batch operation. Perform a transaction log backup.
• D. Create a database snapshot.

You are a professional level SQL Sever 2008 Database Administrator.
It is on a four-processor, quad-core server. CPU pressure is regularly experienced by the server. An extremely large mission-critical database is consisted by the instance, and it is utilized constantly. The online index rebuilds should not occupy all available CPU cycles.
From the following four configurations, which one should you utilize?

• A. You should utilize optimize for ad hoc workloads.
• B. You should utilize affinity mask.
• C. You should utilize max degree of parallelism.
• D. You should utilize affinity I/O mask.

You administer a SQL Server 2008 infrastructure.
The instance contains a database required for the day-to-day business of your company. Users experience slow response time when they execute reports.
You plan to design a performance monitoring strategy that captures and stores the following data:
Executed Transact-SQL statements and query activity.
Blocking and deadlock information.
Counters for disk, CPU, and memory.
You need to implement the monitoring process by using the minimum amount of administrative effort.
What should you do?

• A. Use the dynamic management views.
• B. Use the client-side profiler trace.
• C. Use the System Monitor counter log trace.
• D. Use the data collector

You are a professional level SQL Sever 2008 Database Administrator.
A database is hosted by the instance, and sensitive data is included in the database.
A database backup strategy will be implemented for the database.
You need to have all backups of the database encrypted.
The backups should not be accessed by unauthorized users.
Which action will you perform?

• A. The BACKUP statement should be utilized along with the MEDIAPASSWORD option.
• B. The BACKUP statement should be utilized along with the PASSWORD option.
• C. Transparent database encryption should be utilized.
• D. Windows BitLocker Drive Encryption technology should be utilized.

You administer a SQL Server 2008 infrastructure.
Developers in your company have rights to author policies. A test server is used to develop and test the
policies.
The Policy-Based Management feature generates SQL Server Agent alerts when a policy is violated.
The developers are able to create and modify policies, but are unable to test policy violation alerts.
You need to grant the necessary permission to the developers to test the policies. You also need to comply
with
the least privilege principle when you grant the permission.
What should you do?

• A. Add the developers to the sysadmin server role.
• B. Add the developers to the PolicyAdministratorRole role in the MSDB database.
• C. Grant the EXECUTE permission on the sys.sp_syspolicy_execute_policy stored procedure to the developers.
• D. Grant the ALTER TRACE permission to the developers.