Prepare with Microsoft 070-447 exam practice material, pass for sure

Microsoft 070-447 Exam Overview:

Microsoft 070-447 Exam Syllabus Topics:

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 SQL Server 2005 computer is named SQL1. The company set the SQL Server service and the SQL Server Agent service to start automatically. SQL01 should be set to audit all user names and application names that try to access information from SQL01. Auditing should be performed by SQL01 at all times. And the company requires keeping the results for 10 years. Which action should be performed?

A) The C2 Audit Tracing setting should be disabled on the server properties.
B) The Login auditing setting should be set to not only failed but also successful logins.
C) The server authentication mode should be set as SQL Server and Windows Authentication mode.
D) A SQL Server Agent job should be created to run the SQL Trace stored procedure. And then, the job should be set to start when the SQL Server Agent service starts.

2. A most proper archiving strategy for the SalesData.Orders table in the Sales database should be identified. The archiving strategy should be implemented with the least amount of effect on performance. Which action should be performed?

A) First, the Sales database should be backed up to tape every three months, and then a DELETE statement should be utilized to migrate archived data from the SalesData.Orders table.
B) First, a table named ArchivedData.Orders should be created. Second, the SalesData.Orders table and its indexes should be partitioned. At last, MERGE, SPLIT, and SWITCH functions should be utilized every three months to remove old order data into ArchivedData.Orders.
C) First, a table named ArchivedData.Orders should be created. Second, an INSERT statement will be utilized to copy data from the SalesData.Orders table into ArchivedData.Orders every three months. At last a DELETE statement should be utilized to remove archived data from SalesData.Orders.
D) First, a database snapshot of the Sales database should be created every three months, and then a DELETE statement should be utilized to remove archived data from the SalesData.Orders table.

3. 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. You are in charge of 20 SQL Server 2005 computers. Other administrators are responsible for monitoring these computers. These administrators need to guarantee that query response times meet company service level agreements (SLAs). Since you are the technical support, you are asked to provide these administrators with a consistent way of monitoring query response times on the servers. What action should you perform to achieve this goal?

A) You should create an XML document that contains the event and column names of the query response times that must be captured in SQL Server Profiler. The XML file should be distributed to the database administrators.
B) SQL Server Profiler templates should be created. Query start times and end times should be include in the templates. Distribute these templates to the database administrators.
C) You should write a query. This query queries the sys.dm_db_partition_stats dynamic management view (DMV). This query should be distributed to the administrators.
D) The administrators should be asked to monitor cache hit ratio statistics by using System Monitor.

4. 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. You administer a SQL Server 2005 computer which contains a database named DB01.A table named CustInfo is included in DB01. The table includes an identity column named CustID and an nvarchar column named CustName. According to the requirement of the company policy, rows containing duplicate CustName values should be removed from the CustInfo table once each year. But only when rows have been manually examined and verified as being duplicatesm, they can be removed from the CustInfo table. Since you are the technical support, the company asks you to provide a way to identify potential duplicate rows for manual examination. You should use as little administrative effort as possible when you try to achieve this. So what action should you perform?

A) You should create a new table that contains duplicate rows from the CustInfo table by using a SELECT INTO query that includes GROUP BY and HAVING clauses.
B) A foreign key constraint should be created on the CustName column. This constraint references a distinct list of CustName values.
C) Create a trigger for the CustInfo table that rolls back INSERT queries that contain duplicate data.
D) On the CustName column, a new unique index should be created. When index is being created, Specify IGNORE_DUP_KEY = ON.

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 SQL Server 2005 computer is managed by you, and a database named Customers is included by the SQL Server 2005 computer. A table named CustInfo is included by the database. An identity column named CustID and an nvarchar column named CustName are included by the table. According to the company requirement, rows which duplicate CustName values are included should be moved from the CustInfo table every year. However, the company requests to have rows removed from the CustInfo table after the rows have been manually examined and verified as being duplicates.
Since you are the technical support, you are required to think out a method to have potential duplicate rows identified for manual examination.
The minimum amount of administrative effort should be utilized. Which action should be performed to finish the task?

A) A new unique index should be created on the CustName column. And then, IGNORE_DUP_KEY = ON should be specified when creating index.
B) A trigger should be created for the CustInfo table so as to roll back INSERT queries that duplicate data is included.
C) On the CustName column, a foreign key constraint should be created, and a distinct list of CustName values is referenced by the foreign key constraint.
D) A SELECT INTO query should be utilized, and GROUP BY and HAVING clauses should be included by the query to create a new table that contains duplicate rows from the CustInfo table.

Solutions: