You administer a Microsoft SQL Server 2008 R2 instance. Mirroring has been configured
between two servers.
You need to ensure that automatic failover will occur when the principal server fails
What should you do?

DRAG DROP
You administer a Microsoft SQL Server 2008 R2 database instance.
You plan to automate maintenance tasks.
What task types are included in a SQL Maintenance Plan? (To answer, drag the component
to the correct task or tasks in the answer area. The component may be used once or more
than once. Each task may be used once or not at all. Additionally, you may need to drag the
split bar between panes or scroll to view content.)

You maintain a SQL Server 2008 instance. You discover that the database named
CityPowerLightDB presents data integrity problems. You perform a full database backup at
22:00 hours. The SQL Server instance experiences a power failure at 05:59 hours. No data
modifications have occurred after the backup. When the database is online, you discover
that some table data pages are corrupt. You need to completely repair the database in the
minimum amount of time. What should you do?

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems
table. The table has the following definition: Currently, the table is partitioned by year with
each year in its own filegroup.

You need to add a new partition for the upcoming year.
What should you do?

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems
table. The table has the following definition:

The following DDL has been run on the database:
CREATE PARTITION FUNCTION FUNC_FG (INT)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
You need to create a partition scheme that will place all data to the SECONDARY filegroup.
What should you do?

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems
table. The table has the following definition:

Currently, the table is partitioned by year with each year in its own filegroup.
You need to move the data from the oldest year into a new table in a different Schema to
archive the data. What should you do?

You maintain a SQL Server 2008 instance.
You use SQL Server Database Mail to distribute multiple reports that are scheduled and
generated every night.
You need to enforce a mail retention policy to delete copies of automatically generated mails
after 60 days.
Which process should you use?

DRAG DROP
You administer a Microsoft SQL Server 2008 database for an order-processing application.
The following Transact-SQL statements have been run against the database: You need to
ensure that members of the reporting group can use only up to 35 percent of the CPU and
up to 45 percent of the available RAM.

Which four Transact-SQL statements should you use? (To answer, move the appropriate
SQL statements from the list of statements to the answer area and arrange them in the
correct order.)

You troubleshoot a SQL Server 2008 instance.
You suspect that an application uses an inefficient locking strategy and causes concurrency
problems.
You need to identify the following:
-The sessions that wait for a resource -The resource needed -The sessions that block the
resource
Which dynamic management view should you use?

You administer a SQL Server 2008 instance that contains a very large database named
FinanceDB.

You plan to create a maintenance plan that meets the following objectives for the FinanceDB
database:
-It executes the DBCC CHECKDB statement. -It rebuilds all the indexes. -It updates all index
statistics.
You need to ensure that the maintenance plan is executed in the minimum amount of time.
Which two actions should you perform? (Each correct answer presents part of the solution.
Choose two.)