MS2781 Designing Microsoft SQL Server 2005 Server-Side Solutions
This three-day instructor-led 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.
Before attending this course, students must:
- Have experience reading user requirements and business-need documents. For example,
development project vision/mission statements or business analysis reports.
- Understand Transact-SQL syntax and programming logic.
- Understand XML. Specifically, they must be familiar with the syntax of XML, what elements
and attributes are, and how to distinguish them.
- Understand security requirements. Specifically, must understand how unauthorized users can
gain access to sensitive information and be able to plan strategies to prevent access.
- Be able to design a database to 3NF and know the tradeoffs when backing out of the fully
normalized 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.
- 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.
- Have some experience with a reporting 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.
Module 1: Selecting SQL Server Services to Support Business Needs
This module provides an overview of SQL Server 2005
architecture and the various considerations for
choosing SQL Server services to include in a solution.
The module also describes considerations for using the
database enhancements in SQL Server 2005.
Module 2: Designing a Security Strategy
- Overview of the Built-in SQL Server Services
- Evaluating When to Use the New SQL Server Services
- Evaluating the Use of Database Engine Enhancements
This module describes the considerations for designing
a security strategy for the various components of a
SQL Server 2005 solution. This includes considerations
for choosing authentication and authorization strategy
for a solution, as well as designing security for the
solution components such as Notification Services and
Service Broker. The module also teaches students the
guidelines for designing objects to manage application
access. The module provides students with the
required knowledge to create an auditing strategy for
a database solution. Finally, the module teaches
students how to manage security for multiple
Module 3: Designing a Data Modelling Strategy
- Overview 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
In this module, students learn the various
considerations and guidelines to define standards for
storing XML data in a solution. The module also
provides the knowledge required to design a database
schema. The module provides information about the
considerations for implementing OLTP and OLAP
functionality, considerations for determining
normalization levels, and considerations for creating
indexes. Finally, the module covers the various
considerations for designing a scale-out strategy for a
Module 4: Designing a Transaction Strategy for a SQL Server 2005 Solution
- Defining Standards for Storing XML Data in a Solution
- Designing a Database Solution Schema
- Designing a Scale-Out Strategy
This module describes considerations and guidelines
for defining a transaction strategy for a solution. It
also shows how to define data behaviour requirements
and specify isolation levels for data stores.
- Defining Data Behaviour Requirements
- Defining Isolation Levels
- Designing a Resilient Transaction Strategy
Module 5: Designing a Notification Services Solution
This module describes the guidelines and processes
for designing a Notification Services solution as part of
an overall SQL Server 2005 solution. It shows how to
define event data and how to store this data, how to
design a subscription strategy for a Notification
Services solution, how to design a notification
strategy, and how to design a notification delivery
Module 6: Designing a Service Broker Solution
- Defining Event Data
- Designing a Subscription Strategy
- Designing a Notification Strategy
- Designing a Notification Delivery Strategy
This module describes the guidelines and processes
for designing a Service Broker solution as part of an
overall SQL Server 2005 solution. It covers tasks such
as designing the Service Broker solution architecture,
designing the Service Broker data flow, and designing
Service Broker solution availability.
Module 7: Planning for Source Control, Unit Testing, and Deployment
- Designing a Service Broker Solution Architecture
- Designing Service Broker Data Flow
- Designing Service Broker Solution Availability
This module teaches the guidelines and considerations
for planning for source control, unit testing, and
deployment, during the design of a SQL Server 2005
solution. Design tasks covered include designing a
source control strategy, designing a unit testing plan,
creating a performance baseline and benchmarking
strategy, and designing a deployment strategy.
Module 8: Evaluating Advanced Query and XML Techniques
- Designing a Source Control Strategy
- Designing a Unit Test Plan
- Creating a Performance Baseline and Benchmarking Strategy
- Designing a Deployment Strategy
This module teaches students how to evaluate queries
using the advanced query and XML techniques, which
students might require when designing a SQL Server
2005 solution. Query tasks include evaluating
common table expressions, pivot queries, and ranking
techniques. XML tasks include defining standards for
storing XML data, evaluating the use of XQuery, and
creating a strategy for converting data between XML
and relational formats.
- Evaluating Common Table Expressions
- Evaluating Pivot Queries
- Evaluating Ranking Queries
- Overview of XQuery
- Overview of Strategies for Converting Data Between XML and Relational Formats
|Forsythes Training provides instructor led computer training on the Microsoft Office and Adobe range of applications.|
We service Sydney, Newcastle, the Hunter Valley, Lake Macquarie, Central and Northern Coasts of New South Wales (NSW) Australia.With our mobile training room we can provide a range of courses on-site at your premises.