SQL Server 2000: Administration Course

This course teaches you how to install, configure, administer, and troubleshoot Microsoft SQL Server 2000 in a Windows 2000 Server environment. This course also assists you in preparing for the Microsoft Certified Systems Engineer and Microsoft Certified Database Administrator Exam #70-228, Installing, Configuring, and Administrating Microsoft SQL Server 2000 Enterprise Edition.

To ensure your success, we recommend you first take either the

Windows 2000 for Windows NT Administrators: or have equivalent knowledge.

Topics covered on the 5 day SQL Server 2000: Administration course

Introduction to Microsoft SQL Server

• Exploring the Components of SQL Server
• Exploring the Features of Microsoft SQL Server
• Identifying the Default Databases and System Tables
• Understanding the SQL Server Architecture
• Administering SQL Server
• Identifying Administrative Utilities

Installing SQL Server

• Identifying the Requirements for Installing SQL Server
• Choosing the Hardware and Software for SQL Server 2000
• Selecting Your Servers Hardware
• Identifying Where to Install SQL Server
• Choosing Installation Options for SQL Server
• Installing SQL Server
• Creating and Configuring a Service Account for SQL Server
• Performing Unattended Installations
• Using the SQL Server Service Manager to Manage the SQL Services
• Exploring the SQL Server Installation
• Starting SQL Server Enterprise Manager
• Viewing the System Databases Within SQL Server Enterprise Manager
• Creating and Working with a Database Diagram
• Using SQL Server Enterprise Manager to Configure Your Server
• Troubleshooting the SQL Server Installation
• Testing the SQL Server Installation
• Viewing the SQL Server Log

Designing & Implementing Databases

• Developing a Strategy for Implementing Databases
• Discussing SQL Server Terminology
• Identifying the Space Requirements for Databases
• Viewing Space Statistics
• Optimizing Database Performance
• Creating Databases
• Using Transact-SQL to Create a New Database
• Using SQL Server Enterprise Manager to Create a New Database
• Changing the Default File group
• Using SQL Server Enterprise Manager to Configure a Database
• Using Stored Procedures to View Database Information
• Managing Databases
• Configuring SQL Server to Automatically Expand a Database
• Manually Expanding a Database and its Transaction Log
• Configuring SQL Server to Automatically Shrink a Database File
• Deleting a Database

Creating & Managing Tables

• Creating Tables
• Designing Tables
• Choosing Data Types for a Table
• Identifying the Role of Constraints in a Table
• Using SQL Server Enterprise Manager to Create a Table
• Modifying the Structure of a Table
• Using Transact-SQL to Work with Tables
• Using the INSERT Statement to Add Rows to a Table
• Using the UPDATE Statement
• Using the DELETE Statement
• Formatting Columns in a Query

Designing & Configuring SQL Server Security

• Identifying the Security Features of SQL Server
• Identifying the SQL Server Security Components
• Configuring Your Servers Authentication Mode
• Configuring Encryption
• Creating Login Accounts
• Creating a Login Account for a Windows Account
• Denying Login Privileges
• Creating and Testing a SQL Server Login Account
• Assigning Login Accounts to Server Roles
• Assigning Login Accounts as Database Users
• Assigning a Login Account as a Database User in SQL ServerEnterprise Manager
• Adding a Database User to a Database Role
• Creating a User-defined Database Role
• Implementing Statement and Object Permissions
• Comparing Permission Types
• Granting Statement Permissions
• Granting Object Permissions
• Denying Statement Permissions
• Denying Object Permissions
• Revoking Statement and Object Permissions
• Creating and Using an Application Role
• Creating and Using a View
• Defining Linked Servers
• Defining a Linked Server
• Configuring Linked Server Security

Importing and Exporting Data

• Introduction to Data Transfer
• Comparing the Data Transfer Utilities
• Creating a DTS Package by Using the DTS Import/Export Wizard
• Using DTS Designer to Modify a Package
• Bulk Data Transfers
• Using bcp to generate a Transfer File
• Using bcp to Import a Transfer File
• Configuring XML Support
• Creating a Virtual Directory for SQL Server
• Running a URL Query

Backing Up SQL Server Databases

• Planning Backups
• Planning to Back Up Your Server
• Reviewing the Backup Process
• Backing Up Databases
• Performing a Backup
• Creating a Permanent Backup Device
• Performing a Backup of the Master Database to a PermanentBackup Device
• Performing a Parallel Backup of the Class_mgr Database
• Performing a Filegroup Backup
• Scheduling an Automatic Backup of the Msdb Database
• Designing Your Backup Strategy

Restoring SQL Server Databases

• Restoring User-defined Databases
• Observing an Automatic Recovery
• Reviewing User-initiated Database Recovery
• Creating Tables and Backing Up
• Simulating a Failed Database
• Restoring the Class_mgr Database
• Recovering System Databases
• Simulating a Master Database Failure
• Instructor Demo: Removing the Read-only Attribute From the SQL Server Installation Files
• Restoring the Master Database

Automating SQL Server Administration

• Configuring SQL Server to Send Mail
• Verifying the Configuration of the SQL Server Agent Service
• Instructor Demo: Configuring a Mailbox for the SQL Server Service Account
• Creating a Mail Profile
• Configuring SQL Agent Mail
• Configuring the SQL Server Service to Send and Receive Mail
• Using SQL Mail to Send Query Results
• Defining SQL Operators
• Using SQL Server Enterprise Manager to Define Operators
• Specifying a Fail-Safe Operator
• Managing Jobs
• Creating a Job
• Viewing Job History Information
• Manually Running a Job
• Running the Database Maintenance Plan Wizard
• Managing Alerts
• Creating an Alert
• Configuring Event Forwarding
• Troubleshooting Alerts

Designing, Configuring, and Managing Replication

• Introduction to Replication
• Identifying Publishable Information
• Choosing a Replication Model
• Choosing a Replication Strategy
• Configuring Replication
• Configuring a Publisher/Distributor
• Configuring Distributor and Publisher Properties
• Creating a Publication
• Modifying a Publication
• Configuring a Pull Subscription
• Creating an Initial Snapshot for a Publication
• Monitoring and Maintaining Replication
• Using the Replication Monitor
• Maintaining the Distribution Database
• Creating Replication Scripts in SQL Server Enterprise Manager
• Troubleshooting Replication
• Examining Replication Information in the Error Logs

Implementing Fault Tolerance

• Configuring Fault Tolerance in SQL Server 2000
• Exploring Clustering

Monitoring SQL Server

• Designing a Strategy for Monitoring SQL Server
• Discussing Server Optimization and Monitoring Techniques
• Identifying the Factors That Affect the Performance of SQL Server
• Using Monitoring Tools
• Observing Your Servers Current Activity
• Using SQL Statements and SQL Server Enterprise Manager to Monitor Your Server
• Using SQL Server Profiler
• Using SQL Query Analyzer to Monitor a Query
• Choosing a Monitoring Tool