Implementing and Maintaining a Microsoft SQL Server 2008 Database

This course is intended for IT Professionals who administer and maintain SQL Server databases.

After completing this course, students will be able to:

• Install and configure SQL Server 2008.
• Manage database files.
• Backup and restore databases.
• Manage security.
• Transfer data into and out of SQL Server.
• Automate administrative tasks.
• Replicate data between SQL Server instances.
• Maintain high availability.
• Monitor SQL Server.
• Create databases and database files.
• Create data types and tables.
• Use XML-related features in Microsoft SQL Server 2008.
• Plan, create, and optimize indexes.
• Implement data integrity in Microsoft SQL Server 2008 databases by using constraints.
• Implement data integrity in Microsoft SQL Server 2008 by using triggers.
• Implement views.
• Implement stored procedures.
• Implement functions.
• Implement managed code in the database.
• Manage transactions and locks.
• Use Service Broker to build a messaging-based solution.

Installing and Configuring SQL Server

• Preparing to Install SQL Server
• Installing SQL Server
• Configuring a SQL Server Installation

Creating Databases and Database Files

• Creating Databases
• Creating Filegroups
• Creating Schemas
• Creating Database Snapshots

Creating and Tuning Indexes

• Planning Indexes
• Creating Indexes
• Optimizing Indexes

Implementing Data Integrity by Using Constraints and Triggers

• Data Integrity Overview
• Implementing Constraints
• Implementing Triggers

Using XML

• Using the XML Data Type
• Retrieving XML by Using FOR XML
• Shredding XML by Using OPENXML
• Introducing XQuery
• Creating XML Indexes
• Implementing XML Schemas

Implementing Views

• Introduction to Views
• Creating and Managing Views
• Optimizing Performance by Using Views

Implementing Stored Procedures

• Implementing Stored Procedures
• Creating Parameterized Stored Procedures
• Working With Execution Plans
• Handling Errors

Implementing Functions

• Creating and Using Functions
• Working with Functions
• Controlling Execution Context

Implementing Managed Code in the Database

• Introduction to the SQL Server Common Language Runtime
• Importing and Configuring Assemblies
• Creating Managed Database Objects

Managing Transactions and Locks - Overview

Using Service Broker - Overview

Disaster Recovery

• Planning a Backup Strategy
• Backing Up User Databases
• Restoring User Databases
• Performing Online Restore Operations
• Recovering Data from Database Snapshots
• System Databases and Disaster Recovery

Managing Security

• Overview of SQL Server Security
• Protecting the Server Scope
• Protecting the Database Scope
• Protecting the Server Scope
• Auditing Security

Automating Administrative Tasks

• Automating Administrative Tasks in SQL Server
• Using SQL Server Agent
• Creating Maintenance Plans
• Implementing Alerts
• Managing Multiple Servers
• Managing SQL Server Agent security

Transferring Data

• Overview of Data Transfer
• Introduction to SQL Server Integration Services

Maintaining High Availability

• Introduction to High Availability
• Implementing Log Shipping
• Implementing Database Mirroring
• Implementing Server Clustering
• Using Distributed High Availability Solutions

Monitoring SQL Server

• Viewing Current Activity
• Using SQL Server Profiler
• Monitoring with DDL Triggers
• Using Event Notifications