SQL Server 2000 Administration

Outcomes At successful completion of this course student will be able to: Installing and Configuring SQL Server 2000, Creating SQL Server 2000 Databases, Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases, Extracting and Transforming Data with SQL Server 2000, Managing and Monitoring SQL Server 2000 Security, Managing, Monitoring, and Troubleshooting SQL Server 2000, and be able to operate in medium to very large computing environments that use Microsoft SQL Server 2000. Assessment This course provides a series of assesments in the form of quizzes, pre and post course, as well as a preparation exam CD designed from the general question pool used to certify under exam 70-228. Instructor: Paul D. Sheriff has over nine years of experience programming for various businesses in the aerospace, real estate, medical & hotel areas. Paul is a designer of courseware for Application Developers Training Company & tours with the Visual Basic Master Seminar Series in both the USA & Canada. He has published over 25 articles in major publications.Course Outline: Lesson 1 An Overview of SQL Server

• Why use SQL Server
• Graphical Tools
• Transact SQL
• Database Objects
• Security
• Services
• Capacities
• Connectivity
• Other Features

Installing SQL Server 2000

• Operating Systems
• Versions & Configurations
• Multiple Instances
• Licensing
• File Locations
• Sort Orders
• Types of Installation
• Installation Checklist
• Performing an Installation
• Troubleshooting an Upgrade
• Unattended Installations

Database Setup & Maintenance

• Database Objects
• System Stored Procedures & Functions
• Database Layout
• Database Files & File Groups
• Transaction Log
• Database Growth
• Creating Databases using Enterprise Manager
• Creating Databases using T-SQL
• Sp-helpbd
• Expanding a Database
• Shrinking a Database
• Database Options
• Recovery Models
• Database Spacing Planning
• Multiple Disk Usage

Lesson 2 Security

• Introduction to Security
• Authentication
• Creating Logins
• Server Roles
• Assigning Server Roles
• Database Roles
• Managing Database Roles
• Permissions
• Managing Permissions
• Views & Security
• Stored Procedures & Security
• Application Roles

Operators, Jobs, Alerts & Mail

• Definitions
• SQL Server Agent Configuration
• Operators
• Jobs
• Alerts
• SQL Mail
• Troubleshooting

Lesson 3 DBCC

• Maintenance Statements
• Pin Statements
• Trace Flags
• Information Statements
• Check Statements
• Old Statements

Backup & Restore

• Why do Backup?
• Considerations of the Backup Process
• Managing the Backup Devices
• System Database Backup
• Multiple Backup Devices
• Backup Planning
• Media Choices
• Backup Types
• Backup Scenarios
• Copying/Moving
• Stand-by servers & Clustering
• Performing a Backup
• Restor a Backup

Database Maintenance Plan Wizard

• What is the DMPW?
• Running the Wizard
• Jobs created by the Wizard

Web Assistant

• What is the Web Assistant?
• Running the Web Assistant
• Using a Template File
• Triggers Generated by Web Assistant

Lesson 4 Transferring Objects & Scripting

• Methods of Data Transfer
• DTS Designer
• Copy Database Wizard
• sp_attach_db & sp_detach_db
• Scripting Objects

Error Logs

• Windows Event Logs
• SQL Server Error Logs
• Demo of Windows Logs
• Demo of SQL Server Logs

Distributed Management Objects

• DMO Object Hierarchy
• Logging into SQL Server
• Loading a List of Databases
• Loading Tables
• Users & Table Scripts
• Indexes & Columns

Tips & Tricks

• General Tips & Tricks

Lesson 5 Replication Concepts

• What is Replication?
• Using a Loosely Coupled Model Replication vs. Distributed Transactions
• The Publisher / Subscriber Metaphor
• Role of the Publisher Role of the Distributor Role of the Subscriber Publications Articles
• Replication Types
• Snapshot Transactional Merge Updating Subscribers

Planning a Replication Strategy

• Basic Requirements
• Autonomy vs. Integrity
• Evaluating Business Objectives
• Physical Replication Models
• Central Publisher with Distributor Central Publisher with Remote Distributor Republisher Central Subscriber
• Network Considerations
• Security Issues
• Capacity Planning
• Triggers and Constraints
• Planning Updating Subscribers

Implementing Snapshot Replication

• How Snapshot Replication Works
• Configuring Snapshot Replication

Implementing Transactional Replication

• How Transactional Replication Works
• Configuring Transactional Replication

Merge Replication

• How Merge Replication Works
• Configuring Merge Replication
• Conflict Detection and Resolution
• Using Default Resolvers
• Building Custom Resolvers

Lesson 6 Monitoring and Tuning Replication

• Introduction to Replication Monitor
• Overview of Replication Agents
• Snapshot Agent Distribution Agent Log Reader Agent Queue Reader Agent Merge Agent Cleanup Agents
• Viewing Agent History
• Replication Alerts
• Performance Issues

Replication with Non-SQL Data Sources

• Heterogeneous Subscribers
• Using ODBC/OLE DB for replication Replicating to Microsoft Access Using Merge Replication
• Heterogeneous Publishers

Replication and Database Maintenance

• Backup Issues
• Backing Up the Publisher Backing Up the Subscriber Backing Up the Distributor
• Transactional Replication and Log-Shipping

Overview of Programmatic Replication

• Using ActiveX Controls
• Using the SQL DMO
• The Replication Distributor Interface
• Using Transact SQL Stored Procedures

Contact Hours: 24