MICROSOFT

Designing the Data Tier for Microsoft SQL Server 2005 (Course Code: 2783)

Course Schedule:

Please call 8622 8900 or email us for further information

Overview:

This one-day instructor-led course will provide you with the knowledge and skills to design the data tier for Microsoft SQL Server 2005. The clinic focuses on teaching database developers working in enterprise environments to understand and decide how application developers are going to access and consume their data. This is a major failure point of database solutions today.

Who will benefit from this course?

This course is intended for current professional database developers who have three or more years of on-the-job experience developing SQL Server database solutions in an enterprise environment.

Pre-requisites

Before attending, you must:

have experience reading user requirements and business-need documents. For example, development project vision/mission statements or business analysis reports
have basic knowledge of the Microsoft .NET Framework, .NET concepts, ADO. NET, and service oriented architecture (SOA)
be familiar with the tasks that application developers typically perform
understand Transact-SQL syntax and programming logic
have some experience with professional-level database design and know the tradeoffs when backing out of the fully normalised design (denormalisation) 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. Specifically, how to use SQL Profiler and dynamic management views
have basic knowledge of the operating system and platform. That is, how the operating system integrates with the database, what the platform or operating system can do, and how interaction between the operating system and the database works
have basic knowledge of application architecture. That is, how applications can be designed in three layers, what applications can do, how interaction between the application and the database works, and how the interaction between the database and the platform or operating system works
know how to use a data modelling tool
be familiar with SQL Server 2005 features, tools, and technologies
have a Microsoft Certified Technology Specialist: Microsoft SQL Server 2005 credential, or equivalent experience

In addition, it is recommended, but not required, that students have completed:

Course 2071: Writing Queries Using Microsoft Transact-SQL
Course 2779: Implementing a Microsoft SQL Server 2005 Database
Course 2780: Maintaining a Microsoft SQL Server 2005 Database

What can you expect to gain from this course?

After completing this course, you will be able to:

choose data access technologies and an object model to support an organisation's business needs
design an exception handling strategy
choose a cursor strategy
design query strategies using Multiple Active Result Sets (MARS)
design caching strategies for database applications
design a scalable data tier for database applications

Course Content:

Module 1:Choosing Data Access Technologies and an Object Model

This session explains how to choose data access technologies and an object model to support an organisation's business needs.
Introduction 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 Strategy

This session describes the various types of exceptions that can occur in a database system, how to capture them, and how to manage them appropriately.
Exception Types and Their Purposes
Detecting Exceptions
Managing Exceptions

Module 3:Choosing a Cursor Strategy

This session describes when cursors are appropriate and how to use them to optimise the use of system resources.
Common 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 Sets

This session describes when Multiple Active Result Sets (MARS) can improve application response time and user satisfaction.
Introduction 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 Applications

This session describes how to optimise system resources by caching data and objects in the appropriate layers.
Why 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 Applications

This session describes how to assess scalability needs and design the best architecture to scale the system to meet those needs.
Identifying 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