TS: Microsoft SQL Server 2005 Implementation and Maintenance training for Exam 70-431 - Course Outline

Estimated Time to Complete - 50 hours

 Lecture Videos: 66 Lessons (5 Hrs 13 Mins)
 Demonstration Videos: 78 Lessons (7 Hrs 18 Mins)
 Hands-on Labs: 37 Scenarios (Student lab activities)
 Fact Sheets: 115 (each with 1 or more printable pages)
 Exam Questions: 253 (each with answers and explanations)
 Exams: 52 Practice Tests (each with detailed reporting)

1.0 SQL Server

• 1.1 SQL Server Overview 
  
   1.1.1 Course Introduction (2 Mins 22 Secs)
   1.1.2 SQL Server 2005 Overview 
   1.1.3 SQL Server Editions (1 Mins 43 Secs)
   1.1.4 Server Edition Facts 
  
  1.2 Management Tools 
  
   1.2.1 SQL Management Tools (6 Mins 55 Secs)
   1.2.2 Using Management Tools (14 Mins 17 Secs)
   1.2.3 SQL Tool Facts 
  
  1.3 Database Concepts 
  
   1.3.1 SQL Server Overview (4 Mins 22 Secs)
   1.3.2 Relational Database Concepts 
   1.3.3 SQL Databases 
   1.3.4 Database Objects 
   1.3.5 Data Types (9 Mins 39 Secs)
   1.3.6 Data Type Facts 
  
  1.4 Transact-SQL Overview 
  
   1.4.1 Query Facts 
   1.4.2 SQL Server Objects (2 Mins 13 Secs)
   1.4.3 Identifier Facts 
   1.4.4 Batch Facts

2.0 Installation (11 questions)

• 2.1 SQL Server Installation 
  
   2.1.1 SQL Server Installation (5 Mins 48 Secs)
   2.1.2 Installing SQL Server (13 Mins 39 Secs)
   2.1.3 Installation Requirements 
   2.1.4 Installation Facts 
  
  2.2 Upgrade and Coexistence 
  
   2.2.1 Using Named Instances (3 Mins 37 Secs)
   2.2.2 Instance Facts 
   2.2.3 Running the Upgrade Advisor (3 Mins 06 Secs)
   2.2.4 Upgrade Facts 
   2.2.5 ExamSim Questions - Section 2.2 
  
  2.3 Post Installation 
  
   2.3.1 Configuring Server Properties (2 Mins 07 Secs)
   2.3.2 Configure Server Properties 
   2.3.3 Using the Surface Scan Area Utility (4 Mins 05 Secs)
   2.3.4 Configuring Services and Protocols (3 Mins 25 Secs)
   2.3.5 Services Facts 
   2.3.6 Net-Library Facts 
   2.3.7 SQL Server Configuration 
   2.3.8 Troubleshooting Installation 
   2.3.9 ExamSim Questions - Section 2.3

3.0 Databases (9 questions)

• 3.1 Database Files 
  
   3.1.1 Database and Log Files (4 Mins 58 Secs)
   3.1.2 Managing Databases (10 Mins 14 Secs)
   3.1.3 Data File Facts 
   3.1.4 Create Database Facts 
   3.1.5 Create Database Files 
   3.1.6 Database Properties 
   3.1.7 Modify Database Properties 
   3.1.8 Create a Filegroup 
   3.1.9 ExamSim Questions - Section 3.1 
  
  3.2 Recovery Model 
  
   3.2.1 Recovery Models (2 Mins 04 Secs)
   3.2.2 Setting the Recovery Model (3 Mins 06 Secs)
   3.2.3 Recovery Model Facts 
   3.2.4 Reset the Recovery Model 
   3.2.5 ExamSim Questions - Section 3.2 
  
  3.3 Database Mail 
  
   3.3.1 Configuring Database Mail (8 Mins 20 Secs)
   3.3.2 Database Mail Facts 
   3.3.3 Configure Database Mail 
   3.3.4 ExamSim Questions - Section 3.3

4.0 Database Objects (34 questions)

• 4.1 Tables 
  
   4.1.1 Tables (4 Mins 08 Secs)
   4.1.2 Table Facts 
  
  4.2 Constraints 
  
   4.2.1 Constraints (8 Mins 30 Secs)
   4.2.2 Using DEFAULT (3 Mins 03 Secs)
   4.2.3 Using CHECK (2 Mins 59 Secs)
   4.2.4 Using PRIMARY KEY (2 Mins 40 Secs)
   4.2.5 Using UNIQUE (1 Mins 20 Secs)
   4.2.6 Using FOREIGN KEY (1 Mins 21 Secs)
   4.2.7 Constraint Facts 
   4.2.8 Constraint Management Facts 
   4.2.9 ExamSim Questions - Section 4.2 
  
  4.3 Indexes 
  
   4.3.1 Indexes (11 Mins 10 Secs)
   4.3.2 Creating Indexes (4 Mins 53 Secs)
   4.3.3 Index Facts 
   4.3.4 Index Management Facts 
   4.3.5 ExamSim Questions - Section 4.3 
  
  4.4 Triggers 
  
   4.4.1 Triggers (9 Mins 23 Secs)
   4.4.2 Implementing DML Triggers (4 Mins 15 Secs)
   4.4.3 Implementing INSTEAD OF Triggers (1 Mins 31 Secs)
   4.4.4 DML Trigger Facts 
   4.4.5 DDL Triggers (8 Mins 58 Secs)
   4.4.6 Using DDL Triggers (4 Mins 34 Secs)
   4.4.7 DDL Trigger Facts 
   4.4.8 Nesting and Recursion (2 Mins 37 Secs)
   4.4.9 Nested and Recursive Triggers 
   4.4.10 ExamSim Questions - Section 4.4 
  
  4.5 Views 
  
   4.5.1 Views (4 Mins 10 Secs)
   4.5.2 Creating Updatable Views (2 Mins 53 Secs)
   4.5.3 View Facts 
   4.5.4 ExamSim Questions - Section 4.5 
  
  4.6 Stored Procedures 
  
   4.6.1 Stored Procedures (5 Mins 50 Secs)
   4.6.2 Defining a Stored Procedure (13 Mins 18 Secs)
   4.6.3 Stored Procedure Facts 
   4.6.4 Working with Managed Code (12 Mins 34 Secs)
   4.6.5 Managed Code Facts 
   4.6.6 ExamSim Questions - Section 4.6 
  
  4.7 Functions 
  
   4.7.1 Function Facts 
   4.7.2 Using Functions (2 Mins 29 Secs)
   4.7.3 User-defined Functions (6 Mins 06 Secs)
   4.7.4 Implementing User-defined Functions (6 Mins 16 Secs)
   4.7.5 User-defined Function Facts 
   4.7.6 Identifying Deterministic Functions (2 Mins 25 Secs)
   4.7.7 Deterministic Function Facts 
   4.7.8 ExamSim Questions - Section 4.7

5.0 Data Manipulation (14 questions)

• 5.1 Queries 
  
   5.1.1 Queries (0 Mins 59 Secs)
   5.1.2 Writing Queries (4 Mins 45 Secs)
   5.1.3 Working with Templates and Projects (3 Mins 14 Secs)
   5.1.4 Using Transact-SQL 
   5.1.5 DML Statements (4 Mins 23 Secs)
   5.1.6 Using DML Statements (3 Mins 15 Secs)
   5.1.7 DML Statement Facts 
   5.1.8 System Table Facts 
   5.1.9 ExamSim Questions - Section 5.1 
  
  5.2 Collations 
  
   5.2.1 Collations (6 Mins 05 Secs)
   5.2.2 Managing Collations (4 Mins 18 Secs)
   5.2.3 Collation Facts 
   5.2.4 ExamSim Questions - Section 5.2 
  
  5.3 Data Types 
  
   5.3.1 Data Type Precedence (10 Mins 18 Secs)
   5.3.2 Identifying Data Type Precedence (8 Mins 26 Secs)
   5.3.3 Data Type Precedence Facts 
   5.3.4 Creating User-defined Data Types (5 Mins 44 Secs)
   5.3.5 User-defined Data Type Facts 
  
  5.4 Error Handling 
  
   5.4.1 Error Handing (6 Mins 41 Secs)
   5.4.2 Handling Errors and Exceptions (3 Mins 18 Secs)
   5.4.3 Error Handling Facts 
  
  5.5 Transactions 
  
   5.5.1 Transactions (1 Mins 02 Secs)
   5.5.2 Defining Transactions (4 Mins 13 Secs)
   5.5.3 Transaction Facts 
   5.5.4 ExamSim Questions - Section 5.5 
  
  5.6 Common Table Expression 
  
   5.6.1 Common Table Expressions (5 Mins 42 Secs)
   5.6.2 Common Table Expressions Facts

6.0 Security (27 questions)

• 6.1 Security Concepts 
  
   6.1.1 SQL Server Security Concepts (6 Mins 04 Secs)
   6.1.2 SQL Security Concepts 
   6.1.3 Authentication Mode Facts 
   6.1.4 Change the Authentication Mode 
   6.1.5 ExamSim Questions - Section 6.1 
  
  6.2 Logins and Users 
  
   6.2.1 Logins and Database Users (2 Mins 35 Secs)
   6.2.2 Mapping Logins to Database Users (2 Mins 18 Secs)
   6.2.3 Login Operations 
   6.2.4 Database User Operations 
   6.2.5 Create Login Accounts 
   6.2.6 Deny Database Access 
   6.2.7 ExamSim Questions - Section 6.2 
  
  6.3 Roles 
  
   6.3.1 Using Server Roles (1 Mins 23 Secs)
   6.3.2 Assign Server Roles 
   6.3.3 Using Database Roles (1 Mins 43 Secs)
   6.3.4 Assign Database Roles 
   6.3.5 Using Application Roles (3 Mins 24 Secs)
   6.3.6 Role Facts 
   6.3.7 Role Operations 
   6.3.8 ExamSim Questions - Section 6.3 
  
  6.4 Permissions 
  
   6.4.1 Object Permissions (2 Mins 09 Secs)
   6.4.2 Applying Object Permissions (3 Mins 35 Secs)
   6.4.3 Permission Facts 
   6.4.4 Permission Operations 
   6.4.5 Set Database Permissions 
   6.4.6 Set Permissions on a View 
   6.4.7 Set Permissions on a Stored Procedure 
   6.4.8 Deny Server Access 
   6.4.9 Configure Database Access 
   6.4.10 Execution Context (1 Mins 40 Secs)
   6.4.11 Execution Context Facts 
   6.4.12 ExamSim Questions - Section 6.4 
  
  6.5 Schemas 
  
   6.5.1 Schemas (4 Mins 02 Secs)
   6.5.2 Working with Schemas (6 Mins 15 Secs)
   6.5.3 Schema Facts 
  
  6.6 Encryption 
  
   6.6.1 Encryption Facts 
   6.6.2 ExamSim Questions - Section 6.6

7.0 Protection and Availability (39 questions)

• 7.1 Recovery Planning 
  
   7.1.1 Recovery Models (7 Mins 11 Secs)
   7.1.2 Recovery Model Facts 
   7.1.3 Backup Types (4 Mins 54 Secs)
   7.1.4 Backup Type Facts 
   7.1.5 Backup Considerations (4 Mins 02 Secs)
   7.1.6 Recovery Planning Facts 
   7.1.7 ExamSim Questions - Section 7.1 
  
  7.2 Backup 
  
   7.2.1 Backup Operations (5 Mins 22 Secs)
   7.2.2 Backup Facts 
   7.2.3 Performing a Backup (4 Mins 02 Secs)
   7.2.4 Back Up to a File 
   7.2.5 Managing Backup Devices (3 Mins 04 Secs)
   7.2.6 Create Backup Devices 
   7.2.7 Back Up to a Device 
   7.2.8 Change the Recovery Model 
   7.2.9 Backing Up Transaction Logs (5 Mins 08 Secs)
   7.2.10 Using Backup Statements (2 Mins 21 Secs)
   7.2.11 Backup Operation Facts 
   7.2.12 Implement a Backup Strategy 
   7.2.13 Implement a Filegroup Backup 
   7.2.14 ExamSim Questions - Section 7.2 
  
  7.3 Recovery 
  
   7.3.1 Restore Operations (6 Mins 46 Secs)
   7.3.2 Recovery Strategies (3 Mins 53 Secs)
   7.3.3 Recovery Facts 
   7.3.4 Restore Operation Facts 
   7.3.5 Restoring a Full Backup (5 Mins 54 Secs)
   7.3.6 Using Restore Options (4 Mins 56 Secs)
   7.3.7 Perform a Simple Recovery 
   7.3.8 Perform a Full Recovery 
   7.3.9 Restore to a Point in Time 
   7.3.10 Online Restore (0 Mins 32 Secs)
   7.3.11 Advanced Restore Facts 
   7.3.12 ExamSim Questions - Section 7.3 
  
  7.4 Moving a Database 
  
   7.4.1 Strategies for Moving Databases (2 Mins 18 Secs)
   7.4.2 Using the Copy Database Wizard (4 Mins 14 Secs)
   7.4.3 Database Moving Facts 
   7.4.4 ExamSim Questions - Section 7.4 
  
  7.5 Database Mirroring 
  
   7.5.1 Database Mirroring (5 Mins 53 Secs)
   7.5.2 Configuring Database Mirroring (8 Mins 26 Secs)
   7.5.3 Database Mirroring Facts 
   7.5.4 Mirroring Configuration Facts 
   7.5.5 ExamSim Questions - Section 7.5 
  
  7.6 Log Shipping 
  
   7.6.1 Log Shipping (0 Mins 57 Secs)
   7.6.2 Configuring Log Shipping (6 Mins 26 Secs)
   7.6.3 Log Shipping Facts 
   7.6.4 Configure Log Shipping 
   7.6.5 Monitoring Log Shipping (2 Mins 45 Secs)
   7.6.6 ExamSim Questions - Section 7.6 
  
  7.7 Database Snapshots 
  
   7.7.1 Database Snapshots (7 Mins 05 Secs)
   7.7.2 Snapshot Facts 
   7.7.3 Creating a Database Snapshot (7 Mins 07 Secs)
   7.7.4 Recovering with a Snapshot (2 Mins 36 Secs)
   7.7.5 Snapshot Configuration Facts 
   7.7.6 ExamSim Questions - Section 7.7 
  
  7.8 High Availability 
  
   7.8.1 Availability Solutions (1 Mins 51 Secs)
   7.8.2 High Availability Facts

8.0 Data Access (41 questions)

• 8.1 Linked Servers 
  
   8.1.1 Linked Servers (5 Mins 18 Secs)
   8.1.2 Configuring a Linked Server (6 Mins 37 Secs)
   8.1.3 Linked Server Facts 
   8.1.4 Managing Security Mappings 
   8.1.5 Managing OLE DB Datasources 
   8.1.6 Distributed Queries 
   8.1.7 Create a Linked Server 1 
   8.1.8 Create a Linked Server 2 
   8.1.9 Create a Linked Server 3 
   8.1.10 Create a Linked Server 4 
   8.1.11 ExamSim Questions - Section 8.1 
  
  8.2 Service Broker 
  
   8.2.1 SQL Server Broker Architecture (6 Mins 39 Secs)
   8.2.2 Broker Configuration (1 Mins 46 Secs)
   8.2.3 Creating Service Brokers (8 Mins 36 Secs)
   8.2.4 Service Broker Facts 
   8.2.5 Service Broker Configuration Facts 
   8.2.6 Messages (6 Mins 32 Secs)
   8.2.7 Sending and Receiving Messages (7 Mins 52 Secs)
   8.2.8 Messages Facts 
   8.2.9 ExamSim Questions - Section 8.2 
  
  8.3 Data Transfer 
  
   8.3.1 Data Transfer Methods (2 Mins 35 Secs)
   8.3.2 Data Transfer Methods Facts 
   8.3.3 Using the BCP Utility (2 Mins 44 Secs)
   8.3.4 BCP Facts 
   8.3.5 Using SQL Server Integration Services (14 Mins 56 Secs)
   8.3.6 SSIS Package Facts 
   8.3.7 ExamSim Questions - Section 8.3 
  
  8.4 Data Replication 
  
   8.4.1 Replication (10 Mins 55 Secs)
   8.4.2 Replication Facts 
   8.4.3 Implementing Snapshot Replication (17 Mins 22 Secs)
   8.4.4 Implementing Merge Replication (18 Mins 13 Secs)
   8.4.5 Implementing Transactional Replication (14 Mins 15 Secs)
   8.4.6 Replication Configuration Facts 
   8.4.7 Configure a Publisher 
   8.4.8 Create a Push Subscription 
   8.4.9 Configure Merge Replication 
   8.4.10 Remove a Subscriber 
   8.4.11 ExamSim Questions - Section 8.4 
  
  8.5 HTTP Endpoints 
  
   8.5.1 HTTP Endpoints (5 Mins 19 Secs)
   8.5.2 Securing an HTTP Endpoint (2 Mins 53 Secs)
   8.5.3 HTTP Endpoint Facts 
   8.5.4 ExamSim Questions - Section 8.5

9.0 XML Data (8 questions)

• 9.1 XML Data 
  
   9.1.1 XML Data Type (7 Mins 42 Secs)
   9.1.2 Using XML (1 Mins 45 Secs)
   9.1.3 XML Data Facts 
   9.1.4 ExamSim Questions - Section 9.1 
  
  9.2 XML Schemas 
  
   9.2.1 Schemas and Schema Collections (1 Mins 57 Secs)
   9.2.2 Managing XML Schemas (2 Mins 25 Secs)
   9.2.3 XML Schema Facts 
   9.2.4 ExamSim Questions - Section 9.2 
  
  9.3 XML Queries 
  
   9.3.1 Query Methods (2 Mins 18 Secs)
   9.3.2 Querying XML Data (9 Mins 13 Secs)
   9.3.3 Working with XML Data Types (1 Mins 52 Secs)
   9.3.4 Bulk Loading XML Data (0 Mins 56 Secs)
   9.3.5 Using XML Data 
   9.3.6 ExamSim Questions - Section 9.3 
  
  9.4 XML Indexes 
  
   9.4.1 XML Index Creation (1 Mins 43 Secs)
   9.4.2 Creating XML Indexes (1 Mins 38 Secs)
   9.4.3 XML Index Facts 
   9.4.4 ExamSim Questions - Section 9.4

10.0 Management and Performance (63 questions)

• 10.1 Monitoring Tools 
  
   10.1.1 Monitoring Tools (5 Mins 47 Secs)
   10.1.2 Using SQL Profiler (8 Mins 11 Secs)
   10.1.3 SQL Profiler Facts 
   10.1.4 Using System Monitor (5 Mins 58 Secs)
   10.1.5 System Monitor Facts 
   10.1.6 Using DMVs and Functions (3 Mins 43 Secs)
   10.1.7 DMV Facts 
   10.1.8 Dedicated Administrator Connection (DAC) (4 Mins 24 Secs)
   10.1.9 Using DAC (2 Mins 33 Secs)
   10.1.10 DAC Facts 
   10.1.11 ExamSim Questions - Section 10.1 
  
  10.2 Jobs and Alerts 
  
   10.2.1 Jobs, Alerts, and Operators (8 Mins 32 Secs)
   10.2.2 Job Monitoring (0 Mins 39 Secs)
   10.2.3 Creating a Job Mechanism (11 Mins 12 Secs)
   10.2.4 Job and Alerts Facts 
   10.2.5 Create an Operator 
   10.2.6 Create and Start Jobs 
   10.2.7 Create an Alert 
   10.2.8 ExamSim Questions - Section 10.2 
  
  10.3 Database Maintenance 
  
  10.3.1 Database Maintenance (9 Mins 19 Secs)
   10.3.2 Creating a Database Maintenance Plan (7 Mins 11 Secs)
   10.3.3 Maintenance Plan Facts 
   10.3.4 Database Size (2 Mins 55 Secs)
   10.3.5 Shrinking Database Files (9 Mins 04 Secs)
   10.3.6 Managing Database Size 
   10.3.7 Verifying Database Integrity (1 Mins 42 Secs)
   10.3.8 DBCC Facts 
   10.3.9 ExamSim Questions - Section 10.3 
  
  10.4 Full-text Search 
  
   10.4.1 Populating a Full-Text Catalog and Index (4 Mins 47 Secs)
   10.4.2 Full-text Search Facts 
   10.4.3 ExamSim Questions - Section 10.4 
  
  10.5 Query Performance 
  
   10.5.1 Query Performance (2 Mins 38 Secs)
   10.5.2 Enhancing Performance (7 Mins 15 Secs)
   10.5.3 Query Optimization Facts 
   10.5.4 Database Engine Tuning Advisor 
   10.5.5 ExamSim Questions - Section 10.5 
  
  10.6 Partitioning 
  
   10.6.1 Partitioning Tables (12 Mins 27 Secs)
   10.6.2 Implementing a Data Partition (6 Mins 57 Secs)
   10.6.3 Partitioning Facts 
   10.6.4 Partition Management Facts 
   10.6.5 ExamSim Questions - Section 10.6 
  
  10.7 Index Optimization 
  
   10.7.1 Index Optimizations (0 Mins 55 Secs)
   10.7.2 Optimizing Indexes (1 Mins 50 Secs)
   10.7.3 Index Optimization 
   10.7.4 Index Fragmentation (3 Mins 26 Secs)
   10.7.5 Index Management Facts 
   10.7.6 ExamSim Questions - Section 10.7 
  
  10.8 Blocks and Deadlocks 
  
   10.8.1 Locking and Deadlocks (3 Mins 18 Secs)
   10.8.2 Monitoring Locking and Deadlocks (9 Mins 35 Secs)
   10.8.3 Locking Facts 
   10.8.4 Blocking Facts 
   10.8.5 Troubleshooting Locking Facts 
   10.8.6 ExamSim Questions - Section 10.8 
  
  10.9 Replication Monitoring 
  
   10.9.1 Replication Monitor (0 Mins 57 Secs)
   10.9.2 Replication Monitoring 
   10.9.3 ExamSim Questions - Section 10.9 
  
  10.10 Optimization Review 
  
   10.10.1 Monitoring Tool Facts 
   10.10.2 Database Performance Facts 
   10.10.3 Common DMVs and Functions 
   10.10.4 ExamSim Questions - Section 10.10

ExamSim (253 questions)

• Preparing for Certification 
  
   Exam Objectives 
   Exam FAQs 
   How to Register for an Exam 
   Exam-taking Hints and Tips 
  
   Objective 100, All Questions (56 Questions)
   Objective 200, All Questions (19 Questions)
   Objective 300, All Questions (53 Questions)
   Objective 400, All Questions (35 Questions)
   Objective 500, All Questions (35 Questions)
   Objective 600, All Questions (55 Questions)
   Typical Exam