Microsoft SQL Server 2005 Implementation & Maintenance : 70-431 Exam

Exam Number/Code: 70-431
Exam Name: Microsoft SQL Server 2005 Implementation & Maintenance
VUE Code: 70-431
Questions Type: Single choice,
Real Exam Question Numbers: 51 questions
Exam Language(s): English

“Microsoft SQL Server 2005 Implementation & Maintenance”, also known as 70-431 exam, is a Microsoft certification.
Preparing for the 70-431 exam? Searching 70-431 Test Questions, 70-431 Practice Exam, 70-431 Dumps?

With the complete collection of questions and answers, Pass4sure has assembled to take you through 70 Q&As + 30 Labs to your 70-431 Exam preparation. In the 70-431 exam resources, you will cover every field and category in MCITP helping to ready you for your successful Microsoft Certification.

Candidates for this exam are professionals who typically pursue careers as database administrators, database developers or business intelligence developers. Additionally, they can be people who do not work with Microsoft SQL Server as a part of their primary job functions, but who want to show their breadth of technology experience, such as developers, systems administrators, and others. Candidates have sought out knowledge and experience with Microsoft SQL Server 2005 to enhance their technical expertise, and they can implement and maintain databases by using specific instructions and specifications. Candidates for this exam should be an expert in following areas:

•	Tools usage
•	UI navigation
•	Wizards usage
•	Writing code in the appropriate language (Transact-SQL, CLR language, and other scripting languages)
•	Code debugging or syntactic issue resolution
•	Troubleshooting
•	Accomplishing specific focused tasks by using code or UI navigation.

About this Exam
This Microsoft Technical Specialist (TS) exam, Exam 70-431: TS: Microsoft SQL Server 2005 – Implementation and Maintenance, became available in January 2006. This exam may contain performance-based testing elements.

Audience Profile
Candidates for this exam are professionals who typically pursue careers as database administrators, database developers, or business intelligence developers. Additionally, they can be people who do not work with Microsoft SQL Server as a part of their primary job functions but who want to show their breadth of technology experience, such as developers, systems administrators, and others. Candidates have sought out knowledge and experience with Microsoft SQL Server 2005 to enhance their technical expertise, and they can implement and maintain databases by using specific instructions and specifications.

Candidates for this exam should be experts in following areas:

Tools usage
UI navigation
Wizards usage
Writing code in the appropriate language (Transact-SQL, CLR language, and other scripting languages)
Code debugging or syntactic issue resolution
Troubleshooting
Accomplishing specific focused tasks by using code or UI navigation

Credit Toward Certification
When you pass Exam 70-431: TS: Microsoft SQL Server 2005 – Implementation and Maintenance, you complete the requirements for the following certification(s):
Microsoft Certified Technology Specialist: SQL Server 2005

Exam 70-431: TS: Microsoft SQL Server 2005 – Implementation and Maintenance: counts as credit toward the following certification(s):
MCITP: Database Administrator
MCTS and MCITP upgrade from MCDBA
MCITP: Database Developer
MCSA on Microsoft Windows 2000
MCSA on Windows Server 2003
MCSE on Microsoft Windows 2000
MCSE on Windows Server 2003

Note This preparation guide is subject to change at any time without prior notice and at the sole discretion of Microsoft. Microsoft exams might include adaptive testing technology and simulation items. Microsoft does not identify the format in which exams are presented. Please use this preparation guide to prepare for the exam, regardless of its format.
Skills Being Measured
This exam measures your ability to accomplish the technical tasks listed below.
The percentages indicate the relative weight of each major topic area on the exam.

Installing and Configuring SQL Server 2005
Install SQL Server 2005.
Verify prerequisites.
Upgrade from an earlier version of SQL Server.
Create an instance.
Configure SQL Server 2005 instances and databases.
Configure log files and data files.
Configure the SQL Server DatabaseMail subsystem for an instance.
Choose a recovery model for the database.
Configure SQL Server security.
Configure server security principals.
Configure database securables.
Configure encryption.
Configure linked servers by using SQL Server Management Studio (SSMS).
Identify the external data source.
Identify the characteristics of the data source.
Identify the security model of the data source.
Implementing High Availability and Disaster Recovery
Implement database mirroring.
Prepare databases for database mirroring.
Create endpoints.
Specify database partners.
Specify a witness server.
Configure an operating mode.
Implement log shipping.
Initialize a secondary database.
Configure log shipping options.
Configure a log shipping mode.
Configure monitoring.
Manage database snapshots.
Create a snapshot.
Revert a database from a snapshot.
Supporting Data Consumers
Retrieve data to support ad hoc and recurring queries.
Construct SQL queries to return data.
Format the results of SQL queries.
Identify collation details.
Manipulate relational data.
Insert, update, and delete data.
Handle exceptions and errors.
Manage transactions.
Manage XML data.
Identify the specific structure needed by a consumer.
Retrieve XML data.
Modify XML data.
Convert between XML data and relational data.
Create an XML index.
Load an XML schema.
Implement an HTTP endpoint.
Create an HTTP endpoint.
Create an HTTP endpoint.
Implement Service Broker components.
Create services.
Create queues.
Create contracts.
Create conversations.
Create message types.
Send messages to a service.
Route a message to a service.
Receive messages from a service.
Import and export data from a file.
Set a database to the bulk-logged recovery model to avoid inflating the transaction log.
Run the bcp utility.
Perform a Bulk Insert task.
Import bulk XML data by using the OPENROWSET function.
Copy data from one table to another by using the SQL Server 2005 Integration Services (SSIS) Import and Export Wizard.
Manage replication.
Distinguish between replication types.
Configure a publisher, a distributor, and a subscriber.
Configure replication security.
Configure conflict resolution settings for merge replication.
Monitor replication.
Improve replication performance.
Plan for, stop, and restart recovery procedures.
Maintaining Databases
Implement and maintain SQL Server Agent jobs.
Set a job owner.
Create a job schedule.
Create job steps.
Configure job steps.
Disable a job.
Create a maintenance job.
Set up alerts.
Configure operators.
Modify a job.
Manage a job.
Manage databases by using Transact-SQL.
Manage index fragmentation.
Manage statistics.
Shrink files.
Perform database integrity checks by using DBCC CHECKDB.
Back up a database.
Perform a full backup.
Perform a differential backup.
Perform a transaction log backup.
Initialize a media set by using the FORMAT option.
Append or overwrite an existing media set.
Create a backup device.
Back up filegroups.
Back up a database.
Perform a full backup.
Perform a differential backup.
Perform a transaction log backup.
Initialize a media set by using the FORMAT option.
Append or overwrite an existing media set.
Create a backup device.
Back up filegroups.
Restore a database.
Identify which files are needed from the backup strategy.
Restore a database from a single file and from multiple files.
Choose an appropriate restore method.
Move a database between servers.
Choose an appropriate method for moving a database.
Monitoring and Troubleshooting SQL Server Performance
Gather performance and optimization data by using the SQL Server Profiler.
Start a new trace.
Save the trace logs.
Configure SQL Server Profiler trace properties.
Configure a System Monitor counter log.
Correlate a SQL Server Profiler trace with System Monitor log data.
Gather performance and optimization data by using the Database Engine Tuning Advisor.
Build a workload file by using the SQL Server Profiler.
Tune a workload file by using the Database Engine Tuning Advisor.
Save recommended indexes.
Monitor and resolve blocks and deadlocks.
Identify the cause of a block by using the sys.dm_exec_requests system view.
Terminate an errant process.
Configure SQL Server Profiler trace properties.
Identify transaction blocks.
Diagnose and resolve database server errors.
Connect to a non-responsive server by using the dedicated administrator connection (DAC).
Review SQL Server startup logs.
Review error messages in event logs.
Monitor SQL Server Agent job history.
Identify the cause of a failure.
Identify outcome details.
Find out when a job last ran.
Gather performance and optimization data by using DMVs.
Creating and Implementing Database Objects
Implement a table.
Specify column details.
Specify the filegroup.
Assign permissions to a role for tables.
Specify a partition scheme when creating a table.
Specify a transaction.
Implement a view.
Create an indexed view.
Create an updateable view.
Assign permissions to a role or schema for a view.
Implement triggers.
Create a trigger.
Create DDL triggers for responding to database structure changes.
Identify recursive triggers.
Identify nested triggers.
Identify transaction triggers.
Implement functions.
Create a function.
Identify deterministic versus nondeterministic functions.
Implement stored procedures.
Create a stored procedure.
Recompile a stored procedure.
Assign permissions to a role for a stored procedure.
Implement constraints.
Specify the scope of a constraint.
Create a new constraint.
Implement indexes.
Specify the filegroup.
Specify the index type.
Specify relational index options.
Specify columns.
Specify a partition scheme when creating an index.
Disable an index.
Create an online index by using an ONLINE argument.
Create user-defined types.
Create a Transact-SQL user-defined type.
Specify details of the data type.
Create a CLR user-defined type.
Implement a full-text search.
Create a catalog.
Create an index.
Specify a full-text population method.

Free PassGuide Practice Engine Demo Download Pass4sure offers free demos for each certification exam, including all IT vendors. You can check out the testing engine software, or pdf file question quality and usability of our practice exams before you decide to buy it. We are the only one site that offers demos for almost all IT certification exams.If you want to try p4s exam practice engine demo. http://demo.passguide.com/download

QUESTION 1
You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All
servers on the Certkiller .com network run Windows 2000 Server. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01 that
runs on Windows 2000 Server. Certkiller -DB01 contains a database named
CK_Sales.
Certkiller .com intends migrating Certkiller -DB01 to SQL Server 2005 in the near future. To prepare for the migration, you decide to install SQL Server 2005 Express
on a server named Certkiller -SR31. You verify that Certkiller -SR31 exceeds the minimum recommended hardware requirements for SQL Server 2005.
What should you do next?

A. Upgrade Certkiller -SR31 to Windows Sever 2003 and install Service Pack 1.
B. Install and run the SQL Server Upgrade Advisor on Certkiller -SR31. C. Install SQL Server 2000 on Certkiller -SR31.
D. Download and install the .NET Framework 2.0. Answer: D
Explanation: SQL Server 2005 requires the.NET Framework 2.0. This is automatically installed when SQL Server 2005 is installed but is not automatically installed when SQL Server 2005 Express is installed.
Incorrect Answers:
A: You do not need to upgrade Certkiller -SR31 to Windows Sever 2003 Service Pack
1 as SQL Server 2005 Express can be installed on Windows 2000 Server. You will need
to install Service Pack 4 for Windows 2000 Server though.
B: You should run the SQL Server Upgrade Advisor when you want to upgrade an instance
of a SQL Server 2000 database to SQL Server 2005.
C: You do not need to install SQL Server 2000 before installing SQL Server 2005
Express. Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: installing SQL Server Express, before installing

QUESTION 2
You work as a database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run Windows Server 2003 with the latest Service Packs and Hotfixes, while the client computers run Windows XP Professional, Windows 98
Second Edition, and Novell NetWare 6.5.
You have received instruction to prepare for a new installation of SQL Server 2005
on a member server named Certkiller -SR21. Certkiller -SR21 will host a
database named CK_Sales. All Certkiller .com users must be able to connect to
Certkiller -SR21 by computer name. A Certkiller .com network administrator

named Rory Allen must ensure that the client computers will be able to connect to
Certkiller -DB01.
Which protocols can Certkiller .com users use to Certkiller -SR21?

A. TCP/IP and Named Pipes
B. TCP/IP and IPX/SPX
C. Virtual Interface Adapter (VIA) D. Shared Memory

Answer: A

Explanation: By default, clients have TCP and Named Pipes as available protocols. You can manipulate the protocol ordering by using the SQL Server Client utility.
The client application uses the protocols in the order specified on the client computer. If you are using SQL Server 2005, the protocol order is stored in the ProtocolOrder registry entry under the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0
Incorrect Answers:
B: SQL Server 2005 does not support IPX/SPX. The newer versions of NetWare, such as
NetWare 6.5, support TCP/IP which makes it the common protocol for all clients.
C: The Virtual Interface Adapter (VIA) can be used only by VIA hardware.
D: Shared Memory can only be used on the local computer and cannot be used as a network protocol.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: client connections [SQL Server], about client network connections, choosing a network protocol

QUESTION 3
You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All
servers on the Certkiller .com network run Windows 2000 Server. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01 that
runs on Windows 2000 Server. Certkiller -DB01 contains a database named
CK_Sales.
Certkiller .com intends migrating Certkiller -DB01 to SQL Server 2005 in the near future. To prepare for the migration, you decide to install SQL Server 2005 Express
on a server named Certkiller -SR23. Certkiller -SR31 was used for previously
used by application developer and contains Microsoft Visual Studio 2003, .NET Framework 1.2 and Internet Explorer 5.5. You verify that Certkiller -SR23
exceeds the minimum recommended hardware requirements for SQL Server 2005. What should you do next? (Each correct answer provides part of the solution.
Choose THREE.)

A. Upgrade Certkiller -SR23 to Windows Sever 2003 and install Service Pack 1 for
Windows Sever 2003.
B. Install Service Pack 4 for Windows 2000 Sever on Certkiller -SR23.

C. Upgrade the .NET Framework 1.2 to .NET Framework 2.0 on Certkiller -SR23.
D. Uninstall the .NET Framework 1.2 and install the .NET Framework 2.0 on
Certkiller -SR23.
E. Upgrade Internet Explorer 5.5 to Internet Explorer 6.0 and install Service Pack 1 for
Internet Explorer 6.0 on Certkiller -SR23.
F. Upgrade Microsoft Visual Studio 2003 to Microsoft Visual Studio 2005 and install
Service Pack 1 for Microsoft Visual Studio 2005 on Certkiller -SR23. Answer: B, D, E
Explanation: SQL Server 2005 requires Service Pack 4 for Windows 2000 Sever, Internet Explorer 6.0 with Service Pack 1 and the .NET Framework 2.0. The .NET Framework 2.0 is automatically installed with other versions of SQL Server 2005 but is not automatically installed with SQL Server 2005 Express. You can upgrade
.NET Framework 1.0 and upgrade .NET Framework 1.1 to .NET Framework 2.0
but .NET Framework 1.2 must be uninstalled first. Incorrect Answers:
A: You do not need to upgrade Certkiller -SR31 to Windows Sever 2003 Service Pack
1 as SQL Server 2005 Express can be installed on a Windows 2000 Server once you install Service Pack 4 for Windows 2000 Server.
C: You can upgrade .NET Framework 1.0 and upgrade .NET Framework 1.1 to .NET Framework 2.0 but .NET Framework 1.2 must be uninstalled first.
F: SQL Server 2005 Express does not require any version of Microsoft Visual Studio. Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: installing SQL Server Express, before installing

QUESTION 4
You are employed as a database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com.
You have received instruction from the CIO to prepare a new installation of SQL Server 2005. The new SQL Server 2005 database server is named
Certkiller -DB01. A Certkiller .com user named Mia Hamm is a member of the Marketing department. You instruct her to identify the protocols that the client computers might use to connect to the server. Mia Hamm needs to identify the protocols that are needed to accomplish this goal.
What should she identify? (Choose all that apply)

A. She should use the Multiprotocol.
B. Mia Hamm should use the Named Pipes. C. She should use the Shared Memory.
D. She should use the TCP/IP
E. Mia Hamm should use the Virtual Interface Adapter (VIA). Answer: B, D

Explanation: The clients have by default TCP and Named Pipes as available
protocols. You can make use of the SQL Server Client utility to manipulate the protocol ordering. The client application uses the protocols in the order specified on
the client computer. When you make use of the SQL Server 2005, the protocol order
is stored in the ProtocolOrder registry entry under the following registry subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0
Incorrect Answers:
C: Shared Memory can only be used on the local computer and cannot be used as a network protocol.
E: The Virtual Interface Adapter (VIA) can be used only by VIA hardware. Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: client connections [SQL Server], about client network connections, choosing a network protocol

QUESTION 5
You work as the database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named Certkiller .com. All
servers on the Certkiller .com network run Windows 2000 Server. The Certkiller .com network contains a SQL Server 2000 database server named Certkiller -DB01 that
runs on Windows 2000 Server Service Pack 4. Certkiller -DB01 contains a database named CK_Trading.
Certkiller .com wants to migrate Certkiller -DB01 to SQL Server 2005 but wants to retain the CK_Trading database with the same instance name. You decide to
perform an in-place upgrade to SQL Server 2005. You verify that
Certkiller -DB01 exceeds the minimum recommended hardware requirements for
SQL Server 2005.
What should you do next?

A. Upgrade Certkiller -DB01 to Windows Sever 2003 Service Pack 1. B. Run the SQL Server Upgrade Advisor on Certkiller -DB01.
C. Detach the CK_Trading database on Certkiller -DB01. D. Run the SQL Server 2005 Setup utility.

Answer: B

Explanation: Before you perform an in-place upgrade of an existing database, you should run the SQL Server Upgrade Advisor. The SQL Server Upgrade Advisor produces a list of items that must be addressed before and after performing the upgrade. This list of items is specific to the existing installation.
Incorrect Answers:
A: You do not need to upgrade Certkiller -DB01 to Windows Sever 2003 Service Pack
1 as SQL Server 2005 can be installed on Windows 2000 Server with Service Pack 4.
C: You need to detach a database when you need to move the database to another database instance or another database server. You do not need to detach the database before performing an in-place upgrade.
D: You should run the SQL Server Upgrade Advisor before you run the SQL Server

pass4sure 70-431
Questions and Answers : 70 Q&As + 30 Labs Expected Date: October 23rd , 2008 Price: $129.99 $89.99 Pre-Buy price: $62.99 Save $27

Free download:pass4sure MCITP 70-431 v2.93
Free download:testking MCITP 70-431 v2.93

PassGuide provides high-quality test materials, for example, Cisco CCNA CCNP CCIE, Comptia A + NETWORK + Security +, Juniper jncia, jncis, Vmware VCP-410,certification practice exams and so on.We are committed to give full refund to candidates if they fail the exam with use of our products.And we are confident to make such a guarantee. Buy Best Practice Exam,high-quality ,100% Guarantee ,Pls contact me,Mail:Sales@passguide.com

P4S Free Downloads

Type	Exam Braindumps	New Questions & Answers	Latest Updated	Available link
	All Pass4sure's Exam Pack	858	1 days ago	Download Free Testing Engines

PassGuide Braindumps-Free Test king Help You Quick Pass Any it Certifications Exams

Click links: www.testking.la/braindumps/free/down/crack/all/testking