MCITP Self-Paced Training Kit (Exam 70-442): Designing and Optimizing Data Access by Using Microsoft SQL Server™ 2005

0.0

This book does not come with a CD This is an all-new self-paced training kit designed to help maximize your performance on 70-442, one of the required exams for the Microsoft Certified IT Professional (MCITP) Database Developer certification. This 2-in-1 kit includes the official Microsoft study guide. Work at your own pace through the lessons and lab exercises. Focusing on designing and implementing database solutions in a professional job role, this official study guide covers topics such as designing data access technologies, writing and modifying queries, validating input data and handling errors, developing a transaction strategy, and optimizing performance. Table of Contents Introduction Hardware Requirements Software Requirements o Installing SQL Server 2005 o Installing Visual Studio 2005. o Installing the AdventureWorks Database o Installing Microsoft Web Application Stress Tool Case Scenarios o Case Scenarios and the 70-442 Exam o Case Scenario Structure Microsoft Certified Professional Program Technical Support. Evaluation Edition Software Support 1 Designing a Data Access Strategy Before You Begin Lesson 1: Designing Data Access Technologies. o Selecting a Network Protocol o Selecting a Data Provider o How to Connect by Using ADO.NET o How to Connect by Using MDAC o How to Connect by Using SQL Native Client o Managing Password Policies o Lab: Connecting to a SQL Server Service Lesson 2: Designing a Data Access Connection o Designing Connections for Heterogeneous Client Computers o Designing Connections for HTTP Web Services o Designing Connections for Another SQL Server Instance o Lab: Creating and Consuming HTTP Endpoints. Lesson 3: Designing a Data Access Object Model o What is the ADO Object Model? o Using a Disconnected Model with ADO.NET o Using a Connected Model with ADO.NET o Lab: Selecting a Data Access Object Model Case Scenario: Selecting a Data Access Strategy Suggested Practices References Chapter Summary. 2 Designing Database Queries Before You Begin Lesson 1: Writing Database Queries o Write SELECT Queries. o Writing Full-Text Queries o Lab: Writing Database Queries Lesson 2: Improving Query Performance o Showing the Execution Plan o Using Query Hints. o Using Plan Guides. o Using Searchable Arguments o Lab: Tuning Queries Lesson 3: Retrieving Data from XML Sources o Working with XML Data o Lab: Working with XML Data Case Scenario: Creating a Plan Guide Suggested Practices References Chapter Summary 3 Designing a Cursor and Caching Strategy Before You Begin Lesson 1: Designing Caching Strategies o What is Output Caching? o Designing Custom Caching Functionality o Using Query Notifications. o Designing a Refresh Strategy o Lab: Implementing Output Caching Lesson 2: Designing a Cursor Strategy o Considering Cursor Alternatives o Maximizing Cursor Performance o Evaluating Use of Cursors o Lab: Creating a Cursor Lesson 3: Designing Efficient Cursors o Using Scrollable Cursors o Processing on a Row-by-Row Basis o Using Dynamic SQL o Selecting a Cursor Type o Evaluating Cursor Efficiency o Lab: Designing Cursors Case Scenario: Evaluating Cursor Performance. Suggested Practices References Chapter Summary 4 Advanced Query Topics. Before You Begin Lesson 1: Administering a SQL Server Service o Designing Server Management Objects Applications o Designing Replication Management Objects Applications o Designing Analysis Management Objects Applications o Lab: Administrative Programming Lesson 2: Querying with Multiple Active Result Sets o Using MARS o Using Transactions o Lab: Creating MARS Connections. Lesson 3: Performing Asynchronous Processing o Using Asynchronous Processing o Lab: Performing Asynchronous Processing. Case Scenario: Automating a Data-Mining Solution Suggested Practices References Chapter Summary 5 Data Integrity and Error Handling in SQL Server 2005 Before You Begin Lesson 1: Validating Data and Permissions o Implementing Declarative Data Integrity o Implementing Procedural Data Integrity o Using T-SQL to Verify Permissions o Lab: Comparing Procedural and Declarative o Data Validation Performance Lesson 2: Detecting and Reacting to Errors o Techniques for Detecting and Reacting to Errors o Creating User-Defined Messages o Lab: Using Try/Catch Blocks Case Scenario: Validating Data Integrity Suggested Practices Reference Chapter Summary 6 Designing Transactions and Transaction Isolation Before You Begin Lesson 1: Understanding the Available Transaction Isolation Levels. o Types of Concurrency Problems. o Using Locks to Solve Concurrency Problems o Choosing the Correct Isolation Level o Avoiding Extensive Blocking. o Lab: Comparing Transaction Isolation Levels Lesson 2: Designing Transactions and Optimizing Locking o Optimizing Locking o Minimizing Deadlocks o Working with Distributed Transactions o Lab: Designing Code That Uses Transactions. Case Scenario: Optimizing Locking Suggested Practices References Chapter Summary 7 Optimizing SQL Server 2005 Performance. Before You Begin Lesson 1: Optimizing and Tuning Queries. o Evaluating Query Performance o Optimizing Query Performance o Lab: Comparing Query Performance Lesson 2: Optimizing Index Strategies o Improving Performance with Covered Indexes o Using Included Columns and Index Depth o Using Clustered Indexes o Read Performance vs. Write Performance o Using Computed Columns o Using Indexed Views o Analyzing Index Usage. o Lab: Optimizing Queries Through Indexing Lesson 3: Optimizing Data Storage o Optimizing Row Width. o De-Normalizing. o Data Types o Lab: De-Normalizing an Aggregation Case Scenario: Optimizing Query Performance Suggested Practices References Chapter Summary. 8 Improving Database Application Performance Before You Begin Lesson 1: Scale Database Applications o Specifying a Data-Partitioning Model o Targeting Multiple Servers. o Implementing Scale-Out Techniques o Transparently Distributing Data and Workload o Moving Code to a Different Tier o Rewriting Algorithms o Lab: Scaling Database Applications Lesson 2: Resolving Performance Problems o Analyzing Application Performance o Investigating Performance Issues o Lab: Analyzing Performance Case Scenario: Diagnosing a Performance Problem Suggested Practices References Chapter Summary. Answers A References o Design Appropriate Data Access Technologies o Design an Appropriate Data Access Object Model. o Write Queries o Design Caching Strategies o Design a Cursor Strategy o Design Applications that Administer a SQL Server Service o Scale Database Applications. o Resolve Performance Problems o Manage Concurrency by Selecting the Appropriate o Transaction Isolation Levels o Design Code that Uses Transactions o Optimize and Tune Queries for Performance o Design Code that Validates Input Data and Permissions B SQL Server 2005 Architecture and Internals Hardware Trends and Their Effect on SQL Server 2005 o Hardware Building Blocks o Hardware Trends o Implications of Hardware on Software Design SQLOS o SQLOS Architecture o Using SQLOS to Determine Application Performance Characteristics Common Language Runtime (CLR) Host o Design Goals for the CLR Host o CLR Services o CLR Database Object Types o CLR vs. Transact-SQL o Deciding When to Use CLR Code SQL Server 2005 Storage Engine o Storage Engine Architecture and Terminology o SQL Server Database Engine Physical Operations. SQL Server 2005 Query Engine o Query Engine Processing. SQL Server 2005 Index Internals o SQL Server 2005 Index Organization o Full-Text Indexes o XML Indexes. Database Encryption o Certificates o Configuring SQL Server for SSL Glossary. Index