Question 1.
You work as a database administrator at ABC.com. You are in the process of preparing the deployment of a new database that will have 45 gigabytes storage space for the transaction log file, and 280 gigabytes storage space for the database data file. There are approximately six 120 GB disk drives available for the database in the storage array. ABC.com contains a RAID controller that supports RAID levels 0, 1, 5 and 10. The disks are on the RAID controller. You have received an instruction from the CIO to make sure that the transaction log’s write performance runs at optimum. The CIO has also instructed you to make sure that in the event of a drive failure, the database and transaction log files are protected. To achieve this goal, you decide to configure a storage solution.

Which of the following actions should you take?

A. You should consider using a RAID 1 volume as well as a RAID 5 volume in your storage 
    configuration. 
B. You should consider using a RAID 1 volume as well as a RAID 10 volume in your storage  
    configuration.
C. You should consider using a RAID 3 volume as well as a RAID 5 volume in your storage  
    configuration.
D. You should consider using a RAID 1 volume as well as a RAID 3 volume in your storage  
    configuration.

Answer: A

Explanation:

Question 2.
You work as a database administrator at ABC.com. ABC.com has a database server named ABCDB04 with a SQL Server 2008 instance that includes an extensive mission-critical database that is constantly being used ABC-DB04 has a quad-core motherboard with four CPUs. When it is reported that ABC-DB04 often encounters CPU pressure, you receive an instruction from management to make sure that the accessible CPU cycles are not exhausted by online index rebuilds.

Which of the following actions should you take?

A. You should make use of the affinity I/O mask option.
B. You should make use of the optimize for ad hoc workloads option.
C. You should make use of the affinity mask option.
D. You should make use of the max degree of parallelism option.

Answer: D

Explanation:

Question 3.
You work as a database administrator at ABC.com. ABC.com has a database server named ABCDB01 with a SQL Server 2008 instance. During routine monitoring on ABC-DB01, you discover that the amount of CXPACKET waits experienced by the instance is low, while the amount of lazy writer waits is abundant. You have been instructed to enhance the operation of the instance to ensure productivity. 

Which of the following actions should you take?

A. You should consider setting up the Windows System Monitoring tool to better the performance.
B. You should consider setting up the Asynchronous database mirroring to better the  
    performance.
C. You should consider using the SQLAGENT.OUT log to better the performance.
D. You should consider setting up the software non-uniform memory access (soft-NUMA) to 
    better the performance.

Answer: D

Explanation:

Question 4.
You work as a database administrator at ABC.com. ABC.com has a database server named ABCDB01. ABC-DB01 is configured with 4 quad-core processors, 80 gigabytes of RAM, and multiple independent raid volumes. You are in the process of using a transactional database on the instance. It is anticipated that the transactional database will have a significant amount of INSERT, UPDATE, and DELETE activities, which incorporates the creation of new tables. You receive an instruction from management to minimize the contention in the storage allocation structures so that database performance is optimized, and the disk bandwidth maximized.

Which of the following actions should you take?

A. You should consider enabling Server Auditing.
B. You should consider using multiple data files for the database.
C. You should consider using row-level compression.
D. You should consider using the checksum page verify option.

Answer: B

Explanation:

Question 5.
You work as a database administrator at ABC.com. ABC.com has informed you that a new database, named ABCData, has to be installed on a SQL Server 2008 instance. ABCData is made up of several schemas, of which one will host a significant amount of read-only reference information. Information is regularly inserted and updated on ABCData. You have received instructions from the management to configure a physical database structure that enhances the backup operation.

Which of the following actions should you take?

A. This can be accomplished by using multiple filegroups and a single log file to set up the  
    database.
B. This can be accomplished by using caching on the multiple data files.
C. This can be accomplished by using multiple downstream servers to create the database.
D. This can be accomplished by using the Database Engine Tuning Advisor tool to create the  
    database.

Answer: A

Explanation:

Question 6.
You are the newly appointed database administrator at ABC.com. Your job function includes administering a SQL Server 2008 instance. You have recently implemented a database named ABCResearch that is employed to administer large documents. From time to time, modifications will be made to these documents. You receive instructions from the CIO to implement a table structure that will enable fast read access, and to reduce the capacity prerequisites for storage.

Which combination of the following actions should you take? (Choose all that apply.)

A. This can be accomplished by utilizing the resource-allocation policy for process-based  
    management.
B. This can be accomplished by utilizing the varbinary(MAX) data type with FILESTREAM 
    storage.
C. This can be accomplished by utilizing the NTFS file system compression on the volume.
D. This can be accomplished by utilizing caching on the shared folder.

Answer: B, C

Explanation:

Question 7.
You work as a database administrator at ABC.com. ABC.com currently makes use of Microsoft SQL Server 2008 for hosting databases. You are responsible for administering a database server named ABC-DB01, which has an instance that hosts a database named ABC_SALES. During the course of the day you receive instruction from ABC.com to administer the newly installed instance of SQL Server 2008 Reporting Services (SSRS). ABC.com wants you to plan and implement an authentication solution for SSRS reports, which makes use of SQL Server data connections. ABC.com wants you to make sure that the new instance communicates to all SSRS reports via the Kerberos authentication protocol.

Which of the following actions should you take?

A. You should consider having the named pipes protocol disabled.
B. You should consider having a service principal name (SPN) registered in the Active Directory  
    directory service for the new SSRS instance.
C. You should consider having the default Web site on the new instance configured to use the  
    Integrated Security type.
D. You should consider having the VIA protocol enabled.

Answer: B

Explanation:

Question 8.
You work as a database administrator at ABC.com. You are in the process of implementing security for a database application that will run on an SQL Server 2008 instance. The database 5
has a table that was created by running the following T-SQL code:
CREATE TABLE ABC_Staff
(
StaffID INT NOT NULL IDENTITY(1,1),
JobLevel INT NOT NULL,
Salary MONEY NOT NULL,
Commission VARBINARY(MAX) FILESTREAM
)
You have received instructions from the CIO to make sure that Commission column cannot be accessed by unauthorized users. You want to make sure that this column is a secure as possible.

What should you do?

A. This can be accomplished by ensuring that the Job Activity Monitor tool is utilized on all  
    columns in the database.
B. This can be accomplished by ensuring that the NTFS file system security is utilized and 
    Access of the database files is limited to the SQL Server 2008 Service account.
C. This can be accomplished by ensuring that the appropriate trace flags are utilized for the  
    database.
D. This can be accomplished by ensuring that the SQL Profiler trace is utilized.

Answer: B

Explanation:

Question 9.
You are employed as the database administrator at ABC.com. Your job function encompasses administering a SQL Server 2008 instance that hosts a database named ABC_SALES. ABC_SALES is used by a Web based application that manages an average of 600,000 transactions per hour. ABC_SALES contains a column, named ABCDATA, which stores classified information that is only used by the Web based application. You receive instructions from the CIO to make sure that the classified information is stored as securely as possible, and that the consumption of memory space and processor time is kept to a minimum.

Which of the following actions should you take?

A. You should consider making use of Database snapshot.
B. You should consider making use of the Internet Information Services.
C. You should consider making use of symmetric key encryption.
D. You should consider making use of Key Management Service (KMS).

Answer: C

Explanation:

Question 10.
You work as a database administrator at ABC.com. ABC.com currently makes use of Microsoft SQL Server 2008 for hosting databases. You are responsible for administering a database server named ABC-DB01 with an instance that hosts a database named ABC_SALES. During the course of the day you receive instruction from ABC.com to install a new database application, which will make use of SQL CLR integration to develop stored procedures, to the instance. ABC.com wants you to make sure that the EXTERNAL_ACCESS code access security settings are enabled for the application. You have also been informed that the application must be fully functional subsequent to the deployment.

Which of the following actions should you take?

A. You should consider having the peverify.exe utility used to confirm if the code meets the  
    Typesafety requirements.
B. You should consider registering the VPN solution for the installation.
C. You should consider using the File Server Resource Manager (FSRM) with static fields.
D. You should consider using the Windows System Resource Manager (WSRM) with read write 
    static fields.

Answer: A

Explanation:

	Web www.certsbraindumps.com