SQL Server Upgrade Training Course

SQL Server 2005 / SQL Server 2008 Programming Upgrade (from 2000) Training Course Overview

Course Code:	SQL0040
Price:	£1395
Duration:	3 Days - custom / on-site options available - please call.
Date:
Who should attend:	Experienced SQL Server 2000 Developers (or DBAs with good experience of other SQL-based platforms such as Oracle or Sybase)
Prerequisite Skills:	Delegates should have a good background knowledge of SQL Programming (ideally TSQL).
	See also our SQL Server 2008 Comprehensive Upgrade Course

Our 3-day upgrade course for database application developers with current skills in SQL Server 2000 will give delegates up to speed with the essentials of getting the most out of Microsoft's flagship enterprise database platform.

Because of the similarities in architecture and functionality between SQL Server 2005 and SQL Server 2008, those delegates upgrading from SQL Server 2000 or coming across from Oracle or Sybase platforms will have the opportunity to get hands-on experience on their platform of choice. Demonstrations and course labs will either be on SQL 2005 or SQL 2008 with any specific differences being demonstrated where necessary (usually in a short session at the end of each day). Delegates with some experience of SQL Server 2005 may find this course a useful refresher, and a chance to take on board SQL Server Best Practices.

Course Content

This course has been developed for real-world, commercial scenarios by our expert instructors. See below for detailed syllabus. If you have a technical question, please email

SQL Server 2005 / SQL Server 2008 Training UK
Application Development Upgrade Course Outline

SQL Server 2005 / SQL Server 2008 Overview

New Features for Administrators
Availability and Scalability Features
Security Features
Data Management Features
Administration and Maintenance Features
Development Features
To enable SQLiMail
Testing mail delivery

SQL Server 2005 / SQL Server 2008 Integration Services

Creating the Database Objects
Creating a Package Using Business Intelligence Studio
Integration Services Project Template
OLE DB Command object
Column Mappings
Adding a Data Viewer

Replicating Information

Replication Enhancements
Stand-alone Replication Monitor
Support for Oracle Publishing
HTTP Synchronisation for Merge Replication
Subscriber Initialisation
Replicating DDL Statements
Modified User Interface
Replication Management Objects
To Configure Publishing and Distribution
Configuring Peer-to-Peer Replication
Create a Publication
Create a Subscription with RMO

Enhanced TSQL Commands

SQL Server 2005 / SQL Server 2008 T-SQL Enhancements
Ranking Functions
ROW_NUMBER()
TOP
Recursive Queries
New Relational Operators: PIVOT/UNPIVOT/APPLY
Exception Handling TRY/CATCH

Messaging with the Service Broker

SQL Server Service Broker
The Dialog Protocol
Service Broker Components
Messages
Dialog - Conversations
Conversation Groups
End Point
Service Broker Security
Service Broker inside the Database
Message Types
Contracts
Queues
Services
Routes
Remote Service Binding

Programming with XML

SQL Server 2005 / SQL Server 2008 XML Enhancements
The FOR XML Clause
FOR XML TYPE directive
Nesting FOR XML
Generating inline XSD Schema
ROOT Directive
RAW mode with ELEMENTS directive
PATH mode
XSINIL option
The OPENXML Function
XML datatype input
XML Datatype Output
Batch-level scope
Using the xml Datatype
xml Datatype
Inserting xml Data
Retrieving xml Data
xml Data Variables and Parameters
XML Schema Collection
Create XML Schema
Create a table that uses the Schema:
Alter XML Schema Collections
DROP XML Schema Collections
XML Schema Collections Information
XML Schema Namespaces Information
XML Schema Elements Information
Using XQuery
XML Methods and XML-DML Support in SQL Server 2005 / SQL Server 2008

Connecting via Native HTTP

SQL Server 2005 / SQL Server 2008 Native Web Service Support
Databases and Web Services
HTTP/SOAP Features
Platform Requirements
Architecture
HTTP Endpoint
Modifying Endpoints
HTTP Endpoint Security
HTTP Endpoint Meta Data
Consuming a Web Service
Object Return Types
Create HTTP Endpoints

Programming Notification Services

SQL Server 2005 / SQL Server 2008 Notification Services
Installing Notification Service
Notification Services Architecture
Instances and Applications
Configuration Files
The Application Definition File
The NSControl.exe Utility
Events
Event Providers
Using a FileSystemWatcher Event Provider
Using a Managed Event Provider
Subscriptions
Generating Notifications

Coding the.NET CLR

SQL Server 2005 / SQL Server 2008 and the .NET CLR
Configuring the CLR
Advantages of CLR Integration
Using Managed Code
Creating Managed Stored Procedures
Create a Stored Procedure Class File
Catalogue the Assembly in SQL Server 2005 / SQL Server 2008
Assembly Metadata
Using Statements to Modify the Assembly
Linking to the Assembly
Creating Managed User Defined Functions
Creating Managed Triggers
Debugging SQL CLR Code
TSQL versus CLR Integration Recommendations

Client Application Development

ADO.NET 2.0 Enhancements
Asynchronous Data Access
MARS
Bulk Copy Operations
Batch Updates
Notifications
Snapshot Isolation Mode
Data Binding in ASP.NET2.0

Management with SQL Management Objects

SQL Server 2005 / SQL Server 2008 SQL Management Objects
Building SMO Applications
Enumerating Servers and Databases
Backup and Restoring of Databases
Database Integrity Checks
Extend the SMO Application
Replication Management Objects