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

1. According to the company requirement, you have to design the data archiving solution for the CRM database. You must make sure that your solution implements and satisfies the research and auditing requirements by using as little administrative effort as possible. Of the following actions, which should be specified to occur at the end of each month?

A) You should print a copy of the older CRM data by using SQL Server Reporting Services (SSRS), and keep each report for five years. After printing each report, delete the older data should be deleted from the CRM database after each report is printed.
B) In order to guarantee that the older data and the newer data are kept on different table partitions, the CRM database needs to be reorganized.
C) The CRM data should be exported to a Microsoft Excel spreadsheet and each spreadsheet should be kept for five years. The older data should be deleted from the CRM database after each spreadsheet is created.
D) A full backup of the CRM database should be created and each backup should be kept for five years. After creating each backup, delete the older data should be deleted from the CRM database after each backup is created.

2. 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 regular stored procedure that calls xp_cmdshell 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 EXTERNAL_ACCESS permission set should be used.
E) In order to accomplish your task, a CLR stored procedure with the SAFE permission set should be used.

3. According to the company requirements, the list of expected services for SQLCLUST should be reviews and any nonessential services should be disabled. From the following two services, which one should be disabled? (Choose more than one)

A) Cluster service should be disabled.
B) SQL Server Agent service should be disabled.
C) Messenger service should be disabled.
D) Server service should be disabled.
E) World Wide Web Publishing service should be disabled.

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. Three instances of SQL Server are run by SQL01. Your company's disaster recovery plan of the company contains the ability to connect to the dedicated administrative connection on each instance of SQL Server. While testing this ability, you find that you are able to connect to the dedicated administrative connection on SQL1s default instance. But you fail to connect to the dedicated administrative connection on the two other instances on SQL01. You are sure that the instances are running and can be accessed by client applications. Besides, you also confirm that the dedicated administrative connection is enabled for all instances. You are sure that no other administrators are trying to connect to any dedicated administrative connections on SQL01.
You must make sure that you can connect to the dedicated administrative connection on all three instances. What action should you perform to make sure of this?

A) In order to make sure of this, you should make the dedicated administrative connection by using the Sqlcmd.exe tool.
B) In order to make sure of this, the SQL Server Browser service should be started. The service should be configured to start automatically.
C) In order to make sure of this, the default instance should be reconfigured to use an administrative port number while not 1434.
D) In order to make sure of this, the non-default instances of SQL Server should be stopped and then restarted.

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 a database administrator for your company. A user database named Products is included by your SQL Server 2005 computer. SQL Server Integration Services (SSIS) packages are utilized to export data from the Products database to a text file.
You deliver the text file to your company's trading partners by utilizing FTP. You are required to make sure that your SSIS packages can be recovered from the occurrence of a disaster. Which action should be performed to finish the task?

A) To finish the task, your server should be set to utilize the full recovery model.
B) To finish the task, the Products database should be backed up.
C) To finish the task, the msdb database should be backed up.
D) To finish the task, your server should be set to utilize the bulk-logged recovery model.

Solutions: