Overview
This course provides database administrators working in enterprise environments with the knowledge and skills to design a Microsoft SQL Server 2005 database infrastructure. It focuses on the development of strategies for data archiving, consolidation, distribution, and recovery. The course also stresses the importance of capacity analysis and emphasises the trade-offs that need to be made during design.
Learning Method:
Instructor led.
There is a heavy emphasis on practical exercises.
Who will benefit from this course?
Current professional database administrators who have on-the-job experience administering SQL Server database solutions.
Pre-requisites:
Before attending this course, you should:
- understand the trade-offs among the different redundant storage types, e.g. what RAID levels mean and how they differ from a Storage Area Networks (SAN)
- understand how replication works and how replication is implemented
- be familiar with reading user requirements and business-need documents, e.g. 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)
- know the trade-offs when backing out of the fully normalized design (denormalisation) and designing for performance and business requirements
- be familiar with design models, such as Star and Snowflake schemas
- have monitoring and troubleshooting skills
- have knowledge of the operating system and platform including 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 such as how integrated authentication interacts with active directory service
- have knowledge of application architecture including how applications can be designed in three layers, what applications can do, interaction between application and the database, interaction between database and the platform or operating system
- be familiar with SQL Server 2005 features, tools, and technologies.
What can you expect to gain from this course?
After completing this course, you will be able to:
- analyse 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.
Course content
Module 1: Analysing capacity needs
- In this module you will be introduced to:
- Gathering data about the current capacity of key system resources such as storage, CPU, memory, and network bandwidth
- Using resulting data to estimate future capacity needs.
Module 2: Designing a strategy for data archiving
- Covers the key elements of a data archival plan and the process of creating it as well as explaining how to:
- identify the requirements that affect data archiving
- determine the structure of archival data
- select an appropriate storage format
- develop a data movement strategy.
Module 3: Designing a strategy for database server consolidation
- This module:
- defines the benefits of consolidating database servers in various ways.
- explains how to use multiple SQL Server instances to optimize the design of a database server infrastructure.
- details the process of designing a database server consolidation plan.
Module 4: Designing a strategy for data distribution
- understanding the various tools that are provided by SQL Server 2005 for data distribution.
- selecting an appropriate tool based on the requirements of an organisation.
- creating a data distribution plan specifically for replication.
Module 5: Designing a database server infrastructure
- evaluating the current database server infrastructure of an organization and gather requirements for modifying it.
- understanding guidelines and best practices for designing modifications to the current infrastructure.
- understanding the hardware and software trade-offs involved in the design process.
Module 6: Designing a strategy for data recovery
- creating a backup and recovery strategy.
- understanding the key components of a database disaster recovery plan and the process of creating it.
Module 7: Establishing database conventions and standards
- understanding database naming conventions and how they can simplify administration and provide guidelines for establishing such a convention.
- defining Transact-SQL (T-SQL) coding, database access, and deployment process standards.