MCTS: Microsoft SQL Server 2005 Implementation and Maintenance Study Guide Exam 70-431,9780470025659

MCTS: Microsoft SQL Server 2005 Implementation and Maintenance Study Guide Exam 70-431

by ;
Edition: 1st
ISBN13: 9780470025659
ISBN10: 0470025654
Format: Paperback
Pub. Date: 2006-07-19
Publisher(s): Sybex
  • Free Shipping Icon

    This Item Qualifies for Free Shipping!*

    *Excludes marketplace orders.

List Price: $52.49

Buy New

Arriving Soon. Will ship when available.
$49.99
Add to Cart

Rent Book

Select for Price
Add to Cart
There was a problem. Please try again later.

Used Book

We're Sorry
Sold Out

eBook

We're Sorry
Not Available

Summary

With the release of SQL Server 2005, Microsoft is introducing a new multi-exam certification program. The Microsoft SQL Server 2005 Implementation and Maintenance exam (70-431) is the first stop for everyone entering this new certification track, and serves as both a single exam certification as well as the entry exam for the MCITP-level certifications. This book provides an introduction to the development and administrative aspects of SQL Server 2005 and features practical guidance for all aspects of the exam. Get a great start in your certification process today with this comprehensive guide which includes: Hundreds of challenging practice questions plus two bonus exams Leading-edge exam preparation software, including a test engine Authoritative coverage of all key exam objectives, including: Installing and Configuring SQL Server 2005 Implementing High Availability and Disaster Recovery Supporting Data Consumers Maintaining Databases Monitoring and Troubleshooting SQL Server Performance Creating and Implementing Database Objects Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Author Biography

Joseph L. Jorden, MCTS, MCSE, has been administering and developing databases for the last twelve years. Currently he is the Lead Developer for the Jelly Belly Candy Company.

Dandy Weyn, MCTS, MCSE, MCSA, MCDBA, MCDST, MCITP, is an independent SQL Server technologist based in Belgium, who delivers training and consultancy all over the world. He is a frequent speaker at Microsoft seminars and has more than ten years experience with relational databases in enterprise environments.

Table of Contents

Introduction xxi
Assessment Test xxviii
Installing Microsoft SQL Server 2005
1(30)
Meeting the Prerequisites
2(3)
Preparing to Install
5(3)
Choosing Default Instances or Named Instances
5(1)
Choosing Service Accounts
6(1)
Selecting an Authentication Mode
6(1)
Choosing a Collation Setting
7(1)
Upgrading from a Previous Version
8(2)
Installing SQL Server 2005
10(9)
Installing a Second Instance
19(3)
Troubleshooting the Installation
22(1)
Summary
23(1)
Exam Essentials
23(1)
Review Questions
24(4)
Answers to Review Questions
28(3)
Creating and Configuring Databases
31(30)
Planning Your Database
32(2)
Introducing Database Files
32(1)
Introducing Filegroups
33(1)
Deciding on Database File Placement
34(2)
Introducing RAID-0
35(1)
Introducing RAID-1
35(1)
Introducing RAID-5
35(1)
Introducing RAID-10
35(1)
Creating Data Storage Structures
36(2)
Introducing Extents
36(1)
Introducing Pages
37(1)
Estimating Storage Requirements
38(4)
Estimating Table Storage Requirements
38(3)
Estimating Index Storage Requirements
41(1)
Creating and Configuring Databases
42(8)
Creating a Database
42(2)
Gathering Information about Your Database
44(2)
Setting Database Options
46(4)
Summary
50(1)
Exam Essentials
51(1)
Review Questions
52(7)
Answers to Review Questions
59(2)
Working with Tables and Views
61(46)
Planning Tables
62(5)
Introducing Built-in Datatypes
63(3)
Introducing Computed Columns
66(1)
Creating Tables
67(5)
Restricting the Data
72(10)
Introducing Constraints
73(7)
Partitioning Tables
80(2)
Understanding Views
82(2)
Modifying Data through a View
84(4)
Working with Indexed Views
88(5)
Summary
93(1)
Exam Essentials
94(2)
Review Questions
96(9)
Answers to Review Questions
105(2)
Performing Indexing and Full-Text Searching
107(34)
Understanding Index Architecture
108(14)
Understanding Heaps
109(2)
Understanding Clustered Indexes
111(5)
Understanding Nonclustered Indexes
116(6)
Creating Indexes
122(1)
Using Primary Keys
123(3)
Using Full-Text Searching
126(7)
Summary
133(1)
Exam Essentials
133(1)
Review Questions
134(5)
Answers to Review Questions
139(2)
Introducing More Database Objects
141(48)
Introducing Stored Procedures
142(8)
Understanding the Types of Stored Procedures
142(3)
Creating Stored Procedures
145(4)
Recompiling Stored Procedures
149(1)
Introducing Triggers
150(16)
Understanding the Types of Triggers
150(1)
Understanding DML Triggers
151(3)
Understanding DDL Triggers
154(4)
Understanding Trigger Recursion and Nesting
158(1)
Understanding Disabling Triggers
159(1)
Understanding Event Notifications
159(7)
Introducing Functions
166(6)
Understanding the Types of Functions
166(1)
Using Scalar Functions
166(1)
Introducing Table-Valued Functions
167(1)
Introducing Built-in Functions
167(2)
Introducing CLR Functions
169(2)
Introducing Deterministic and Nondeterministic Functions
171(1)
Introducing User-Defined Types
172(3)
Creating T-SQL User-Defined Types
172(1)
Creating CLR User-Defined Types
173(1)
Getting More CLR Functionality
174(1)
Summary
175(1)
Exam Essentials
175(1)
Review Questions
176(9)
Answers to Review Questions
185(4)
Implementing Security in SQL Server 2005
189(52)
Understanding Security Modes
190(3)
Using Windows Authentication Mode
190(1)
Using Mixed Mode
191(1)
Setting the Authentication Mode
191(2)
Understanding SQL Server Logins
193(6)
Using Standard Logins
193(3)
Using Windows Logins
196(3)
Understanding the Items Common to All Logins
199(1)
Understanding Fixed Server Roles
199(3)
Creating Database User Accounts
202(2)
Understanding Permissions
204(6)
Applying Statement Permissions
205(2)
Applying Object Permissions
207(3)
Understanding Database Roles
210(7)
Using Fixed Database Roles
210(2)
Using Custom Database Roles
212(3)
Using Application Roles
215(2)
Understanding Permission States
217(4)
Granting a Permission
217(1)
Revoking a Permission
217(1)
Denying a Permission
217(4)
Introducing Ownership Chains
221(2)
Introducing Linked Server Security
223(1)
Introducing Encryption
224(1)
Creating a Security Plan
225(2)
Summary
227(2)
Exam Essentials
229(1)
Review Questions
230(8)
Answers to Review Questions
238(3)
Working with Relational Data
241(40)
Understanding and Using Transactions
242(5)
Executing Implicit and Explicit Transactions
243(1)
Committing and Rolling Back
244(2)
Executing Distributed Transactions
246(1)
Populating Tables
247(5)
Importing Data Using Bulk Insert
247(1)
Importing Data Using the bcp Utility
248(2)
Copying Data Using SSIS
250(2)
Bulk Inserting XML Data
252(1)
Supporting the Bulk-Logged Recovery Model
253(2)
Supporting Different Collation Types and Orders When Querying Data
255(11)
Formatting and Converting Datatypes
256(5)
Casting and Converting
261(3)
Understanding Datatype Precedence
264(1)
Understanding Collations
265(1)
Introducing Error Handling
266(6)
Using RAISERROR
266(1)
Using @@ERROR
267(1)
Using Error Messages
267(1)
Using TRY...CATCH Blocks
268(4)
Summary
272(1)
Exam Essentials
272(2)
Review Questions
274(4)
Answers to Review Questions
278(3)
Working with XML Data
281(30)
Understanding XML Data
282(1)
Using the xml Datatype
283(2)
Using Untyped XML
284(1)
Using Typed XML
285(1)
Working with XML Schema
285(3)
Querying XML Data
288(7)
Using the query Method
288(1)
Using the value Method
289(1)
Using the exist Method
290(1)
Using the modify Method
290(1)
Using the nodes Method
291(4)
Creating XML Indexes
295(2)
Summary
297(1)
Exam Essentials
297(1)
Review Questions
298(10)
Answers to Review Questions
308(3)
Working with Service Broker and HTTP
311(36)
Understanding the SQL Server Service Broker Architecture
312(1)
Working with Service Broker
313(5)
Creating a Message Type
313(1)
Creating a Queue
314(1)
Creating a Contract
315(1)
Creating a Service
316(1)
Creating a Route
317(1)
Using Service Broker
318(10)
Sending Messages
318(3)
Receiving Messages
321(2)
Automating the Queue Processing
323(5)
Introducing HTTP Endpoints
328(6)
Configuring HTTP Endpoints
329(5)
Securing HTTP Endpoints
334(1)
Summary
334(2)
Exam Essentials
336(1)
Review Questions
337(8)
Answers to Review Questions
345(2)
Maintaining and Automating SQL Server
347(68)
Maintaining Indexes
348(6)
Understanding sys.DM_DB_INDEX_PHYSICAL_STATS
349(3)
Reorganizing and Rebuilding Indexes
352(2)
Maintaining Statistics
354(1)
Maintaining Databases
355(4)
Understanding DBCC CHECKDB
355(2)
Shrinking Files
357(2)
Understanding Automation Basics
359(2)
Configuring Database Mail
361(5)
Creating Operators
366(3)
Creating Jobs
369(7)
Creating Alerts
376(13)
Creating Event Alerts Based on Standard Errors
377(5)
Creating Event Alerts Based on Custom Errors
382(3)
Creating Performance Alerts
385(2)
Creating WMI Alerts
387(2)
Using the Maintenance Plan Wizard
389(10)
Copying Databases
399(6)
Summary
405(1)
Exam Essentials
406(1)
Review Questions
407(6)
Answers to Review Questions
413(2)
Performing Backups and Restores
415(48)
Backing Up Your Data
416(22)
Understanding How Backups Work
417(1)
Creating a Backup Device
418(1)
Performing Full Backups
419(4)
Performing Differential Backups
423(3)
Performing Transaction Log Backups
426(2)
Performing Filegroup Backups
428(6)
Backing Up to Multiple Devices
434(4)
Restoring Databases
438(10)
Performing Standard Restores
439(4)
Performing Point-in-Time Restores
443(3)
Performing Piecemeal Restores
446(2)
Devising a Backup Strategy
448(4)
Planning for Full Backups Only
448(1)
Planning for Full with Differential Backups
449(1)
Planning for Full with Transaction Log Backups
450(1)
Planning for Full, Differential, and Transaction Log Backups
451(1)
Planning for Filegroup Backups
451(1)
Summary
452(1)
Exam Essentials
453(1)
Review Questions
454(6)
Answers to Review Questions
460(3)
Achieving High Availability through Replication
463(58)
Introducing Replication
464(1)
Introducing the Publisher/Subscriber Metaphor
465(2)
Introducing Articles
466(1)
Introducing Publications
467(1)
Understanding Replication Factors and Distribution Types
467(5)
Using Distributed Transactions
468(1)
Using Transactional Replication
469(1)
Using Transactional Replication with Immediate Updating Subscribers
469(1)
Using Snapshot Replication
470(1)
Using Snapshot Replication with Immediate Updating Subscribers
470(1)
Using Merge Replication
470(1)
Using Queued Updating
471(1)
Understanding Replication Internals
472(6)
Understanding Merge Replication
473(3)
Understanding Snapshot Replication
476(1)
Understanding Transactional Replication
476(2)
Considering Publication Issues
478(1)
Considering Distributor Issues
479(1)
Introducing Replication Models
480(4)
Introducing Central Publisher/Central Distributor
480(1)
Introducing Remote Distribution
481(1)
Introducing Central Subscriber/Multiple Publishers
482(1)
Introducing Multiple Publishers/Multiple Subscribers
483(1)
Replicating over the Internet and to Heterogeneous Database Servers
484(1)
Using Heterogeneous Replication
484(1)
Using Internet Replication
484(1)
Installing and Using Replication
485(18)
Configuring SQL Server for Replication
485(1)
Installing a Distribution Server
486(4)
Adding a Publication
490(6)
Creating a Subscription
496(6)
Testing Replication
502(1)
Managing Replication
503(8)
Considering Administrative Issues
504(1)
Considering Replication Backup Issues
504(1)
Using the Replication Monitor
505(3)
Working with Replication Scripts
508(1)
Enhancing Replication Performance
509(2)
Summary
511(1)
Exam Essentials
512(1)
Review Questions
513(6)
Answers to Review Questions
519(2)
Introducing More High-Availability Methods
521(36)
Choosing the High-Availability Features You Need
522(2)
Implementing Database Mirroring
523(1)
Understanding Database Mirroring Concepts
524(8)
Preparing for Mirroring
525(1)
Creating Endpoints
526(3)
Specifying Partners and Witnesses
529(1)
Configuring the Operating Mode
530(1)
Switching Roles
531(1)
Implementing Log Shipping
532(7)
Monitoring the Server
533(1)
Configuring Log Shipping
533(5)
Changing Roles
538(1)
Monitoring Log Shipping
538(1)
Managing Database Snapshots
539(8)
Creating a Snapshot
540(3)
Reverting from a Snapshot
543(4)
Summary
547(1)
Exam Essentials
548(1)
Review Questions
549(6)
Answers to Review Questions
555(2)
Monitoring and Optimizing SQL Server 2005
557(38)
Using System Monitor
559(6)
Monitoring with SQL Profiler
565(11)
Replaying a Trace File
570(2)
Using the Database Engine Tuning Advisor
572(4)
Troubleshooting SQL Server
576(7)
Reading Error and Event Logs
577(2)
Troubleshooting Blocks and Deadlocks
579(3)
Troubleshooting Jobs
582(1)
Using the Dedicated Administrator Connection
583(2)
Summary
585(2)
Exam Essentials
587(1)
Review Questions
588(5)
Answers to Review Questions
593(2)
Glossary 595(14)
Index 609

An electronic version of this book is available through VitalSource.

This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.

By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.

Digital License

You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.

More details can be found here.

A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.

Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.

Please view the compatibility matrix prior to purchase.