IMPLEMENTING A SQL SERVER 2005 DATABASECourse Introduction
Course Introduction
Creating Databases and Database Files
Lesson 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 Tables
Lesson 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 XML
Lesson 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 Indexes
Lesson 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 Integrity
Lesson 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 Views
Lesson 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 Functions
Lesson 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 Database
Lesson 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 Broker
Lesson 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 SERVER 2005 DATABASECourse Introduction
Course Introduction
Installing and Configuring SQL Server 2005
Lesson 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 Files
Lesson 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 Recovery
Lesson 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 Security
Lesson 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 Server
Lesson 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 Data
Lesson 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 Tasks
Lesson 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 Availability
Lesson 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 Replication
Lesson 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
|