Online Access Free 70-447 Practice Test

You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions, monitoring the database server, and design deployment. In the company, you major job is the deployment, maintenance and support of Microsoft SQL Server 2005. You work as the database administrator for your company. A database named DB1 is used by the company. The SQL statements used to create the Sales table in DB1, an existing index, and the two most common queries executed against this table are shown in the exhibit. The existing indexes have just been rebuilt. The VideoID and VideoTitle fields are highly selective. The RetailPrice, WholesalePrice and Genre fields have low selectivity. Since you are the technical support, the company asks you to optimize the performance of both queries. The company also requires that you must reduce the size of the index to the least when trying to achieve this goal. What action should you perform to accomplish your task?

• A.On the Barcode and VideoID fields, a nonclustered index should be created. The WholesalePrice and Genre fields should be included.
• B.On the VideoTitle, Barcode, VideoID, WholesalePrice, and Genre fields, a nonclustered index should be created.
• C.On the VideoTitle and RetailPrice columns, a clustered index should be created.
• D.On the VideoTitle field, drop the clustered index. On the VideoTitle field, a nonclustered index should be created.
• E.You should use the REORGANIZE option to change the existing nonclustered index.

You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions, monitoring the database server, and design deployment. In the company, you major job is the deployment, maintenance and support of Microsoft SQL Server 2005.You work as the administrator of a SQL Server 2005 computer, and the computer is called SQL01. The users complain the slow response of all client applications which connect to SQL01. You find that the CPU and memory utilization of SQL01 is low. In addition, the disk activity is low. The cause of the performance problem should be identified. Which action should be performed?

• A.System Monitor should be utilized to have SQL1s cache hit ratio examined.
• B.Task Manager should be utilized to have utilization of SQL1s network adapter examined.
• C.The sys.dm_db_partition_stats dynamic management view (DMV) should be examined.
• D.The sys.dm_db_index_physical_stats dynamic management function (DMF) should be examined.

In order to satisfy the business requirements of the company, the recovery plan for the CRM database needs to be modified. The company assigns this task to you. What action should you perform to accomplish your task?

• A.In order to accomplish your task, the full backups should be changed to run every night.
• B.In order to accomplish your task, use full backups to take the place of the nightly differential backups.
• C.In order to accomplish your task, the log backups should be changed to run once every two hours.
• D.In order to accomplish your task, the log backups should be changed to run twice per hour.
• E.In order to accomplish your task, every three hours, differential backups should be added.

According to the company requirement, you are asked to implement encryption for SQL01 on the basis of the technical requirements of the company. What action should you perform?

• A.On SQL01, the use of Kerberos authentication should be enforced.
• B.The acct1 database should be created, and encrypt all data should be encrypted.
• C.On SQL01, the Server (Request Security) IPSec policy should be implemented.
• D.For the SQL Server instance, encrypted connections should be enabled with the ForceEncryption option enabled.

According to the company requirements, a unique nonclustered index will be created on
the CustomerID, LastName, and FirstName columns of the CustomerData.Customers table in the CRM database. The variables should be identified to help estimate the space requirements for the index.
Estimated number of rowsClustered index key Number of columns in the indexFixed data sizeNumber of variable-length columnsAverage variable-length data size.
The most significant additional variable that will enhance the estimated index size should be identified. From the following four additional variables, which one should be identified to accomplish the task?

• A.To accomplish the task, null bitmap should be identified.
• B.To accomplish the task, fill factor should be identified.
• C.To accomplish the task, automatically generated uniqueifier value should be identified.
• D.To accomplish the task, current number of rows should be identified.