Publisher: Microsoft

Version: 2005
Length: 4 day s
Course-ID: SX678GRB

Course Overview

Students will learn to install and configure SQL Server 2005. A full range of implementation and administration subjects is covered.

Target Student

This 4 day course is intended for students who need to learn the skills necessary to maintain a Microsoft SQL Server 2005 database infrastructure. Being comprehensive and hands-on, it is aimed at quickly getting students familiar with the Microsoft SQL Server 2005 administration concepts, tools and utilities.

Prerequisites

No previous knowledge of SQL Server 2005 is assumed although an understanding of relational database concepts would be helpful. An understanding of working in a Microsoft Windows operating system environment. An understanding of relational databases and design principles. Some experience of the SQL language would be beneficial.


Introduction
Installing SQL Server 2005
  • SQL Server Editions
  • Infrastructure Requirements
  • Default, Named and Multiple Instances of SQL Server 2005
  • Upgrading a SQL Server 2005 Installation
Configuring SQL Server 2005
  • Configuring Log and Data Files
  • Configuring Database Mail
  • Specifying a Recovery Model
  • Configuring SQL Server Security Principals
  • Configuring Database Securables
  • Configuring Encryption
  • Configuring Linked Servers
Creating Tables, Constraints and User-Defined Types
  • Creating Tables
  • Implementing Constraints
  • User-Defined Types
Creating Indices
  • Index Structures
  • Clustered Indices
  • Non-Clustered Indices
  • Maintaining
Working with T-SQL
  • Querying Data
  • Formatting Result Sets
  • Modifying Data
  • Transactions
Creating Partitions
  • Partition Functions
  • Partition Schemes
  • Partitioning Tables and Indices
  • Querying Partitions
  • Managing Partitions
Implementing Views
  • Creating Views
  • Modifying Data through Views
  • Creating an Indexed View
Functions, Stored Procedures and Triggers
  • Functions
  • Stored Procedures
  • Triggers
Working with Flat Files
  • The bcp Utility
  • BULK INSERT
  • Bulk XML
  • Using the SSIS Import/Export Wizard
Back Up, Restore and Move a Database
  • Backing Up a Database
  • Restoring a Database
  • Moving a Database
Using T-SQL to Manage Databases
  • Managing Index Fragmentation
  • Managing Statistics
  • Shrinking Files
  • Using DBCC CHECKDB
HTTP Endpoints
  • HTTP Endpoint Security theory
  • Creating a Secure HTTP Endpoint
SQL Server Agent Jobs
  • Creating a SQL Server Agent Job
  • Maintenance Plans
  • Operators
  • Allerts
Monitoring and Troubleshooting SQL Server Performance1
  • System Monitor
  • Database Engine Tuning Advisor
  • Using Dymanic Management Views and Functions
  • Correlating Performance Data
  • Resolving Blocking and Deadlocking Issues
  • Using DAC
Managing Database Snapshots
  • Creating a Database Snapshot
  • Reverting a Database from a Database Snapshot
  • Snapshot Issues
Database Mirroring
  • Understanding Database Mirroring Roles
  • Preparing Databases for Database Mirroring
  • Establishing Endpoints
  • Operating Modes
  • Failing over a Database Mirror
  • Removing Database Mirroring
Log Shipping
  • Preparing for Log Shipping
  • Configuring Log Shipping Options
  • Configuring Log Shipping Mode
  • Configuring Monitoring
Replication
  • Replication Types
  • Setting up Replication
  • Configuring Replication Security
  • Configuring Conflict Resolution for Merge Replication
  • Monitoring Replication
Service Broker
  • Service Broker Architecture
  • Creating Message Types and Contracts
  • Creating Queues and Services
  • Creating Conversations
  • Sending and Receiving Messages
Full Text Catalogues
  • Creating a Full-Text Catalogue
  • Creating a Full-Text Index
  • Populating a Full-Text Index
  • Querying Data using a Full-Text Index