Microsoft UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005 Sample Questions:

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

2. 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 find a problem that there are three reports which are running slowly. And through detailed analysis, you conclude that no error messages are generated from these reports. Not only the indexing strategy but also the status of the current indexes is tested by you. You confirm that the slow performance of the financial reports is not caused by it. Since you are the technical support, you are required to find out which statement is causing the slow performance. Which action should be performed first?

A) The Database Engine Tuning Advisor (DTA) should be run with the default options.
B) The Database Engine Tuning Advisor (DTA) f should be run or at least one hour.
C) SQL Server Profiler should be run with the TSQL_Duration template.
D) SQL Server Profiler should be run with the Lock:Deadlock event class.

3. 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 a database administrator for your company.
A vendor company's application is utilized, and the application is based on a proprietary database. Data is queried from the application database and the data is imported into a SQL Server 2005 database. A new OLE DB driver is offered by the application vendor, and the new OLE DB driver will be utilized when the application database is queried. According the company requirements, the connections between the SQL Server database and the application database is prohibited, except when this data is queried. You should think out a method to have data queried from the application database.
Which action should be performed to achieve the goal?

A) To achieve the goal, a Linked Server connection should be configured to the application database.
B) To achieve the goal, a query should be written, and an OPENDATASOURCE command is utilized by the query.
C) To achieve the goal, a query should be written, and an OPENQUERY command is utilized by the query.
D) To achieve the goal, a Remote Server connection to the application database should be configured.

4. According to the company requirements, a data archival strategy should be designed for order data. You can see the placements of data and partitions for the Sales.Orders table at the end of July from display below. From the following filegroups, which one should partition 2 of the Sales.OrderArchive table locate on?

A) Partition 2 of the Sales.OrderArchive table should locate on FG2
B) Partition 2 of the Sales.OrderArchive table should locate on FG4
C) Partition 2 of the Sales.OrderArchive table should locate on FG3
D) Partition 2 of the Sales.OrderArchive table should locate on FG1

5. 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 SQL Server 2005 computer is named SQL1. The company set the SQL Server service and the SQL Server Agent service to start automatically. SQL01 should be set to audit all user names and application names that try to access information from SQL01. Auditing should be performed by SQL01 at all times. And the company requires keeping the results for 10 years. Which action should be performed?

A) The C2 Audit Tracing setting should be disabled on the server properties.
B) The Login auditing setting should be set to not only failed but also successful logins.
C) The server authentication mode should be set as SQL Server and Windows Authentication mode.
D) A SQL Server Agent job should be created to run the SQL Trace stored procedure. And then, the job should be set to start when the SQL Server Agent service starts.

Solutions: