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 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, a DML trigger on each table should be created to prevent deletion of data.
B) 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.
C) To achieve the goal, a DDL trigger should be created, and the ON ALL SERVER
parameter is utilized by the DDL trigger.
D) 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.

2. According to the company business and technical requirements,a management plan should be created for the goldengenius_biz database. The action which should be taken is needed to be specified, before manually inserting data through a bulk load process. The availability and recoverability business requirements should be met, and the least amount of administrative effort should be utilized. From the following four actions, which one should be specified to finish the task?

A) To finish the task, a database snapshot should be created.
B) To finish the task, a differential backup should be created.
C) To finish the task, a full backup should be created.
D) To finish the task, replication should be configured.

3. You are adding functionality for the MarketingAnalysis database, and the functionality has the results of a query in an e-mail message sent to the marketing supervisors when new sales data has been entered. A method should be implemented to satify this requirement, and the security risks should be cut to the least. Which method should be utilized to achieve the goal?

A) To achieve the goal, Database Mail should be utilized.
B) To achieve the goal, SQL Mail should be utilized.
C) To achieve the goal, a command line program that is called by xp_cmdshell should be utilized.
D) To achieve the goal, a managed stored procedure should be utilized.

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 administer a SQL Server 2005 computer named SQL01. Backups of all user databases are performed on Sunday nights. Backups of all system databases are performed on Saturday nights. All backups are stored in an off-site facility. SQL01 shuts down unexpectedly because of a power failure. The SQL Server service fails to start when SQL01 is brought back online. The SQL Server error log is shown in the exhibit below. Since you are the technical support, you have to return SQL01 to working order in a minimum possible time. What action should you perform first?

A) All of the system databases should be restored.
B) Setup.exe should be run from a command line and a rebuild of the master database
should be specified.
C) All of the user databases should be restored from backup.
D) After the SQL Server 2005 installation CD is inserted, you should reinstall the default instance by running the setup utility.

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. In the company, a SQL Server 2005 database experiences frequent blocking. You are asked to minimize the impact that caused by the blocking. Therefore, the database needs to be modified. You must make sure that no dirty reads are allowed. So what action should you perform?

A) The database should be configured to use the Read Committed Snapshot option.
B) The Snapshot isolation level should be implemented.
C) The Read Uncommitted isolation level should be implemented.
D) The database should be configured to use the Auto Close option.

Solutions: