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 a database administrator for your company. There is a Data Transformation Services (DTS) package, and the DTS package is stored in SQL Server 2000. And you plans to have the SQL Server 2000 migrated into SQL Server 2005. The DTS package dynamically configures precedence constraints by utilizing complex Microsoft Visual Basic Scripting Edition (VBScript). The SQL Server 2005 Package Migration Wizard is run by you, but the wizard cannot convert the package from SQL Server 2000 to SQL Server 2005.
You are required to make sure that the DTS package runs in SQL Server 2005. And the package should be run in the shortest time. Which action should be utilized?

A) The SQL Server 2000 Data Transformation Services (DTS) package should be wrapped in an Execute DTS 2000 Package task.
B) The SQL Server 2000 Data Transformation Services (DTS) task should be configured to not utilize precedence constraints. Then, the SQL Server 2005 Package Migration Wizard should be run again.
C) The SQL Server 2000 Data Transformation Services (DTS) package should be manually recreated as a SQL Server Integration Services (SSIS) package.
D) The SQL Server 2005 Package Migration Wizard should be run. And when the upgrade fails, a Transfer SQL Server Objects task should be run.

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. A new stored procedure is created by you. You find an error when running the stored procedure. The company asks you to collect and process any error information that is generated while running the procedure. Error logging must use the least possible resources while providing the needed functionality. You have to find out what caused the error. The appropriate error functions need to be included in the new stored procedure. What action should you perform?

A) The @@error function should be used.
B) A GOTO statement should be used.
C) The XACT_ABORT command should be set to off.
D) TRY...CATCH blocks should be used.

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 the database administrator for your company. You are in charge of 20 SQL Server 2005 computers. Other administrators are responsible for monitoring these computers. These administrators need to guarantee that query response times meet company service level agreements (SLAs). Since you are the technical support, you are asked to provide these administrators with a consistent way of monitoring query response times on the servers. What action should you perform to achieve this goal?

A) The administrators should be asked to monitor cache hit ratio statistics by using System Monitor.
B) You should create an XML document that contains the event and column names of the query response times that must be captured in SQL Server Profiler. The XML file should be distributed to the database administrators.
C) SQL Server Profiler templates should be created. Query start times and end times should be include in the templates. Distribute these templates to the database administrators.
D) You should write a query. This query queries the sys.dm_db_partition_stats dynamic management view (DMV). This query should be distributed to the administrators.

4. 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) SQL Server Profiler should be run with the TSQL_Duration template.
B) SQL Server Profiler should be run with the Lock:Deadlock event class.
C) The Database Engine Tuning Advisor (DTA) f should be run or at least one hour.
D) The Database Engine Tuning Advisor (DTA) should be run with the default options.

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 database administrator for your company. You receive nightly data feeds from your consumer electronics suppliers that show their inventory levels. The data value format that you receive from each supplier is not necessarily consistent. You have to make sure that the data that you import into your database is accurate and is not duplicated. What action should be performed?

A) On the table that contains the imported data, CHECK constraints should be configured.
B) A query that includes the HAVING clause should be written to remove duplicate values before the data is imported into your database.
C) A query that uses the CHARINDEX function should be written to use the SQL Server Import Wizard to import the data. It should be configured to ignore duplicate values.
D) The data should be imported into your database by using a Fuzzy Lookup transform in SQL Server Integration Services (SSIS).

Solutions: