Implementing and Maintaining a Microsoft SQL 2005 Database

Course Code: MS27792780
Duration: 5 Days

Bookmark and Share

Overview

This training will provide delegates with the knowledge and skills needed to implement and maintain a Microsoft SQL Server 2005 database. This course covers the syllabus from both

and

Delegates must reach the pre-requisites of this course to fully benefit. The course focuses on how to use SQL Server 2005 product features and tools related to implementing and maintaining the database. This event will also give delegates an overview of writing and creating Transact SQL queries.

This is an accelerated training event bringing 10 days training into 5 days, that will use elements from the above courses and it is recommend that ONLY delegates who have prior experience with SQL attend this intensive delivery.

Related Certifications

This course forms part of the following certification track(s):

Prerequisites

Before attending this course, students must have:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of Transact-SQL.
  • Working knowledge of relational databases.
  • Some experience with database design.

Delegates will learn how to

  • Install and configure SQL Server 2005
  • Manage database files.
  • Manage security.
  • Perform administrative tasks.
  • Backup databases
  • Rstore databases.
  • Monitor SQL Server.
  • Troubleshoot SQL Server.
  • Transfer data.
  • Maintain high availability.
  • Create databases and database files.
  • Create data types and tables.
  • Use XML-related features in Microsoft SQL Server 2005.
  • Plan, create, and optimize indexes.
  • Implement data integrity in Microsoft SQL Server 2005 databases by Using constraints, triggers, and XML schemas
  • Implement views.
  • Implement stored procedures and functions.
  • Implement managed code in the database.
  • Use Service Broker to build a messaging-based solution.

Outline

Installing and configuring SQL Server 2005

  • Preparing to install SQL Server
  • Installing SQL Server
  • Managing a SQL Server 2005 Installation

Managing Databases and Files

  • Planning databases
  • Creating databases
  • Creating Filegroups
  • Creating Schemas
  • Creating Database Snapshots
  • Managing databases

Creating Data Types and Tables

  • Creating Data Types
  • Creating Tables
  • Creating Partitioned Tables

Writing Queries Using Transact-SQL

  • Getting started with Transact-SQL.
  • Performing basic queries.
  • Grouping and summarizing data.
  • Joining data from multiple tables.
  • Working with subqueries.
  • Modifying data.
  • Querying metadata, XML, and full-text indexes.
  • Using programming objects for data retrieval.
  • Using advanced querying techniques.

Using XML

  • Retrieving XML by Using FOR XML
  • Shredding XML by Using OPENXML
  • Using the xml Data Type

Creating and Tuning Indexes

  • Planning Indexes
  • Creating Indexes
  • Optimizing Indexes
  • Creating XML Indexes

Implementing Data Integrity

  • Data Integrity Overview
  • Implementing Constraints
  • Implementing Triggers
  • Implementing XML Schemas

Implementing Views

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

Implementing Stored Procedures and Functions

  • Implementing Stored Procedures
  • Creating Parameterized Stored Procedures
  • Creating Functions
  • Handling Errors
  • 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

Using Service Broker

  • Service Broker Overview
  • Creating Service Broker Objects
  • Sending and Receiving Messages

Managing Security

  • Overview of SQL Server Security
  • Securing the Server Scope
  • Securing the Database Scope
  • Implementing Cryptography in SQL Server

Disaster Recovery

  • Planning a Backup Strategy
  • Backing Up User Databases
  • Restoring User Databases
  • Recovering Data from Database Snapshots

System Database and Disaster Recovery Monitoring SQL Server

  • Viewing Current Activity
  • Using System Monitor and SQL Server Profiler
  • Using DDL Triggers and Event Notifications

Implementing Replication

  • Overview of Replication
  • Implementing Replication
  • Configuring Replication in Some Common Scenarios

Creating Databases and Database Files

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

Implementing Data Integrity by Using Triggers and XML Schemas

  • Implementing Triggers
  • Implementing XML Schemas

Managing Transactions and Locks

  • Overview of Transactions and Locks
  • Managing Transactions
  • Understanding SQL Server Locking Architecture
  • Managing Locks

Using Notification Services (Optional)

  • Introduction to Notification Services
  • Developing Notification Services Solutions

Implementing Functions

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

Implementing Stored Procedures

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

Transferring Data

  • Overview of Data Transfer
  • Introduction to SQL Server Integration Services (SSIS)
  • Using SQL Server Integration Services

Automating Administrative Tasks

  • Automating Administrative Tasks in SQL Server 2005
  • Configuring the SQL Server Agent
  • Creating Jobs and Operators
  • Creating Alerts
  • Managing Multiple Servers
  • Managing SQL Server Agent Security

Maintaining High Availability

  • Introduction to High Availability
  • Implementing Server Clustering, Database Mirroring and Log Shipping

Introduction to Replication

  • Overview of Replication