|
|
MCTS SQL Server 2005 (Exam 70-431)
|
|
|
|
|
|
More on Single User, Multi User & Network Licences
More on Online Training
15% Off List Prices
PACKAGE INCLUDESImplementing a Microsoft SQL Server 2005 Database Maintaining a Microsoft SQL Server 2005 Database
6 DVD-ROMs featuring live instructor-led classroom sessions with full audio, video and demonstration components Printable courseware Step-by-step hands-on-labs Comprehensive, understandable, and reusable as reference material Focused on practical solutions to real-world development problems 2 Microsoft Authorized MeasureUp Exam Simulations Exclusive LearningZone Live Mentor(Value at £290) Help whenever you need it! Exclusive LearningZone - Why wait for email support? Chat Live with our Certified Instructors anytime around the clock (24x7)
COURSE INTRODUCTIONThe MCTS certifications enable professionals to target specific technologies and distinguish themselves by demonstrating in-depth knowledge and expertise in their specialized technologies.
A MCTS Certification in Microsoft SQL Server 2005 (MCTS: SQL Server 2005) teaches technicians how to implement and maintain databases using specific instructions and specifications. Those who possess a MCTS Certification also have thorough knowledge of the product, understand how to use the tools and Transact-SQL language, and know how to explore the user interface. Additionally, MCTS Certification holders will typically pursue careers as database administrators, database developers, or business intelligence developers. They may also be developers and systems administrators who do not work with SQL Server daily but who want to show their breadth of technology experience.
Career Academy Training for SQL Server 2005 helps database administrators, database developers, and business intelligence developers focus their MCTS Certification exam preparation training and find learning resources.
How to Earn Your MCTS : SQL 2005 Earn your MCTS Certification by passing the following exam that focuses on the implementation and maintenance of SQL Server 2005:
Exam 70-431 : Microsoft SQL Server 2005 - Implementation and Maintenance
Required TrainingImplementing a Microsoft SQL Server 2005 Database
The course focuses on teaching individuals how to use SQL Server 2005 product features and tools related to implementing a database. The course is intended for IT Professionals wanting to learn SQL Server 2005 product features and technologies.
9 CD-ROMs featuring live instructor-led classroom sessions with full audio, video and demonstration components with over 8 hours of instruction by Mark Russell Printable courseware Sample codes Step-by-step hands-on-labs MeasureUp Exam Simulator
Maintaining a Microsoft SQL Server 2005 Database
The course focuses on teaching individuals how to use SQL Server 2005 product features and tools related to maintaining a database. This course is intended for IT Professionals wanting to learn SQL Server 2005 product features and technologies.
9 CD-ROMs featuring live instructor-led classroom sessions with full audio, video and demonstration components with over 8 hours of instruction by Mark Russell Printable courseware Sample codes Step-by-step hands-on-labs MeasureUp Exam Simulator
SPECIAL FEATURES OF OUR TRAINING COURSES |
Comprehensive Menu Structure
Move through hours of in-depth content - quickly and easily with the time saving Main Menu and Sub-Menu structure. Search for a specific topic or go through the course step by step. The choice is yours.
|  |
Player Controls
Move forward or back, repeat entire topics or just a section. A progress bar illuminates as you advance through the video tutorials and exercises. Play, pause rewind, fast forward or stop at any time
|  |
Continue Where You Left Off
All courses are resumed to where you left off last session allowing you to learn when it is convenient for you without the hassle of remembering where you were.
|  |
Full Motion Video and Audio
All courses feature full-motion videos of instructors teaching the information as if they are speaking directly to you. Our unique delivery simulates a one-on-one classroom environment creating a more personal lesson and learning experience.
|  |
Printable Study Guides
Printable study guides for the entire course are included. This allows all material to be viewed, reviewed, and printed for viewing at a later date.
|  |
Review Exercises
Each section has a review quiz to aid in the learning process by validating the comprehension of the material covered before moving on to a new section.
|  |
24/7 Online Tutor Support
Train around the clock, around the world. Our certified online instructors are located at global support centers in the U.S., U.K., Australia, and Singapore to provide real-time answers to technology- and soft-skill-related questions 24x7. This means access to a live subject matter expert no matter where you are - day or night. This level of student/instructor interaction provides real-time training specific support and extends training beyond content and instructor-led solutions by providing flexibility to accommodate individual needs and schedules.
|  |
Authorised Practice Exams
We include an Online Subscription to Authorised Practice Exams from Certified Practice Test Providers to fully prepare you for the certification exams. Take the Practice Exams as many times as needed until you are 100% confident of passing the Certification Exam.
|  |
Live Demonstrations
Demonstrations are a way for the instructor to show and tell the user how to perform a task by actually doing it on screen in front of them. In this format it gives the user the power to see things done by a professional in the intended environment as many times as they would like.
|  |
Interactive Labs
This course features cutting-edge, interactive labs, which provide a hands-on learning experience. This environment allows you to interact with the software without the need for purchasing it or having it installed on your computer.
|  |
PowerPoint Presentations
Utilizing PowerPoint presentations enhances the delivery by displaying a variety of visual information to the user. This type of representation allows the user to better interpret the material through charts, definitions, graphs, and more...
|  |
Multi-User Licencing
All of our courses can be purchased with a standard Single User Licence, Multi User Library Licence or a Network Licence. Please contact us for more information and prices.
|  |
Certificate of Completion
Our Training Courses are recognized worldwide for their technology-based IT training curriculums. Upon successful completion of our program, you will be receiving a CareerVision Distance Education Certificate of Completion.
|
|
COURSE OUTLINE
|
IMPLEMENTING A SQL SERVER 2005 DATABASE
COURSE OUTLINECourse IntroductionCourse Introduction
| Creating Databases and Database FilesLesson 1: Databases
Considerations
Transaction Logging
Options
Finding Information about Databases
Demo: Creating a Database
Lesson 2: Filegroups
What Are Filegroups?
Why Create Filegroups?
Demo: Creating a Filegroup
Lesson 3: Schemas
What is a Schema?
Object Name Resolution
Demo: Creating a Schema
Lesson 4: Database Snapshots
What is a Database Snapshot?
How does a Database Snapshot Work?
Demo: Creating a Database Snapshot
Module 1 Review
| Creating Data Types and TablesLesson 1: Data Types
System-Supplied Data Types
Alias Data Types
Demo: Creating a Data Type
Lesson 2: Tables
Data Organization in Rows
Large Data Value Organization
Considerations
Demo: Creating a Table
Lesson 3: Partitioning a Table
What is a Partitioned Table?
What is a Partition Function?
What is a Partition Scheme?
Demo: Creating a Partitioned Table
What can you do with Partitioned Data?
Module 2 Review
| Using XMLLesson 1: Using FOR XML
Introduction
RAW Mode Queries
AUTO Mode Queries
EXPLICIT Mode Queries
PATH Mode Queries
Retrieving Nested XML
Demo: Using FOR XML
Lesson 2: Using OPENXML
Overview
Managing In-Memory Node Trees
OPENXML Syntax
XML Namespaces
Demo: Shredding XML
Lesson 3: xml Data Type
Introduction to the xml Data Type
Xquery
Query, Value, and Exist Methods
Modify Method
The nodes Method
Demo: The xml Data Type
Module 3 Review
| Creating and Tuning IndexesLesson 1: Planning
SQL Server Data Access
Clustered Indexes
Heaps
Nonclustered Indexes
Lesson 2: Creating
Introduction
Unique Indexes
Creating Indexes with Multiple Columns
Indexes on Computed Columns
Partitioned Indexes
Incorporating Free Space in Indexes
Obtaining Index Information
Demo: Creating Indexes
Lesson 3: Optimizing
Database Tuning Advisor
Demo: Database Tuning Advisor
Index Fragmentation
Defragmenting Indexes
Demo: Defragmenting Indexes
Lesson 4: XML Indexes
Kinds of XML Indexes
Module 4 Review
| Implementing Data IntegrityLesson 1: Overview
Types of Data Integrity
Enforcing Data Integrity
Lesson 2: Constraints
Introduction
PRIMARY KEY
DEFAULT
CHECK
UNIQUE
FOREIGN KEY
Cascading Referential Integrity
Considerations
Demo: Creating Constraints
Lesson 3: Triggers
Introduction
INSERT Triggers
DELETE Triggers
UPDATE Triggers
How an INSTEAD OF Trigger Works
Nested Triggers
Recursive Triggers
Demo: Creating Triggers
Lesson 4: XML Schemas
Introduction
XML Schema Collections
Typed XML
Demo: Typed XML
Module 5 Review
| Implementing ViewsLesson 1: Introduction
What are Views?
Kinds of Views
Advantages
Lesson 2: Managing Views
Creating Views
Demo: Creating a View
Altering and Dropping Views
How Ownership Chains Affect Views
How To Get Information About Views
Encryption
Considerations for Modifying Data
Lesson 3: Optimizing Performance
Considerations
Indexed Views
Partitioned Views
Module 6 Review
| Implementing Stored Procedures and FunctionsLesson 1: Introduction
Stored Procedures
Creating Stored Procedures
Guidelines
Altering & Dropping Stored Procedures
Lesson 2: Parameterized SP's
Using Input Parameters
Using Output Parameters/Return Values
Demo: Creating a Parameterized SP
Lesson 3: Functions
Types of Functions
Scalar Functions
Inline Table-Valued Functions
Multi-Statement Table-Valued Functions
Demo: Creating Functions
Lesson 4: Error Handling
Structured Exception Handling
Guidelines
Demo: Handling Errors
Lesson 5: Execution Context
Execution Context
EXECUTE AS
Extending Impersonation Context
Demo: Controlling Execution Context
Module 7 Review
| Implementing Managed Code in the DatabaseLesson 1: Introduction
Advantages
SQL Server CLR Integration
When to use Managed Code
Lesson 2: Importing Assemblies
What are Assemblies
Importing an Assembly
Assembly Trust Levels
Demo: Import & Configure an Assembly
Lesson 3: Creating Database Objects
Overview
Stored Procedures, Triggers, Functions
Aggregates and User-Defined Types
Demo: Managed Database Objects
Module 8 Review
| Using Service BrokerLesson 1: Overview
Introduction
System Architecture
Conversation Architecture
Conversation Process
Security Architecture
Lesson 2: Service Broker Objects
Creating Message Types
Creating Contracts
Creating Queues
Creating Services
Demo: Creating Service Broker Objects
Lesson 3: Messages
Sending Messages
Receiving Messages
Demo: Sending and Receiving Messages
Module 9 Review
Course Closure
|
MAINTAINING A SQL 2005 DATABASE
COURSE OUTLINECourse IntroductionCourse Introduction
| Installing and Configuring SQL Server 2005Lesson 1: Preparing to Install
SQL Server 2005 Editions
Hardware Requirements
Software Requirements
SQL Server Instances
Licensing Options
Security Considerations
Collation Considerations
Considerations for Upgrading
Demo - Finding Pre-Installation Info
Lesson 2: Installing
Overview of the Installation Process
System Consistency Checker?
Options
Performing an Unattended Installation
Demo: Managing Comp Installations
Lesson 3: Managing an Installation
Surface Area Configuration Tool
SQL Server Configuration Manager
SQL Server Management Studio
Demo: SQL Server Management Studio
sqlcmd
Demo: sqlcmd
Server-Level Options?
Module 1 Review
| Managing Databases and FilesLesson 1: Planning
Data Storage
Transaction Logging
File Placement
What Are Filegroups?
Filegroup Considerations
Capacity Planning Considerations
Lesson 2: Creating
Considerations
Syntax for Creating a Database
Schemas
Syntax for Creating a Schema
Name Resolution
Demo: Creating Databases
Lesson 3: Managing
Options
Finding Information about Databases
Demo: Finding Information
Module 2 Review
| Disaster RecoveryLesson 1: Backup Strategy
Backup Types
Recovery Models?
Full Database Backup Strategy
DB & Transaction Log Backup Strategy
Differential Backup Strategy
File or Filegroup Backup Strategy
Backup Operators
Backup Media
Lesson 2: Back Up User Databases
Full Database Backup
Transaction Log Backup
Differential Backup
File and Filegroup Backups
Ensuring Backup Integrity
Demo: Back Up a Database
Lesson 3: Restore User Databases
The Restore Process
Restoring a Database
Restoring a Transaction Log Backup
Restoring Pages, Files, and Filegroups
Piecemeal Restore
Demo: Restoring a Database
Lesson 4: Recover Data -DB Snapshots
Database Snapshots
How Database Snapshots Work
Recovering Data from a DB Snapshot
Lesson 5: Disaster Recovery
Backing Up System Databases
Restoring System Databases
Restoring the Master Database
Module 3 Review
| Managing SecurityLesson 1: Overview
Principals
Securables
Permissions
Lesson 2: Server Scope
Authentication Modes
Password Policies
Managing SQL Server Logins
Fixed Server Roles
Delegation
Credentials
Server-Scope Permissions
Demo: Managing Logins
Lesson 3: Database Scope
Managing Users
Special Users
Database Roles
Application Roles
Database-Scope Permissions
Schema-Scope Permissions
Demo: Managing Users
Lesson 4: Managing Keys & Certificates Keys
Certificates?
Cryptography Architecture
Keys and Certificates
Demo: Using Certificates
Module 4 Review
| Monitoring SQL ServerLesson 1: Current Activity
Activity Monitor
Dynamic Management Objects
Demo: Viewing Current Activity
Lesson 2: System Monitor
System Monitor
Performance Objects
Monitoring SQL Server
Demo: Using System Monitor
Lesson 3: SQL Server Profiler
SQL Server Profiler
Profiler Trace Options
Categories, Events, and Columns
Demo: Using SQL Server Profiler
Lesson 4: DDL Triggers
Introduction
Creating DDL Triggers
Managing DDL Triggers
Demo: Creating a DDL Trigger
Lesson 5: Event Notifications
Introduction
Creating Event Notifications
Processing Event Notifications
Managing Event Notifications
Demo: Implementing Event Notifications
Module 5 Review
| Transfering DataLesson 1: Introduction to Data Transfer
Data Transfer
Data Transformation
Tools for Transferring Data
Lesson 2: SQL Server Integration Srvcs
Introduction to SSIS
SSIS Task Processing
SSIS Tools
Demo: Import and Export Wizard
Lesson 3: How to use SSIS
Data Sources and Data Source Views
Connection Manager
Package Control Flow
Data Flow Tasks
Deploying Packages
Module 6 Review
| Automating Administrative TasksLesson 1: Overview
Benefits
The Maintenance Plan Wizard
The SQL Server Agent
Demo: The Maintenance Plan Wizard
Lesson 2: Configuring SQL Server Agent
Considerations
SQL Server Agent Mail Options
Demo: Database Mail
Lesson 3: Jobs and Operators Jobs
Operators
Creating Job Steps
The Job Activity Monitor
Demo: Jobs and Operators
Lesson 4: Alerts
Introduction
Creating an Alert
Demo: Alerts
Lesson 5: Multiple Servers
Master and Target Servers
Considerations
Event Forwarding
Lesson 6: Managing Security
SQL Server Agent Roles
SQL Server Agent Proxies
Demo: SQL Server Agent Proxies
Module 7 Review
| Maintaining High AvailabilityLesson 1: Introduction
Availability Factors
Considerations
Lesson 2: Server Clustering
Introduction
Considerations
Installing SQL Server in a Cluster
Lesson 3: Database Mirroring
Introduction
Options
Database Mirroring Endpoints
Preparing Servers for Database Mirroring
Establishing a Mirror Session
Handling Failover
Lesson 4: Log Shipping
Introduction
Implementing Log Shipping
Switching Server Roles
Demo: Log Shipping
Module 8 Review
| Introduction to ReplicationLesson 1: Introduction
Introduction
Replication Roles
Replication Types
Articles, Publications, and Subscriptions
Lesson 2: Scenarios
Server to Server Scenarios
Server to Client Scenarios
Module 9 Review
Course Closure
|
|
|
|
|
|
|
|
Printer friendly version
|
|
|