Updating Your Database Development Skills to Microsoft SQL Server 2005

Introduction
This three-day instructor-led course providesstudents with the knowledge and skills to upgrade their skills to SQL Server 2005so that they can design, build, query and develop enterprise SQL Server 2005databases and servers.

Audience
This course is intended for experienced databasedevelopers and database administrators, who are responsible for the design,build, query, and develop enterprise SQL Server 2005 databases and servers.

At Course Completion
After completing this course, students will beable to:

Use the Development Tools provided with SQL Server 2005.

Use Transact-SQL enhancements to perform database development tasks.

Develop XML-based Solutions using SQL Server 2005.

Build Message-Based Services with the Service Broker.

Implement Web Services using Native HTTP Endpoints.

Build Notification Services Applications.

Implement Database Functionality with Managed (.NET) Code.

Build Client Applications with the .NET Framework.

Build Administrative Applications with SQL Management Objects (SMO).

Prerequisites
Before attending this course, students must have:

MCDBA (Microsoft Certified Database Administrator) certification or equivalent knowledge.

Development experience with Microsoft SQL Server 2000 or Microsoft SQL Server 7.0.

Knowledge of SQL Server 2000/7.0:

Database and server architecture.

Database Security

Transact SQL

Query Engine

DDL

DML

Optimization

Schema and Objects

Programming Objects

UDDT

UDF

Triggers

Stored Procedures

Optimization

SQL XML

.NET Development (Visual Basic .NET or C# .NET)

ADO.NET
In addition, it is recommended, but not required,that students have completed:

Course 2073 , Programming a Microsoft SQL Server 2000 Database.

Microsoft Certified Professional Exams
No Microsoft Certified Professional exams areassociated with this course currently.

Course Materials
The student kit includes a comprehensive workbookand other necessary materials for this class.

Course Outline
Module1: SQL Server 2005 Overview
The information in this module introduces the mainnew features and enhancements in SQL Server 2005 and describes the newdevelopment tools.
Lessons

SQL Server 2005 Components and Architecture

SQL Server Developer Tools

SQL Server 2005 Security Implementation
Lab 1:Exploring SQL Server 2005

Using SQL Server Management Studio

Using Schemas
After completing this module, students will be ableto:

Describe the components and architecture of SQL Server 2005.

Use SQL Server Management Studio to create a database solution.

Use security features of SQL Server 2005.

Module2: Transact-SQL Enhancements in SQL Server 2005
In this module, students learn about theimprovements in Transact-SQL in SQL Server 2005.
Lessons

Data Definition Language Enhancements

Data Manipulation Language Enhancements

Structured Exception Handling
Lab2: Programming with Transact-SQL

Partitioning a Table

Using Common Table Expressions

Using Relational Operators
After completing this module, students will be ableto:

Use new Data Definition Language (DDL) features in SQL Server 2005.

Use new Data Manipulation Language (DML) features in SQL Server 2005.

Implement structured exception handling in Transact-SQL.

Module3: Using XML in SQL Server 2005
In this module, students learn about theXML-related functionality in the SQL Server 2005 database engine will bedescribed.
Lessons

XML Enhancements in SQL Server 2005

The xml Data Type

Using XQuery
Lab3: Working with XML

Retrieving XML from Relational Data

Storing XML Natively in the Databases

Using XQuery with xml Methods
After completing this module, students will be ableto:

Use enhanced XML features in SQL Server 2005.

Use the xml data type for typed and untyped XML.

Use XQuery expressions with XML data.

Module4: Using Service Broker
The information in this module introducesMicrosoft SQL Server 2005 Service Broker-a message-based platform for buildingservice-oriented database solutions.
Lessons

Service Broker Architecture

Using the Service Broker
Lab 4:Using Service Broker

Creating Service Broker Objects

Implementing the Customer Service

Implementing the E-mail Service
After completing this module, students will be ableto:

Describe the Service Broker architecture.

Use Service Broker for message-based communication.

Module5: Using Native HTTP Support
In this module, students learn how to create HTTPendpoints that make database services available to Web services clients.
Lessons

Native HTTP Support in SQL Server 2005

Configuring Native HTTP Support
Lab5: Implementing a Web Service with HTTP Endpoints

Create an HTTP Endpoint

Test the HTTP Endpoint

Secure the HTTP Endpoint
After completing this module, students will be ableto:

Describe the native HTTP support features in Microsoft SQL Server 2005.

Implement a Web service using native HTTP endpoints.

Module6: Using Notification Services
The information in this module introduces NotificationServices and how to develop Notification Services applications.
Lessons

Notification Services Architecture

Building Notification Services Solutions
Lab6: Using Notification Services

Creating a Notification Services Application

Creating a Subscription Management Application

Creating an Event Provider
After completing this module, students will be ableto:

Describe the architecture of a Notification Services solution.

Design and implement a simple Notification Services solution.

Module7: Using the .NET CLR in SQL Server 2005
In this module, students learn how to implementmanaged code in a SQL Server 2005 database.
Lessons

SQL Server and the .NET CLR

Implementing Managed Code in SQL Server 2005
Lab7: Implementing Managed Code in the Database

Creating a Managed Stored Procedure

Creating a Managed Function

Creating a Managed User-Defined Type
After completing this module, students will be ableto:

Identify appropriate scenarios for using managed code in the database.

Implement managed database objects.

Module8: Developing Client Applications
The information in this module introduces databasedevelopers to client application development techniques that use the dataaccess classes provided in the .NET Framework.
Lessons

Developing Client Applications with ADO.NET

Data Binding in the .NET Framework 2.0
Lab8: Developing Client Applications

Creating a Data Bound Windows Application

Creating a Data bound ASP.NET Application
After completing this module, students will be ableto:

Use ADO.NET to build a database client application.

Use data binding in a client application.

Module9: Using SQL Management Objects
The information in this module introduces the SQL ManagementObjects (SMO) Application Programming Interface (API) in Microsoft SQL Server2005, which provides classes that you can use to manage SQL Server. SQL Server2005 also introduces Replication Management Objects (RMO) to allow automationof replication.
Lessons

Introduction to SQL Management Objects

Replication Management Objects
Lab9: Using SQL Management Objects

Using SMO to Retrieve Server Information

Using SMO to Create Database Objects

Using SMO to Modify Database Objects
After completing this module, students will be ableto:

Use SMO to manage SQL Server.

Use RMO to manage replication.