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. The new server named SQLCLUST is set with test versions of the Research, Engineering, and Parts databases. The data in these databases is merely utilized for testing and is not present. According to the company requirements, the new Research, Engineering, and Parts databases should be moved from the test stage to the production stage with the highest efficiency level. Before making the databases accessible to all users, which three actions should be performed? (Choose more than one)

A) One or more jobs should be scheduled to move all data from the mainframe computer databases to the new databases.
B) All indexes should be built, and the statistics should be updated on all tables.
C) Scripts should be run on the production server to build all tables, views, and stored procedures.
D) All data should be manually moved from the mainframe computer to the new databases by utilizing the SELECT INTO command.
E) The DELETE statement should be utilized to move all test data from the three databases.

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. There is a SQL Server 2005 computer in the company. A single user database named DB01 is contained in this computer. All company employees connect to DB1. According to the requirement of the company policy, the database should be fault-tolerant and that failovers require no administrative effort. Therefore, you are asked to provide fault tolerance for the database.
Your solution must guarantee that even if a complete hardware failure occurs on the SQL Server 2005 computer, client computers are able to connect to the database, including a failure of all disks. What action should you perform to achieve your goal?

A) Database mirroring to another SQL Server 2005 computer needs to be implemented.
B) A two-node server cluster that contains the database needs to be implemented.
C) Log shipping needs to be implemented to another SQL Server 2005 computer.
D) Transactional replication needs to be implemented to another SQL Server 2005 computer.

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. There are lots of complex SQL Server Integration Services (SSIS) packages for you. These SSIS packages move data among your servers. They have data imported and exported to and from trading partners of the company. Only after certain conditions are met that exist outside of SQL Server, can the SSIS packages run. A third-party tool to schedule the running of nightly scripts is scheduled to test for these conditions with the use of a third-party tool by your department. SSIS needs to be run from your scripts as seamlessly as possible. So what action should you perform?

A) At the time that conditions are met in the scripts, the SQL Server Integration Services (SSIS) packages should be run by making the scripts execute the DTExec utility.
B) You should make the scripts write a status code to a text file that the SQL Server Integration Services (SSIS) packages look for and act upon.
C) A trigger should be created. The SQL Server Integration Services (SSIS) packages should be run by this trigger.
D) At the time that conditions are met in the scripts, the SQL Server Integration Services (SSIS) packages should be run by making the scripts execute the DTUtil utility.

Solutions: