Description
This course introduces the basics of installing, monitoring, and maintaining Microsoft SQL Server and prepares participants to administer a Microsoft SQL Server database.

Audience
Anyone who needs to administer a Microsoft SQL Server database.

Prerequisites
Participants must have prior knowledge of SQL and of relational database theory.

Hardware and Software
A PC network with Microsoft SQL Server 7.0 available. MS SQL Server client tools installed. Windows 95/98/ME/2000 or NT clients are appropriate. Administrator privileges must be available for student exercises.

Format

• Presentation
  
• Hands-on
  

Objectives
After completing this course, participants should be able to:

• Describe database administration procedures
• Identify the features of Microsoft SQL Server
• Install and monitor Microsoft SQL Server

Setting Up SQL Server

• Overview of SQL Server Administration
  • Application Architecture
  • SQL Server Architecture
  • Benefits of the SQL Server Architecture
  • SQL Server Features
  • What Comes with SQL Server
  • Overview of SQL Server Databases
  • The Role of the Database Administrator
• Installation, Configuration, & Upgrades
  • Planning an Installation
  • Preparing the System for Installation
  • SQL Server Installation
  • Performing an Unattended Installation
  • Testing the Installation
  • Upgrading From Previous Versions
  • Performing the Upgrade
  • Starting and Stopping the SQL Server
• Creating Databases
  • Database Structures
  • Database Files
  • Planning the Database
  • Creating the Database
  • Viewing Database Information
  • Creating Tables and Adding Data
  • Altering and Databases
  • Shrinking a Database
• Transferring Data
  • Data Transfer Methods
  • Using INSERT for Data Transfer
  • Bulk Insert
  • Using Bulk Copy Process (bcp)
  • Data Transformation Service (DTS)

Basic Administration Tasks

• Ensuring System Security
  • What is Security
  • NT Authentication
  • SQL Server Authentication
  • Changing the Login Password
  • Roles
  • Assigning Server Roles
  • Database Roles
  • User Defined Database Roles
  • Application Roles
  • Accessing Databases
  • Special Users
  • Permissions
  • Granting Permissions
  • Denying User or Group Access
  • Viewing Permissions
  • Permission Chains
• Backing Up and Restoring Databases
  • Backup Overview
  • Adding a Backup Device
  • Backing Up the Database
  • Restoring the Database
  • Restore Options
• Monitoring the Server
  • Tools
  • Update Statistics
  • Errorlog
  • Troubleshooting Potential Problems
  • DBCC
  • Deadlocking

   

Replication

• Introduction to Replication
  • What is Replication
  • Types of Replication
  • Planning for Replication
  • Setting Up Replication
  • Configuring a Server as a Publisher
  • Declaring Publications for Snapshot or Transaction Replication
  • Defining Articles
  • Creating a Snapshot Agent
  • Configuring Subscribers
  • Adding Pull Subscriptions
• Advanced Replication
  • Replication Wizards
  • Configure Publishing and Distribution Wizard
  • Creating a Publication
  • Adding Subscriptions
  • Managing Replication

SQL Server Maintenance

• Alerts, Operators, and Jobs
  • Using the SQL Server Agent
  • Creating Alerts
  • Creating Operators
  • Setting Up Jobs
  • Scheduling Jobs
  • Using the Error Log
  • The Application Log
• Preventative Maintenance
  • Planning for Disaster
  • Server Maintenance
  • Database Maintenance
  • Table Maintenance
  • Index Tuning Wizard
  • Checklists