UPDATING YOUR DATABASE DEVELOPMENT SKILLS TO MICROSOFT SQL SERVER 2005 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: Course 2073, Programming a Microsoft SQL Server 2000 Database Microsoft Certified Professional Exams No Microsoft Certified Professional exams are associated with this course currently. 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 the xml data type for typed and untyped XML. 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. 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. Inclusive to the overall costs of this Microsoft training course is equipment, training material, lunch and refreshments.