Microsoft SQL Server 2005 2008: Programming Upgrade (TSQL and .NET)

Whetstone (N20)

14/04/10

£

1,395

Please click above and we will take care of the rest.

Please click above and we will be happy to discuss the benefits of onsite and bespoke courses with you.

If suitable dates or locations are not available, click this link and we will arrange one for you

Course Overview

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.

This course has been developed for real-world, commercial scenarios by our expert instructors.

Audience

Experienced SQL Server 2000 Developers (or DBAs with good experience of other SQL-based platforms such as Oracle or Sybase)

Prerequisites

• Delegates should have a good background knowledge of SQL Programming (ideally TSQL).
• See also our SQL Server 2008 Comprehensive Upgrade Course

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

Follow On Courses

Associated Courses

• Microsoft Business Intelligence Training Course (SQL Server 2008 SSRS / SSAS / SSIS)
• SQL Server 2008 Reporting Services Fundamentals Training Course
• SQL Server 2008 Analysis Services Fundamentals Training Course
• SQL Server 2008 Integration Services Fundamentals Training Course

• Microsoft Business Intelligence Training Course (SQL Server 2005 / SQL Server 2008 SSRS / SSAS / SSIS)
• SQL Server 2005 / SQL Server 2008 Reporting Services Fundamentals (SSRS) Training Course
• SQL Server 2005 / SQL Server 2008 Analysis Services Fundamentals (SSAS) Training Course
• SQL Server 2005 / SQL Server 2008 Integration Services (SSIS) Training Course

• Advanced Microsoft Business Intelligence with SSAS & PerformancePoint Server 2007 Training
• Advanced SQL Server Analysis Services Training Course
• Microsoft Performance Point 2007 Training Course - Visualising Business Intelligence

• SQL Server 2008 Upgrade Training Course for existing SQL Server 2000 / SQL Server 2005 Developers & DBAs

• SQL Server 2005 / SQL Server 2008 Applications Development Fundamentals training course
• SQL Server 2005 / SQL Server 2008 Database Administration Training Course
• SQL Server 2005 / SQL Server 2008 Comprehensive Upgrade Training Course for existing SQL Server 2000 / Oracle / MySQL users