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 administer a SQL Server 2005 computer named SQL01. Two user databases are contained in SQL01. The publisher database is also contained in SQL01 for a transactional replication publication. A full backup is made of all databases nightly. For both user databases, transaction log backups are made twice each day. The company has configured SQL01 to use SQL Server and Windows authentication. Client applications use SQL Server login accounts to connect. Since a complete failure occurs on the disk subsystem on SQL01, you replace the failed hardware; reinstall Microsoft Windows Server 2003 and SQL Server 2005. The company requires that SQL01 is operating normally as quickly as possible and with a minimum loss of data. Hence you have to make sure of this. Your must make client applications be able to access the user databases as soon as those databases are restored. What action should you perform first?

• A. First only the publisher database should be restored.
• B. First the transactional replication publication should be re-created.
• C. First all of the system databases should be restored.
• D. First only the two user databases should be restored.

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 and RetailPrice columns, a clustered index should be created.
• C. You should use the REORGANIZE option to change the existing nonclustered index.
• D. On the VideoTitle field, drop the clustered index. On the VideoTitle field, a nonclustered index should be created.
• E. On the VideoTitle, Barcode, VideoID, WholesalePrice, and Genre fields, a nonclustered index should be created.

DRAG DROP
Drag Drop question

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, automatically generated uniqueifier value should be identified.
• C. To accomplish the task, fill factor should be identified.
• D. To accomplish the task, current number of rows should be identified.

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. You are in charge of a SQL Server 2005 database named DB1. You are given a SQL Server Profiler sample workload with queries to DB1 on a typical day. Now the company assigns a task to you. You are asked to evaluate the effectiveness of current indexing and the identify changes that would be appropriate. What action should be performed first?

• A. A query should be created with the five stored procedures that are used the most frequently. Then click the Analyze Query in Database Engine Tuning Advisor button.
• B. The Database Engine Tuning Advisor (DTA) should be used with the sample workload.
• C. The sample workload should be run by using SQL Server Profiler.
• D. The Index Tuning Wizard should be used with the sample workload.