Audience
This course is intended for students who need to learn the skills necessary to maintain a Microsoft SQL Server 2008 database infrastructure. Being comprehensive and hands-on, it is aimed at quickly getting students familiar with the Microsoft SQL Server 2008 administration concepts, tools and utilities.
Prerequisites
Delegates ideally need an understanding of relational databases and design principles. Some experience of the SQL language would be beneficial.
Duration
5 days. Hands on.
Course Objectives
This intensive course is aimed at getting students familiar with SQL Server 2008 admin, tools and utilities. The Appendix module detailed in the outline are included in the course material but are not delivered as part of the training due to time constraints. However, as this course is fully modularised, an Appendix module could be swapped out with any one of the main modules of comparable size and duration if so desired. Further, this course can be tailored to include only the material required. Upon successful completion of this course, students will be able to:
- Perform an Installation of SQL Server 2008
- Upgrade Previous Versions of SQL Server to SQL Server 2008
- Configure SQL Server Post Installation
- Plan and Create Databases
- Configure and Manage Databases
- Create and Manage Tables in a Database
- Implement and Manage Data Integrity
- Implement and Manage SQL Server Indexes
- Understand and Manage Index Fragmentation
- Create and Manage Partitions
- Query Data In Partitions
- Understand and Implement SQL Server 2008 Security
- Encrypt Data in a Database
- Implement Security Auditing
- Implement Policy-Based Management
- Understand Data Transfer Utilities
- Backup and Restore Databases
- Create and Implement Database Snapshots
- Automate Tasks in SQL Server 2008
- Use the SQL Server 2008 Tools and Utilities to Monitor Performance
- Understand the Concepts of Maintaining High Availability
- Implement Database Mirroring
- Implement Log Shipping
- Troubleshoot and Performance Tune SQL Server 2008
- Understand and Implement Replication
Course Content
Install and Configure SQL Server 2008
Installation Prerequisites
The Installation Process
Upgrading from Previous Versions
Post Install Configuration Options
Creating, Configuring and Managing Databases
Planning to Implement a Database
Creating a Database
Creating and Managing Filegroups
Configuring and Managing a Database
Database Schemas
Creating and Managing Tables
Creating a Table
Native SQL Server Data Types
Columns and Column Properties
User-Defined Types
Implementing and Managing Data Integrity
Introducing Data Integrity
Using Constraints to Implement Data Integrity
Using Triggers to Implement Data Integrity
Creating and Managing Indexes
SQL Server Index Architecture
Creating Clustered Indexes
Creating Non-Clustered Indexes
Included Columns
Managing Index Fragmentation
Managing Statistics
Creating and Managing Partitions
Partition Functions
Partition Schemes
Partitioning Tables and Indexes
Query Data in Partitions
Managing Partitions
SQL Server 2008 Security
Overview of SQL Server Security
Security at the Server Level
Security at the Database Level
Encrypting Data Security
Auditing
Policy-Based Management
Designing Policies
The Central Management Server
Importing and Exporting Policies
Transferring Data
Overview of Data Transfer
Examining the Data Transfer Utilities
Bulk Loading Data
Using the Import/Export Wizard
Introducing SQL Server Integration Services (SSIS)
Backups and Recovery
Backup Types and Strategies
Backing up a Database
Restoring a Database
Online Restore Operations
Database Snapshots
Recovering System Databases
SQL Server Automation
Overview of SQL Server Automation
The SQL Server Agent Service
Creating Maintenance Plans
Creating and Managing Jobs
Creating and Managing Operators
Creating and Managing Alerts
Managing Multiple Servers
Managing SQL Server Agent Security
Monitoring SQL Server
Overview of Performance Monitoring
Viewing the Current Activity Using System Monitor
Using SQL Server Profiler
Using DDL Triggers
Using Event Notifications
Maintaining High Availability
Introduction to High Availability
Implementing Server Clustering
Implementing Database Mirroring
Implementing Log Shipping
Troubleshooting and Performance Tuning
Troubleshooting SQL Server Performance Tuning in SQL Server
Using the Database Engine Tuning Advisor
Using Resource Governor
Using Data Collector
Introduction to Replication
Overview of Replication
Replication Types and Models
Configuring and Implementing Replication
Creating Publications
Creating Subscriptions
Snapshot Replication
Peer-to-Peer Transactional Replication
HTTP Merge Replication
Appendix 1. Querying Full Text Indexes
Overview of Full Text Indexes
Full Text Indexing and the Querying Process
How SQL Server Implements Full Text Indexes
Overview of Full Text Search
Using the CONTAINS Predicate
Using the FREETEXT Predicate
Using the Full Text Functions
Differences Between Full Text Functions and Predicates
Combining Full Text Search and Transact-SQL Predicates