SQL Server 7.0 MCDBA Certification

21 Sessions –
63 Hours of Interactive Training

MCDBA certified professionals demonstrate the skills necessary to successfully design, implement and administer Microsoft SQL Server databases. LearnKey courses meet or exceed all Microsoft certification objectives for professionals who originate physical database designs, develop logical data models and create physical databases and data services. With LearnKey courses you will be prepared to succeed as a highly sought-after database developer and administrator. This series includes comprehensive instruction in preparation for Microsoft MCP exams 70-215, 70-028, 70-029 and 70-216. Passing three core exams and one elective leads to Microsoft Certified Database Administrator certification.

Benefits

You will be confident in your ability to pass the exams and become a Microsoft Certified Database Administrator.

Your IT knowledge and proficiency will open doors and maximize earning potential.

Gain technical mastery and capitalize on the need for qualified IT professionals.

About The Authors

Wayne Snyder is an expert in application development, database design and modeling, specializing Microsoft SQL Server. Wayne has worked with SQL from its first release, and won the prized Microsoft Most Valuable Professional award in 1998. His certifications include MCDBA, MCSE, and MCT. Wayne is a highly respected speaker at industry conferences and a reviewer for major technical publications, currently working with IKON Education Services.

Aaron Spurlock is an MCSE+I, MCT, CNE and CNI. He has consulted and trained on networks and computing for the past 15 years with Lucent Technologies, Mastering Computers, Scientific-Atlanta and the Florida Marlins, among others. He currently owns Technovation Design in Ogden, Utah, specializing in Windows 2000 and NetWare 5 consulting as well as home and corporate automation technologies.

Michael Storm manages the design, security and implementation of Fortune 100 enterprise networks. As founder of Brainstorm International, Inc., Storm specializes in InfoSec Security and Internetwork Solutions Architectures, and is currently the Director of Network Engineering, Security Officer for Interface Technical Training and creator of the Immersion Learning System^TM. Storm's certifications include the Cisco® CCIE® Security, CCNP®, CCSP^TM, CCDP®, NSA/CNSS CISSP, MCSE and MCT.

SQL 7.0 Admin
Session 1

Section A: Introduction
· What is a SQL Server?
· SQL 7 Goals
· SQL Server Platforms
· Integration
· System Services
· System Features

Section B: Installation Planning
· System Requirements
· Architecture Enhancements
· Editions
· Installation Options
· Sort Order
· Logon Account

Section C: Installation
· Adding Logon Account
· Installing Standard Edition
· Explore MSSQL 7 Folder
· Registry Entries
· Post Installation Issues
· Accessing SQL Server

Section D: SQL Enterprise Manager
· New Groups
· Registration Information
· Manage Tables & Data
· Database Diagram
· Register a New Server
· Change SA Password
· Adding Books Online

Section E: SQL Tools
· Query Analyzer
· Create Query
· Multiple Query Windows
· Open Query File
· Naming Convention
· Quoted Names
· Using Brackets
· Unicode
· ANSI Padding
· ANSI Nulls
· ANSI Null Default
· Concat_Null_Yields_Null
· Network Utilities
· Command Line Tools

SQL 7.0 Admin
Session 2

Section A: Troubleshooting Installation
· Starting & Stopping SQL Server
· Troubleshooting Logs
· Install Failure
· Logon Problems
· SQL Server Agent

Section B: Advanced Installation
· Unattended Install
· Upgrading
· Consolidation
· Data Transfer Method
· Upgrade Planning Checklist

Section C: Upgrade
· Upgrade Wizard
· Rename Server
· Expand Temp Database
· Choose Scripts & Database
· Configuring the System
· View Files

Section D: Client Install
· Client Settings
· Client Installation
· Connecting to Server
· Register Server
· Client Network Utility
· Common Problems

Section E: Creating Databases
· What is a Database?
· Database Objects
· Database Files
· Transaction Log
· Creating a Database
· System Tables
· System Procedures & Function
· Properties

Section F: Database Considerations
· Files & Filegroups
· Recovery Considerations

SQL 7.0 Admin
Session 3

Section A: Performance Considerations
· Raid 0
· Raid 1
· Raid 10
· Raid 5

Section B: Managing Databases
· Changing Environment
· Shrinking a Database
· Drop a Database
· Database Options
· Examining Your Database
· Space Used
· Space Estimation
· Transaction Log Size
· Databases-Review

Section C: Security Models
· SQL Security Steps
· Authentication Types
· Choosing an Authentication Mode
· Implementing an Authentication Mode
· Commands for Login

Section D: Logins & Users
· NT Groups
· Adding Logins
· Login Wizard
· NT Group Logins
· Logging In
· Adding Users
· Database Access

Section E: Permissions & Roles
· Type of Permissions
· Object Permissions
· Chain of Ownership
· Roles
· Application Roles

Section F: Applying Permissions & Roles
· How they Work
· Public Role
· Deny & Grant Databases
· Ownership

SQL 7.0 Admin
Session 4

Section A: Backups
· Why Backup?
· Backup Permissions
· Backup Media
· Restore Issues
· Backup Frequency?
· Backup Methods
· Full Backup
· Options
· Differential Backups

Section B: Transaction Logs
· Backup Log
· Normal Log Backup
· Backup Options
· Log-Space Issues
· Scenarios

Section C: Filegroup Backups
· Large Database Backup
· Filegroup Scenario

Section D: Optimizing Backups
· Striped Backup
· Performance

Section E: Restore
· Restore vs. Recovery
· Automatic Recovery
· Why Restore?
· Restore Process
· Check Media
· Restore Steps
· Commands
· Restore Options
· Restoring Master

Section F: Backup & Restore
· Plan
· Prepare for Backup
· Backup
· Restore
· DBCC Commands
· Standby Server
· Cluster Server

Section G: Relocating Data
· Methods of Moving Data
· Select Into & Insert Select
· BCP & Bulk Insert
· DTS Tools
· Transformation Task
· DTS Wizard

SQL 7.0 Admin
Session 5

Section A: Replication Overview
· Distributed vs. Replication
· Roles & Language
· Publication & Articles
· Topologies
· Types of Replication
· Planning for Replication
· Solutions

Section B: Configuring Replication
· Register Servers
· Distribution Wizard
· Make a Publication
· Push a Subscription
· Conflict Resolution
· Troubleshooting

SQL Server 7.0 MCBDA Certification continued

Section C: Linked Server
· Distributed Query
· Linked Server Information
· Setting Up a Linked Server
· Joining a Database & a Server
· Troubleshooting

Section D: Automation
· Players
· Enable Mail
· Building Operators
· Adding Jobs

Section E: Alerts & Troubleshooting
· How Alerts Work
· New Alerts
· Troubleshooting Automation

Section F: SQL Mail
· Create a Table & a Trigger
· Receive Mail
· Open Mail & Process Mail

Section G: Monitoring & Optimizing
· Performance Monitor
· Server Profiler
· Modify Trace
· Login Failure Trace
· Admin To-do List
· Configuration
· Principles of Tuning

Section H: Full Text Search
· Install
· Indexing Wizard
· Queries
· For More Information

SQL 7.0 Imp DB Des
Session 1

Section A: Developing a Logical Data Model
· Normalization
· First Normal Form
· Second Normal Form
· Third Normal Form
· Relationships & Rules

Section B: Creating a Database
· Database Basics
· Memory
· Transaction Log
· Database Creation
· Files & Filegroups
· Recovery Considerations

Section C: Modifying a Database
· Database Size
· Log Size
· Changing a Database
· Examining a Database
· Database Options

Section D: SQL Date Types
· Table Syntax
· Data Types
· Advanced Data (Unicode)
· Date & Time
· Other Data Types

Section E: Creating a Table
· User Defined Data Types
· Table Basics
· Altering & Dropping Tables

SQL 7.0 Imp DB Des
Session 2

Section A: Data Integrity Concepts
· Types
· Entity
· Domain
· Referential
· Example

Section B: Defaults & Check Constraints
· Enforce Data Integrity
· Constraints
· Generic Constraint Syntax
· Defaults
· Check Constraints
· Capabilities
· Code
· Table Level Constraints
· Using Like Clause
· State Code

Section C: Primary Key & Unique Constraints
· PK Constraints
· Multi-Columned
· Unique Constraints

Section D: Foreign Key Constraints
· Foreign Key Rules
· Boss ID
· Dynamic State Code
· Tables vs. Columns
· Foreign Key Clause
· No Orphan Records
· Disabling Constraints
· Business Rules

Section E: Basic Select Statements
· DML Select
· Four Part Naming
· Where Clauses
· Comparison Operators
· Where Clause Testing
· Range of Values
· String Comparison Operators
· Multi-Select
· Logical Operators
· Order of Evaluation

Section F: Formatting Result Sets
· Sorting
· Sort Columns
· Where, Order by
· Eliminating Duplicates
· Column Aliasing

SQL 7.0 Imp DB Des
Session 3

Section A: Summarizing Data
· Aggregate Functions
· Aggregate Examples
· Count Distinct
· Group by Clause
· Having
· Rollup
· Cube
· Top

Section B: Functions & Session Options
· Functions
· Configuration & Batches
· Object Properties
· Mismatched Datatypes
· String Functions
· Converting Date & Time
· Data Function
· Set Options

Section C: Inner Joins
· Joins
· Inner Join
· Table Dot Column
· Multi-tables

Section D: Outer Joins & Cross Joins
· Left vs. Right
· Cross Join
· Self Join
· Denormalization

Section E: Unions
· Union Uses
· Show Unions

Section F: Subqueries
· Nested Queries
· Rules
· Average Price
· Compare Issues
· Correlated
· Divide & Conquer

SQL 7.0 Imp DB Des
Session 4

Section A: Insert Records
· Insert
· Defaults
· Insert Select

Section B: Deleting Records
· Deleting with Where Clause
· Deleting with Joint Records

Section C: Updating Records
· Error Checking
· Change Price
· Update with Join

Section D: Creating Indexes
· Index Issues
· Heap vs. Clustered
· Data Access
· Clustered Index
· Non-Clustered
· Creating Indexes

Section E: Maintaining Indexes
· Page Balance
· Too Full Pages
· Fill Factor
· Dropping an Index
· Index Hints
· Data Fragmentation
· Scan Density
· Index Statistics
· Show Statistics

Section F: Query Optimizer
· Show Queries
· Recommendations
· Testing Queries
· Index Intersection

Section G: Views
· What's a View?
· View Details
· Create Views
· With Where
· Function Call Issues

SQL 7.0 Imp DB Des
Session 5

Section A: Distributed Queries
· Query Types
· Open Row Set
· Examples
· Setups
· Security
· LS Options
· LS Information
· Using Four Part Naming
· Open Query
· Remote Stored Procedures
· Demonstrations

Section B: Programming SQL
· Global Functions
· Local Variables
· Control of Flow
· Syntax
· Language Elements
· Comments
· Case Statement
· Error Checking

Section C: Transactions
· Transaction Control
· Avoid Nested
· Implicit
· Restriction
· Error Checking
· Best Practices
· Distributed & Linked Server
· Remote Procedure

SQL Server 7.0 MCDBA Certification continued

Section D: Locking
· Intent Locks
· Ansi Isolation
· Session Settings
· Show Locking
· Dirty Reads
· Table Options
· Isolation Hints
· Granularity Hints
· Deadlock Errors
· Deadlock Avoidance
· Deadlock Handling
· Lock Tracking
· Best Practices

Section E: Cursors
· Cursor Basics
· Cursor Uses
· Cursor Types
· Cursor Behaviors
· Cursor Steps
· Declare Syntax
· Cursor Information
· Best Practices

SQL 7.0 Imp DB Des
Session 6

Section A: Stored Procedures
· Execution & Sharing
· Creating an SP
· Parameters
· Error Messages
· Recompiling SPs
· Best Practices
· Dynamic SQL

Section B: Triggers
· Demo
· Truncate Table
· New Trigger
· Business Rules
· Denormalized Data
· Cascading
· Transaction Control
· Nested Triggers
· Best Practices

Section C: Data Movement
· DTS
· Transformation Task
· Data Lineage
· Workflows
· Import/Export Wizards

Section D: Full Text Search
· Linguistic Search
· Full Text Index Wizard
· Rebuild Catalog
· Contain Clause

Section E: SQL Profiler
· Tracking a Database
· View Capture
· Performance Testing
· Index Tuning Wizard
· More Resources

W2K Server Admin
Session 1

Section A: Introduction
· 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
· Driver Signing
· Update Drivers
· Group Policy
· Troubleshooting

W2K Server Admin
Session 2

Section A: Hard Disk Support
· Basic Upgrade to Dynamic
· Disk Manager Service
· Volume Types
· Configure Volumes

Section B: Fault Tolerance
· RAID 5
· Recovering Data
· Break & Reestablish Minor

Section C: Volume Mount Point
· 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
· Domain User
· Create Local User
· User Properties
· Access Permissions

Section F: Groups
· Mixed Mode
· Native Mode
· Creating Groups
· Using Groups for Access
· Windows 2000 Group Structuring

W2K Server Admin
Session 3

Section A: Network Connections
· LAN
· Configure Connections
· New Connection
· Network Services
· Add Network Services

Section B: Remote Access
· 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
· Create Policy
· Permissions
· Dial-In Profiles
· Default Policy
· Accepting Connections
· VPN Policies

Section E: Terminal Services
· 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
· Add Application
· Application Compatibility
· Configure Remote User
· Use Terminal Service

W2K Server Admin
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
· DFS Setup
· Connect to DFS
· DFS Replication
· Root Replica

Section C: Web Service
· NTFS Permissions
· Web Folders
· Configure Folder Security
· Access Web Folder

Section D: Printer Access
· 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

W2K Server Admin
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
· 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

SQL Server 7.0 MCDBA Certification continued

W2K Net Inf Admin
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

W2K Net Inf Admin
Session 2

Section A: IP Routing
· Routing Example
· Protocols
· RIP & OSPF
· OSPF Areas
· ROUTE Command

Section B: RIP Routing
· Enable Routing
· Connection 1 & 2
· Routing Table
· Verify Routing

Section C: OSPF & Static Routing
· Add OSPF
· OSPF Properties
· Connection 1 & 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
· Performance Monitor

Section F: LAN Internet Connection Shearing
· Features
· Configure NAT
· ICS Addresses
· DHCP
· DNS

Section G: Modem Internet Connection Sharing
· Dial-Up Connection
· Internet Connection Wizard
· Dial-Up Properties
· Enable ICS

W2K Net Inf Admin
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

W2K Net Inf Admin
Session 4

Section A: WINS
· 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
· 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
· DNS in DHCP
· WINS in DHCP
· Scope vs. Server Options
· WINS in DNS

Section F: Certificate Authority Setup
· Certificate Contents
· Certificate Authority
· Cryptography
· Certificate Options

W2K Net Inf Admin
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
· 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