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. 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 Auto Close option.
B) The Read Uncommitted isolation level should be implemented.
C) The database should be configured to use the Read Committed Snapshot option.
D) The Snapshot isolation level should be implemented.

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 administrator a SQL Server 2005 computer named SQL01. The company has configured the SQL Server service and the SQL Server Agent service to start automatically. There are users and applications that try to access information from SQL01. You are asked to configure SQL01 to audit all the user names and application names. SQL01 must perform auditing at all times, and the results must be kept for 10 years. What action should be performed?

A) A SQL Server Agent job should be created to run the SQL Trace stored procedure. The job should be configured to start when the SQL Server Agent service starts.
B) On the server properties, the C2 Audit Tracing setting should be diabled.
C) The Login auditing setting should be set to Both failed and successful logins.
D) The server authentication mode should be configured as SQL Server and Windows Authentication mode.

3. DRAG DROP
According to the company requirement, the backup schedule should be designed for the customer database.
Which action should be performed to achieve the goal? (Drag the proper backup type to the right location in the answer area.)

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

A) The data should be imported from each of the databases into a single database in an instance of SQL Server 2005.
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 separate instance of SQL Server 2005 should be installed for each of the databases on a single server.
D) A SQL Server database should be created for each of the databases in a single instance of SQL Server 2005.

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 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, the SQL Server Browser service should be started. The service should be configured to start automatically.
B) In order to make sure of this, you should make the dedicated administrative connection by using the Sqlcmd.exe tool.
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.

Solutions: