Click Here to Install Silverlight*
United StatesChange|All Microsoft Sites
Microsoft Learning

 
Home > Learning Catalog > Course
Course 2785AL:
Implementing and Maintaining a Microsoft SQL Server 2005 Database  
  Length:     5 Day(s)  
  Published:     December 29, 2006  
  Language(s):     English  
  Audience(s):     IT Professionals  
  Level:     200  
  Technology:     Microsoft SQL Server 2005  
  Type:     Course  
  Delivery Method:     Instructor-led (online)  
Overview Course Details Prerequisites
About this Course
Course 2785AL: Five weeks; Microsoft Official Distance Learning (MODL)
Elements of this syllabus are subject to change.
This Distance Learning version of the course consists of 10 online live instructor-led sessions over 5 weeks (2 hours for each session). These sessions would be accompanied by additional self-paced e-learning content, scenario-based labs, and assessments to provide students with the knowledge and skills to manage and maintain Microsoft SQL Server 2005.
Audience Profile
This course is intended for IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for maintaining a database.
At Course Completion
After completing this course, students will be able to:
  • Install and configure SQL Server 2005.
  • Create databases and database files.
  • Create data types and tables.
  • Generate basic SQL Server 2005 queries and XML reports.
  • Manage SQL security.
This course syllabus should be used to determine whether the course is appropriate for the students, based on their current skills and technical training needs.
Course content, prices, and availability are subject to change without notice.
  • Backup and restore user databases.
  • Recover system database in the event of a disaster.
  • Implement data integrity for a database by using the constraints, triggers, and XML schema.
  • Implement basic stored procedures and functions.
  • Mirror SQL databases.
Course OutlineModule 1: Module 1-1: Installing and Configuring SQL Server 2005
This module introduces views and summarizes the advantages that they provide. It describes how to create views by using Microsoft SQL Server Management Studio and Transact-SQL, the options available, and how to find information about views. The module then addresses restrictions on modifying data through views and how views can improve database performance.
Lessons
  • SQL Server 2005 Architecture
  • Preparing to Install SQL Server 2005
  • Installing SQL Server 2005
  • Installing and Configuring Administrative Tools
  • SQL Server 2005 Conformation Tools
Lab : Lab 1-1 Installing SQL Server 2005
After completing this module, students will be able to:
  • Describe the architecture and components of SQL Server 2005.
  • Describe how to prepare for a SQL Server installation.
  • Install SQL Server 2005.
  • Manage a SQL Server installation.
Module 2: Module 1-2: Creating Databases and Database Files
This module describes how data is stored in SQL Server 2005. It provides information on how to create filegroups, databases, and schemas. It also covers the aspects of creating database snapshots and managing them.
Lessons
  • Planning Data Storage for SQL Server 2005
  • Creating Databases and File Storage
  • Understanding Name Resolution and Creating Schemas
  • Creating Database Snapshots
  • Managing Databases
Lab : Lab 1-2 Creating Databases and Database Files
After completing this module, students will be able to:
  • Plan the creation of databases.
  • Create databases.
  • Create schemas
  • Manage databases.
Module 3: Module 2-1: Creating Data Types and Tables
It is important to know the characteristics of the various data types so that you can assign the appropriate data values for any column in an SQL Server database table. SQL Server provides a number of data types that are associated with the categories of data types used by common programming languages. This module describes the characteristics of the various SQL Server data types.
 
This module will also help you understand how SQL Server organizes data in rows. This knowledge is important, as it can enable you to accurately estimate the size of a table.
Lessons
  • What Are Data Types?
  • Creating Data Types
  • Creating Tables
  • Creating Partitioned Tables
Lab : Lab 2-1 Creating Data Types and Tables
After completing this module, students will be able to:
  • Describe data types.
  • Create data types and tables.
Module 4: Module 2-2: Querying SQL Server 2005 Databases
This module provides the knowledge and skills to perform basic Transact SQL queries in Microsoft SQL Server 2005. It also provides an overview of T-SQL and shows how to retrieve, group, summarize, and modify data. It provides an overview of querying multiple tables by using different types of joins, and gives you the foundation to query XML data by using XQuery.
Lessons
  • What is T-SQL?
  • Retrieving Data
  • Grouping and Summarizing Data
  • Modifying Data
  • Working with XML Data
Lab : Lab 2-2 Querying SQL Databases
After completing this module, students will be able to:
  • Explain printing in the Windows Server 2003 family.
  • Retrieve data from tables by using the SELECT statement.
  • Use the GROUP BY clause to filter data.
  • Write INSERT, DELETE, and UPDATE statements to modify data in tables.
  • Organize summary data for a column by using aggregate functions with the GROUP BY and HAVING clauses.
  • Generate XML-based reports.
Module 5: Module 3-1: Managing Security in SQL Server 2005
Security is a primary consideration when designing and managing a database environment. In this module, you will learn about the Microsoft SQL Server security model in SQL Server 2005, which is considerably enhanced and extended from previous versions of SQL Server.
Lessons
  • SQL Server 2005 Security Overview
  • Managing SQL Server 2005 Security
  • Monitoring SQL Server 2005
  • Automating SQL Server 2005 Administration
Lab : Lab 3-1 Managing SQL Security
After completing this module, students will be able to:
  • Describe the security architecture in SQL Server 2005.
  • Monitor SQL Server 2005 performance and activity.
  • Automate tasks by creating jobs, operators, and alerts.
Module 6: Module 3-2: Disaster Recovery
Frequently, the data in a database substantiates an organization’s operations. Without the database, the organization cannot function properly. It is important that every mission-critical database system has adequate disaster-recovery procedures in place.
 
Backup and restore operations are a vital part of data management and are essential for recovering from failures and disasters. Therefore, a major part of a database administrator’s role is to ensure that data is backed up and can be restored quickly in the event of a disaster.
Lessons
  • Planning a Backup Strategy
  • Backing Up User Databases
  • Restoring User Databases
  • Recovering Data from Database Snapshots
  • System Databases and Disaster Recovery
Lab : Lab 3-2 Disaster Recovery
After completing this module, students will be able to:
  • Plan a backup strategy.
  • Back up and restore user databases.
  • Recover data from database snapshots.
  • Recover system databases.
Module 7: Module 4-1: Creating and Tuning Indexes
This module provides an overview of planning, creating, and optimizing indexes. It explains the differences between heaps, clustered indexes, and nonclustered indexes and where you would use each one. It describes how to create the different types of indexes and how to configure and maintain them to attain optimal performance benefits.
Lessons
  • Planning Indexes
  • Creating Indexes
  • Optimizing Indexes
  • Creating XML Indexes
Lab : Lab 4-1 Creating and Tuning Indexes
After completing this module, students will be able to:
  • Plan, create, and optimize SQL indexes.
  • Create XML indexes.
Module 8: Module 4-2: Implementing Data Integrity
This module provides an overview of the types of data integrity that will concern you as a database developer, and summarizes the features provided by SQL Server 2005 to meet those concerns. It also provides and opportunity to discuss the powerful data integrity features of SQL Server 2005 - constraints, triggers, and XML schemas.
Lessons
  • Data Integrity Overview
  • Implementing Constraints
  • Implementing Triggers
  • Implementing XML Schemas
Lab : Lab 4-2 Implementing Data Integrity
After completing this module, students will be able to:
  • Implement data integrity for a database by using the constraints, triggers, and XML schema features of SQL Server 2005.
Module 9: Module 5-1: Implementing Programming Objects
This module introduces views and summarizes the advantages that they provide. It describes how to create views by using Microsoft SQL Server Management Studio and Transact-SQL, and how to find information about views. The module then addresses restrictions on modifying data through views and how views can improve database performance.
 
You will also learn how to create stored procedures and user-defined functions. You will also learn how to implement structured error handling and understand execution context.
Lessons
  • Implementing Views
  • Implementing Stored Procedures
  • Implementing User Defined Functions
  • Introducing Database Programming Objects
Lab : Lab 5-1 Implementing Stored Procedures and Functions
After completing this module, students will be able to:
  • Describe the types and uses of views.
  • Create and manage views.
  • Implement stored procedure