SQL Server 2008 DBA Administration Upgrade training course

Course Outline

Our 3-day upgrade course for DBAs with current skills in SQL Server 2000 will bring delegates up to speed with the essentials of setting up, maintaining and getting the most out of Microsoft's flagship database server.

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 [email protected]

Database Administration Upgrade Course Syllabus Course Outline

SQL Server 2012 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 2008

SQL Server 2005 / SQL Server 2008 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 / SQL Server 2008 Services node
SQL Server 2005 / SQL Server 2008 Network Configuration node
SQL Server 2005 / 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 2005 / 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 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

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

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

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 Bindings