Online Access Free 070-447 Exam Questions

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, you should use SQL Server Profiler to create a trace that uses a predefined template.
• 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, the sp_who system stored procedure should be run.
• D.In order to achieve the goal, you should view the activity of specific business analysts by using the Activity Monitor.

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 find a problem that when the following query is executed, a SQL Server 2005 application will perform slowly.
SELECT * FROM Orders WHERE OrderStatus = 'InProgress'
This query is executed by the application as an ad hoc query. In most occasions, this query performs much more quickly. The company has the OrderStatus column indexed. The value InProgress is included by only a small number of the rows. Nevertheless, the OrderStatus column index which is the most ideal for this query is not being utilized. The performance of this query should be enhanced. However, the application that executes the query should not be changed. In addition, the effect on end users should be cut to the least.
Which action should be performed to finish the task?

• A.To finish the task, the statistics should be updated on the index.
• B.To finish the task, the index should be built by utilizing a FILLFACTOR of 10 percent.
• C.To finish the task, the index should be recreated to have all of the columns in the table included.
• D.To finish the task, an indexed view of the Orders table should be viewed.

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

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 in the company. A single user database named DB01 is contained in this computer. All company employees connect to DB1. According to the requirement of the company policy, the database should be fault-tolerant and that failovers require no administrative effort. Therefore, you are asked to provide fault tolerance for the database.
Your solution must guarantee that even if a complete hardware failure occurs on the SQL Server 2005 computer, client computers are able to connect to the database, including a failure of all disks. What action should you perform to achieve your goal?

• A.Log shipping needs to be implemented to another SQL Server 2005 computer.
• B.Transactional replication needs to be implemented to another SQL Server 2005 computer.
• C.A two-node server cluster that contains the database needs to be implemented.
• D.Database mirroring to another SQL Server 2005 computer needs to be implemented.

According to the company requirements, a solution should be assigned to enable the accounting managers to review accounting changes. Which action should be performed?

• A.A job should be created to create a database snapshot at close of business every Friday. And then, another job should be created to migrate snapshots that are longer than two weeks.
• B.A job should be created to create a database snapshot at close of business daily. And then, another job should be created to migrate snapshots that are longer than eight days.
• C.Snapshot replication should be set up to a new database named Accounting_Changes. And then, the snapshot should be scheduled to be sent at close of business every Friday.
• D.Transactional replication should be set up to a new database named Accounting_Changes. And then, a job should be created to migrate all rows that have an inserted date that is longer than eight days.