Microsoft 50401 Training

Designing and Optimizing Database Solutions with Microsoft SQL Server 2008

Designing and Optimising Database Solutions with Microsoft SQL Server 2008 M50401

Course Title: Designing and Optimising Database Solutions

with Microsoft SQL Server 2008

Course Code: M50401
Version: A
Level: 300
Duration: 5 Days

Course Overview

The Designing and Optimising Database Solutions with Microsoft SQL Server 2008 M50401 instructor-led training course has been designed to provide candidates with the required skills and knowledge that IT Professionals need to design, optimise, and maintain SQL Server 2008 database.

Target Audience

Candidates who should consider attending the M50401 Microsoft training course are developers who implement database solutions or perform development utilising the programming features and functionality of SQL Server. Candidates taking this course are expected to have three or more years of experience working on databases for two or more of the following phases in the product lifecycle - design, development, deployment, optimisation, maintenance, or support. They should possess a four-year college degree, BS or BA, in the computer field. Candidates are required to have experience in the following areas: developing databases, writing Transact-SQL queries, designing, implementing and troubleshooting programming objects, doing database performance tuning and optimisation, designing databases at both the conceptual and logical levels, implementing databases at the physical level, designing and troubleshooting the data access layer of an application and gathering business requirements.

Course Objectives

On successfully completing the M50401 course candidates will be able to:

• Design a database design strategy
• Design a database for optimal performance
• Design security for a database
• Design programming objects
• Design queries for performance
• Design a transaction and concurrency strategy
• Design an XML strategy

Course Prerequisites

Candidates attending the M50401 Microsoft training course should have a working knowledge of data storage, specifically about row layout, fixed length field placement and varying length field placement. Candidates should have a prior knowledge about index structures and index utilisation, specifically understanding the interaction between non-clustered indexes, clustered indexes and heaps and why a covering index can improve performance. Candidates are also required to have hands-on database developer experience; specifically they should have three years of experience as a full-time database developer in an enterprise environment and they should also have knowledge about the locking model and understand lock modes, lock objects and isolation levels and be familiar with process blocking.

In addition, candidates are required to have an understanding of Transact-SQL syntax and programming logic and be completely fluent in advanced queries, aggregate queries, subqueries, user-defined functions, cursors, control of flow statements, CASE expressions, and all types of joins. Candidates should also have knowledge about the trade-offs when backing out of the fully normalised design and designing for performance and business requirements in addition to being familiar with design models, such as Star and Snowflake schemas and should be able to design a database to third normal form (3NF). Strong monitoring and troubleshooting skills are also required, including usage of monitoring tools.

Candidates should also have a basic knowledge of the operating system and platform - 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. This also includes application architecture - 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.

Finally, candidates should have prior knowledge of using a data modelling tool and SQL Server 2005 features, tools, and technologies as well as holding a valid Microsoft Certified Technology Specialist: Microsoft SQL Server 2005 certification or possess the equivalent skills and experience. Candidates are also recommended to have previously attended and completed courses M2779 Implementing a Microsoft SQL Server 2005 Database and M2780 Maintaining a Microsoft SQL Server 2005 Database.

Testing and Certification

There are currently no exams or Microsoft Certification directly associated to this course however this course may assist in preparing candidates for other exams that are closely associated with this technology.

Course Content

Module 1: Designing a Conceptual Database Model

In this module candidates will learn guidelines for designing a conceptual database model with a systematic perspective. A systematic approach involves formulating a database design process, following guidelines on how to gather and document database requirements, and following best practices when formulating a conceptual design. Finally, candidates will learn the guidelines for using Entity Framework.

Lessons:

• Overview of Database Design
• Gathering Database Requirements
• Creating a Conceptual Database Design
• Overview of Entity Framework

Lab: Designing a Conceptual Database Model

• Formulating a Conceptual Database Design
• Creating a Conceptual Database Design

After completing this module, candidates will be able to:

• Explain the key steps in the database design process
• Gather database requirements
• Describe the guidelines for creating a conceptual database design
• Explain the guidelines for creating a conceptual database design by using the Entity Framework

Module 2: Designing a Logical Database Model

In this module candidates will learn best practices to follow when building a new logical database model. Candidates will also learn the guidelines for normalisation when designing an OLTP model and when designing a data warehouse database. Finally, candidates will learn to evaluate the existing logical model of a database.

Lessons:

• Guidelines for Building a Logical Database Model
• Planning for OLTP Activity
• Evaluating Logical Models

Lab: Designing a Logical Database Model

• Creating a Logical Database Model
• Normalising the Logical Database Model

After completing this module, candidates will be able to:

• Explain the guidelines for building a logical database model
• Plan for OLTP activity
• Evaluate Logical models

Module 3: Designing a Physical Database Model

In this module candidates will learn guidelines to follow when designing physical database objects and constraints. The module also covers the best practices for designing database tables and for designing data integrity.

Lessons:

• Selecting Data Types
• Designing Database Tables
• Designing Data Integrity

Lab: Designing a Physical Database Model

• Specifying Database Object Naming Standards
• Converting a Logical Database Model into a Physical Database Model

After completing this module, candidates will be able to:

• Design column data types
• Design database tables
• Design data integrity

Module 4: Designing Databases for Optimal Performance

In this module candidates will learn considerations for designing indexes. The module also covers the guidelines for designing scalable databases, and choosing additional optimisation techniques, including designing for plan guide and partition.

Lessons:

• Guidelines for Designing Indexes
• Designing a Partitioning Strategy
• Designing a Plan Guide
• Designing Scalable Databases

Lab: Designing Databases for Optimal Performance

• Applying Optimisation Techniques
• Creating Plan Guides
• Designing a Partitioning Strategy

After completing this module, candidates will be able to:

• Design indexes
• Design a partitioning strategy
• Design a plan guide
• Design scalable databases

Module 5: Designing Security for SQL Server 2008

In this module candidates will learn best practices to be followed when designing for security in a database. The module covers guidelines for designing security for identity and access control, SQL development, database deployment and designing secure operations.

Lessons:

• Exploring Security in SQL Server 2008
• Implementing Identity and Access Control
• Guidelines for Secure Development in SQL Server 2008
• Guidelines for Secure Deployment of SQL Server 2008
• Guidelines for Secure Operations

Lab: Designing Security for SQL Server 2008

• Designing Secure Development
• Implementing Secure Operations
• Copying an Unencrypted Database
• Implementing Transparent Data Encryption
• Attempting to Copy an Encrypted Database

After completing this module, candidates will be able to:

• Explore different aspects of security in SQL Server 2008
• Implement Identity and Access Control
• Describe the guidelines for Secure development in SQL Server 2008
• Describe the guidelines for secure deployment of SQL Server 2008
• Describe the guidelines for secure operations

Module 6: Designing a Strategy for Database Access

In this module candidates will learn best practices to follow when designing a database access strategy. The module covers guidelines for designing views, stored procedures, user defined function and CLR development.

Lessons:

• Guidelines for Designing Secure Data Access
• Designing Views
• Designing Stored Procedures
• Designing User-Defined Functions

Lab: Designing a Strategy for Database Access

• Designing Security for Data Retrieval Objects
• Designing Data Retrieval Objects

After completing this module, candidates will be able to:

• Describe the guidelines for designing secure data access
• Design views
• Design T-SQL stored procedures
• Design user-defined functions

Module 7: Designing Queries for Optimal Performance

In this module candidates will learn considerations for optimising and tuning queries to improve performance. The module also covers the design considerations to refactor cursors into queries.

Lessons:

• Considerations for Optimising Queries for Performance
• Refactoring Cursors into Queries
• Extending Set-Based Operations

Lab: Designing Queries for Optimal Performance

• Optimising Query Performance
• Refactoring Cursors into Queries

After completing this module, candidates will be able to:

• Explain the considerations for optimising queries for performance
• Refactor cursors into queries
• Explain Set-Based Operations

Module 8: Designing a Transaction and Concurrency Strategy

In this module candidates will learn about considerations and guidelines for defining a transaction strategy for a solution. The module also shows the guidelines to specify isolation levels for data stores.

Lessons:

• Guidelines for Defining Transactions
• Defining Isolation Levels
• Guidelines for Designing a Resilient Transaction Strategy

Lab: Designing a Transaction and Concurrency Strategy

• Determining the Database Isolation Level
• Determining the Order of Object Access
• Designing Transactions

After completing this module, candidates will be able to:

• Describe the guidelines for defining transactions
• Define isolation levels
• Describe the guidelines for designing a resilient transaction strategy

Module 9: Designing an XML Strategy

In this module candidates will learn guidelines to design XML techniques. The module covers the design considerations for XML storage, XQuery, XPath, and FOR XML clause. Finally, the module also covers the considerations for converting data between XML and relational formats.

Lessons:

• Designing XML Storage
• Designing an XML Query Strategy
• Designing a Data Conversion Strategy

Lab: Designing an XML Strategy

• Designing an XML Data Storage Model
• Converting Data Between XML and Relational Forms

After completing this module, candidates will be able to:

• Design XML storage
• Design an XML query strategy
• Design data conversion between XML and Relational forms

Module 10: Designing SQL Server 2008 Components

In this module candidates will learn about SQL Server 2008 architecture and the various considerations for choosing SQL Server components to be included in a solution. The module also covers the considerations for designing service broker and full text search.

Lessons:

• Overview of SQL Server 2008 Components
• Designing a Service Broker Architecture
• Designing the Service Broker Data Flow
• Designing the Service Broker Availability
• Exploring Full-Text Search
• Designing a Full-Text Search Strategy

Lab: Designing SQL Server 2008 Components

• Analysing the Organisational Needs
• Designing a Service Broker Solution Model
• Designing a Detailed Service Broker Solution
• Implementing the Service Broker Solution

After completing this module, candidates will be able to:

• Examine the SQL Server 2008 architecture
• Design a Service Broker architecture
• Design the Service Broker data flow
• Explore full-text search
• Design a full-text search strategy

PLEASE NOTE: Every effort has been made to ensure the accuracy of all information contained herein. IT Help and Support Centre Ltd makes no warranty expressed or implied with respect to accuracy of this information, including price, product editorials or product specifications. IT Help and Support Centre Ltd or its suppliers shall not be liable for incidental, consequential or special damages arising from, or as a result of, any electronic transmission or the accuracy of the information contained herin, even if IT Help and Support Centre Ltd has been advised of the possibility of such damages. Product and manufacturer names are used only for the purpose of identification.