MOC-2781: Designing Microsoft SQL Server 2005 Server-Side Solutions Training

MOC 2781 - Designing Microsoft SQL Server 2005 Server-Side Solutions Training Package Includes

• 8 CD-ROMs featuring live instructor-led classroom sessions with full audio, video and demonstration components
• Printable courseware
• Step-by-step hands-on-labs
• Microsoft Authorised MeasureUp Exam Simulations
• Focused on practical solutions to real-world development problems

Course Introduction

This video-based course provides students with the knowledge and skills to design server-side solutions for Microsoft SQL Server 2005. The course focuses on teaching database developers who work in enterprise environments to identify and place database technologies during design to achieve a suitable solution that meets the needs of an organization. Students will also learn to consider the solution from a system-wide view instead of from a single database or server perspective.

• Exam 70–441 : Designing Database Solutions by Using Microsoft SQL Server 2005 Open License 6.0
• Exam 70–442 : Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

Keith Nicholson - SQL 2005 Trainer (MCT, MCDBA, MCSD.NET, MCSE Security, CEH, Security+) Keith Nicholson is an engaging international speaker and trainer focusing on .NET development and database technologies. A year ago, Keith completed a multi-city tour and introduced the new features and benefits of .NET 2.0. Prior to founding KSDynamics, Keith architected and created some award-winning applications using .NET and SQL Server. He is the INETA Membership Manager for user groups in Arkansas, Oklahoma and North Texas. Keith has been a Microsoft MVP for over 2 years in .NET development (C#). He enjoys sharing his enthusiasm and helps others gain a love for technology and its uses.

List of Course Features

MOC 2781 - Designing Microsoft SQL Server 2005 Server-Side Solutions Course Contents
 

Module 1 - Server Solutions with SQL Server 2005

Server Solutions with SQL Server 2005
There is More to SQL Server Than Just SQL Server
Full-Text Services
Native XML Web Services
Replication Concerns
SQL Server Agent
New Database Mail
Demo - Turning on Database Mail
Notification Services
Service Broker
Reporting Services
SQL Server Integration Services
T-SQL Enhancements
Demo - Ranking Function
.NET CLR
SQLXML
Review

Module 2 - Security – Its More Than Just a Password

Security – Its More Than Just a Password
Security Concerns
Demo - SQL Injection
Who Are You?
Preferred Approach Toward Securing Native Web Services
Preferred Approach Toward Code Access Security
Preferred Approach Toward Reporting Services Security
Preferred Approach Toward Notification Services Security
Preferred Approach Toward Integration Services Security
Preferred Approach Toward Replication Security
Preferred Approach Toward Agent and Database Mail Security
Protect Objects With Roles and Schemas
Demo - Revoke Is Not Enough
Execution Context
Protect Stored Procedures, Views, and User Defined Functions
Protect Columns
How to Audit SQL Server
Protect your Audit
Not All Employees Are Equal
Demo - Remove Built-in Admins
Use DDL Triggers for Audit Information
Demo - Protect Tables From Oops!!
Review

Module 3 - Plan for the Future by Planning for Today

Plan for the Future by Planning for Today
Using XML Requires Forethought
XML Has Redundant Data
How to Store – Character Type vs. New XML Type
Demo -What Impact Does XML Storage Have on Storage?
Model Your Database Solution
Balancing with OLAP
Rules of Normalization and When
Can I Index to Often?
Demo - Include Implications
Clustered Indexes on Non-Primary Keys
Scalability Up or Out
Spreading the Databases
Partition Tables and Indexes
Redundant Solutions with Scaling Out
Scaling Up
Review

Module 4 - Transaction – Concurrency and Consistency

Transaction – Concurrency and Consistency
Exploring Data Phenomenon
Isolation Levels Options
Demo - Connection and Database Options for Row Versioning
Change Data Processing Plans
Balance Locks and Performance as a Result of Errors
Accessing Objects
Rollback Transactions Correctly
Long Running Transactions
Hints or Directives
Review

Module 5 - Defining a Push Solution with Notification Services

Defining a Push Solution with Notification Services
Where and How to Store Data
What is an Event Schema
How does Indexing Help?
Archiving Events
Demo - Subscription Classes
Plan Toward a Subscription Schema
Plan the Rules
Plan the Subscription Indexes
Maintain Subscriptions
Demo - Event Classes
Demo - Notification Classes
Define the Notification
Plan the Notification Indexes
Plan Toward Notification Delivery
Protocol Planning
Setting Execution Settings
Review

Module 6 - Reliable Messaging with Service Broker

Reliable Messaging with Service Broker
Why Use Service Broker?
Locate Services
Locate Conversations
Plans for Dialogs
Plan for Queue Usage
Demo - Preparing Communication
Plan Toward Good Data Flow
Locate Data Staging
Locate Routes
Demo - Messaging
Plan for Activating Methods
Backup Planning
Review

Module 7 - Protect, Test, and Execute

Protect, Test, and Execute
Source Control Saves Time and Effort
Demo - Using Visual SourceSafe
Plan for Source Control
Protect Intellectual Property
Unit Tests Provide Low Level Confidence
Planning for Unit Tests
Perform Iterative Development
Compare Performance to Known Levels
Know What To Measure
Know How To Measure
Demo - Combining SQL Profiler and PerfMon
Adapting to Change
Put Services Where They Need to Be
Plan for Deployment
Module 7 Review

Module 8 - T-SQL Programming Enhancements

T-SQL Programming Enhancements
Common Table Expressions
Performance Gains over Sub-Queries?
Demo - Using CTEs
Pivot Queries
Demo - Pivot Queries at Work
Ranking Queries
Demo - Ranking Query Usages
XQueries and FLWORs
Planning for XQueries
Follow the Path
WHERE Matters
Steps in Converting Data
To XML…
…And Back Again
Review
Course Closure