Call 0800 056 3983 to discuss payment options.

Prerequisites

To successfully complete your exams, you should have

• Knowledge and experience with Microsoft SQL Server 2005
• Be a professional database administrator who optimises and maintains database solutions
• At least 3 years dedicated to database work, which includes 2 or more of design, development, deployment, optimisation, maintenance, or support
• Worked in an enterprise or a mid-size organisation.

You should also be an expert in:

• Defining high-availability solutions
• Automating administrative tasks
• Defining security solutions
• Monitoring and troubleshooting the database server
• Designing and executing deployments
• Defining the infrastructure (e.g. storage, hardware, number of servers, etc.).

Who is this course for?

This course is ideal for those looking to highlight their ability to constantly keep up with their company’s enterprise business solutions, and to excel in installing, configuring and maintaining databases.

What will I learn?

Upon successful completion of the course, you will be able to:

SQL Server 2005:

• Install and configure SQL Server 2005
• Implement high availability and disaster recovery
• Support data consumers
• Maintain databases
• Monitor and troubleshoot SQL Server performance
• Create and implement database objects.

Database Design:

• Design a database server infrastructure
• Design security for a database server solution
• Design a physical database
• Design a database solution for high availability
• Design a data recovery solution for a database
• Design a strategy for data archiving.

Database Optimisation and Maintenance:

• Optimise the performance of database servers and databases
• Optimise and implement a data recovery plan for a database
• Design a strategy to monitor and maintain a database solution
• Design a database data-management strategy
• Design a strategy to manage and maintain database security.

What certificate will I get?

After passing this course, you will receive the MCITP Database Administrator certification, as well as the MCTS SQL Server 2005 certification.

You will also have passed one of three exams necessary to gain the MCITP Database Developer certification.

What course materials do I get?

To help you excel in your examinations through effective and efficient learning, ICS will provide you with all the learning materials necessary:

• Flash video tutorials led by a virtual instructor
• Interactive simulations and resources
• E-books
• Practice exams
• Microsoft-branded supporting textbook
• Course and assessment guide to help you pass your exams.

What support do I get?

You will have access to our dedicated ICS Technology Student Community, which will allow you to share and collaborate with your peers, get interactive and expert feedback from tutors and browse our resource library.

You will also receive 12 months of email tutorial support.

Sample Course Material

You need to upgrade your Flash Player!

This web site makes use of the Macromedia Flash player version 8 or newer.

Related Courses

• MCITP Database Developer
• MCTS SQL Server 2005.

Database Administrator (MCITP)

EXAM 70-431

Implementing a Microsoft SQL Server 2005 Database

• Module 1 - Creating Databases and Database Files
• Creating Databases
• Creating Filegroups
• Creating Schemas
• Creating Database Snapshots
• Module 2 - Creating Data Types and Tables
• Creating Data Types
• Creating Tables
• Creating Partitioned Tables
• Module 3 - Using XML
• Retrieving XML by Using FOR XML
• Shredding XML by Using OPENXML
• Using the XML Data Type
• Module 4 - Creating and Tuning Indexes
• Planning Indexes
• Creating Indexes
• Optimising Indexes
• Creating XML Indexes
• Module 5 - Implementing Data Integrity
• Data Integrity Overview
• Implementing Constraints
• Implementing Triggers
• Implementing XML Schemas
• Module 6 - Implementing Views
• Introduction to Views
• Creating and Managing Views
• Optimising Performance by Using Views
• Module 7 - Implementing Stored Procedures and Functions
• Implementing Stored Procedures
• Creating Parameterised Stored Procedures
• Creating Functions
• Handling Errors
• Controlling Execution Context
• Module 8 - Implementing Managed Code in the Database
• Introduction to the SQL Server Common Language Runtime
• Importing and Configuring Assemblies
• Creating Managed Database Objects
• Module 9 - Using Service Broker
• Service Broker Overview
• Creating Service Broker Objects
• Sending and Receiving Messages.

Maintaining a Microsoft SQL Server 2005 Database

• Module 1 - Installing and Configuring SQL Server 2005
• Preparing to install SQL Server
• Installing SQL Server
• Managing a SQL Server 2005 Installation
• Module 2 - Managing Databases and Files
• Planning Databases
• Creating Databases
• Managing Databases
• Module 3 - Disaster Recovery
• Planning a Back-up Strategy
• Backing Up User Databases
• Restoring User Databases
• Recovering Data from Database Snapshots
• System Database and Disaster Recovery
• Module 4 - Managing Security
• Overview of SQL Server Security
• Securing the Server Scope
• Securing the Database Scope
• Managing Keys and Certificates in SQL Server
• Module 5 - Monitoring SQL Server
• Viewing Current Activity
• Using System Monitor
• Using SQL Server Profiler
• Using DDL Triggers
• Using Event Notifications
• Module 6 - Transferring Data
• Overview of Data Transfer
• Introduction to SQL Server Integration Services
• Using SQL Server Integration Services
• Module 7 - 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
• Module 8 - Maintaining High Availability
• Introduction to High Availability
• Implementing Server Clustering
• Implementing Database Mirroring
• Implementing Log Shipping
• Module 9 - Introduction to Replication
• Overview of Replication
• Replication Scenarios.

EXAM 70-443

Designing High Availability Database Solutions Using Microsoft SQL Server 2005

• Determining Appropriate High-Availability Solutions
• Identifying High-Availability Requirements and Constraints
• Analysing High-Availability Requirements and Constraints
• High-Availability Solution
• Determining Storage Requirements for SQL Server Databases
• Determining Storage Requirements for SQL Server Components and Files
• Designing Storage Solutions for SQL Server Databases
• Designing a Back-up and Restore Strategy
• Designing the Platform for Failover Clustering
• Designing the SQL Server Cluster Implementation
• Designing Recovery Strategies and a Test Plan for Failover Clustering
• Migrating and Upgrading SQL Server Clusters
• Designing an Operations Plan for Clustering
• Introduction to Designing a Log Shipping Solution
• Designing Log Shipping Server Roles and Topology
• Designing a Log Shipping Upgrade Strategy
• Designing an Operations Plan for Log Shipping
• Introduction to Designing a Database Mirroring Solution
• Designing Database Roles and Topology for Database Mirroring
• Converting High-Availability Solutions to Database Mirroring
• Designing an Operations Plan for Database Mirroring
• Introduction to Designing a Replication Solution
• Designing a Replication Solution
• Designing a Replication Upgrade Strategy
• Designing an Operations Plan for Replication
• Evaluating Weaknesses in Each High-Availability Technology
• Maximising Availability by Combining High-Availability Technologies
• Documenting High-Availability Solutions
• Creating a Test Plan for High-Availability Solutions.

Designing Security for Microsoft SQL Server 2005

• Principles of Database Security
• Security Standards
• Methodology for Designing a SQL Server Security Policy
• Monitoring SQL Server Security
• Developing an Authentication Policy
• Developing Server-Level Security Policies
• Developing a Secure Communication Policy
• Defining SQL Server Security-Monitoring Standards
• Designing an Instance-Level Security Policy
• Designing a Database-Level Security Policy
• Designing an Object-Level Security Policy
• Defining Security Monitoring Standards for Instances and Databases
• Securing Data by Using Encryption and Certificates
• Designing Data Encryption Policies
• Determining a Key Storage Method
• Analysing Business and Regulatory Requirements
• Determining the Exceptions and Their Impact
• Constraints of Government Regulations
• Designing a Response Policy for Virus and Worm Attacks
• Designing a Response Policy for Denial of Service Attacks
• Designing a Response Policy for Internal and SQL Injection Attacks.

Designing a Microsoft SQL Server 2005 Infrastructure

• Estimating Storage Requirements
• Estimating CPU Requirements
• Estimating Memory Requirements
• Estimating Network Requirements
• Analysing Capacity Needs
• Identifying Requirements That Affect Data Archiving
• Determining the Structure of Archival Data
• Creating a Data Archival Plan
• Overview of Database Server Consolidation
• Designing a Database Server Consolidation Strategy
• Selecting a Data Distribution Tool
• Creating a Data Distribution Plan Using Replication
• Evaluating the Current Database Server Infrastructure
• Gathering Requirements for Changing a Database Server Infrastructure
• Designing Modifications to a Database Server Infrastructure
• Designing a Back-up and Restore Strategy
• Creating a Database Disaster Recovery Plan
• Establishing Database Naming Conventions
• Defining Database Standards.

EXAM 70-444

Designing Security for Microsoft SQL Server 2005

• Principles of Database Security
• Security Standards
• Methodology for Designing a SQL Server Security Policy
• Monitoring SQL Server Security
• Developing an Authentication Policy
• Developing Server-Level Security Policies
• Developing a Secure Communication Policy
• Defining SQL Server Security-Monitoring Standards
• Designing an Instance-Level Security Policy
• Designing a Database-Level Security Policy
• Designing an Object-Level Security Policy
• Defining Security Monitoring Standards for Instances and Databases
• Securing Data by Using Encryption and Certificates
• Designing Data Encryption Policies
• Determining a Key Storage Method
• Analysing Business and Regulatory Requirements
• Determining the Exceptions and Their Impact
• Constraints of Government Regulations
• Designing a Response Policy for Virus and Worm Attacks
• Designing a Response Policy for Denial of Service Attacks
• Designing a Response Policy for Internal and SQL Injection Attacks.

Administering and Automating Microsoft SQL Server 2005 Databases and Servers

• Planning an Automated System for Database Maintenance
• Planning an Automated System for Server Maintenance
• Documenting Administration and Automation Information
• Managing SSIS Packages
• Managing Replication
• Managing Reporting Services.

Troubleshooting and Optimising Database Servers Using Microsoft SQL Server 2005

• Using SQL Server Profiler and System Monitor
• Troubleshoot Performance Issues
• Narrowing Down a Performance Issue to a Database Object
• Troubleshooting Database Server Performance Issues
• Query Performance Troubleshooting Process
• Optimising the Query Performance Environment
• Troubleshooting SQL Server Connectivity Issues
• Areas to Troubleshoot for Connectivity Issues
• Troubleshooting SQL Server Data Issues
• Troubleshooting Data Integrity Issues
• Troubleshooting Concurrency Issues
• SQL Server Data Concurrency Issues.

Assessment

Your knowledge of the course materials will be assessed by three examinations. These can be sat at your local authorised Prometric test centre:

• TS: Microsoft SQL Server 2005 - Implementation and Maintenance: Exam 70-431
• PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005: Exam 70-443
• PRO: Optimising and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005: Exam 70-444

Please note that examination fees are additional to course fees.

Hours of Study

120 hours

Minimum System Requirements

• 600MHz Intel Pentium III processor or equivalent
• Windows 98, Windows 2000, Windows XP or Windows Vista
• 128MB RAM (256MB RAM recommended)
• 128MB available disc space
• CD-ROM drive
• Microsoft Internet Explorer 5.01SP2.