| Module 1: SQL Server Overview
|
|
|
| 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 TasksRoutine SQL Server Administrative TasksAutomating Routine Maintenance TasksCreating AlertsTroubleshooting SQL Server AutomationAutomating 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. |
Top