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, and the computer is named SQL1. Microsoft Windows Server 2003 is run by all servers. All the servers belong to a Microsoft Active Directory domain. According to the company written security policy, strong passwords are mandated for all domain, local, and application accounts. The company utilizes a Group Policy object (GPO) to deploy the proper domain password policy. MSDOS-based or UNIX-based operating systems are run by many of the company's computers. Varying levels of access to a database named Services on SQL1 is requested by the computers. Since you are the database administrator, you are required to confirm that the Services database can be accessed by these computers.
And the company's written security policy should not be violated. Which action should be performed by you?

A) A virtual directory should be configured through Internet Information Services (IIS) for access to SQL1. And then, users should be directed to the virtual directory for data access.
B) A local logon account should be created on SQL1 for each of the company's users. And then, each logon account should be offered with access to SQL1 and the Services database.
C) A SQL Server logon account should be created for each of the company's users. And then, the Enforce password policy option should be enabled for each logon account. At last, each logon account should be offered with access to the Services database.
D) A domain account should be created for each of the company's users. And then, each domain account should be offered with access to SQL1 and the Services database.
E) One SQL Server logon account should be created for all of the company's computers running MS-DOS or UNIX-based operating systems. And then, the Enforce password policy option should be enabled for users' accounts. At last, the logon accounts should be provided with access to the Services database.

2. 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) On SQL02, a new database should be created. 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, transactional replication should be configured.
C) 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.
D) 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.
E) 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.

3. In order to satisfy the security requirements of the company, you are asked to make recommendations for tracking usage on the acct1 database. What should you recommend?

A) On all sensitive tables, DDL triggers should be created.
B) On all sensitive tables, DML triggers should be created.
C) In SQL Trace, the Security Audit event category in SQL Trace should be set up to track the Audit Database Object Management event class.
D) In SQL Trace, the Security Audit event category should be set up to track the Audit Schema Object Access event class.

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

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 mission-critical customer relationship management (CRM) database is hosted by a new SQL Server 2005 computer, and the new SQL Server 2005 computer is called SQL2. The CRM database is available to all of the company's 500 employees in the sales and marketing departments. The purchase of new hardware and software should not exceed the annual budget for your department. A monitoring strategy should be devised, and the strategy enables you to make decisions about the history of server performance. In addition, how to arrange the most efficient method to allocate funds for hardware upgrades should be suggested by the monitoring strategy. Which action should be performed?

A) SQL Server Profiler should be utilized to capture information to a SQL Server database table. And then, this task should be performed at regular intervals during periods of peak server activity.
B) A counter log should be set to capture information about %Processor Time, Average Disk Queue Length, Pages/sec, and Bytes Total/sec. And then, the log should be scheduled to execute at regular intervals, and the information should be stored in a comma-delimited text file.
C) System Monitor in graph mode should be utilized to display information about %Processor Time, Average Disk Queue Length, Pages/sec, and Bytes Total/sec. And then, this task should be performed at regular intervals during periods of peak server activity.
D) A series of performance alerts should be created to notify you when the acceptable thresholds are exceeded by %Processor Time, Average Disk Queue Length, Pages/sec, and Bytes Total/sec.

Solutions: