Microsoft PRO:MS SQL Serv 08,Design,Optimize, and Maintain DB Admin Solu Sample Questions:

1. You are a professional level SQL Sever 2008 Database Administrator.
After a regular test, you find that performance degradation is experienced by an instance for the three reasons:
Excessive CPU usage, Server processes paging and Deadlocks
A monitoring solution should be implemented to provide data, monitor and troubleshoot performance issues and detailed deadlock information should be contained in the provided data.
You should utilize the least amount of administrative effort to finish the task.
Which tool will you utilize to finish the task?

A) To finish the task, you should utilize Extended Events.
B) To finish the task, you should utilize Performance Monitor (SYSMON).
C) To finish the task, you should utilize Database Engine Tuning Advisor.
D) To finish the task, you should utilize Resource Governor.

2. You administer a SQL Server 2008 infrastructure.
You implement log shipping for several databases on three SQL Server instances. The logs are shipped to a fourth SQL Server instance. You plan a manual failover.
You need to ensure that the database applications use the secondary server after failover. You also need to ensure that users can access the most recent data.
Which three tasks should you perform? (Each correct answer presents part of the solution. Choose three.)

A) Redirect client computers to the secondary instance.
B) Apply any unapplied transaction log backups in sequence to each secondary database by using the WITH RECOVERY option on the last log.
C) Back up the tail of the transaction log of primary databases by using the WITH NORECOVERY option.
D) Copy all log shipping network shares to the secondary instance.
E) Back up all databases on the secondary instance.

3. You administer a SQL Server 2008 instance.
You plan to deploy multiple databases to the instance. You design the security policy for the instance.
A stored procedure verifies the partition compression settings on each database deployed to the instance.
You plan to use a policy to execute the stored procedure.
You need to ensure that the policy is enforced by using Policy-Based Management Framework.
What should you do?

A) Grant EXECUTE permissions on the stored procedure to the ##MS_PolicyEventProcessingLogin## login.
B) Enable the nested triggers server configuration option.
C) Grant EXECUTE permissions on the stored procedure to the ##MS_PolicyTsqlExecutionLogin## login.
D) Grant EXECUTE permissions on the stored procedure to the SQLAgent service account.

4. You administer a SQL Server 2008 infrastructure.
You discover that an instance experiences performance degradation for the following reasons:
Excessive CPU usage
Server processes paging
Deadlocks
You need to design a monitoring solution that can provide data, including detailed deadlock information, to monitor and troubleshoot performance issues. You want to achieve this goal by using the minimum amount of administrative effort.
What tool should you use?

A) Database Engine Tuning Advisor
B) Resource Governor
C) Performance Monitor (SYSMON)
D) Extended Events

5. You administer a SQL Server 2008 instance. You plan to design a high-availability solution by using database mirroring.
The current application includes programming logic that will retry transactions if a failover occurs.
You need to configure a strategy that will provide the minimum amount of latency for committed transactions.
What should you do?

A) Configure manual failover role switching.
B) Pause database mirroring.
C) Configure automatic failover role switching.
D) Configure forced service role switching.

Solutions: