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

Question 1

You administer a SQL Server 2008 Enterprise Edition instance that hosts a large database.
The database uses the simple-recovery model.
The database contains the following three additional filegroups:
FileGroupA that is read/write
FileGroupB that is read-only
FileGroupC that is read-only
FilegroupB contains less critical data than FilegroupC.
You discover that the PRIMARY filegroup and FileGroupB have failed.
You need to recover the database in the minimum possible time. You also need to ensure that
data is available as soon as possible.
What should you do?

A. Perform a full database restore.

B. Perform a partial restore of the PRIMARY filegroup and FileGroupA from a partial backup. Perform an online recovery of FileGroupC, and then perform an online restore of FileGroupB.

C. Perform a partial restore of the PRIMARY filegroup and FileGroupA from a partial backup. Perform an online recovery of FileGroupB, and then perform an online restore of FileGroup

D. Perform a partial restore of the PRIMARY filegroup and FileGroupB from a partial backup.

Correct Answer: B

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 2

You administer a SQL Server 2008 infrastructure.
Several production databases use Transparent database encryption.
You perform full database backups of the production databases as part of your backup strategy.
You need to ensure that the database backups can be restored on another SQL Server 2008 instance if the original host server fails.
What should you do?

A. Ensure that the instances use the same service account.

B. Back up the database master key.

C. Back up the resource database.

D. Back up the server master key.

Correct Answer: B

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 3

You administer a SQL Server 2008 infrastructure.
Your company requires capacity planning information.
You need to design a long-term infrastructure monitoring strategy.
Which two tasks should you include in your design? (Each correct answer presents part of the solution.
Choose two.)

A. Create a maintenance plan that rebuilds indexes every week.

B. Clear the system log and the application log every hour.

C. Backup all databases every day.

D. Baseline the system before you deploy production databases.

E. Review system monitor counters on a regular basis.

Correct Answer: D,E

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 4

You are a professional level SQL Sever 2008 Database Administrator.
New SQL Server 2008 Integration Services (SSIS) packages will be deployed, and the packages are created by the SQL Server Import and Export Wizard. The packages will utilize stored user names and passwords to connect to external data sources.
Since you are the technical support of your company, you are required to confirm that unauthorized users can not get access to the user names and passwords.
Which action will you perform?

A. The SQL Server 2008 server should be utilized to store the packages, and administrate the security in the SQL Server 2008 server.

B. No sensitive data should be saved together with the packages, and then the packages should be saved in the SQL Server 2008 server.

C. No other data should be saved together with the packages, and then files should be saved to the most secure directory.

D. A common password should be set to make a highest level of security for all information in the package when each package is saved.

Correct Answer: A

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 5

You are a professional level SQL Sever 2008 Database Administrator.
You are experienced in managing databases in an enterprise-level organization,optimizing and sustaining
the
database life cycle. In the company, your job is to implement solutions on security, troubleshooting,
deployment
and optimization. A SQL Server 2008 infrastructure is managed by you.
A database is utilized by the instance, and the database is utilized by a Web-based application. 15,000
transactions
are processed by the application every minute. A column is contained by a table in the database, and the
column is
utilized only by the application. Sensitive data is stored in this column. The sensitive data should be stored
with the
highest security level. In addition, the least amount of memory space and processor time should be
utilized.
From the following four encryption types, which one should you utilize?

A. Asymmetric key encryption should be utilized.

B. Certificate-based encryption should be utilized.

C. Transparent data encryption should be utilized.

D. Symmetric key encryption should be utilized.

Correct Answer: D

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 6

You are a professional level SQL Sever 2008 Database Administrator.
All data changes are implemented through stored procedures, and only the INSERT, UPDATE, or DELETE statements are utilized by the procedures. A backup strategy should be implemented.
The business requirements listed below should be satisfied:
Point-in-time recovery for failure is supported by the backup strategy at any time of day.
The least amount of disk space should be utilized by the transaction log.
Which action should you perform to finish the task?

A. To finish the task, the simple-recovery model along with differential backups should be utilized.

B. To finish the task, the full-recovery model along with differential backups should be utilized.

C. To finish the task, hourly database snapshots should be utilized.

D. To finish the task, the full-recovery model along with transaction log backups should be utilized.

Correct Answer: D

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 7

You administer a SQL Server 2008 infrastructure.
You plan to deploy a new SQL Server 2008 multi-node failover cluster. The failover cluster uses a storage
area network (SAN)
that will use redundant array of independent disks (RAID) level 5.
You need to format the logical unit numbers (LUNs) for optimal performance of the database data files.
Which of the following is the best allocation unit size you should use?

A. 32-KB

B. 64-KB

C. 156-KB

D. 8-KB

Correct Answer: B

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 8

You are planning to upgrade a database application that uses merge replication.
The table currently has a column type of UNIQUEIDENTIFIER and has a DEFAULT constratin that uses
the NEWID() function.
A new version of the application requires that the FILESTREAM datatype be added to a table in the
database.
The data type will be used to store binary files. Some of the files will be larger than 2 GB in size.
While testing the upgrade, you discover that replication fails on the articles that contain the FILESTREAM
data.
You find out that the failure occurs when a file object is larger than 2 GB.
You need to ensure that merge replication will continue to function after the upgrade.
You also need to ensure that replication occurs without errors and has the best performance.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Change the DEFAULT constraint to use the NEWSEQUENTIALID() function.

B. Drop and recreate the table that will use the FILESTREAM data type.

C. Place the table that will contain the FILESTREAM data type on a separate filegroup.

D. Use the sp_changemergearticle stored procedure and set the @stream_blob_columns option to true for the table that will use the FILESTREAM data type.

Correct Answer: D

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Microsoft PRO:MS SQL Serv 08,Design,Optimize, and Maintain DB Admin Solu - 70-450 Exam Practice Test

Latest Update

Useful Links

Contact Us