SQL Server 2008 Comprehensive DBA & Programming Upgrade Training Course

SQL Server 2008 Comprehensive DBA & Programming Upgrade Training Course Overview

Course Code:	SQL0120
Price:	£1995
Duration:	5 days
Date:
Who should attend:	Database professionals who need to get quickly up to speed with the powerful new features of Microsoft SQL Server 2008
Prerequisite Skills:	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.

The full 5-day Comprehensive Upgrade, primarily aimed at SQL Server 2000 and 2005 Developers and Database Administrators 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.
New features of SQL Server 2008 include:
LINQ
Transparent Data Encryption
External Key Management
Data Auditing
Pluggable CPU
Transparent Failover for Database Mirroring
Declarative Management Framework
Server Group Management
Streamlined Installation
Enterprise System Management
Performance Data Collection
System Analysis
Data Compression
Query Optimization Modes
Resource Governor
Entity Data Model
Visual Entity Designer
Entity Aware Adapters
SQL Server Change Tracking
Synchronized Programming Model
Visual Studio Support
SQL Server Conflict Detection
FILESTREAM data type
Integrated Full Text Search
Sparse Columns
Large User Defined Types
Date/Time Data Type
LOCATION data type
SPATIAL data type
Virtual Earth Integration
Partitioned Table Parallelism
Query Optimizations
Persistent Lookups
Change Data Capture
Backup Compression
MERGE SQL Statement
Data Profiling
Star Join
Enterprise Reporting Engine
Internet Report Deployment
Block Computations
Scale out Analysis
BI Platform Management
Export to Word and Excel
Author reports in Word and Excel
Report Builder Enhancements
TABLIX
Rich Formatted Data
Personalized Perspectives
Grouping Sets
Table-valued parameters
Table-valued constructors
HierarchyID data type
Large User Defined Aggregates
New versions of ODBC driver, OLE DB provider, and ADO.NET data provider
SQLCE Version 3.5
Database Mirroring Enhancements
Data Mining Algorithm Enhancements
SSIS support for the new date/time data types
SSIS VSTA support
Course Content
This course has been developed for real-world, commercial scenarios by our expert instructors 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
Installing SQL Server 2008
SQL Server 2008 Editions
Installing Microsoft SQL Server
System Requirements
Preparing for a SQL 2008 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 2008 Services node
SQL Server 2008 Network Configuration node
SQL Server 2008 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 2008
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 2008 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 2008 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 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 2008 T-SQL Enhancements
Ranking Functions
ROW_NUMBER()
TOP
Recursive Queries
New Relational Operators: PIVOT/UNPIVOT/APPLY
Exception Handling TRY/CATCH
Programming with XML
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 2008
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 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 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 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 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.NET 2.0
Management with SQL Management Objects
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