This course provides students with the knowledge and skills required to install, configure, administer, and troubleshoot Microsoft SQL Server client/server database management system version 7.0. Course 832 is a major revision of course 867: System Administration for Microsoft SQL Server 6.5. The course content is being revised to reflect significant changes in the product and to be more task-oriented.

At Course Completion

At the end of the course, students will be able to:

• Describe SQL Server architecture.
• Plan, install and configure SQL Server.
• Manage files and databases.
• Choose a login security method and configure login security.
• Plan and implement database permissions.
• Transfer and migrate data into databases.
• Back up databases.
• Restore databases.
• Monitor SQL Server performance.
• Automate administrative tasks.
• Replicate data from one SQL Server to another.
• Create custom administrative tools.
• Perform basic tuning of a SQL Server.

Prerequisites

• Course 922: Supporting Microsoft Windows NT 4.0 Core Technologies, or equivalent knowledge. Equivalent knowledge includes the ability to:

• Install and configure Windows NT
• Change network settings using the Control Panel
• Be familiar with disk mirroring, disk striping, and striping with parity (RAID 0, 1, and 5)
• View and interpret data from the Windows NT event log using the Event Viewer
• View and interpret data from the Windows NT Performance Monitor

• An understanding of basic relational database concepts, including:

• Logical and physical database design
• Data integrity concepts
• Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many)
• How data is stored in tables (rows and columns)

• Knowledge of basic Transact-SQL syntax (SELECT, UPDATE, and INSERT statements)
• Familiarity with the role of the database administrator

Topics:

What is SQL Server
SQL Server architecture
SQL Server components
Working with SQL Server

Lab:

Using SQL Server books online

Skills:

Students will be able to:

• Describe Microsoft SQL Server 7.0 and its supporting operating systems.
• Describe SQL Server architecture.
• Describe SQL Server components.
• Describe SQL Server design options, as well as SQL Server implementation and administration activities.

Topics:

Hardware and software requirements
Understanding SQL Server installation options
Running the SQL Server Setup program
Verifying the installation
Configuring SQL Server
Troubleshooting

Lab:

Installing and configuring SQL Server

Skills:

Students will be able to:

• Determine hardware and software requirements for Microsoft SQL Server 7.0 and the SQL Server management tools.
• Determine the SQL Server installation options appropriate for your system.
• Install SQL Server and SQL Server management tools using the SQL Server Setup program.
• Verify the installation of SQL Server.
• Configure SQL Server.
• Troubleshoot the installation and configuration.

Topics:

Introduction to security
Implementing an authentication mode
Assigning login accounts to users and roles
Assigning permissions to users and roles
Planning security
Managing security with views and stored procedures
Managing application security

Lab:

Managing security

Skills:

Students will be able to:

• Distinguish between Windows NT and mixed authentication modes.
• Assign login accounts to database user accounts and roles.
• Assign permissions to user accounts and roles.
• Plan a security system.
• Manage security with views and stored procedures.
• Create and use application roles to manage application security.

Topics:

Introduction to databases
Working with databases
Modifying databases
Managing databases on multiple disks
Capacity planning

Lab:

Creating databases

Skills:

Students will be able to:

• Evaluate database considerations.
• Create and drop a Microsoft SQL Server 7.0 database.
• Grow or shrink a database.
• Describe options that can be set for a database.
• Describe strategies for managing databases on multiple disks.
• Create filegroups.
• Estimate the amount of space that your database will require.

Topics:

Data transformation architecture
Tools for transferring data
Importing and exporting data
Transforming data
Transferring SQL Server databases

Lab:

Transferring data

Skills:

Students will be able to:

• Describe the process of, and architecture for, transferring data into Microsoft SQL Server 7.0.
• Describe the tools that are available for transferring data.
• Import and export data using the bulk copy program (bcp) and Data Transformation Services (DTS).
• Transform data using DTS.
• Transfer SQL Server database schema, data, and objects using DTS Transfer Manager.
• Design a data warehouse using DTS Package Designer.

Topics:

Preventing data loss
Introduction to backing up databases
When to back up databases
Performing backups
Types of backup methods
Planning a backup strategy

Lab:

Backing up databases

Skills:

Students will be able to:

• Create backup files and backup sets.
• Back up user and system databases using Transact-SQL and SQL Server Enterprise Manager.
• Back up databases created on multiple files and filegroups.
• Apply the appropriate backup options to each of the different Microsoft SQL Server 7.0 backup methods.
• Use the BACKUP LOG statement to manage transaction logs.
• Design an appropriate backup strategy.

Topics:

SQL Server automatic recovery
Preparing to restore a database
Restoring backups
Restoring databases from different backup types
Using a standby SQL Server
Recreating and restoring damaged system databases

Lab:

Restoring databases

Skills:

Students will be able to:

• Use the RESTORE statement to get information about a backup file before restoring a database, file, or transaction log.
• Restore backups from different backup types and use the appropriate options.
• Set up a standby SQL Server and restore a production server.
• Recreate and restore damaged system databases.

Topics:

Why monitor SQL Server
Tools for monitoring SQL Server
Using the Flight Recorder

Lab:

Monitoring SQL Server

Skills:

Students will be able to:

• Explain why monitoring SQL Server is important.
• Describe factors that impact SQL Server performance.
• Monitor hardware performance using Microsoft Windows NT Performance Monitor.
• Monitor SQL Server 7.0 activity with SQL Server Profiler.
• Use Transact-SQL tools to monitor performance.
• Examine specific query performance in SQL Server Query Analyzer.
• View the 100 actions in SQL Server Profiler.

Topics:

Introduction to automation
Automating routine maintenance tasks
Creating alerts
Troubleshooting automation
Automating jobs in a multiserver environment

Lab:

Automating administrative tasks

Skills:

Students will be able to:

• Create and schedule jobs.
• Create alerts to respond to SQL Server errors.
• Use SQL Server Performance Monitor to alert operators of potential system or database problems before they occur.
• Create operators to be notified when a job completes successfully or unsuccessfully, and when an alert is raised.
• Troubleshoot potential problems with automated jobs or alerts that are not executing as anticipated.
• Automate administrative tasks within a multiserver environment.

Topics:

SQL Server and the World Wide Web
Creating a Web page using the SQL Server Web Assistant
Creating a Web page using a system stored procedure
Managing web jobs

Lab:

Publishing database data on the Web

Skills:

Students will be able to:

• Describe the data push model and the data pull model for publishing Microsoft SQL Server 7.0 data on the World Wide Web.
• Create a Web page using the SQL Server Web Assistant.
• Create a Web page using a system stored procedure.
• Manage a Web assistant job.

Topics:

Introduction to distributed data
Introduction to SQL Server replication
SQL Server replication processing
SQL Server replication methods
Publisher-subscriber replication models

Lab:

Replication fundamentals

Skills:

Students will be able to:

• List the various methods for distributing data.
• Describe the characteristics of SQL Server replication.
• Explain the publisher-subscriber metaphor, including the use of articles, publications, and subscriptions.
• Explain partitioning a table for replication.
• Describe SQL Server replication processing.
• Explain the SQL Server replication methods.
• Describe the publisher-subscriber replication models.

Topics:

Planning replication
Configuring the network
Preparing the servers
Publishing
Subscribing

Lab:

Planning and setting up replication

Skills:

Students will be able to:

• Address issues in planning a replication scenario.
• Identify the tasks that must be performed to configure SQL Server for replication.
• Install the distribution database.
• Configure a publication server.
• Set up a subscription server.
• Create publications.
• Configure synchronization.
• Set up subscriptions.

Topics:

Replicating in heterogeneous environments
Monitoring replication
Troubleshooting

Lab:

Managing replication

Skills:

Students will be able to:

• Replicate to heterogeneous databases.
• Explain what is involved in replicating from heterogeneous databases.
• Monitor replication using SQL Server tools, system stored procedures, and system tables.
• Troubleshoot replication by viewing error logs and task histories, and by using SQL Server Services.
• Identify common replication problems.
• Describe replication performance considerations.