SQL Server 2005 > MOC 2781: Designing SQL Server 2005 Server Side Solutions

MOC 2781: Designing SQL Server 2005 Server Side Solutions


MOC 2781: Designing SQL Server 2005 Server Side Solutions

PACKAGE INCLUDES

  • 8 CD-ROMs featuring live instructor-led classroom sessions with full audio, video and demonstration components
  • Printable courseware
  • Sample Code
  • Step-by-step hands-on-labs
  • Comprehensive, understandable, and reusable as reference material


    • Single User CD Course
    £ 248
    Single User Online Course
    £ 198
    Multi User CD Course
    £ 496

    COURSE INTRODUCTION

    The Technology Specialist certifications enable professionals to target specific technologies and distinguish themselves by demonstrating in-depth knowledge and expertise in their specialized technologies.

    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.

    Course Outline


    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
    Page Execution time is 6.063 seconds.