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. You receive report from users saying that some reports are performing poorly. You are sure that your tables have the appropriate indexes and that your queries should be performing optimally. After a SHOWPLAN statement is executed on the poorly performing reports, you find that incorrect indexes are being used in some cases. You want the SQL Server 2005 query optimizer to select the correct indexes automatically. So what action should be performed?

A) A maintenance plan should be created. A Check Database Integrity task should be included in this plan.
B) The indexes should be rebuilt and the server should be restarted.
C) A maintenance plan should be created. An Update Statistics task should be included in this plan.
D) All poorly performing queries should be rewritten so that they use query hints.

2. A most proper archiving strategy for the SalesData.Orders table in the Sales database should be identified. The archiving strategy should be implemented with the least amount of effect on performance. Which action should be performed?

A) First, a table named ArchivedData.Orders should be created. Second, the SalesData.Orders table and its indexes should be partitioned. At last, MERGE, SPLIT, and SWITCH functions should be utilized every three months to remove old order data into ArchivedData.Orders.
B) First, the Sales database should be backed up to tape every three months, and then a DELETE statement should be utilized to migrate archived data from the SalesData.Orders table.
C) First, a table named ArchivedData.Orders should be created. Second, an INSERT statement will be utilized to copy data from the SalesData.Orders table into ArchivedData.Orders every three months. At last a DELETE statement should be utilized to remove archived data from SalesData.Orders.
D) First, a database snapshot of the Sales database should be created every three months, and then a DELETE statement should be utilized to remove archived data from the SalesData.Orders table.

3. The DeliveryVans, AirlinePreferences, and SeasonalContractors databases should be removed into the data center. Which action should be performed?

A) A separate instance of SQL Server 2005 should be installed for each of the databases on a single server.
B) A default instance of SQL Server 2005 should be installed on three separate servers. And then, one server should be utilized for each of the databases.
C) A SQL Server database should be created for each of the databases in a single instance of SQL Server 2005.
D) The data should be imported from each of the databases into a single database in an instance of SQL Server 2005.

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 administrator of a SQL Server 2005 computer, and the computer is called SQL1. There are two databases on the SQL1, and the two databases are respectively named Production and DW. A new database named Staging is created. When the data are moving from the Production database into the DW database, the Staging database is utilized temporarily to store and manipulate data.
Since you are the database administrator, you are required to make sure the points listed below.
First, the tables should not be removed from the Staging database.
Second, any attempts to have tables migrated should be logged.
Third, your solution should not impact on other databases.
Which action should be performed to achieve the goal?

A) To achieve the goal, an event notification should be created to fire when the TRUNCATE TABLE Transact-SQL statement is submitted to the Staging database.
B) To achieve the goal, a DDL trigger should be created, and the ON ALL SERVER
parameter is utilized by the DDL trigger.
C) To achieve the goal, a DDL trigger should be created, and that the ON DATABASE parameter is utilized by DDL trigger for the Staging database.
D) To achieve the goal, a DML trigger on each table should be created to prevent deletion of data.

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 computer is named SQL1. And it is reported by the users report that SQL01 responses slowly. You find that the network of SQL01 is excessively utilized. You cannot utilize the graphical SQL Server administrative tools to connect to SQL01. It is doubted whether a Denial of Service (DoS) attack targets at SQL01.
SQL01 should be connected to examine the present activities. Which action should be performed first to achieve the goal?

A) To achieve the goal, the dedicated administrative connection should be utilized to connect to SQL1.
B) To achieve the goal, SQL1 should be restarted.
C) To achieve the goal, the SQL Server service should be stopped and restarted on SQL1.
D) To achieve the goal, SQL Server Profiler should be utilized to connect to SQL1.

Solutions: