Home > Courses Certifications > Data Management > Course 2783 Synopsis

2783 - Designing the Data Tier for Microsoft SQL Server 2005

Prerequisites: Before attending this course, students must have:

• Have experience reading user requirements and businessneed
  documents.
• Be familiar with the tasks that application developers typically perform.
• Understand TransactSQL
  syntax and programming logic.
• Have basic knowledge of the Microsoft .NET Framework, .NET concepts, ADO.NET, and service oriented architecture (SOA).
• Have some experience with professionallevel database design and know the tradeoffs when backing out of the fully normalized design (denormalization) and designing for performance and business requirements, in addition to being familiar with design models such as Star and Snowflake schemas.
• Have basic monitoring and troubleshooting skills.
• Have basic knowledge of the operating system and platform.
• Have basic knowledge of application architecture.
• Have a Microsoft Certified Technology Specialist: Microsoft SQL Server 2005
• credential, or equivalent experience.

Duration: 1 day

Module 1: Accessing Data with SQL Server 2005

• Accessing Data with SQL Server 2005
• Reaching The Server
• MDAC vs. SQLNCLI
• ADO.NET SqlClient
• Native XML Web Services
• Remote Data Calls
• Data Access Layers
• Data Pooling
• CLR in SQL
• SQLNCLI Objects
• Managing SQL Server Objects

Module 2: Exception Handling

• Exceptions, Bugs, Defects
• Severity Levels
• Patterns to Creating Exceptions
• How to CATCH the Exception
• Deadlocks
• What Is Done With Errors?
• Even If Code is Good…

Module 3: Cursors and Sets

• Cursors Have Their Place….
• … Yet, Not Usually in SQL Server
• So, Why Do We Create Them?
• Thoughts on Refactoring
• A Good Use for Pivots
• CTE’s Make Life Easier
• Server Side Cursors
• Client Side Cursors

Module 4: Multiple Active Result Sets

• Destination – MARS
• MARS Depends on Client Libraries
• Snapshot Isolation in MARS
• System Catalog Views
• Interleaving
• Data Manipulation Practices With MARS

Module 5: Store Your Cache for Databases Applications

• So Much Cache! Where to Begin?
• Balance the Budget
• DataSet Benefits
• Denormalization is Not a Bad Word
• Let Someone Else Hold Your Cache
• Persist Data at Client

Module 6: Serve Several Requests with a Scalable Architecture

• High Availability vs. Scalability
• Scalability Up or Out
• It’s All About Your Attitude
• Spreading the Databases
• Partition Tables & Indexes
• Distributed Data Options
• Not All Requests Are Alike
• Designing the Middle Tier
• Component Services
• Service Oriented Architecture
• Bypass the Physical Middle Man
• Scaling Out Application Servers