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

1. According to the company requirement, you are asked to design a stored procedure to import the registry information from AppServer1. The company requires that you do not execute unmanaged code. So what action should you perform to accomplish your task?

A) In order to accomplish your task, a CLR stored procedure with the EXTERNAL_ACCESS permission set should be used.
B) In order to accomplish your task, an extended stored procedure should be used.
C) In order to accomplish your task, a CLR stored procedure with the UNSAFE permission set should be used.
D) In order to accomplish your task, a CLR stored procedure with the SAFE permission set should be used.
E) In order to accomplish your task, a regular stored procedure that calls xp_cmdshell should be used.

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 receive report from users saying that a SQL Server 2005 application makes no response for several seconds at a time during the day. This problem happens at random throughout the day and many users are affected at once. You check at the time that the problem happens. You find that both CPU utilization and disk utilization on the SQL Server 2005 computer are low, but that the network is operating normally. Therefore, you have to examine additional information that is related to the problem. What action should you perform?

A) You have to check the total number of index and table scans on the SQL Server 2005 computer.
B) You have to check the sys.dm_tran_locks dynamic management view (DMV).
C) You have to check the output of the DBCC SQLPERF(IOSTATS) command.
D) You have to check the output of the DBCC SHOWCONTIG command.
E) You have to check the total number of lock requests on the SQL Server 2005 computer.

3. In order to satisfy the business requirements of the company, the recovery plan for the CRM database needs to be modified. The company assigns this task to you. What action should you perform to accomplish your task?

A) In order to accomplish your task, every three hours, differential backups should be added.
B) In order to accomplish your task, the log backups should be changed to run twice per hour.
C) In order to accomplish your task, the log backups should be changed to run once every two hours.
D) In order to accomplish your task, the full backups should be changed to run every night.
E) In order to accomplish your task, use full backups to take the place of the nightly differential backups.

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. Developers need access to create, alter, and drop tables in your SQL Server 2005 database. There is a table named Valid_Production_Tables in the database. This table maintains a list of production tables. There are users who attempt to use commands to drop or alter current production tables. You must be able to automatically roll back any commands. When you try to achieve this, you must reduce the processor resources being used to the least. So what action should you perform?

A) You should execute the FOR DROP_TABLE, ALTER_TABLE command by using a DDL trigger.
B) You should execute the FOR ALTER_TABLE, DROP_TABLE command by using Event Notification.
C) You should execute the FOR DDL_TABLE_EVENTS command by using Event Notification.
D) You should execute the FOR UPDATE, DELETE command on the Valid_Production_Tables table by using a DML trigger.

5. 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, replication should be configured.
B) To finish the task, a full backup should be created.
C) To finish the task, a database snapshot should be created.
D) To finish the task, a differential backup should be created.

Solutions: