| Course Index |
|
Server Administrator
Session 1
Section A: Introduction
Overview
Our Company
Windows 2000 Family
Section B: Installation
Requirements
Upgrade Requirements
Upgrade Process
Clean Installation
After the Install
Performing the Upgrade
Configuration
Section C: Installation Automation
Unattended Installation
Setup manager
Extracting a File
Setup manager Wizard
Section D: Installation Issues
Sysprep
Running the Unattended Install
Troubleshooting
Service Packs
Section E: Hardware Support
Overview
Driver Signing
Update Drivers
Group Policy
Troubleshooting
|
Server Administrator
Session 2
Section A: Hard Disk Support
Overview
Basic Upgrade to Dynamic
Disk Manager Service
Volume Types
Configure Volumes
Section B: Fault Tolerance
Overview
RAID 5
Recovering Data
Break & Reestablish Minor
Section C: Volume Mount Point
Overview
Move Folder to New Drive
Mount Volumes
Verify Volume Mount
Section D: NTFS Options
Compression
NTFS vs. FAT Properties
Compress Drives & Folders
Copy & Move Compressed Files
Disk Quotas
Monitoring Quotas
Changing Quotas
Changing Ownership
Section E: User Accounts
Overview
Domain User
Create Local User
User Properties
Access Permissions
Section F: Groups
Overview
Mixed Mode
Native Mode
Creating Groups
Using Groups for Access
Windows 2000 Group Structuring
|
|
Server Administrator
Session 3
Section A: Network Connections
Overview
LAN
Configure Connections
New Connection
Network Services
Add Network Services
Section B: Remote Access
Overview
RRAS
Virtual Private Network
Configure RAS & RRAS
Enable RAS & RRAS
Configure Server Properties
Configure Dial-In Ports
Section C: VPN Features
Setup VPN
Filters
Section D: Remote Access Policies
Overview
Create Policy
Permissions
Dial-In Profiles
Default Policy
Accepting Connections
VPN Policies
Section E: Terminal Services
Overview
Requirements
Remote Administration
Application Server Mode
Install Terminal Services
Section F: Terminal Service Licensing
Services Licensing
Licensing Server
Installing Server
Activate Server
Section G: Using Terminal Services
Overview
Add Application
Application Compatibility
Configure Remote User
Use Terminal Service
|
Server Administrator
Session 4
Section A: Local & Shared Access
Local File Access
Local NTFS Permissions
Shared File Access
Shared Permissions
Permissions Tips
Section B: Distributed File Systems
Overview
DFS Setup
Connect to DFS
DFS Replication
Root Replica
Section C: Web Service
Overview
NTFS Permissions
Web Folders
Configure Folder Security
Access Web Folder
Section D: Printer Access
Overview
Printer Connection
New Printer Port
Share Printer
Find Printer
Printing Control
Permissions & Properties
Internet Printing
Section E: Interoperability
NetWare
Gateway for NetWare
Install Gateway Service
Macintosh
Create Macintosh Shares
Macintosh File Server
|
|
Server Administrator
Session 5
Section A: Monitoring Performance
Checking Performance
Use System Monitor
Performance Stats
Monitor % Processor Time
Performance Logs & Baselines
Section B: Optimizing System Performance
Tuning Memory
Page Files
Task Manager
Optimize Disks
Optimize Applications
Section C: Security Policies
Overview
Local Security Policies
Auditing & User Rights Policies
Auditing Setup
Event Log
EFS
Section D: Enterprise Security
Group Policy
Security configuration
Use Security Templates
Security Analysis
Security Templates
Section E: Disaster Recovery
Backup
Backup Wizard
System State Data
Restore Wizard
Safe Boot Options
Recovery Console
Recovery Console Uses
Section F: Troubleshooting
Tools & Tips
Connectivity
Disks
Printing
|
Network Infrastructure Administrator
Session 1
Section A: Windows 2000 Networking
Networking Features
OSI Model
Layered Components
TCP/IP Protocol Architecture
Native ATM
Network Services
Section B: TCP/IP & IPX/SPX Installation
Install Protocols
TCP/IP Properties
DNS & WINS
IPX/SPX Properties
Section C: Network Bindings & Packet Filters
Network Bindings
TCP/IP Packet Filtering
Enable Filtering
Section D: DHCP Installation
Features
How DHCP Works
Install DHCP
Section E: DHCP Scoping
Scopes
Create Scopes
Superscopes
Create Superscope
Multicast Scope
Create Multicast Scope
Section F: DHCP Options
Server Wide
Scope Wide
Authorize
Verify DHCP
Monitoring DHCP
Conflict Detection
Reservations
Performance Monitor
|
|
Network Infrastructure Administrator
Session 2
Section A: IP Routing
Routing Example
Protocols
RIP & OSPF
OSPF Areas
ROUTE Command
Section B: RIP Routing
Enable Routing
Connection 1
Connection 2
Routing Table
Verify Routing
Section C: OSPF & Static Routing
Add OSPF
OSPF Properties
Connection 1
Connection 2
OSPF Stats
Add Static Routes
Routing Table
Section D: Demand-Dial Routing
Other Routing Service
Features
Example
Add Interface
Properties
Demand-Dial Filters
Add IP Filter
Section E: Monitoring Traffic
Overview
Performance Monitor
Section F: LAN Internet Connection Shearing
Introduction
Features
Configure NAT
ICS Addresses
DHCP
DNS
Section G: Modem Internet Connection Sharing
Dial-Up Connection
Internet Connection Wizard
Dial-Up Properties
Enable ICS
|
Network Infrastructure Administrator
Session 3
Section A: IP Security
Features
Options
IPSec Snap-In
Policy Details
Transport Mode
Filter Settings
Key Exchange
Section B: IPSec Assignment
Secures Server
Group Policy
Assign Policy
IPSec Verify
Restart IPSec Service
Section C: DNS Overview
Name Space
Resolution Types
Zones
Features
Install DNS
Root Servers
New Zone
New Records
Section D: DNS Servers
Secondary Zone Server
Enable Zone
Delegate A Zone
Caching Only Server
Section E: DNS/Zone Settings
DNS Properties
Advanced
Logging/Monitoring
Dynamic Updates
Start Authority
Zone Transfers
Security
Section F: DNS Monitoring
DNS Client
Configure Client
Select Counters
Logging
Log Settings
DNSCMD/NS Lookup
Create Reverse Lookup
|
|
Network Infrastructure Administrator
Session 4
Section A: WINS
Introduction
Enhanced Features
Installing WINS
WINS MMC
Back Up
Intervals
Database Verification
Advanced Settings
Section B: WINS Client
Troubleshooting
Name Check
Set WINS Server
Verify
WINS Table
Node Type
Section C: WINS Replication
Introduction
Usage
Configure
Type
Partners Properties
Advanced Properties
Verify
Section D: WINS Monitoring
Static Mapping
Tombstoning
Verify Options
Back Up Database
Performance Monitor
Section E: Network Services Integration
Introduction
DNS in DHCP
WINS in DHCP
Scope vs. Server Options
WINS in DNS
Section F: Certificate Authority Setup
Introduction
Certificate Contents
Certificate Authority
Cryptography
Certificate Options
|
Network Infrastructure Administrator
Session 5
Section A: Certificate Management
Certificate Details
Policy Module
Other Policy
Certificate to Issue
Status Object
Assign Certificate
Public Key Policies
Auto Certificate Request
Verify Client Authentication
Section B: Remote Access Services
Introduction
RAS Example
Configure RAS
Authentication & Encryption
Other Properties
Section C: RAS Policies
Features
Set Dial-In Permissions
Create Policy
Configure Profile
Policy Ordering
Section D: RAS & VPN
Access Ports
VPN Ports
VPN Addresses
VPN Client
Setup
Connect
VPN Status
Performance Monitor
|
|
SQL Server 2000 Administration
Session 1
Section A: Introduction
SQL Server Goal
SQL Platforms
SQL Server
Section B: Installation Options
System Requirements
Architecture Enhancement
Server Editions
Multiple Instances
Installation Options
File Locations
Collation
Sort Order
Domain Login
Network Protocol
Unattended Setup
Section C: Installation
Basic Install
Post Installation
Register Server
Check & Verify Logins
Network Libraries
Startup Option
Start & Stop Services
Verify Directories
Section D: Troubleshooting Installations
Basic Steps
Common Install Failures
SQL Agent Issues
Client Issues
Named Pipes
TCP/IP
Section E: Upgrade from 6.5
Requirements
One or Two Computer Upgrade
Consolidation
Data Transfer Method
Upgrade Checklist
Upgrade Wizard
Section F: Upgrade Methods
Overview
Hardware & Software Requirements
Installation Upgrade
Post Upgrade
Warning
Online Database Upgrade
Online Upgrade Exceptions
Upgrade & Replication
Online Database Upgrade
Installation Upgrade
Section G: Server Security
Basic Steps
Logon Methods
Authentication Mode
SQL Login Commands
Group Usage
Add NT User
Connect Using NT
Add SQL User
Add NT Group
Validate Login
Deny Access
|
SQL Server 2000 Administration
Session 2
Section A: Database Security
Adding Users
New User & Login
Special Users
Disable Guest
Database User Command
Section B: Database Permissions
Overview
Object Permission
Permission Rules
Object References
Chain of Ownership
Chain Issues
Dbo Ownership
Roles
Server Fixed Roles
Database Fixed Roles
Create a Role
Permissions Accumulate
Application Role
Use of Application Roles
Creating a Role
Section C: Creating Databases
Databases in SQL Server
Master & Model
Temp db, MSdb & Distribution
Database Objects
File Types & Pages
Transaction Log
Create Database Options
Syntax
Recovery Considerations
Create a Database
Section D: Placing Database Files
Storage Management
RAID Options
RAID 1
RAID 0 on RAID 1
RAID 5
Filegroups
Files in Filegroup
Create Filegroups
Using Filegroups
Filegroups & Maintenance
Performance Considerations
Section E: Altering a Database
Using Alter Database
Changing Database Options
Setting Options
Shrink Databases
Examining Databases
Space Estimation
Rules of Thumb for Logs
|
|
SQL Server 2000 Administration
Session 3
Section A: Constraints
Default Constraint
Create Default Constraint
Check Constraint
Check Constraint Abilities
Using Check Constraints
Add Records Using Check Constraints
Table Level Constraints
Primary Key Constraints
Create Primary Key Constraints
Multiple Column Primary Key
Unique Constraints
Section B: Foreign Key Constraints
Planning Foreign Keys
Foreign Keys & Table Actions
Rules for Foreign Keys
Create Foreign Keys
Using Foreign Keys
Create Table Level Constraint
Altering Tables
Disabling Constraints
Section C: Indexes
Overview
Indexes & Data Storage & Access
Clustered Index
Planning Clustered Index
Non-Clustered Index
Non-Clustered with Clustered
Section D: Index Maintenance
Creating Indexes
Empty Pages
Full Pages
Fill Factor
Drop an Index
Tips for Using Indexes
Using Indexes
Section E: Stored Procedures
Overview
Planning Stored Procedures
Create & Execute
Using Stored Procedures
|
SQL Server 2000 Administration
Session 4
Section A: Triggers
Overview
Planning Use of Triggers
After Trigger
Inserted or Deleted Table
Create Triggers
Using Triggers
After vs. Instead of
Section B: Views
Overview
Planning Use of Views
Details of Views
Customize View
Insert in Views
Indexed View
Set Stats & Execution Plan
Partitioned Views
Section C: Database Backup
Why Backups?
Backup Models
Full Recovery
Bulk-Logged Recovery
Simple Recovery
Permissions Required
Backup Methods
Backup Timing
Full Backup
Using Dump Device
Backup Options
Tape Option
Create Backup
Enterprise Manager
Differential Backup
Section D: Backup Transaction Logs
Why Backup Logs?
Normal Log Backup
Truncate Only & No Log
Log Full
Long Transactions
View from Enterprise Manager
Backup No Truncate
Standard Scenario
Section E: Filegroup & Optimize Backups
Overview
Filegrouping
Large Database Backup
Filegroup Scenario
Optimizing Backups
Stripped Backup
Backup Performance
|
|
SQL Server 2000 Administration
Session 5
Section A: Restore & Recovery
Automatic Recovery
Transaction Log with Recovery
Restore Process
Check Media
Restore Steps
Restore Options
Restoring Log
Restoring Master
Restore Scenario
Restore Database
Restore Logs
Basic Restore Commands
Section B: High Availability Solutions
Overview
Standby Server
Log Shipping
Starting SQL Agent
Setup Log Shipping
Setup Backup Criteria
Troubleshooting Log Shipping
Log Ship Role Change
User/Login Mapping
Cluster Server
Windows Clustering
SQL Clustering
Active/Passive Cluster
Active/Active Cluster
Log vs. Cluster
Section C: Relocating Data
Overview
Attach or Detach Database
DTS
Create DTS Package
DTS Tasks & Steps
Transform Data
Data Lineage
Use DTA Wizard
DTS Designer
Verify Metadata
DTS Workflow
Change Data Type
Export & Import DTS
Import File
Bulk Insert & BCP
Section D: Replication Overview
Why Replication
Replication vs. DTS
Roles & Language
Publications & Articles
Publisher & Subscriber
Remote Distribution
|
SQL Server 2000 Administration
Session 6
Section A: Types of Replication
Overview
Snapshot
Transactional
Merge
Updating Subscribers
New Replication Features
Section B: Implementing Replication
Planning
Designing
Distributor Setup
Configuring a Distributor
Publisher Setup
Configuring a Publisher
Create Publication
Push Subscription
Replication Monitor
Alerts
Updating Subscribers
Troubleshooting Replication
Internet & Heterogeneous Replication
Section C: Linked Servers
Distributed Query
Setting Up a Linked Server
Linked Server Options
Troubleshooting Linked Servers
Changing Data
Create Linked Server
Link to Excel Spreadsheet
Section D: Server Automation
Overview
Setup Mail Integration
Test Mail Integration
Create Operator
Create Jobs
Advanced Options & Create Steps
Notifications
Using Jobs
|
|
SQL Server 2000 Administration
Session 7
Section A: Managing Automation
Alerts
Using Raise Error
Error Log & NT Event Log
XP-Log Events
Create Alerts
Respond to Alerts
Multiple Operator Notification
Performance Monitor Alert
Failsafe Operator & Master Server Wizard
Alert Response Delay
Automation Troubleshooting
Section B: SQL Mail Integration
SQL Mail Setup
Using SQL Mail
Triggers
Mail Troubleshooting
Section C: Monitoring SQL Server
Overview
Memory Monitoring
Processor Monitoring
Disk I/O Monitoring
System Monitor
SQL Server Monitoring
Optimize Processor Use
Monitoring Locks
SP_WHO Command
Locks in Enterprise Manager
Deadlock (1205) Error
Profiler
Using Profiler
Server Configuration
DBCC
Section D: Optimizing Database Performance
External Fragmentation
Data Fragmentation
Index Statistics
Using Indexes
Query Statistics
Execution Plans
Optimize Queries
Re-Compiling Stored Procedures
Section E: IIS Virtual Directory
Creating the Virtual Directory
Configure XML Support
Using Internet Explorer with SQL
Section F: Security Auditing
Methods
C2 Auditing
C2 vs. SQL Profiler
Additional Resources
|
SQL Server 2000 Imp DB Design
Session 1
Section A: Introduction
Overview
Section B: Normalization
Basic Concept
Redundancy
Design Language
Entity
First Normal Form
Primary Keys
Second Normal Form
Third Normal Form
Relationships
Business Rules
Normalization Benefits
Section C: Creating a Database
Overview
Database Objects
Database Creation Process
Transaction Log
Create Option
Create Syntax
Query Analyzer
Collate Order
Enterprise Manager
Recovery Considerations
Section D: Placing Database Files
Storage Management
Hardware RAID
Filegroups
Files & Filegroups
Filegroups Maintenance
Performance Considerations
Section E: Maintaining Databases
Growth
Alter Database
Database Options
Shrink Database
Exam Database
Space Estimation
Transaction Log Size
Rules of Thumb
Log File Issues
Extensive Log Activity
Section F: Basic SQL Data Types
Create Table
Standard Data Types
Exact Numerics
Integer Variables
Approximate Numerics
Character
Variable vs. Fixed
Unicode
|
|
SQL Server 2000 Imp DB Design
Session 2
Section A: Advanced SQL Data Types
Date & Time
Date Format
Money
Invalid Entry
Binary
GUID
BLOB
Special Data Types
Section B: Creating a Table
Simply Syntax
Null or Not Null
Space Allocation
Temporary Tables
Primary Key Values
Identity Column
Using Identity Values
Scope Identity
Unique Identifier
Newid Function
Extended Properties
Altering & Dropping Tables
Section C: Data Integrity Concepts
Types
Data Integrity
Entity Integrity
Domain Integrity
Referential Integrity
Section D: Data Integrity Implementation
Enforcement
Constraints
Generic Constraints
Defaults
Check Constraints
Table vs. Column Constraint
Column Constraint
Alter Constraints
Constraint Options
Section E: Primary Key Constraint
Overview
Implement Primary Key Constraint
Duplicate Key Values
Multi-Column Key
Unique Constraint
Add Unique Constraint
Section F: Foreign Key Constraint
Overview
Foreign Key Rules
Cascade Update
|
SQL Server 2000 Imp DB Design
Session 3
Section A: Select Statement
Basic Select
Special Identifiers
4-Part Naming Convention
Select Order
Where Clauses
Comparison Operators
Range of Values
Values in a List
String Pattern Matching
String Comparison Operator
Like Operator
Not Like Operator
Null Checking
Logical Operator
Not, And, Or Operators
Dynamic SQL
Section B: Formatting Result Sets
Sorting
Order By
Eliminating Duplicates
Order By with Distinct
Column Alias
Using Literals
Section C: Summarizing Data
Aggregate Functions
Aggregate Examples
Count Distinct
Group By
Group By Having
Having with Aggregates
Rollups
Cube
Section D: Functions & Set Options
System Functions
Set Options
Scalar Functions
Object Properties
Convert Function
String Functions
GetDate Function
Date & Time Functions
Row Count Set Option
Section E: Inner Joins
Joins
GUI Joins
Inner Join
Alias Table Names
Join Result Options
Multiple Table Joins
|
|
SQL Server 2000 Imp DB Design
Session 4
Section A: Outer Joins, Cross Joins & Unions
Outer Joins
Left & Right Outer Join
Cross Join
Self-Referencing Table
Self-Reference Outer Join
Denormalizing
Unions
Section B: Subqueries
Introduction
Rules
Nested Subquery
Multiple Value Nested Subqueries
Distinct Subqueries
Correlated Subqueries
Complicated Subqueries
Section C: Insert, Delete & Update
Insert Statement
Defaults
Insert Select
Truncate Table
Deleting Records
Updating Records
Update with Join
Section D: Indexing
Introduction
Heap vs. Clustered
Data Access
Clustered Index
Why Clustered Indexes?
Non-Clustered Indexes
Why Non-Clustered Indexes?
Section E: Indexes & Fillfactor
Creating Indexes
Computed Columns
Maintenance Issues
Full Pages
Fillfactor
Execution Plan with Index
Aggregate with Index
Clustered Index
Section F: Indexes & Fragmentation
External Fragmentation
Data Fragmentation
Scan Density
DBCC Index Defrag
Dropping Index
Index Hints
|
SQL Server 2000 Imp DB Design
Session 5
Section A: Index Statistics
Overview
Density
Index Statistics
Index Statistics Usage
Updating Statistics
View Statistics
Set Statistic Properties
Using Query Plan
Update Statistics
Force Index Usage
Use Updated Statistics
Update Statistic Commands
Show Sysindexes
Turn On Statistic
Section B: Query Optimization
Slow Queries
Query Plan
Dual Indexes Usage
And Operators
Or Operators
Aggregates
Joins
Merge Join
Entity Relationship Diagram
Verify Cost
Force Index
Search Arguments
Like Clause
Section C: SQL Profiler
Overview
Event Classes
Run Profiler
Tracefile Properties
Index Tuning Wizard
Analysis
Trace Replay
Access Query Analyzer
Overview
Set Processor Usage
Query Governor
Section D: Views
Overview
View Details
Create View
Using Views
Updateable Views
Alter View
Insert Record
Check Options
Update & Delete Records
Section E: Linked Servers
Overview
Query Types
Setting up the Link
Login
Options
Open Query
RPC's
Create Linked Server (Excel)
Distributed Passthrough Queries
RPC/Adhoc
Union Query
|
|
SQL Server 2000 Imp DB Design
Session 6
Section A: Indexed & Distributed Views
Indexed View Usage
Scheme Bound View
Indexed View
Data Partitioning
Partition View
Create Data Partition
Create Data Partition View
Setting Up
Modify Users View
Insert, Update & Delete
View Issues
Section B: Programming SQL
Global Functions
Local Variables
Define a Local Variable
Define Multi-Local Variables
Assignment Select Statement
Variable Value Assignment
Control of Flow
Multiple Statement
While Loop
Until & Break
GoTo & WaitFor
Section C: Other Language Elements
Simple Case Statements
Searched Case
Raiserror
View Logs
User Messages
Validate Data
XPLOG Events
Commenting
Section D: Transactions
Overview
Rollback
Transaction Examples
Partial Rollback
Nested Transactions
Avoid Nested
Implicit
Implicit Starters
Error Checking
Set Implicit On
Restricted & Best Practices
Linked Server & Remote Procedure
Distributed Transaction
Section E: Locks & Lock Types
Lost & Erroneous Transactions
Non-Repeatable & Phantoms
Benefits & Usage
Lock Types
Lock Process
Intent Locks & Bulk Update
Isolation Levels
Locked Record Trace
Set Lock Time Out
|
SQL Server 2000 Imp DB Design
Session 7
Section A: Lock Manipulation
Lock Hints
Using Locks
Deadlock Error
Avoiding & Handling Deadlocks
Locking Best Practices
Section B: Cursors
Overview
Cursor Types & Behavior
Cursor Steps
Modifying Data
Using Cursor
Update Data
Cursor Best Practices
Section C: Stored Procedures
Overview
Create & Execute
Using Stored Procedures
Parameters
Using Parameters
Local Variables
Default Parameter Values
Output Parameters
Error Checking
Testing Stored Procedures
Return Status
Plan Sharing & Best Practices
Section D: User Defined Functions
Function Types
Calling Functions
Schema Binding
Determinism
Function Syntax
Calling Scalar Functions
Using Functions
Table Value Functions
Calling Table Functions
Multi-Statement Table
Best Practices
Section E: After Triggers
Overview
Plan Trigger Use
Trigger Functionality
Inserted, Updated, Deleted Tables
Using Triggers
Testing Triggers
Cascading Updates
|
|
SQL Server 2000 Imp DB Design
Session 8
Section A: Instead-Of Triggers
Overview
Using Instead-Of Triggers
Create Trigger
Nested Triggers
Instead-Of vs. After
Section B: Data Movement
Overview
DTS Tools
DTS Packages
DTS Data Lineage
DTS Import/Export Wizard
Export to Flat File
Import Data
DTS Package Workflow
Section C: Replication
Overview
Replication Roles
Replication Types
Merge
Create Merge Publication
Subscriptions
Updating Subscribers
New Replication Features
Section D: Permissions & Roles
Overview
Object Permissions
Chain of Ownership
Permissions & Chain of Ownership
Row Level Security
Assign Row Level Security
Roles
Understanding Roles
Application Roles
Section E: XML
Selecting
For XML Auto
For XML Raw
For XML Explicit
Open XML
Additional Resources
|
