Online Access Free 070-450 Practice Test

You deploy a new database application to a SQL Server 2008 instance on a server that has 4 processor
cores and 64 GB of RAM. The application uses partitioned tables.
During testing, deadlocks occasionally occur on queries that are attempting to read data.
You find out that the deadlocks are related to the table partitions.
You need to ensure that the application executes without deadlocks for the read queries.
You also need to ensure that dirty reads do not occur.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.Move each partitioned table to its own filegroup.
• B.Add additional database files to each partitioned table.
• C.Move the clustered indexes for the partitioned tables to a separate filegroup.
• D.Disable partition-level lock escalation.

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 1 volume and a RAID 5 volume should be utilized.
• B.A single RAID 10 volume should be utilized.
• C.A RAID 0 volume and a RAID 5 volume should be utilized.
• D.A single RAID 5 volume should be utilized.

You administer a SQL Server 2008 infrastructure.
You plan to design a maintenance strategy for a mission-critical database that includes a large table named Orders. The design plan includes index maintenance operations.
You must design the strategy after considering the following facts:
The Orders table in the database is constantly accessed.
New rows are frequently added to the Orders table.
The average fragmentation for the clustered index of the Orders table is less than 2 percent.
The Orders table includes a column of the xml data type.
You need to implement the strategy so that the performance of the queries on the table is optimized.
What should you do?

• A.Drop the clustered index of the Orders table.
• B.Reorganize the clustered index of the Orders table by decreasing the fill factor.
• C.Rebuild the clustered index of the Orders table offline once a month.
• D.Exclude the clustered index of the Orders table from scheduled reorganizing or rebuilding operations.

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.Peer-to-Peer replication
• B.Log shipping to servers at two of the sites to provide read-only copies of data
• C.Failover clustering
• D.Asynchronous database mirroring without a witness server

You are a professional level SQL Sever 2008 Database Administrator in an international corporation named Wiikigo.
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 corporate backup and recovery strategy that has to be validated is designed.
You are required to make sure that any single database can be restored successfully from a severe failure, and during the restoration, a backup data center should not be required in a different location.
Which three actions should be contained by your solution? (Choose more than one)

• A.All backup media offsite should be stored.
• B.SQL login accounts and credentials should be scripted.
• C.The administrative processes and application access requirements should be documented.
• D.One list of all Windows logins and passwords should be sustained.
• E.All SQL Server instances should be installed on a failover cluster.