Administering a Microsoft SQL Server 2000 Database

This 5-day course introduces administrators and other IT professionals to the advanced features of Microsoft SQL Server 2000. Course participants are familiarised with SQL Server architecture and learn to plan, install and configure for a SQL Server installation. This includes configuring the server to manage files and databases, configuring a login security method, planning and implementing database permissions, and backing up and restoring databases. Course participants also learn to automate administrative tasks using SQL Server Agent, monitor and maintain SQL Server performance and replicate data from one SQL Server to another.Course contentModule 1-SQLServer OverviewThe following topics are covered in this module:

• What IsSQLServer?
• SQLServer Integration
• SQLServer Databases
• SQLServer Security
• Working withSQLServer

The following lab is covered in this module:

• SQLServer Overview

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

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

Module 2 - Planning to InstallSQLServerThe following topics are covered in this module:

• Hardware Installation Considerations
• SQLServer 2000 Editions
• Software Installation Considerations
• Methods of InstallingSQLServer
• Verifying the Installation
• ConfiguringSQLServer Enterprise Manager
• Troubleshooting

The following lab is covered in this module:

• InstallingSQLServer

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

• Determine hardware requirements forSQLServer 2000 and theSQLServer management tools.
• Describe the variousSQLServer Editions
• Describe the different types of licensing
• Determine software installation options that are appropriate for your environment
• Describe various methods of installing an instanceSQLServer and install it by usingSQLServer Setup
• Verify the installation ofSQLServer
• ConfigureSQLServer Enterprise Manager
• Troubleshoot the installation

Module 3 - Managing Database FilesThe 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-basedRAID
• Optimizing the Database Using Filegroups
• Optimizing the Database Using Filegroups with Hardware-basedRAID
• 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 howSQLServer 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-basedRAID
• Determine when and how to use filegroups to optimize a database
• Optimize a database by using filegroups with hardware-basedRAID
• Estimate the amount of space that a database requires

Module 4 - Managing SecurityThe 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 WithinSQLServer
• Managing Application Security
• ManagingSQLServer 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 withinSQLServer
• Manage security with views and stored procedures
• Create and use application roles to manage application security
• ManageSQLServer security in the enterprise environment

Module 5 - Performing Administrative TasksThe following topics are covered in this module:

• Configuration Tasks
• RoutineSQLServer Administrative Tasks
• Automating Routine Maintenance Tasks
• Creating Alerts
• TroubleshootingSQLServer Automation
• Automating Multiserver Jobs

The following labs are covered in this module:

• ConfiguringSQLServer
• Creating Jobs and Operators
• Creating Alerts

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

• Perform commonSQLServer configuration tasks
• Describe how to upgradeSQLServer version 6.5 andSQLServer 7.0 toSQLServer 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 DatabasesThe following topics are covered in this module:

• Preventing Data Loss
• Setting and Changing a Database Recovery Model
• SQLServer 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 andSQLServer Enterprise Manager
• Back up databases that are created on multiple files and filegroups
• Apply the appropriate backup options to each of the differentSQLServer 2000 backup methods
• Use theBACKUPLOGstatement to back up and clear transaction logs
• Design an appropriate backup strategy

Module 7 - Restoring DatabasesThe following topics are covered in this module:

• SQLServer 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 theSQLServer recovery process
• Verify backups and perform specific tasks that enable the restore process
• Use theRESTOREstatement 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 - MonitoringSQLServer for PerformanceThe following topics are covered in this module:

• Why to MonitorSQLServer
• Performance Monitoring and Tuning
• Tools for MonitoringSQLServer
• Common Monitoring and Tuning Tasks

The following lab is covered in this module:

• MonitoringSQLServer

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

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

Module 9 - Transferring DataThe following topics are covered in this module:

• Introduction to Transferring Data
• Tools for Importing and Exporting Data inSQLServer
• Introduction toDTS
• Transforming Data withDTS

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 inSQLServer 2000
• Transform data by using Data Transformation Services (DTS)
• Create and edit aDTSpackage by using theDTSImport andDTSExport Wizards

Module 10 - Maintaining High AvailabilityThe 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
• UseSQLServer failover clustering
• Configure a standby server and use log shipping to maintain its integrity

Module 11 - Introducing ReplicationThe following topics are covered in this module:

• Introduction to Distributed Data
• Introduction toSQLServer Replication
• SQLServer Replication Agents
• SQLServer 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 inSQLServer 2000
• Explain the publisher-subscriber metaphor, including articles, publications, and subscriptions
• DescribeSQLServer replication agents
• Explain theSQLServer replication types
• Describe the physical replication models