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

1. According to the company requirements, a solution should be assigned to enable the accounting managers to review accounting changes. Which action should be performed?

A) Snapshot replication should be set up to a new database named Accounting_Changes. And then, the snapshot should be scheduled to be sent at close of business every Friday.
B) A job should be created to create a database snapshot at close of business daily. And then, another job should be created to migrate snapshots that are longer than eight days.
C) A job should be created to create a database snapshot at close of business every Friday. And then, another job should be created to migrate snapshots that are longer than two weeks.
D) Transactional replication should be set up to a new database named Accounting_Changes. And then, a job should be created to migrate all rows that have an inserted date that is longer than eight days.

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) TRY...CATCH blocks should be used.
C) A GOTO statement should be used.
D) The XACT_ABORT command should be set to off.

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 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) A query that includes the HAVING clause should be written to remove duplicate values before the data is imported into your database.
B) The data should be imported into your database by using a Fuzzy Lookup transform in SQL Server Integration Services (SSIS).
C) On the table that contains the imported data, CHECK constraints should be configured.
D) 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.

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

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 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.

Solutions: