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

• Describe SQL Server architecture.
• Plan for a SQL Server installation, and then install an instance of SQL Server.
• Manage files and databases, including determining resource requirements.
• Choose a login security method, configure login security, plan and implement database permissions, and describe how to help protect SQL Server in an enterprise network.
• Perform and automate administrative tasks and create custom administrative tools.
• Back up databases and implement a backup strategy.
• Restore databases.
• Monitor and optimize SQL Server performance.
• Transfer and migrate data into databases.
• Maintain the high availability of SQL Server.
• Describe how to replicate data from one SQL Server to another.

Before attending this course, students must have:

• Experience using the Microsoft Windows^® 2000 operating system to:
  • Connect clients running Windows 2000 to networks and the Internet.
  • Configure the Windows 2000 environment.
  • Create and manage user accounts.
  • Manage access to resources by using groups.
  • Configure and manage disks and partitions, including disk striping and mirroring.
  • Manage data by using NTFS.
  • Implement Windows 2000 security.
  • Optimize performance in Windows 2000.
  
  For students who do not meet these prerequisites, the following courses provide students with the necessary knowledge and skills:
  
  
  • Course 2151, Microsoft Windows 2000 Network and Operating System Essentials
  • Course 2152, Implementing Microsoft Windows 2000 Professional and Server
    
    
• 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, and many-to-many).
  • How data is stored in tables (rows and columns).
  
  For students who do not meet these prerequisites, the following course provides students with the necessary knowledge and skills:
  
  
  • Course 1609, Designing Data Services and Data Models
    
    
• Knowledge of basic Transact-SQL syntax (SELECT, INSERT, UPDATE, and DELETE statements).
  
  For students who do not meet these prerequisites, the following course provides students with the necessary knowledge and skills:
  
  
  • Course 2071, Querying Microsoft SQL Server 2000
    
    
• Familiarity with the role of the database administrator.

Module 1: SQL Server Overview

Take a closer look: Download Sample Module 1 (Portable Document Format, 990 KB).

The following topics are covered in this module:

• What Is SQL Server
• SQL Server Integration
• SQL Server Databases
• SQL Server Security
• Working with SQL Server

The following lab is covered in this module:

• SQL Server Overview

At the end of this module, you will be able to:

• Describe SQL Server 2000 and its supported operating system platforms.
• Describe SQL Server architecture.
• Describe SQL Server databases.
• Describe SQL Server security.
• Describe SQL Server querying, implementation, administration, and data warehousing activities, as well as client application design options.

Module 2: Planning to Install SQL Server

The following topics are covered in this module:

• Hardware Installation Considerations
• SQL Server 2000 Editions
• Software Installation Considerations
• Methods of Installing SQL Server
• Verifying the Installation
• Configuring SQL Server Enterprise Manager
• Troubleshooting

The following lab is covered in this module:

• Installing SQL Server

At the end of this module, you will be able to:

• Determine hardware requirements for SQL Server 2000 and the SQL Server management tools.
• Describe the various SQL Server Editions.
• Describe the different types of licensing.
• Determine software installation options that are appropriate for your environment.
• Describe various methods of installing an instance SQL Server and install it by using SQL Server Setup.
• Verify the installation of SQL Server.
• Configure SQL Server Enterprise Manager.
• Troubleshoot the installation.

Module 3: Managing Database Files

The following topics are covered in this module:

• Introduction to Data Structures
• Creating Databases
• Managing Databases
• Placing Database Files and Logs
• Optimizing the Database Using Hardware-based RAID
• Optimizing the Database Using Filegroups
• Optimizing the Database Using Filegroups with Hardware-based RAID
• Capacity Planning
• Performance Considerations

The following lab is covered in this module:

• Managing Database Files

At the end of this module, you will be able to:

• Describe how SQL Server stores data and handles transactions.
• Create a database, including specifying options during and after database creation.
• Grow, shrink, or delete a database.
• Determine the placement of database files and transaction logs for performance and fault tolerance.
• Optimize a database by using hardware-based RAID.
• Determine when and how to use filegroups to optimize a database.
• Optimize a database by using filegroups with hardware-based RAID.
• Estimate the amount of space that a database requires.

Module 4: Managing Security

Take a closer look: Download Sample Module 4 (Portable Document Format, 1.08 MB).

The following topics are covered in this module:

• Implementing an Authentication Mode
• Assigning Logins to Users and Roles
• Assigning Permissions to Users and Roles
• Managing Security Within SQL Server
• Managing Application Security
• Managing SQL Server Security in the Enterprise

The following labs are covered in this module:

• Managing Security
• Managing Permissions
• Managing Application Security

At the end of this module, you will be able to:

• Implement Windows Authentication Mode and Mixed Authentication Mode.
• Assign login accounts to database user accounts and roles.
• Assign permissions to user accounts and roles.
• Manage security within SQL Server.
• Manage security with views and stored procedures.
• Create and use application roles to manage application security.
• Manage SQL Server security in the enterprise environment.

Module 5: Performing Administrative Tasks

The following topics are covered in this module:

• Configuration Tasks Routine SQL Server Administrative Tasks Automating Routine Maintenance Tasks Creating Alerts Troubleshooting SQL Server Automation Automating Multiserver Jobs

The following labs are covered in this module:

• Configuring SQL Server
• Creating Jobs and Operators
• Creating Alerts

At the end of this module, you will be able to:

• Perform common SQL Server configuration tasks.
• Describe how to upgrade SQL Server version 6.5 and SQL Server 7.0 to SQL Server 2000.
• Describe routine database administration tasks.
• Automate routine maintenance tasks by creating and scheduling jobs.
• Create alerts and operators.
• Troubleshoot automated jobs, alerts, or notifications.
• Automate administrative jobs in a multiserver environment.

Module 6: Backing Up Databases

The following topics are covered in this module:

• Preventing Data Loss
• Setting and Changing a Database Recovery Model
• SQL Server Backup
• When to Back Up Databases
• Performing Backups
• Types of Backup Methods
• Planning a Backup Strategy
• Performance Considerations

The following lab is covered in this module:

• Backing Up Databases

At the end of this module, you will be able to:

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

Module 7: Restoring Databases

The following topics are covered in this module:

• SQL Server Recovery Process
• Preparing to Restore a Database
• Restoring Backups
• Restoring Databases from Different Backup Types
• Restoring Damaged System Databases

The following lab is covered in this module:

• Restoring Databases

At the end of this module, you will be able to:

• Describe the SQL Server recovery process.
• Verify backups and perform specific tasks that enable the restore process.
• Use the RESTORE statement to get information about a backup file before you restore a database, file, or transaction log.
• Restore backups from different backup types and use the appropriate options.
• Restore damaged system databases.

Module 8: Monitoring SQL Server for Performance

The following topics are covered in this module:

• Why to Monitor SQL Server
• Performance Monitoring and Tuning
• Tools for Monitoring SQL Server
• Common Monitoring and Tuning Tasks

The following lab is covered in this module:

• Monitoring SQL Server

At the end of this module, you will be able to:

• Describe the reasons why monitoring SQL Server 2000 is important.
• Develop a performance monitoring and tuning methodology.
• Describe the tools available for monitoring SQL Server.
• Perform common monitoring and tuning tasks by using counters and appropriate tools.

Module 9: Transferring Data

The following topics are covered in this module:

• Introduction to Transferring Data
• Tools for Importing and Exporting Data in SQL Server
• Introduction to DTS
• Transforming Data with DTS

The following lab is covered in this module:

• Transferring Data

At the end of this module, you will be able to:

• Describe the rationale for, and the process of, importing, exporting, and transforming data.
• Describe the tools for importing and exporting data in SQL Server 2000.
• Transform data by using Data Transformation Services (DTS).
• Create and edit a DTS package by using the DTS Import and DTS Export Wizards.

Module 10: Maintaining High Availability

The following topics are covered in this module:

• Introduction to Availability
• Increasing Availability Using Failover Clustering
• Standby Servers and Log Shipping

The following lab is covered in this module:

• Automating the Maintenance of a Standby Server

At the end of this module, you will be able to:

• Determine availability requirements and strategies for a Microsoft Windows Server System™ environment.
• Use SQL Server failover clustering.
• Configure a standby server and use log shipping to maintain its integrity.

Module 11: Introducing Replication

The following topics are covered in this module:

• Introduction to Distributed Data
• Introduction to SQL Server Replication
• SQL Server Replication Agents
• SQL Server Replication Types
• Physical Replication Models

The following lab is covered in this module:

• Implementing Replication

At the end of this module, you will be able to:

• Describe the various methods to distribute data in SQL Server 2000.
• Explain the publisher-subscriber metaphor, including articles, publications, and subscriptions.
• Describe SQL Server replication agents.
• Explain the SQL Server replication types.
• Describe the physical replication models.