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.
An instance runs on a computer, and the computer has eight quad-core processors and 128-GB RAM. The instance is utilized by four different applications. Not only a low number of CXPACKET waits but also a large number of lazy writer waits are experienced by the instance. The performance of the instance should be optimized.
Which action will you perform to accomplish the goal?

A) To accomplish the goal, the Resource Governor should be configured.
B) To accomplish the goal, an increase should be configured in the maximum degree of parallelism option.
C) To accomplish the goal, the Windows System Resource Manager should be configured.
D) To accomplish the goal, software non-uniform memory access (soft-NUMA) should be configured.

2. You administer SQL Server 2008 instances at three sites.
All sites share a mission-critical database. The business requirements specify that users at each site must be able to perform the following tasks:
Access and modify data on all sites with minimal latency.
Minimize data loss in the event of a server failure.
You need to provide a high-availability solution that meets the business requirements.
Which solution should you implement?

A) Failover clustering
B) Asynchronous database mirroring without a witness server
C) Peer-to-Peer replication
D) Log shipping to servers at two of the sites to provide read-only copies of data

3. You administer a SQL Server 2008 instance.
Customers report server performance degradation because of a newly implemented process. You use Dynamic Management Views to verify that there are no long running queries.
You need to correlate the operating system performance data with the actual query execution trace by using minimum administrative effort.
What should you do?

A) Use the SQLdiag.exe utility.
B) Use SQL Server Profiler and System Monitor.
C) Use Data Collector.
D) Use SQL Server Profiler and the tracerpt.exe utility.

4. You administer a SQL Server 2008 infrastructure.
You plan to upgrade a Microsoft SQL Server 2005 instance to SQL Server 2008 by using an in-place
upgrade.
All user databases use the simple recovery model and have the PAGE_VERIFY option turned off.
You need to ensure that the database engine can identify I/O path errors and memory corruptions for the
data in tables.
Which two actions should you perform before the upgrade? (Each correct answer presents part of the
solution. Choose two.)

A) Set the PAGE_VERIFY database option to CHECKSUM.
B) Execute the DBCC CHECKDB command by using the DATA_PURITY option.
C) Rebuild the clustered indexes and heaps.
D) Rebuild the nonclustered indexes.
E) Set the database recovery model to FULL.
F) Set the PAGE_VERIFY database option to TORN_PAGE_DETECTION.

5. You administer a SQL Server 2008 infrastructure . You design a corporate backup and recovery strategy that has to be validated.
You need to ensure the successful recovery of any single database from a catastrophic failure without requiring a backup data center in a different location.
Which three tasks should you include? (Each correct answer presents part of the solution. Choose three.)

A) Install all SQL Server instances on a failover cluster.
B) Store all backup media offsite.
C) Document the administrative processes and application access requirements.
D) Maintain one list of all Windows logins and passwords.
E) Script SQL login accounts and credentials.

Solutions: