”

SQL Server 2005 – Designing & Optimizing Data Access (70-442)
Training Course Curriculum:

Designing Microsoft SQL Server 2005
Server-Side Solutions

Module 1 – Selecting SQL Server Services to Support Business NeedsOverview of the Built-in SQL Server Services
Evaluating When to Use the New SQL Server Services
Evaluating the Use of Database Engine Enhancements 
Module 2 – Designing a Security StrategyOverview of Authentication Modes and Authorization Strategies
Designing a Security Strategy for Components of a SQL Server  2005 Solution
Designing Objects to Manage Application Access
Creating an Auditing Strategy
Managing Multiple Development Teams Using the SQL Server 2005 Security Features 
Module 3 - Designing a Data Modeling StrategyDefining Standards for Storing XML Data in a Solution
Designing a Database Solution Schema
Designing a Scale-Out Strategy 
Module 4 - Designing a Transaction Strategy for a SQL Server 2005 SolutionDefining Data Behavior Requirements
Defining Isolation Levels
Designing a Resilient Transaction Strategy 
Module 5 - Designing a Notification Services SolutionDefining Event Data
Designing a Subscription Strategy
Designing a Notification Strategy
Designing a Notification Delivery Strategy 
Module 6 - Designing a Service Broker SolutionDesigning a Service Broker Solution Architecture
Designing Service Broker Data Flow
Designing Service Broker Solution Availability 
Module 7 - Planning for Source Control| Unit Testing| and DeploymentDesigning a Source Control Strategy
Designing a Unit Test Plan
Creating a Performance Baseline and Benchmarking Strategy
Designing a Deployment Strategy 
Module 8 - Evaluating Advanced Query and XML TechniquesEvaluating Common Table Expressions
Evaluating Pivot Queries
Evaluating Ranking Queries
Overview of XQuery
Overview of Strategies for Converting Data Between XML and Relational Formats 

Designing the Data Tier for a Microsoft
SQL Server 2005

Module 1 – Choosing Data Access Technologies and an Object ModelIntroduction to Data Access Technologies
Choosing Technologies for Accessing Data
Building a Data Access Layer
Designing Data Access from SQL Common Language Runtime (CLR) Objects
Available Data Object Models for Administering SQL Server 
Module 2 – Designing an Exception Handling StrategyException Types and Their Purposes
Detecting Exceptions
Managing Exceptions 
Module 3 - Choosing a Cursor StrategyCommon Scenarios for Row-Based vs. Set-Based Operations
Selecting Appropriate Server-Side Cursors
Selecting Appropriate Client-Side Cursors 
Module 4 - Designing Query Strategies Using Multiple Active Result SetsIntroduction to MARS
Designing Query Strategies for Multiple Reads
Designing Query Strategies for Mixing Reads and Writes in the Same Connection
Concurrency Considerations When Using MARS 
Module 5 - Designing Caching Strategies for Database ApplicationsWhy Caching Is Important
Data and Query Caching in SQL Server 2005
Using Caching Technologies Outside of SQL Server
Custom Caching Techniques 
Module 6 - Designing a Scalable Data Tier for Database ApplicationsIdentifying the Need to Scale
Scaling Database Applications to Avoid Concurrency Contention
Scaling SQL Server Database Systems
Scaling Database Applications Using a Service-Oriented Architecture
Improving Availability and Scalability by Scaling Out Front-End Systems 

Tuning and Optimizing Queries using Microsoft
SQL Server 2005

Module 1 – Measuring Database PerformanceImportance of Benchmarking
Key Measures for Query Performance: Sysmon
Key Measures for Query Performance: Profiler
Guidelines for Identifying Locking and Blocking 
Module 2 – Optimizing Physical Database DesignPerformance Optimization Model
Schema Optimization Strategy: Keys
Schema Optimization Strategy: Responsible Denormalization
Schema Optimization Strategy: Generalization 
Module 3 - Optimizing Queries for PerformancePerformance Optimization Model: Queries
What Is Query Logical Flow?
Considerations for Using Subqueries 
Module 4 - Refactoring Cursors into QueriesPerformance Optimization Model: Query-Set-based solutions
Five Steps to Building a Cursor
Strategies for Refactoring Cursors 
Module 5 - Optimizing an Indexing StrategyPerformance Optimization Model: Indexes
Considerations for Using Indexes
Best Uses of the Clustered Index
Best Practices for Non-Clustered Index Design
How to Document an Indexing Strategy 
Module 6 - Managing ConcurrencyPerformance Optimization Model: Locking and Blocking
Multimedia – ‘How to Use Efficient Queries to Reduce Locking and Blocking’
Strategies to Reduce Locking and Blocking

“