Elements of this syllabus are subject to change.
This five-day instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2008 database. The course focuses on teaching individuals how to use SQL Server 2008 product features and tools related to maintaining a database.

Audience

This course is intended for IT Professionals who administer and maintain SQL Server databases.

Module 1: Installing and Configuring SQL Server

• Preparing to Install SQL Server
• Installing SQL Server
• Configuring a SQL Server Installation

Module 2: Managing Databases and Files

• Planning Databases
• Creating Databases
• Using Policy-Based Management

Module 3: Disaster Recovery

• Planning a Backup Strategy
• Backing Up User Databases
• Restoring User Databases
• Performing Online Restore Operations
• Recovering Data from Database Snapshots
• System Databases and Disaster Recovery

Module 4: Managing Security

• Overview of SQL Server Security
• Protecting the Server Scope
• Protecting the Database Scope
• Protecting the Server Scope
• Auditing Security

Module 5: Transferring Data

• Overview of Data Transfer
• Introduction to SQL Server Integration Services

Module 6: Automating Administrative Tasks

• Automating Administrative Tasks in SQL Server
• Using SQL Server Agent
• Creating Maintenance Plans
• Implementing Alerts
• Managing Multiple Servers
• Managing SQL Server Agent security

Module 7: Implementing Replication

• Overview of Replication
• Managing Publications and Subscriptions
• Configuring Replication in Some Common Scenarios

Module 8: Maintaining High Availability

• Introduction to High Availability
• Implementing Log Shipping
• Implementing Database Mirroring
• Implementing Server Clustering
• Using Distributed High Availability Solutions

Module 9: Monitoring SQL Server

• Viewing Current Activity
• Using SQL Server Profiler
• Monitoring with DDL Triggers
• Using Event Notifications

Module 10: Troubleshooting and Performance Tuning

• Troubleshooting SQL Server
• Performance Tuning in SQL Server
• Using Resource Governor
• Using Data Collector

Before attending this course, students must have:

• Basic knowledge of the Microsoft Windows operating system and its core functionality.
• Working knowledge of Transact-SQL.
• Working knowledge of relational databases.
• Some experience with database design.

This five-day instructor-led course is intended for Microsoft SQL Server database developers who are responsible for implementing a database on SQL Server 2008 R2. In this course, students learn the skills and best practices on how to use SQL Server 2008 R2 product features and tools related to implementing a database server.

Audience Profile

This course is intended for IT Professionals who want to become skilled on SQL Server 2008 R2 product features and technologies for implementing a database. To be successful in this course, the student should have knowledge of basic relational database concepts and writing T-SQL queries.

Module 1: Introduction to SQL Server and its Toolset

• Introduction to SQL Server Platform
• Working with SQL Server Tools
• Configuring SQL Server Services

Module 2: Working with Data Types

• Using Data Types
• Working with Character Data
• Converting Data Types
• Working with Specialized Data Types

Module 3: Designing and Implementing Tables

• Designing Tables
• Working with Schemas
• Creating and Altering Tables

Module 4: Designing and Implementing Views

• Introduction to Views
• Creating and Managing Views
• Performance Considerations for Views

Module 5: Planning for SQL Server Indexing

• Core Indexing Concepts
• Data Types and Indexes
• Single Column and Composite Indexes

Module 6: Implementing Table Structures in SQL Server

• SQL Server Table Structures
• Working with Clustered Indexes
• Designing Effective Clustered Indexes

Module 7: Reading SQL Server Execution Plans

• Execution Plan Core Concepts
• Common Execution Plan Elements
• Working with Execution Plans

Module 8: Improving Performance through Nonclustered Indexes

• Designing Effective Nonclustered Indexes
• Implementing Nonclustered Indexes
• Using the Database Engine Tuning Advisor

Module 9: Designing and Implementing Stored Procedures

• Introduction to Stored Procedures
• Working With Stored Procedures
• Implementing Parameterized Stored Procedures
• Controlling Execution Context

Module 10: Merging Data and Passing Tables

• Using the MERGE Statement
• Implementing Table Types
• Using Table Types as Parameters

Module 11: Creating Highly Concurrent SQL Server Applications

• Introduction to Transactions
• Introduction to Locks
• Management of Locking
• Transaction Isolation Levels

Module 12: Handling Errors in T-SQL Code

• Designing T-SQL Error Handling
• Implementing T-SQL Error Handling
• Implementing Structured Exception Handling

Module 13: Designing and Implementing User-Defined Functions

• Designing and Implementing Scalar Functions
• Designing and Implementing Table-valued Functions
• Implementation Considerations for Functions
• Alternatives To Functions

Module 14: Ensuring Data Integrity through Constraints

• Enforcing Data Integrity
• Implementing Domain Integrity
• Implementing Entity and Referential Integrity

Module 15: Responding to Data Manipulation via Triggers

• Designing DML Triggers
• Implementing DML Triggers
• Advanced Trigger Concepts

Module 16: Implementing Managed Code in SQL Server

• Introduction to SQL CLR Integration
• Importing and Configuring Assemblies
• Implementing SQL CLR Integration

Module 17: Storing XML Data in SQL Server

• Introduction to XML and XML Schemas
• Storing XML Data and Schemas in SQL Server
• Implementing the XML Data Type

Module 18: Querying XML Data in SQL Server

• Using the T-SQL FOR XML Statement
• Getting Started with XQuery
• Shredding XML

Module 19: Working with SQL Server Spatial Data

• Introduction to Spatial Data
• Working with SQL Server Spatial Data Types
• Using Spatial Data in Applications

Module 20: Working with Full-Text Indexes and Queries

• Introduction to Full-Text Indexing
• Implementing Full-Text Indexes in SQL Server
• Working with Full-Text Queries

Before attending this course, students must have:

• Working knowledge of Transact-SQL (ability to write Transact-SQL queries) or Course 2778A: Writing Queries Using Microsoft SQL Server 2008 Transact-SQL
• Working knowledge of relational databases (database design skills).
• Core Windows Server skills.
• Basic programming language.

This five-day instructor-led course provides the knowledge and skills to design, optimize, and maintain a database administrative solution for Microsoft SQL Server 2008.

Audience Profile

The primary audience of this course is IT professionals who design and maintain SQL Server databases. The audience can also comprise technical architects and consultants who design and implement SQL Server solutions. Students 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, optimization, maintenance, or support.
The students should have experience in the following areas:

• Administering databases
• Designing logical database schema solutions
• Defining high-availability solutions
• Automating administrative tasks
• Defining security solutions
• Monitoring and troubleshooting the database server
• Designing and executing deployments
• Defining the infrastructure (storage, hardware, and number of servers or instances, etc.)

Module 1: Designing an Administrative Solution for SQL Server 2008

• Overview of SQL Server 2008  
• Designing an Administrative Solution
• Developing and Deploying an Administrative Solution for SQL Server 2008
• Implementing an Administrative Solution in SQL Server Management Studio (SSMS)

Module 2: Deploying SQL Server 2008

• System Requirements for SQL Server 2008
• Upgrading and Migrating to SQL Server 2008
• Configuring Instances in SQL Server 2008

Module 3: Designing the Physical Structure of SQL Server 2008

• Introduction to the Physical Structure of a Database
• Planning for Partitioning
• Planning for Full-Text Indexing

Module 4: Designing a Strategy for Maintaining a Database in SQL Server 2008

• Designing a Strategy for Maintaining Statistics for a Database
• Designing a Strategy for Maintaining Indexes
• Designing a Strategy for Database Compression

Module 5: Designing Solutions for Managing SQL Server 2008

• Designing a Policy-Based Management Strategy
• Controlling Resource Usage by using Resource Governor
• Auditing Database Changes

Module 6: Automating the Database Management Strategy for SQL Server 2008

• Managing SQL Server Agent
• Automating Administrative Tasks in SQL Server
• Monitoring Events in SQL Server

Module 7: Designing a Strategy for Securing SQL Server 2008

• Securing SQL Server 2008 Instances
• Planning Database Security
• Managing Certificate and Keys in SQL Server 2008
• Implementing Database Encryption

Module 8: Designing a Strategy for Monitoring SQL Server 2008

• Overview of Monitoring SQL Server 2008
• Monitoring SQL Server Instances
• Monitoring SQL Server Databases
• Monitoring Multiple SQL Servers by Using Data Collection

Module 9: Designing a Strategy for Content Distribution in SQL Server 2008

• Distributing Data in SQL Server 2008
• Working with Distributed Queries
• Managing Distributed Content by Using Distributed Transactions
• Managing SQL Server Integration Services (SSIS) Packages
• Setting Up Scalable Shared Databases

Module 10: Designing a Strategy for Replication in SQL Server 2008

• Designing a Database Replication Strategy
• Implementing Content Distribution by using Replication
• Configuring a High-Availability Solution by Using Replication

Module 11: Designing a High-Availability Solution for SQL Server 2008

• Introduction to High Availability
• Implementing Log Shipping
• Implementing Database Mirroring
• Implementing Failover Clustering
• Designing a High-Availability Strategy

Module 12: Designing a Backup and Recovery Strategy for SQL Server 2008

• Planning a Backup Strategy
• Planning a Recovery strategy
• Designing a Backup and Recovery Strategy
• Implementing a Repair Strategy

Before attending this course, students must:

• Understand the tradeoffs among the different redundant storage types. For example, what RAID levels mean, and how they differ from Storage Area Networks (SAN).
• Understand how replication works and how replication is implemented.
• Be familiar with reading user requirements and business-need documents. For example, development project vision/mission statements or business analysis reports.
• Have some knowledge of how queries execute. Must be able to read a query execution plan and understand what is happening.
• Have basic knowledge of the dependencies between system components.
• Be able to design a database to third normal form (3NF) and know the tradeoffs when backing out of the fully normalized design (denormalization) and designing for performance and business requirements in addition to being familiar with design models, such as Star and Snowflake schemas.
• Have monitoring and troubleshooting skills.
• Have 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 the interaction between the operating system and the database works. For example, how integrated authentication interacts with Active Directory directory service.
• Have knowledge of application architecture. That is, how applications can be designed in three layers, what applications can do, interaction between applications and the database, interaction between the database and the platform or operating system.
• Must already know how to use:
• A data modeling tool
• Microsoft Office Visio (to create infrastructure diagrams)
• 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 2779: Implementing a Microsoft SQL Server 2005 Database. 
  • Course 2780: Maintaining a Microsoft SQL Server 2005 Database.
  In addition to their professional experience, students who attend this training should have technical knowledge equivalent to the following courses:
• Course 6231: Maintaining a Microsoft SQL Server 2008 Database 
• Course 6232: Implementing a Microsoft SQL Server 2008 Database

This five-day instructor-led course provides the knowledge and skills that IT Professionals need to design, optimize, and maintain SQL Server 2008 database.

Audience Profile

The audience of this course is developers who implement database solutions or perform development utilizing the programming features and functionality of SQL Server. Students 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, optimization, maintenance, or support. They should possess a four-year college degree, BS or BA, in the computer field. The students should have experience in the following areas:

• Developing databases
• Writing Transact-SQL queries
• Designing, implementing and troubleshooting programming objects
• Doing database performance tuning and optimization
• 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
• Gathering business requirements

Module 1: Designing a Conceptual Database Model

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

Module 2: Designing a Logical Database Model

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

Module 3: Designing a Physical Database Model

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

Module 4: Designing Databases for Optimal Performance

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

Module 5: Designing Security for SQL Server 2008

• 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

Module 6: Designing a Strategy for Database Access

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

Module 7: Designing Queries for Optimal Performance

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

Module 8: Designing a Transaction and Concurrency Strategy

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

Module 9: Designing an XML Strategy

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

Module 10: Designing SQL Server 2008 Components

• 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

Before attending this course, students must have:

• Working knowledge of data storage. Specifically, they should know about row layout, fixed length field placement and varying length field placement.
• Knowledge about index structures and index utilization. Specifically, they must understand the interaction between non-clustered indexes, clustered indexes and heaps. They must know why a covering index can improve performance.
• Hands-on database developer experience. Specifically, they should have three years of experience as a full-time database developer in an enterprise environment.
• Knowledge about the locking model. Specifically, students should have an understanding of lock modes, lock objects and isolation levels and be familiar with process blocking.
• Understanding of Transact-SQL syntax and programming logic. Specifically, students should be completely fluent in advanced queries, aggregate queries, subqueries, user-defined functions, cursors, control of flow statements, CASE expressions, and all types of joins.
• Knowledge about the trade offs when backing out of the fully normalized design and designing for performance and business requirements in addition to being familiar with design models, such as Star and Snowflake schemas. They should be able to design a database to third normal form (3NF).
• Strong monitoring and troubleshooting skills, including usage of monitoring tools.
• 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.
• 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.
• Knowledge of using a data modeling tool.
• Knowledge of 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 2779, Implementing a Microsoft SQL Server 2005 Database.
• Course 2780, Maintaining a Microsoft SQL Server 2005 Database.

About this Course
This 3-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2008.

Audience Profile

This course is intended for SQL Server database administrators, implementers, system engineers, and developers who are responsible for writing queries.

Module 1: Getting Started with Databases and Transact-SQL in SQL Server 2008

• Overview of SQL Server 2008
• Overview of SQL Server Databases
• Overview and Syntax Elements of T-SQL
• Working with T-SQL Scripts
• Using T-SQL Querying Tools

Module 2: Querying and Filtering Data

• Using the SELECT Statement
• Filtering Data
• Working with NULL Values
• Formatting Result Sets
• Performance Considerations for Writing Queries

Module 3: Grouping and Summarizing Data

• Summarizing Data by Using Aggregate Functions
• Summarizing Grouped Data
• Ranking Grouped Data
• Creating Crosstab Queries

Module 4: Joining Data from Multiple Tables

• Querying Multiple Tables by Using Joins
• Applying Joins for Typical Reporting Needs
• Combining and Limiting Result Set

Module 5: Working with Subqueries

• Writing Basic Subqueries
• Writing Correlated Subqueries
• Comparing Subqueries with Joins and Temporary Tables
• Using Common Table Expressions

Module 6: Modifying Data in Tables

• Inserting Data into Tables
• Deleting Data from Tables
• Updating Data in Tables
• Overview of Transactions

Module 7: Querying Metadata, XML, and Full-Text Indexes

• Querying Metadata
• Overview of XML
• Querying XML Data
• Overview of Full-Text Indexes
• Querying Full-Text Indexes

Module 8: Using Programming Objects for Data Retrieval

• Overview of Views
• Overview of User-Defined Functions
• Overview of Stored Procedures
• Overview of Triggers
• Writing Distributed Queries

Module 9: Using Advanced Querying Techniques

• Considerations for Querying Data
• Working with Data Types
• Cursors and Set-Based Queries
• Dynamic SQL
• Maintaining Query Files

Before attending this course, students must have:

• Knowledge of data integrity concepts.
• Core Windows Server skills.
• Relational database design skills.
• Programming skills.