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.
A new database should be deployed, and the capacity requirements listed below should be satisfied.
There should be 275 GB for the database data file, while there should be 50 GB for the transaction log file. There are six 100-GB disk drives which are accessible for the database in the storage array. The six disks are attached to a redundant array of independent disks (RAID) controller, and the controller supports
RAID levels 0, 1, 5, and 10.
Since you are the technical support, you are required to maximize the write performance of the transaction
log. In addition, you should protect the database and transaction log files,
when a drive fails.
The storage system should be designed.
From the following four storage configuration, which one should you utilize?

A) A RAID 0 volume and a RAID 5 volume should be utilized.
B) A single RAID 5 volume should be utilized.
C) A RAID 1 volume and a RAID 5 volume should be utilized.
D) A single RAID 10 volume should be utilized.

2. 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, software non-uniform memory access (soft-NUMA) should be configured.
D) To accomplish the goal, the Windows System Resource Manager should be configured.

3. You are a professional level SQL Sever 2008 Database Administrator.
A new database application is hosted by the instance. The data security strategy should be designed for the application. The data should be duplicated to another instance of SQL Server 2008 by utilizing the Snapshot duplication.
Since you are the technical support of the company, you should make sure that all stored data should be
encrypted,
and the minimum amount of administrative effort should be utilized.
Which action will you perform?

A) Transparent data encryption should be enabled for the Publisher, Distribution, and Subscriber databases
B) Transparent data encryption for the MSDB database should be enabled on the two instances.
C) Certificate-based encryption should be enabled for the Publisher, Distribution, and Subscriber databases.
D) Encrypted connections should be enabled between the instances.

4. You are a professional level SQL Sever 2008 Database Administrator.
Your company wants to enforce naming standards for the database objects.
A solution should be designed by you to enforce naming standards,
and the least amount of administrative and programming efforts should be utilized.
Which action should you perform?

A) DDL triggers should be created for one of the instances, and then Microsoft SQL Server Management Studio (SSMS) should be utilized to script the definition of the DDL triggers. At last, the script should be run on all instances by utilizing the registered server??s node from SSMS.
B) Event notifications should be utilized to handle the DDL events.
C) Policy-Based Management policies should be created, and then the policies should be distributed by utilizing a configuration server.
D) DDL triggers should be created for one of the instances, and then SQL Server Management Objects (SMO) should be utilized to script the definition of the DDL triggers. At last, the script should be run by utilizing Microsoft Windows PowerShell on all instances.

5. Your SQL Server 2008 database contains two tables named Customer and Order inside the Sales schema.
The Sales schema is defined as follows:
It has a stored procedure named SetCustomerStatus that reads from Order and modifies Customer.
It is owned by a user named SalesUser.
Another user named MarketingUser wants to be able to call SetCustomerStatus and change the status of Customer.
You need to assign the least permissions necessary to MarketingUser.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A) Set only Execute on SetCustomerStatus.
B) Perform the following tasks: Set Execute on SetCustomerStatus.
C) Perform the following tasks: Set Select on Customer.
D) Set only Take Ownership on Customer and Order.

Solutions: