Online Access Free 070-450 Exam Questions

You are a professional level SQL Sever 2008 Database Administrator.
A new database named Engineering is deployed. Large documents are managed by the Engineering
database,
and the documents will be revised occasionally. A table structure should be designed to permit fast read
access.
The storage space requirements should be cut to the least.
What will you do to achieve the goal? (Choose more than one.)

• A.To achieve the goal, varbinary(MAX) data type should be utilized with FILESTREAM storage.
• B.To achieve the goal, row-level compression should be utilized on the document table.
• C.To achieve the goal, row-level compression should be enabled on all columns that the vardecimal() data type is utilized.
• D.To achieve the goal, NTFS file system compression should be utilized on the volume.

You administer a SQL Server 2008 instance that hosts a database.
The backup strategy used by the database is as shown in the following table: You run a batch process on Saturday that lasts from 21:00 hr to 23:00 hr. You discover that the batch process is invalidated because a user has modified some data at 21:05 hr on the same day.

You need to restore the database to its state at the start of the batch process in the least possible time.
What should you do?

• A.Restore the full database backup that was performed on Thursday Restore all transaction logs from the time of the full backup on Thursday and stop at 21:00 hr on Saturday.
• B.Restore the full database backup that was performed on Saturday.
• C.Restore the full database backup that was performed on Thursday. Restore the differential backup that was performed on Friday. Restore all transaction logs from the time of the differential backup on Friday and stop at 21:00 hr on Saturday.
• D.Restore the full database backup that was performed on Saturday. Restore all transaction logs from the time of the full backup on Saturday and stop at 21:00 hr on Saturday.

You administer a SQL Server 2008 infrastructure.
The instance contains a database required for the day-to-day business of your company. Users experience slow response time when they execute reports.
You plan to design a performance monitoring strategy that captures and stores the following data:
Executed Transact-SQL statements and query activity.
Blocking and deadlock information.
Counters for disk, CPU, and memory.
You need to implement the monitoring process by using the minimum amount of administrative effort.
What should you do?

• A.Use the data collector
• B.Use the System Monitor counter log trace.
• C.Use the client-side profiler trace.
• D.Use the dynamic management views.

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.Perform the following tasks: Set Select on Customer.
• B.Set only Take Ownership on Customer and Order.
• C.Perform the following tasks: Set Execute on SetCustomerStatus.
• D.Set only Execute on SetCustomerStatus.

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 Windows System Resource Manager should be configured.
• B.To accomplish the goal, software non-uniform memory access (soft-NUMA) should be configured.
• C.To accomplish the goal, the Resource Governor should be configured.
• D.To accomplish the goal, an increase should be configured in the maximum degree of parallelism option.