DURATION
3 days
Introduction
This three-day instructor-led course provides students with the knowledge and skills to upgrade their skills to SQL
Server 2005 so that they can design, build, query and develop enterprise SQL Server 2005 databases and servers.
Audience
This course is intended for experienced database developers 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 be able 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:
Microsoft Certified Professional Exams
No Microsoft Certified Professional exams are associated with this course currently.
Course Materials
The student kit includes a comprehensive workbook and other necessary materials for this class.
( COURSE OUTLINE
Module 1: SQL Server 2005 Overview
The information in this module introduces the main new features and enhancements in SQL Server 2005 and describes
The new development 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 able to:
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.
Module 2: Transact-SQL Enhancements in SQL Server 2005
In this module, students learn about the improvements in Transact-SQL in SQL Server 2005.
Lessons
Data Definition Language Enhancements
Data Manipulation Language Enhancements
Structured Exception Handling
Lab 2: Programming with Transact-SQL
Partitioning a Table
Using Common Table Expressions
Using Relational Operators
After completing this module, students will be able to:
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.
Module 3: Using XML in SQL Server 2005
In this module, students learn about the XML-related functionality in the SQL Server 2005 database engine will be
described.
Lessons
XML Enhancements in SQL Server 2005
The xml Data Type
Using XQuery
Lab 3: 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 able to:
Use enhanced XML features in SQL Server 2005.
Use the xml data type for typed and untyped
Use XQuery expressions with XML data.
Module 4: Using Service Broker
The information in this module introduces Microsoft SQL Server 2005 Service Broker-a message-based platform for
building service-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 able to:
Describe the Service Broker architecture.
Use Service Broker for message-based communication.
Module 5: Using Native HTTP Support
In this module, students learn how to create HTTP endpoints that make database services available to Web services
clients.
Lessons
Native HTTP Support in SQL Server 2005
Configuring Native HTTP Support
Lab 5: 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 able to:
Describe the native HTTP support features in Microsoft SQL Server 2005.
Implement a Web service using native HTTP endpoints.
Module 6: Using Notification Services
The information in this module introduces Notification Services and how to develop Notification Services applications.
Lessons
Notification Services Architecture
Building Notification Services Solutions
Lab 6: Using Notification Services
Creating a Notification Services Application
Creating a Subscription Management Application
Creating an Event Provider
After completing this module, students will be able to:
Describe the architecture of a Notification Services solution.
Design and implement a simple Notification Services solution.
Module 7: Using the .NET CLR in SQL Server 2005
In this module, students learn how to implement managed code in a SQL Server 2005 database.
Lessons
SQL Server and the .NET CLR
Implementing Managed Code in SQL Server 2005
Lab 7: 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 able to:
Identify appropriate scenarios for using managed code in the database。
Implement managed database objects.
At the end of this module, you will be able to configure access to remote computers. This includes:
Configuring a VPN connection.
Configuring and using Remote Desktop.
Storing user names and passwords.
Module 8: Developing Client Applications
The information in this module introduces database developers to client application development techniques that use the
data access classes provided in the .NET Framework.
Lessons
Developing Client Applications with ADO.NET
Data Binding in the .NET Framework 2.0
Lab 8: Developing Client Applications
Creating a Data Bound Windows Application
Creating a Data bound ASP.NET Application
After completing this module, students will be able to:
Use ADO.NET to build a database client application.
Use data binding in a client application.
Module 9: Using SQL Management Objects
The information in this module introduces the SQL Management Objects (SMO) Application Programming Interface (API)
in Microsoft SQL Server 2005, which provides classes that you can use to manage SQL Server. SQL Server 2005 also
introduces Replication Management Objects (RMO) to allow automation of replication.
Lessons
Introduction to SQL Management Objects
Replication Management Objects
Lab 9: 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 able to:
Use SMO to manage SQL Server.
Use RMO to manage replication.
Programme scope, delivery arrangements and suitability are confirmed during enquiry.