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. There is a SQL Server 2005 computer named SQL01 in the company. A stores sales history data is stored in a database on SQL01 for the company's Web-based order system. Twenty of the business analysts in the company create ad hoc queries against the database. SQL01 has a slow performance all the time. Since you work as the technical support, the company asks you to identify which business analyst is causing the problem. What action should you perform to achieve this goal? (Choose more than one.)

A) In order to achieve the goal, the sp_who system stored procedure should be run.
B) In order to achieve the goal, you should gather database activity by running SQL Trace system stored procedures.
C) In order to achieve the goal, you should view the activity of specific business analysts by using the Activity Monitor.
D) In order to achieve the goal, you should use SQL Server Profiler to create a trace that uses a predefined template.

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 a database administrator for your company. You utilize the SP:Recompile event class to run SQL Server Profiler. You find that a great amount of recompiles occurring. The possible causes of the large number of recompiles should be identified. From the flowing four options, which one is the most possible cause of the problem?

A) Ad hoc batches are not being recovered from the procedure cache.
B) The sp_executesql batches are not being recovered from the procedure cache.
C) A database administrator executed UPDATE STATISTICS statements on all tables, and the most common stored procedures reference all the tables.
D) One new stored procedure is created by a database administrator.

3. 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) 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.
B) 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.
C) 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.
D) 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.

4. 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, snapshot 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) 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) 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 database should be created. Between internet_rentals and the new database, transactional replication should be configured.

5.

Solutions: