Abreast of the times 70-431: Testking real pdf from 17 to 32

70-431 Royal Pack Testengine pdf

100% Actual & Verified — 100% PASS

Unlimited access to the world's largest Dumps library! Try it Free Today!

Get it now →

Product Description:
Exam Number/Code: 70-431
Exam name: Microsoft SQL Server 2005 Implementation & Maintenance
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Free Certification Real IT 70-431 Exam pdf Collection

Exam Code: 70-431 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Microsoft SQL Server 2005 Implementation & Maintenance
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-431 Exam.

2016 Mar 70-431 Study Guide Questions:

Q17. You manage a SQL Server 2005 database that contains a table with many indexes. You notice that data modification performance has degraded over time. You suspect that some of the indexes are unused. You need to identify which indexes were not used by any queries since the last time SQL Server 2005 started. Which dynamic management view should you use?

A.

B.

C.

D.

Answer: C


Q19. You are creating a Web-based application to manage data aggregation for reports. The application connects to a SQL Server 2005 database named DataManager. One page in the application has controls that execute stored procedures in a database named ReportingDatabase. There is an existing Service Broker connection between the DataManager database and ReportingDatabase. You want to add two new message types to the existing service. In each database, you create message types named ProcessReport and SendResult. You need to add the two new message types to the existing service. What should you do first?

A. Create a queue on each database with the ACTIVATION argument set to DataManager.dbo.ProcessReport.

B. Create a conversation between the databases by using the following statement.BEGIN DIALOG FROM SERVICE 'ProcessReport' TO SERVICE 'SendResult'

C. Create a contract between the services by using the following statement.CREATE CONTRACT ProcessData (ProcessReport SENT BY INITIATOR, SendResult SENT BY TARGET)

D. Create services for each database by using the following statement.CREATE SERVICE DataManager ON QUEUE ProcessReport

Answer: C


Q20. Your company has a network that consists of two domains named DOM1 and DOM2. There is no trust relationship between DOM1 and DOM2. DOM1 contains a server named SQL1. DOM2 contains a server named SQL2. Both servers run SQL Server 2005. The SQL Server instance on SQL1 uses the DOM1\SQLService1 service account. The SQL Server instance on SQL2 uses the DOM2\SQLService2 service account. There are no mirroring endpoints on either server. SQL1 contains a database named DB1. You want to mirror DB1 to SQL2 by using high-protection mode without a witness. You need to create the database mirroring endpoints and configure security on all participating server instances. What should you do?

A. Create endpoints and configure security by using the Configure Database Mirroring Security Wizard for DB1. Set up the endpoints to use SQL Server Authentication.

B. Configure database mirroring endpoints and security for each server instance by using Transact-SQL. Allow database mirroring to use encrypted certificates for outbound and inbound connections.

C. Create logins and add them to the sysadmin group. Configure the logins to use SQL Server Authentication on each of the server instances. Create endpoints by using the Configure Database Mirroring Security Wizard on the Database Properties Mirroring page for DB1.

D. Connect to each server instance and execute the following statement.CREATE ENDPOINT mirroring

STATE = STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING (

AUTHENTICATION = WINDOWS NTLM, ROLE=PARTNER);

Answer: B


Q21. You are creating a SQL Server 2005 database for a mortgage company. The database will support a new Web-based application that will handle up to 1,000 simultaneous users. This application must quickly display the results of calculation-intensive operations, such as calculating mortgage payments and amortization schedules. You need to ensure that the database processes calculations as quickly and efficiently as possible. What should you do?

A. Implement parameterized Transact-SQL queries in the application.

B. Implement Transact-SQL stored procedures in the database.

C. Implement CLR stored procedures in the database.

D. Implement distributed Web services.

Answer: C


70-431 download

Update 70-431 training:

Q22. Your company has two SQL Server 2005 computers named SQL1 and SQL2. A user in your company named Eric writes many ad hoc queries against the company databases.

Eric has access to the Customer database on SQL1. He does not have access to the Sales database on SQL2. You need to ensure that Eric can write queries that join information from both servers. What should you do first?

A. Create a linked server on SQL1 to SQL2. Configure the linked server to use impersonation.

B. Create a linked server on SQL1 to SQL2. Configure the linked server to use mapped logins.

C. Instruct Eric to write the queries on SQL2 by using the OPENQUERY statement and specifying SQL1 as the server name.

D. Instruct Eric to specify SQL Server object names by using four-part notation.

Answer: B


Q23. You are moving a SQL Server 2005 database named Timesheet to a new database server. The Timesheet database is used by the TimeEntry application. You need to minimize the amount of time that the TimeEntry application is unavailable while you move the database. What should you do?

A. Move the database to the new server by using the SQL Management Object method in the Copy Database Wizard.

B. Detach the current database. Copy the data files to the new server. Attach the files.

C. Back up the database. Copy the backup file to the new server. Restore the database.

D. Move the data files. Specify the new location by using ALTER DATABASE.

Answer: B


Q24. You use a SQL Server 2005 database named DB1, which is located on a server named SQL1. DB1 is in use 24 hours a day, 7 days a week. A recent copy of DB1 exists on a second server named SQLtest that also runs SQL Server 2005. You detect a high number of full scans on SQL1 and conclude that additional indexes in DB1 are needed. A workload file that is suitable for Database Engine Tuning Advisor (DTA) already exists. You need to analyze the workload file by using DTA. You must ensure maximum performance on SQL1 during analysis. You must also ensure availability during the implementation of any recommendations suggested by the DTA. What should you do?

A. Store the workload file on SQL1. Start DTA on SQLtest and connect to SQL1. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the Generate only online recommendations check box.

B. Store the workload file on SQLtest. Start DTA on SQLtest and connect to SQLtest. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the Generate only online recommendations check box.

C. Store the workload file on SQL1. Start DTA on SQL1 and connect to SQL1. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the All recommendations are offline check box.

D. Store the workload file on SQLtest. Start DTA on SQLtest and connect to SQLtest. Specify all workload and tuning options as necessary. In the Advanced Tuning Options dialog box, select the All recommendations are offline check box.

Answer: B


Q25. Your company uses SQL Server 2005. A user reports that an order processing application stopped responding during a transaction. You investigate and discover that the user's session, which is session ID 85, is being blocked by an errant process. The session ID of the errant process is 101. You need to solve this blocking situation quickly so that the user can continue using the application without losing any work. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

A. In SQL Server Management Studio, open a new query window. Execute the following statement.KILL 85

B. In SQL Server Management Studio, open a new query window. Execute the following statement.KILL 101

C. In SQL Server Management Studio, open the Activity Monitor. Open the Process Info page. Right-click session ID 85 and select Kill Process.

D. In SQL Server Management Studio, open the Activity Monitor. Open the Process Info page. Double-click session ID 101 to show the process details. Click the Kill Process button.

E. In SQL Server Configuration Manager, stop and then restart the SQL Server (MSSQLSERVER) service.

F. At a Microsoft Windows command prompt, execute the following statements.NET STOP MSSQLSERVERNET START MSSQLSERVER

Answer: BD


Q26. You manage a SQL Server 2005 database that contains a table with many indexes. You notice that data modification performance has degraded over time. You suspect that some of the indexes are unused. You need to identify which indexes were not used by any queries since the last time SQL Server 2005 started. Which dynamic management view should you use?

A. sys.dm_fts_index_population

B. sys.dm_exec_query_stats

C. sys.dm_db_index_usage_stats

D. sys.dm_db_index_physical_stats

Answer: C


70-431 testing engine

Pinpoint 70-431 free question:

Q27. You have a SQL Server 2005 computer named SQL1. Users report that the time required to run their queries on SQL1 is steadily increasing. You want to investigate this issue by using SQL Server Profiler to trace all user queries. You need to decide on which computer to run SQL Server Profiler, and where to store the trace. You must minimize the impact of this operation on SQL1 performance. What should you do?

A. Run SQL Server Profiler from the console of SQL1. Store the trace in a table on SQL1.

B. Run SQL Server Profiler over a Remote Desktop connection to SQL1. Store the trace in a file on a separate computer.

C. Run SQL Server Profiler on a separate computer that is connected to SQL1. Store the trace in a table on SQL1.

D. Run SQL Server Profiler on a separate computer that is connected to SQL1. Store the trace in a file on the computer that is running SQL Server Profiler.

Answer: D


Q28. You work for a company that sells books. You are creating a report for a SQL Server 2005 database. The report will list sales representatives and their total sales for the current month. The report must include only those sales representatives who met their sales quota for the current month. The monthly sales quota is $2,000. The date parameters are passed in variables named @FromDate and @ToDate. You need to create the report so that it meets these requirements. Which SQL query should you use?

A. SELECT s.AgentName, SUM(ISNULL(o.OrderTotal,0.00))AS SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID = o.AgentIDWHERE o.OrderDate BETWEEN @FromDate AND @ToDateGROUP BY s.AgentName

B. SELECT s.AgentName, SUM(ISNULL (o.OrderTotal,0.00))AS SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID = o.AgentIDWHERE o.OrderDate BETWEEN @FromDate AND @ToDate AND o.OrderTotal >= 2000GROUP BY s.AgentName

C. SELECT s.AgentName, SUM(ISNULL (o.OrderTotal,0.00)) AS SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID = o.AgentID WHERE o.OrderDate BETWEEN @FromDate AND @ToDateGROUP BY s.AgentNameHAVING SUM(o.OrderTotal) >= 2000

D. SELECT s.AgentName, SUM(ISNULL(o.OrderTotal,0.00)) AS SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID = o.AgentIDWHERE o.ordertotal = 2000 AND o.OrderDate BETWEEN @FromDate AND @ToDateGROUP BY s.AgentNameHAVING SUM(o.OrderTotal) >= 2000

Answer: C


Q29. You work at the regional sales office. You are responsible for importing and exporting data in SQL Server 2005 databases. The main office asks you to send them a text file that contains updated contact information for the customers in your region. The database administrator in the main office asks that the data be sorted by the StateProvince, Surname, and FirstName columns. You need to satisfy these requirements by using the least amount of effort. What should you do?

A. Specify StateProvince, Surname, and FirstName in the ORDER hint in the bcp out command.

B. Create a format file for the export operation.

C. Specify StateProvince, Surname, and FirstName in the ORDER BY clause in the bcp queryout command.

D. Copy the data into a new table that has a clustered index on StateProvince, Surname, and FirstName. Export the data.

Answer: C


Q30. You work with a SQL Server 2005 database that provides banking information for customers. You want customers to see banking reports that combine data that is retrieved from the database with real-time investment information that comes from a Web service provided by a third party. The investment information must be current when the reports are executed.

You need to create the appropriate objects that support the reports. What should you do?

A. Publish the data in the database as an XML Web service by using the FOR XML AUTO clause.

B. Create a table to store the banking information for each customer. Create a trigger that fires when data is inserted into the table that joins with the data coming from the Web service.

C. Create a Transact-SQL stored procedure that uses a temporary table to store the banking information for each customer. Update the table with the values from the Web service.

D. Have a developer in your company create an assembly that calls the remote Web service. Create a CLR function by using the assembly. Call the CLR function and combine the results with banking information in the database.

Answer: D


Q31. You discover that all indexes of the Sales table on your SQL Server 2005 database server are heavily fragmented. You need to decrease the fragmentation of all indexes of the Sales table to a minimum, while keeping the Sales table available to all users. What should you do?

A. Defragment the disk that contains the Sales table.

B. Execute the following statement.USE master;GODBCC CLEANTABLE ('DB1','sales');GO

C. Execute the following statement.USE DB1;GOALTER INDEX ALL ON sales REORGANIZE WITH (LOB_COMPACTION = ON);GO

D. Execute the following statement.USE DB1;GOALTER INDEX ALL ON sales REBUILD;GO

Answer: C


Q32. Your company has two SQL Server 2005 computers named SQL1 and SQL2. Transaction log shipping occurs from SQL1 to SQL2 by using default SQL Server Agent schedule settings. You need to reconfigure transaction log shipping to provide minimum latency on SQL2. What should you do?

A. On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, maintain default schedule settings for both the log shipping copy and the restore jobs.

B. On SQL1, change the schedule type for the transaction log backup to Start automatically when SQL Server Agent starts. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically when SQL Server Agent starts.

C. On SQL1, maintain default schedule settings for the transaction log backup job. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically when SQL Server Agent starts.

D. On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, reschedule both the log shipping copy and the restore jobs so that they occur every minute.

Answer: D



see more 70-431 - Microsoft SQL Server 2005 Implementation & Maintenance