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. An e-mail message should be sent to the company database auditing department, when the SQL Server service and the SQL Server Agent service are started, stopped, or paused. In addition, an e-mail message should be sent to them, when the users utilize the EXECUTE AS option with stored procedures and other commands.
Since you are the administrator, you are required to make sure that the e-mail messages that the auditing department needs should be sent to them. What should you do to finish the task? (Choose more than one.)

A) To finish the task, an Event Notification should be created on the Audit Server Starts and Stops event class.
B) To finish the task, SQL Server Profiler should be utilized to track the Audit Statement Permission event class.
C) To finish the task, an Event Notification should be created on the Audit Server Principal Impersonation event class.
D) To finish the task, a Windows Group Policy Object should be created to audit System Events.
E) To finish the task, SQL Server Profiler should be utilized to track the Audit Server Starts and Stops event class.
F) To finish the task, SQL Server Profiler should be utilized to track the Audit Server Principal Impersonation event class.

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 administrator of a SQL Server 2005 computer, and the computer is called SQL01. Three instances of SQL Server are run by the SQL01. After implementing the recovery plan, the dedicated administrative connection is available on each instance of SQL Server. After the ability is tested, a problem has been found that the dedicated administrative connection on SQL01s default instance can be connected, while the dedicated administrative connection on the two other instances on SQL1 cannot be connected. You confirm that the instances are running and the instances can be accessed by the client applications. In addition, the dedicated administrative connection is enabled for all instances. What??s more, there is no connection that is tried to by other administrators to any dedicated administrative connections on SQL1.
Since you are the administrator, you are required to make sure the dedicated administrative connection can be connected on all three instances. To achieve the goal, which action should be utilized?

A) The SQL Server Browser service should be started. And then, the service should be set to start automatically.
B) The non-default instances of SQL Server should be stopped and restarted.
C) The default instance should be set to utilize an administrative port number which differs from 1434.
D) The Sqlcmd.exe tool should be utilized to make the dedicated administrative connection.

3. According to the performance and availability requirements of the company, you are asked to implement the new read-only copy of the internet_rentals database on SQL02. What should you do?

A) A new SQL Server 2005 computer should be installed. Then a new database should be created on that server. Between internet_rentals and the new database, transactional replication should be configured.
B) A new SQL Server 2005 computer should be installed. Then a new database should be created on that server. Between internet_rentals and the new database, merge replication should be configured.
C) On SQL02, a new instance of SQL Server 2005 should be installed. A new database should be created. Between internet_rentals and the new database, transactional replication should be configured.
D) On SQL02, a new database should be created. Between internet_rentals and the new database, transactional replication should be configured.
E) A new SQL Server 2005 computer should be installed. Then a new database should be created on that server. Between internet_rentals and the new database, snapshot replication should be configured.

4. According to the company requirements, a unique nonclustered index will be created on
the CustomerID, LastName, and FirstName columns of the CustomerData.Customers table in the CRM database. The variables should be identified to help estimate the space requirements for the index.
Estimated number of rowsClustered index key Number of columns in the indexFixed data sizeNumber of variable-length columnsAverage variable-length data size.
The most significant additional variable that will enhance the estimated index size should be identified. From the following four additional variables, which one should be identified to accomplish the task?

A) To accomplish the task, automatically generated uniqueifier value should be identified.
B) To accomplish the task, current number of rows should be identified.
C) To accomplish the task, null bitmap should be identified.
D) To accomplish the task, fill factor should be identified.

5. 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) World Wide Web Publishing service should be disabled.
B) SQL Server Agent service should be disabled.
C) Cluster service should be disabled.
D) Server service should be disabled.
E) Messenger service should be disabled.

Solutions: