Introduction to SQL Server 2000

Exploring the components of SQL Server
Exploring the features of SQL Server
Discussing the components of SQL Server
Identifying the default databases
Identifying the system tables
Understanding the SQL Server Architecture
Administering SQL Server
Identifying Administrative Utilities

Installing SQL Server 2000

Requirements for installing SQL Server
Hardware and software for SQL Server 2000
Selecting your server’s hardware
Identifying where to install SQL Server
Installing SQL Server
Service account for SQL Server
Performing unattended installations
Managing services with Service Manager
Exploring the SQL Server installation
Starting Enterprise Manager
Viewing System Databases
Creating Database Diagrams
Working with Database Diagrams
Configuring Enterprise Manager
Troubleshooting the installation
Testing the installation
Viewing the SQL Server Log

Design and implement Databases

Strategy for implementing databases
SQL Server terminology
Space requirements from databases
Viewing space statistics
Optimizing database performance
Creating databases
Transact-SQL: Creating new databases
Enterprise Manager: Creating new databases
Changing the default filegroup
Database configuration
Using Stored Procs to view database info
Managing databases
Automatic database expansion
Manual database and log file expansion
Automatic database shrinkage
Deleting a database

Creating and managing tables

Creating tables
Designing tables
Choosing data types
Table constraints
Modifying table structures
Using Transact-SQL to work with tables
Using INSERT statement to add rows
Using the UPDATE statement
Using the DELETE statement
Formatting columns in a query

Designing and configuring security

Security features of SQL Server
SQL Server security components
Configuring authentication mode
Configuring encryption
Creating login accounts
Creating a Windows login account
Denying login privileges
Creating and testing login accounts
Assigning login accounts to server roles
Assigning login accounts as database users
Assignlogin accounts in Enterprise Manager
Adding a dabase user to a database role
Creating a User Defined database role
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 views
Defining linked servers
Configuring linked server security

Importing and exporting data

Introduction to data transfer
Comparing the data transfer utilities
Creating a DTS package
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

Backing up SQL Server data

Planning backups
Performing a backup
Creating a permanent backup device
Backing up the master database
Parallel backup of the Class_mgr database
Filegroup backup
Scheduling automatic backups
Designing your backup strategy

Restoring SQL Server data

Restoring user-defined databases
Observing an automatic recovery
Reviewing user initiated recovery
Creating tables and backing up
Simulating a failed database
Restoring the Class_mgr database
Recovering system databases
Simulating a Master database failure
Changing attributes on the installation files
Restoring the Master database

Automating Server Administration

Configuring SQL Server to send mail
Agent Service configuration
Configuring Service account mailbox
Creating a mail profile
Configuring SQL Agent mail
Configuring for Send and Receive
Mailing query results
Defining SQL operators
Specifying a fail-safe operator
Managing jobs
Creating a job
Viewing job history information
Manually running a job
The Database maintenance plan wizard
Managing alerts
Creating an alert
Configuring event forwarding
Troubleshooting alerts

Replication

Introduction to replication
Identifying publishable information
Choosing a replication model
Choosing a replication strategy
Configuring replication
Configuring a publisher/distributor
Configuring distributor /publisher properties
Creating a publication
Modifying a publication
Configuring a pull subscription
Creating an initial snapshot for publication
Monitoring and maintaining replication
Using the replication monitor
Maintaining the distribution database
Creating replication scripts
Troubleshooting replication
Errors

Implementing fault tolerance

Configuring fault tolerance
Exploring clustering

Monitoring SQL Server

Designing a strategy for monitoring
Server Optimization
Monitoring techniques
The factors that affect performance
Using monitoring tools
Observing SQL Server’s current activity
SQL Server Profiler
Using Query Analyzer to monitor a query
Choosing a monitoring tool