|
|
|
Designing a Microsoft
SQL Server 2005 Infrastructure
Course 2786: Two days; Instructor-Led
Introduction
This two-day instructor-led course
provides database administrators working in enterprise environments with the
knowledge and skills to design a Microsoft SQL Server 2005 database
infrastructure. The course focuses on the development of strategies for data
archiving, consolidation, distribution, and recovery. The course also stresses
the importance of capacity analysis and emphasizes the tradeoffs that need to
be made during design.
Audience
This course is intended for current
professional database administrators who have three or more years of on-the-job
experience administering SQL Server database solutions in an enterprise
environment.
At Course Completion
After completing this course,
students will be able to:
•
Analyze storage, CPU, memory, and
network capacity needs.
•
Design a strategy for data
archiving.
•
Design a strategy for database
server consolidation.
•
Design a strategy for data
distribution.
•
Design a database server
infrastructure.
•
Design a strategy for data recovery.
•
Establish database conventions and
standards.
Before attending this course,
students must:
•
Understand the tradeoffs among the
different redundant storage types. For example, what RAID levels mean, and how
they differ from Storage Area Networks (SAN).
•
Understand how replication works and
how replication is implemented.
•
Be familiar with reading user
requirements and business-need documents. For example, development project
vision/mission statements or business analysis reports.
•
Have some knowledge of how queries
execute. Must be able to read a query execution plan and understand what is
happening.
•
Have basic knowledge of the
dependencies between system components.
•
Be able to design a database to
third normal form (3NF) and know the tradeoffs when backing out of the fully
normalized design (denormalization) and designing for
performance and business requirements in addition to being familiar with design
models, such as Star and Snowflake schemas.
•
Have monitoring and troubleshooting
skills.
•
Have knowledge of the operating
system and platform. That is, how the operating system integrates with the
database, what the platform or operating system can do, and how the interaction
between the operating system and the database works. For example, how
integrated authentication interacts with Active Directory directory
service.
•
Have knowledge of application
architecture. That is, how applications can be designed in three layers, what
applications can do, interaction between applications
and the database, interaction between the database and the platform or
operating system.
•
Must already know how to use:
•
A data modeling tool
•
Microsoft Office Visio (to create
infrastructure diagrams)
•
Be familiar with SQL Server 2005
features, tools, and technologies.
•
Have a Microsoft Certified
Technology Specialist: Microsoft SQL Server 2005 credential or equivalent
experience.
Course Outline
Module 1: Analyzing Capacity Needs
This module explains how to gather
data about the current capacity of key system resources such as storage, CPU,
memory, and network bandwidth. It also explains how the resulting data can be
used to estimate future capacity needs.
Module 2: Designing a Strategy for
Data Archiving
This module explains how to identify
the requirements that affect data archiving, determine the structure of
archival data, select an appropriate storage format, and develop a data
movement strategy. It also describes the key elements of a data archival plan
and the process of creating it.
Module 3: Designing a Strategy for
Database Server Consolidation
This module describes the benefits
of consolidating database servers in various ways and explains how to use
multiple SQL Server instances to optimize the design of a database server
infrastructure. It also details the process of designing a database server
consolidation plan.
Module 4: Designing a Strategy for
Data Distribution
This module describes the various
tools that are provided by SQL Server 2005 for data distribution and explains
how to select an appropriate tool based on the requirements of an organization.
It also details the process of creating a data distribution plan specifically
for replication.
Module 5: Designing a Database
Server Infrastructure
This module explains how to evaluate
the current database server infrastructure of an organization and gather
requirements for modifying it. It also provides guidelines and best practices
for designing modifications to the current infrastructure and describes the
hardware and software tradeoffs involved in the design process.
Module 6: Designing a Strategy for
Data Recovery
This module explains how to create a
backup and recovery strategy. It also describes the key components of a
database disaster recovery plan and the process of creating it.
Module 7: Establishing Database
Conventions and Standards
This module describes how well a
database naming convention simplifies administration, and provides guidelines
for establishing such a convention. It also explains how to define Transact-SQL
coding, database access, and deployment process standards.