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


This comprehensive book will guide readers through Microsoft's brand new SQL 2005 certification, Certified Technology Specialist: SQL Server 2005 (exam: 70-431). This new exam is both a one-exam certification, granting students the Technology Specialist designation, and an entry exam for both the IT Professional Developer and IT Professional Administrator certifications. This entry-level exam gives readers a solid introduction to both the development and administrative aspects ...

See more details below
Paperback (BK&CD-ROM)
$49.99 price
Other sellers (Paperback)
  • All (14) from $1.99   
  • New (6) from $29.05   
  • Used (8) from $1.99   
Sending request ...


This comprehensive book will guide readers through Microsoft's brand new SQL 2005 certification, Certified Technology Specialist: SQL Server 2005 (exam: 70-431). This new exam is both a one-exam certification, granting students the Technology Specialist designation, and an entry exam for both the IT Professional Developer and IT Professional Administrator certifications. This entry-level exam gives readers a solid introduction to both the development and administrative aspects of SQL Server 2005.

Read More Show Less

Product Details

  • ISBN-13: 9780470025659
  • Publisher: Wiley
  • Publication date: 7/19/2006
  • Edition description: BK&CD-ROM
  • Edition number: 1
  • Pages: 634
  • Product dimensions: 7.50 (w) x 9.25 (h) x 1.36 (d)

Meet the Author

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

Dandy Weyn, MCTS, MCSE, MCSA, MCDBA, MCDST, MCITP, is anindependent SQL Server technologist based in Belgium, who deliverstraining and consultancy all over the world. He is a frequentspeaker at Microsoft seminars and has more than ten yearsexperience with relational databases in enterpriseenvironments.

Read More Show Less

Table of Contents


Assessment Test.

Chapter 1 Installing Microsoft SQL Server 2005.

Meeting the Prerequisites.

Preparing to Install.

Choosing Default Instances or Named Instances.

Choosing Service Accounts.

Selecting an Authentication Mode.

Choosing a Collation Setting.

Upgrading from a Previous Version.

Installing SQL Server 2005.

Installing a Second Instance.

Troubleshooting the Installation.

Chapter 2 Creating and Configuring Databases.

Planning Your Database.

Introducing Database Files.

Introducing Filegroups.

Deciding on Database File Placement.

Introducing RAID-0.

Introducing RAID-1.

Introducing RAID-5.

Introducing RAID-10.

Creating Data Storage Structures.

Introducing Extents.

Introducing Pages.

Estimating Storage Requirements.

Estimating Table Storage Requirements.

Estimating Index Storage Requirements.

Creating and Configuring Databases.

Creating a Database.

Gathering Information about Your Database.

Setting Database Options.

Chapter 3 Working with Tables and Views.

Planning Tables.

Introducing Built-in Datatypes.

Introducing Computed Columns.

Creating Tables.

Restricting the Data.

Introducing Constraints.

Partitioning Tables.

Understanding Views.

Modifying Data through a View.

Working with Indexed Views.

Chapter 4 Performing Indexing and Full-TextSearching.

Understanding Index Architecture.

Understanding Heaps.

Understanding Clustered Indexes.

Understanding Nonclustered Indexes.

Creating Indexes.

Using Primary Keys.

Using Full-Text Searching.

Chapter 5 Introducing More Database Objects.

Introducing Stored Procedures.

Understanding the Types of Stored Procedures.

Creating Stored Procedures.

Recompiling Stored Procedures.

Introducing Triggers.

Understanding the Types of Triggers.

Understanding DML Triggers.

Understanding DDL Triggers.

Understanding Trigger Recursion and Nesting.

Understanding Disabling Triggers.

Understanding Event Notifications.

Introducing Functions.

Understanding the Types of Functions.

Using Scalar Functions.

Introducing Table-Valued Functions.

Introducing Built-in Functions.

Introducing CLR Functions.

Introducing Deterministic and Nondeterministic Functions.

Introducing User-Defined Types.

Creating T-SQL User-Defined Types.

Creating CLR User-Defined Types.

Getting More CLR Functionality.

Chapter 6 Implementing Security in SQL Server 2005.

Understanding Security Modes 190

Using Windows Authentication Mode 190

Using Mixed Mode 191

Setting the Authentication Mode 191

Understanding SQL Server Logins 193

Using Standard Logins 193

Using Windows Logins 196

Understanding the Items Common to All Logins 199

Understanding Fixed Server Roles 199

Creating Database User Accounts 202

Understanding Permissions 204

Applying Statement Permissions 205

Applying Object Permissions 207

Understanding Database Roles 210

Using Fixed Database Roles 210

Using Custom Database Roles 212

Using Application Roles 215

Understanding Permission States 217

Granting a Permission 217

Revoking a Permission 217

Denying a Permission 217

Introducing Ownership Chains 221

Introducing Linked Server Security 223

Introducing Encryption 224

Creating a Security Plan.

Chapter 7 Working with Relational Data.

Understanding and Using Transactions 242

Executing Implicit and Explicit Transactions 243

Committing and Rolling Back 244

Executing Distributed Transactions 246

Populating Tables 247

Importing Data Using Bulk Insert 247

Importing Data Using the bcp Utility 248

Copying Data Using SSIS 250

Bulk Inserting XML Data 252

Supporting the Bulk-Logged Recovery Model 253

Supporting Different Collation Types and Orders

When Querying Data 255

Formatting and Converting Datatypes 256

Casting and Converting 261

Understanding Datatype Precedence 264

Understanding Collations 265

Introducing Error Handling 266


Using @@ERROR

Using Error Messages 267




Chapter 8 Working with XML Data.

Understanding XML Data 282

Using the xml Datatype 283

Using Untyped XML 284

Using Typed XML 285

Working with XML Schema 285

Querying XML Data 288

Using the query Method.

Using the value Method.

Using the exist Method.

Using the modify Method.

Using the nodes Method.

Creating XML Indexes.

Chapter 9 Working with Service Broker and HTTP.

Understanding the SQL Server Service Broker Architecture.

Working with Service Broker.

Creating a Message Type.

Creating a Queue.

Creating a Contract.

Creating a Service.

Creating a Route.

Using Service Broker.

Sending Messages.

Receiving Messages.

Automating the Queue Processing.

Introducing HTTP Endpoints.

Configuring HTTP Endpoints.

Securing HTTP Endpoints.

Chapter 10 Maintaining and Automating SQL Server.

Maintaining Indexes.


Reorganizing and Rebuilding Indexes.

Maintaining Statistics.

Maintaining Databases.

Understanding DBCC CHECKDB.

Shrinking Files.

Understanding Automation Basics.

Configuring Database Mail.

Creating Operators.

Creating Jobs.

Creating Alerts.

Creating Event Alerts Based on Standard Errors.

Creating Event Alerts Based on Custom Errors.

Creating Performance Alerts.

Creating WMI Alerts.

Using the Maintenance Plan Wizard.

Copying Databases.

Chapter 11 Performing Backups and Restores.

Backing Up Your Data.

Understanding How Backups Work.

Creating a Backup Device.

Performing Full Backups.

Performing Differential Backups.

Performing Transaction Log Backups.

Performing Filegroup Backups.

Backing Up to Multiple Devices.

Restoring Databases.

Performing Standard Restores.

Performing Point-in-Time Restores.

Performing Piecemeal Restores.

Devising a Backup Strategy.

Planning for Full Backups Only.

Planning for Full with Differential Backups.

Planning for Full with Transaction Log Backups.

Planning for Full, Differential, and Transaction LogBackups.

Planning for Filegroup Backups.

Chapter 12 Achieving High Availability throughReplication.

Introducing Replication.

Introducing the Publisher/Subscriber Metaphor.

Introducing Articles.

Introducing Publications.

Understanding Replication Factors and Distribution Types.

Using Distributed Transactions.

Using Transactional Replication.

Using Transactional Replication with Immediate UpdatingSubscribers.

Using Snapshot Replication.

Using Snapshot Replication with Immediate UpdatingSubscribers.

Using Merge Replication.

Using Queued Updating.

Understanding Replication Internals.

Understanding Merge Replication.

Understanding Snapshot Replication.

Understanding Transactional Replication.

Considering Publication Issues.

Considering Distributor Issues.

Introducing Replication Models.

Introducing Central Publisher/Central Distributor.

Introducing Remote Distribution.

Introducing Central Subscriber/Multiple Publishers.

Introducing Multiple Publishers/Multiple Subscribers.

Replicating over the Internet and to Heterogeneous DatabaseServers.

Using Heterogeneous Replication.

Using Internet Replication.

Installing and Using Replication.

Configuring SQL Server for Replication.

Installing a Distribution Server.

Adding a Publication.

Creating a Subscription.

Testing Replication.

Managing Replication.

Considering Administrative Issues.

Considering Replication Backup Issues.

Using the Replication Monitor.

Working with Replication Scripts.

Enhancing Replication Performance.

Chapter 13 Introducing More High-AvailabilityMethods.

Choosing the High-Availability Features You Need.

Implementing Database Mirroring.

Understanding Database Mirroring Concepts.

Preparing for Mirroring.

Creating Endpoints.

Specifying Partners and Witnesses.

Configuring the Operating Mode.

Switching Roles.

Implementing Log Shipping.

Monitoring the Server.

Configuring Log Shipping.

Changing Roles.

Monitoring Log Shipping.

Managing Database Snapshots.

Creating a Snapshot.

Reverting from a Snapshot.

Chapter 14 Monitoring and Optimizing SQL Server 2005.

Using System Monitor.

Monitoring with SQL Profiler.

Replaying a Trace File.

Using the Database Engine Tuning Advisor.

Troubleshooting SQL Server.

Reading Error and Event Logs.

Troubleshooting Blocks and Deadlocks.

Troubleshooting Jobs.

Using the Dedicated Administrator Connection.



Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)