Designing and Optimizing Database Solutions with Microsoft SQL Server 2008

After completing this module, students 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

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.

Guidelines for Building a Logical Database Model

Planning for OLTP Activity

Evaluating Logical Models

Creating a Logical Database Model

Normalizing the Logical Database Model

After completing this module, students will be able to:

Explain the guidelines for building a logical database model

Plan for OLTP activity

Evaluate Logical models

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 tables and for designing data integrity.

Selecting Data Types

Designing Database Tables

Designing Data Integrity

Specifying Database Object Naming Standards

Converting a Logical Database Model into a Physical Database Model

After completing this module, students will be able to:

Design column data types

Design database tables

Design data integrity

This module explains the considerations for designing indexes. The module also covers the guidelines for designing scalable databases, and choosing additional optimization techniques, including designing for plan guide and partition.

Guidelines for Designing Indexes

Designing a Partitioning Strategy

Designing a Plan Guide

Designing Scalable Databases

Applying Optimization Techniques

Creating Plan Guides

Designing a Partitioning Strategy

After completing this module, students will be able to:

Design indexes

Design scalable databases

Design a plan guide

Design a partitioning strategy

This module explains the best practices to be followed when designing for security in a database. The module will cover the guidelines for designing security for identity and access control, SQL development, database deployment. Finally, this module explains the guidelines for designing secure operations.

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

Designing Secure Development

Implementing Secure Operations

Enabling Database Encryption

After completing this module, students 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

This module explains the best practices to be followed when designing a database access strategy. The module will cover the guidelines for designing views, stored procedures, and user defined function. Finally, this module explains the guidelines for CLR development.

Guidelines for Designing Secure Data Access

Designing Views

Designing Stored Procedures

Designing User-Defined Functions

Designing Security for Data Retrieval Objects

Designing Data Retrieval Objects

After completing this module, students will be able to:

Describe the guidelines for designing secure data access

Design views

Design T-SQL stored procedures

Design user-defined functions

This module will explain the considerations for optimizing and tuning queries to improve performance. The module will cover the design considerations to refactor cursors into queries. Finally, explain about set-based operations.

Considerations for Optimizing Queries for Performance

Refactoring Cursors into Queries

Extending Set-Based Operations

Optimizing Query Performance

Refactoring Cursors into Queries

After completing this module, students will be able to:

Explain the considerations for optimizing queries for performance

Refactor cursors into queries

Explain set-based operations

This module describes considerations and guidelines for defining a transaction strategy for a solution. It also shows the guidelines to specify isolation levels for data stores. It also covers the guidelines for designing a resilient transaction strategy.

Guidelines for Defining Transactions

Defining Isolation Levels

Guidelines for Designing a Resilient Transaction Strategy

Determining the Database Isolation Level

Determining the Order of Object Access

Designing Transactions

After completing this module, students will be able to:

Describe the guidelines for defining transactions

Define isolation levels

Describe the guidelines for designing a resilient transaction strategy

This module describes the guidelines to design XML techniques. This module will cover the design considerations for XML storage, XQuery, XPath, and FOR XML clause. Finally, this module will cover the considerations for converting data between XML and relational formats.

Designing XML Storage

Designing a Data Conversion Strategy

Designing an XML Query Strategy

Designing XML Storage

Converting Data Between XML and Relational Forms

After completing this module, students will be able to:

Design XML storage

Design data conversion between XML and Relational forms

Design an XML query strategy

This module provides an overview of SQL Server 2008 architecture and the various considerations for choosing SQL Server components to be included in a solution. The module will also cover the considerations for designing service broker and full text search.

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

Analyzing the Organizational Needs

Designing a Service Broker Solution Architecture

Designing a Detailed Service Broker Solution

Executing the Service Broker Solution

After completing this module, students 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