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

You are a professional level SQL Sever 2008 Database Administrator.
A large financial database is hosted by the instance. The backup strategies listed below are contained by
the database:
First, the performance of a full database backup is implemented once a week.
Secondly, the performance of a differential backup is implemented every day.
Thirdly, the performance of a transaction log backup is implemented every hour.
An end-of-year batch process should be implemented, and the process takes two hours to run.
About five percent of data within the database will be changed by the batch process.
Since you are the technical support, you are required to make sure that the batch operation can be rolled
back
in the shortest time, if the batch process is not approved by the Finance department.
What should you do first before start the batch process?

• A. You should create a database snapshot.
• B. You should record the time before the batch operation. And then you should perform a transaction log
• C. You should perform a differential backup.
• D. You should create a marked transaction. And then you should perform a transaction log backup.

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 are a professional level SQL Sever 2008 Database Administrator.
A maintenance strategy should be designed for a mission-critical database, and a large table named Orders is contained by the database. Index maintenance operations are contained in the design plan. When you design the strategy, the facts listed below should be taken into consideration.
First, the users continuously access to the Orders table in the database.
Secondly, a column of the xml data type is contained by Orders table.
Thirdly, the new rows are regularly added to the Orders table.
Fourthly, the average fragmentation for the clustered index of the Orders table is no more than 2
percent.
A strategy should be designed to have the performance of the queries on the table optimized.
Which action will you perform?

• A. The clustered index of the Orders table should be dropped.
• B. The clustered index of the Orders table offline should be rebuilt once a month.
• C. The clustered index of the Orders table should be excluded from scheduled reorganizing or rebuilding operations.
• D. The clustered index of the Orders table should be reorganized by reducing the fill factor.

You administer a SQL Server 2008 instance that hosts a large database.
The database experiences high volumes of queries and transactions that are processor-intensive. You plan to implement an incremental backup strategy for the database. You also plan to use backup compression.
You need to ensure that the backup jobs have a minimal effect on the performance of the server.
What should you do?

• A. Use database snapshots.
• B. Spread the database backup across multiple backup devices.
• C. Use the Resource Governor.
• D. Reconfigure the Affinity I/O Mask configuration option.

You administer a SQL Server 2008 infrastructure.
Your company requires capacity planning information.
You need to design a long-term infrastructure monitoring strategy.
Which two tasks should you include in your design? (Each correct answer presents part of the solution.
Choose two.)

• A. Clear the system log and the application log every hour.
• B. Backup all databases every day.
• C. Baseline the system before you deploy production databases.
• D. Review system monitor counters on a regular basis.
• E. Create a maintenance plan that rebuilds indexes every week.