About this class
This five-day course provides database developers working in enterprise environments with the knowledge and skills to develop a Microsoft SQL Server 2005 database infrastructure. The course uses the Microsoft Official Curriculum and focuses on the core modules from the four courses that make up the MCITP: Database Developer certification track, in the form of instructor-led training, workshop and clinic formats. Additional work will be required from delegates during this course should they wish to achieve certification.
Who will the lesson benefit?
This course is intended for current professional database administrators who have on-the-job experience administering SQL Server database solutions in an enterprise environment.
What prerequisites are required
- Before attending this course, students must have attended the following courses or possess an equivalent level of knowledge:
- Course 50027: Querying and Processing MS SQL Server Data with SQL Statements.
- Course 2779: Implementing a Microsoft SQL Server 2005 Database.
- Course 2780: Maintaining a Microsoft SQL Server 2005 Database.
Contents of this class
Approaching Database Design Systematically
This module explains how to acquire the skills to approach database design with a systematic perspective. A systematic approach involves formulating your database design process, following guidelines on how to gather and document database requirements, and following best practices when formulating a conceptual design.
Lessons
Overview of Database Design Gathering Database Requirements Creating a Conceptual Database Design Lab: Beginning the Database Design Process Gathering Database Requirements Creating a Conceptual Design After completing this module, students will be able to: Apply a systematic approach to database design. Devise an appropriate strategy for gathering database requirements for a specified project. Formulate requirements into a conceptual model that serves as a basis for defining entities, attributes, and relationships.
Modelling a Database at the Logical Level
This module explains the best practices followed when you build a new logical database model. You will also learn the guidelines for normalization when designing an OLTP model and when designing a data warehouse database. Finally, you will learn to evaluate the existing logical model of a database.
Lessons
- Building a Logical Database Model
- Designing for OLTP Activity
- Designing for Data Warehousing
- Evaluating Logical Models
Lab: Modelling a Database at the Logical Level
- Determine Entities, Attributes, Relationships, Keys, and Constraints
- Normalisation and Schema Assignment
After completing this module, students will be able to:
- Apply best practices to the task of building a new logical database model.
- Apply guidelines for normalization when designing an OLTP model.
- Apply guidelines for designing a data warehouse database.
- Evaluate an existing logical model of a database.
Modelling a Database at the Physical Level
This module explains the guidelines to be followed when designing physical database objects and constraints. The module also covers the best practices for designing database security and for designing database and server options. Finally, this module covers the best practices for evaluating the physical model.
Lessons
- Designing Physical Database Objects
- Designing Constraints
- Designing for Database Security
- Designing Server and Database Options
- Evaluating the Physical Model
Lab: Modelling a Database at the Physical Level
- Specify Database Object Naming Standards
- Define Tables and Columns and Choose Data Types
After completing this module, students will be able to:
- Apply guidelines for designing physical database objects.
- Apply best practices when designing constraints.
- Include security best practices in the design of databases.
- Apply best practices when designing database and server options.
- Apply best practices when evaluating the physical model.
Designing for Database Performance
This module explains the best practices to be followed for designing indexes. The module also covers the guidelines for planning table optimization, and choosing additional optimisation techniques.
Lessons
- Designing Indexes
- Planning for Table Optimisation
- Planning for Database Optimisation
Lab: Designing for Database Scalability
- Apply Optimisation Techniques
After completing this module, students will be able to:
- Apply best practices for designing indexes.
- Apply guidelines when planning for table optimisation.
- Apply guidelines in choosing additional optimisation techniques.
Optimising Physical Database Design
In this unit, students work with strategies for optimising physical database design. Students will optimise a database schema using normalisation, generalisation, and denormalisation.
Lessons
- Performance Optimisation Model
- Schema Optimisation Strategy: Keys
- Schema Optimisation Strategy: Responsible Denormalisation
- Schema Optimisation Strategy: Generalisation
Lab: Optimising Schemas
- Optimising Memberships
- Optimising Events
- Normalising Event Sponsorships
- Denormalising Membership Visits
- Cleaning Up Schema
- Adapting the Solution to the New Database Schema
- Determining Performance
After completing this module, students will be able to:
- Explain the strategy for database optimisation presented in the Performance Optimisation Model.
- Explain the importance of schema design in database optimisation.
- Describe the strategic use of natural and surrogate keys and their roles in schema optimisation.
- Describe responsible denormalisation and the role of this strategy in schema optimisation.
- Describe generalization and the role of this strategy in schema optimisation.
- Normalise a database schema for optimisation.
- Generalise a database schema for optimisation.
- Denormalise a database schema for optimisation.
- Clean up database schema by verifying and adjusting data types and verifying referential integrity.
- Convert data to the new schema.
- Correct table and column names in queries, stored procedures, and triggers to reconcile schema changes.
- Test for performance.
Optimising Queries for Performance
In this unit students experience optimising and tuning queries to improve performance. In the lab, students will optimize stored procedures, views, and non-cursor aggregate queries to improve database performance and user experience. Each query that is optimised improves the overall system because the query will use fewer resources, freeing up those resources for other queries, and reducing the amount of locking done by the query. The domino effect is profound.
Lessons
- Performance Optimisation Model: Queries
- What Is Query Logical Flow?
- Considerations for Using Subqueries
- Guidelines for Building Efficient Queries
Lab: Optimising Queries
- Optimising and Rewriting Slow Performing Stored Procedures
- Optimising and Rewriting Slow Performing Views
- Optimising and Rewriting Slow Performing Non-Cursor Aggregate Queries
- Determining Performance
After completing this module, students will be able to:
- Explain the importance of set-based solutions in database optimisation.
- Explain the utility of the query logical flow diagram in query optimisation.
- Discuss considerations when using subqueries in query optimisation.
- Describe strategies for building efficient queries.
- Rewrite stored procedures for optimisation.
- Rewrite views for optimisation.
- Rewrite non-cursor aggregate queries for optimisation.
- Test queries for performance.
Optimising an Indexing Strategy
In this unit, students will work on optimizing indexing strategies. Students will work with a given database to add and delete indexes, by providing the optimum bridge between the query and the data without any redundancies.
Lessons
- Performance Optimisation Model: Indexes
- Considerations for Using Indexes
- Best Uses of the Clustered Index
- Best Practices for Non-Clustered Index Design
- How to Document an Indexing Strategy
Lab: Optimising an Indexing Strategy
- Identifying Tables to Optimise
- Designing Indexes
- Determining Performance
After completing this module, students will be able to:
- Explain the importance of optimising index strategies in database optimisation.
- Explain considerations for using indexes in database optimisation.
- Describe the best uses of clustered indexes as they relate to optimisation.
- Describe the best practices for designing non-clustered databases.
- Explain the methodology for using an indexing strategy worksheet.
- Determine tables that need to be optimized based on slow running code.
- Design, implement, and adjust clustered and non-clustered indexes.
- Test for performance.
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.
Lessons
- Overview of the Built-in SQL Server Services
- Evaluating When to Use the New SQL Server Services
- Evaluating the Use of Database Engine Enhancements
Lab: Selecting SQL Server Services to Support Business Needs
- Translating Business Requirements into SQL Server Services
- Analysing the Needs of Real Organisations
After completing this module, students will be able to:
- Evaluate the use of the built-in SQL Server services.
- Evaluate the use of the new SQL Server services.
- Evaluate the use of database engine enhancements.
Designing a Security Strategy
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 development teams.
Lessons
- Overview of Authentication Modes and Authorisation 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
Lab: Designing a Security Strategy
- Evaluating the Security Tradeoffs of SQL Server Services
- Designing a Database to Enable Auditing
- Designing Objects to Manage Application Access
- Justifying Security Decisions
After completing this module, students will be able to:
- Select the authentication mode and authorization strategy for a SQL Server 2005 solution.
- Design a security strategy for components of a SQL Server 2005 solution.
- Design objects to manage application access.
- Create an auditing strategy.
- Manage multiple development teams by using the SQL Server 2005 security features.
Designing a Transaction Strategy for a SQL Server 2005 Solution
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.
Lessons
- Defining Data Behaviour Requirements
- Defining Isolation Levels
- Designing a Resilient Transaction Strategy
Lab: Designing a Transaction Strategy for a SQL Server 2005 Solution
- Determining the Database Isolation Level
- Determining the Order of Object Access
- Designing Transactions
- Justifying a Transaction Strategy
After completing this module, students will be able to:
- Define data behaviour requirements.
- Define isolation levels for a data store.
- Design a resilient transaction strategy.
Planning for Source Control, Unit Testing, and Deployment
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.
Lessons
- Designing a Source Control Strategy
- Designing a Unit Test Plan
- Creating a Performance Baseline and Benchmarking Strategy
- Designing a Deployment Strategy
Lab: Planning for Source Control, Unit Testing, and Deployment
- Designing a Source Control Strategy
- Designing a Unit Testing Plan
- Designing a Deployment Strategy
- Justifying Source Control, Unit Test, and Deployment Strategies
After completing this module, students will be able to:
- Design a source control strategy.
- Design a unit test plan.
- Create a performance baseline and benchmarking strategy.
- Design a deployment strategy.
Evaluating Advanced Query and XML Techniques
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.
Lessons
- Evaluating Common Table Expressions
- Evaluating Pivot Queries
- Evaluating Ranking Queries
- Overview of XQuery
- Overview of Strategies for Converting Data Between XML and Relational Formats
Lab: Evaluating Advanced Query Techniques
- Evaluating Common Table Expressions
- Evaluating Pivot Queries
- Evaluating Ranking Queries
- Evaluating Techniques for Converting XML into Relational Data
After completing this module, students will be able to:
- Evaluate the use of Common Table Expressions.
- Evaluate the use of pivot queries.
- Evaluate the use of ranking queries.
- Evaluate the use of XQuery.
- Evaluate strategies for converting data between XML and relational formats.
Choosing Data Access Technologies and an Object Model
This module explains how to choose data access technologies and an object model to support an organization's business needs.
Lessons
- 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
After completing this module, students will be able to:
- Describe a typical database system and the role that data access technologies play in that system.
- Select appropriate technologies for accessing data stored in SQL Server 2005.
- Explain how to build a data access layer.
- Explain how to design SQL Server objects that use the In-Process data provider.
- Describe the data object models for administering SQL Server 2005 components and objects.
Designing an Exception Handling Strategy
This module describes the various types of exceptions that can occur in a database system, how to capture them, and how to manage them appropriately.
Lessons
- Exception Types and Their Purposes
- Detecting Exceptions
- Managing Exceptions
After completing this module, students will be able to:
- Describe the various types of exceptions that can be detected in a SQL Server 2005 system and how they affect applications and users.
- Design strategies to detect exceptions at the appropriate layer.
- Design strategies to log and communicate exceptions according to business requirements. Choosing a Cursor Strategy.
This module describes when cursors are appropriate and how to use them to optimize the use of system resources.
Lessons
- Common Scenarios for Row-Based vs. Set-Based Operations
- Selecting Appropriate Server-Side Cursors
- Selecting Appropriate Client-Side Cursors
After completing this module, students will be able to:
- Explain when cursors are appropriate and when they are not.
- Explain the considerations for selecting server-side cursors.
- Explain the considerations for selecting client-side cursors.
Designing Query Strategies Using Multiple Active Result Sets
This module describes when Multiple Active Result Sets (MARS) can improve application response time and user satisfaction.
Lessons
- 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
After completing this module, students will be able to:
- Explain why MARS is useful, as compared to the set-based execution of Microsoft SQL Server 2000. . Explain when multiple simultaneous reads can be beneficial for an application, and explain the implications of using this technique.
- Explain specific scenarios in which it might be beneficial to use MARS to combine write and read operations.
- Explain the locking implications of using MARS and how these locks affect other transactions.
Designing Caching Strategies for Database Applications
This module describes how to optimise system resources by caching data and objects in the appropriate layers.
Lessons
- Why Caching Is Important
- Data and Query Caching in SQL Server 2005
- Using Caching Technologies Outside of SQL Server
- Custom Caching Techniques
After completing this module, students will be able to:
- Explain why caching is important.
- Explain the advantages of using the data and query caching automatically performed by SQL Server 2005.
- Explain how caching data outside of SQL Server works and how to manage conflicts that these technologies might produce.
- Explain the various ways to cache frequently used data, objects, and results in the appropriate tier to improve performance.
Designing a Scalable Data Tier for Database Applications
This module describes how to assess scalability needs and design the best architecture to scale the system to meet those needs.
Lessons
- 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
After completing this module, students will be able to:
- Identify when to scale database applications and what layer to scale.
- Select an appropriate technology to avoid concurrency problems and to improve application performance.
- Evaluate whether scaling out or scaling up is appropriate for the scalability requirements of your database system.
- Explain how to improve middle tier processing by using multiple instances of Web services and object pooling.
- Explain how to improve response time and availability by scaling out front-end systems.
