SQL Server 2005: Updating your Administration & Development Skills

Scheduled Dates (sort by: location | date)

Course Overview

This full 5-day Comprehensive Upgrade course, is primarily aimed at SQL Server 2000 and 2005 Developers and Database Administrators . It will give delegates intensive hands-on experience of utilising the new features of SQL Server 2008. Relational Database professionals from backgrounds such as Oracle and Sybase will also gain a lot from this course.

Audience

Database professionals who need to get quickly up to speed with the powerful new features of Microsoft SQL Server 2008

Prerequisites

Delegates will ideally have a background knowledge of SQL Server 2000 Administration and developing SQL Server 2000 applications, or equivalent skills in other SQL-based relational databases such as Sybase or Oracle.

Course Outline

SQL Server 2005 Comprehensive DBA & Programming Upgrade Course Outline

• SQL Server 2005 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

Installing SQL Server 2005

• SQL Server 2005 Editions
• Installing Microsoft SQL Server
• System Requirements
• Preparing for a SQL 2005 Installation
• Installation Checklist
• Setup Features
• Using the System Consistency Checker
• Component Install
• Unattended Installation
• Installation Sequence
• Installation Maintenance
• Upgrading from previous versions of SQL Server
• Retaining SQL 2000
• To verify the Installation

Administrative Tools

• Upgrading to SQL Server Management Studio
• Management Studio Windows
• Registered Servers
• Object Explorer
• Review Database Objects
• Modify Database Properties
• Create Database Scripts
• The Maintenance Plans Node
• Solution Explorer
• Query Editor
• Template Explorer
• Dynamic Help
• Upgrading to SQL Server Computer Manager
• SQL Server 2005 Services node
• SQL Server 2005 Network Configuration node
• SQL Server 2005 Native Client Configuration node
• The SQLCMD Management Utility
• SQLCMD Syntax
• SQLCMD Variables
• Dedicated Administrative Connection
• SQL Server Management Studio SQLCMD Mode
• Using SQL Management Objects
• SMO Object Model

Monitoring the Database Server

• Monitoring with SQL Profiler
• DDL Triggers
• Using DDL Triggers
• Managing DDL Triggers
• Managing DDL Triggers from TSQL
• Managing DDL Triggers from Management Studio
• DDL Event Notifications
• Handling Events
• Creating an Event Notification
• Managing Event Notifications
• Managing Metadata Views
• Metadata Storage
• System Views
• Information Schema
• Catalog Views
• Sys All
• Dynamic Management Views
• System Stored Procedures

Database and Index Maintenance

• Index Management
• New Index Features
• Creating Indexes
• Online Index Maintenance
• Parallel (multiple CPU) Index Operations
• Lock Options
• Included Columns in Indexes
• Partitioned Tables and Indexes
• XML Index Support
• Altering an Index
• Rebuilding an Index
• Reorganising an Index
• Disabling an Index
• Dropping an Index
• Index Fragmentation
• Using the Database Tuning Advisor

Securing SQL Server 2005

• Implementing Securables
• New Security Features
• Principals
• Securables
• Permissions
• Permission Types
• Managing Logins
• Managing Users
• Managing Schemas
• Schema Definition
• Using Schemas
• Managing Execution Context
• Managing Permissions
• Manage Server Permissions
• Manage Server-Scope Securable Permissions
• Manage Database Permissions
• Manage Database-Scope Securable Permissions
• Managing Certificates
• Native Data Encryption
• Code and Module Signing

SQL Server Disaster Recovery

• Disaster Recovery
• SQL 2005 Disaster Recovery Feature
• Enhanced Features
• Database Snapshots
• Creating a Snapshot
• Common Snapshot Applications
• Undeleting table rows
• Undoing a table update
• Recovering a dropped object
• Backup and Restore
• Backup Changes
• Restore Changes
• Media Integrity Changes
• Recovering the MASTER Database
• Create a mirrored backup
• Restore from the backup mirror
• Create a snapshot
• Restore the snapshot data
• Automate the creation of daily snapshots

Enhancing Database Availability

• SQL 2005 Data Availability
• Database Mirroring
• The Witness Server and Automatic Failover
• Configuring Database Mirroring
• Transaction Safety Level
• Operating Modes
• High Availability Operating Mode
• High Protection Operating Mode
• High Performance Operating Mode
• Mirroring Security
• Monitoring Mirroring
• Database States for Database Mirroring
• Prepare the Servers
• Configure Mirroring
• Verify Principal and Mirror Access
• Execute the Mirror Failover
• Verify Mirror and Witness Status
• Terminating the Mirror Session

SQL Server 2005 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 T-SQL Enhancements
• Ranking Functions
• ROW_NUMBER()
• TOP
• Recursive Queries
• New Relational Operators: PIVOT/UNPIVOT/APPLY
• Exception Handling TRY/CATCH

b

Programming with XML

• SQL Server 2005 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

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 Bindings

Connecting via Native HTTP

• SQL Server 2005 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 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 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
• 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 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

• SQL Server 2008 Comprehensive Upgrade Training Course for existing SQL Server 2005 & 2000 / Oracle / MySQL users
• SQL Server 2005 Applications Development training course
• SQL Server 2005 Database Administration Training Course
• SQL Server 2005 Comprehensive Upgrade Training Course for existing SQL Server 2000 / Oracle / MySQL users
• SQL Server 2005 Business Intelligence Training Course: SSAS & SSIS Analysis and Integration Services Training Course
• SQL Server 2005 Business Intelligence Training Course: SSRS Reporting Services Training Course
• SQL Server 2000 Applications Development training
• SQL Server 2000 Database Administration
• SQL Server Performance Tuning
• SQL Server OLAP, DTS & Cubes Training
• SQL Design and Information Retrieval

SQL Server 2005: Updating your Administration & Development Skills / SQL0020