Microsoft SQL Server 2000: Database Administration

This curriculum provides learners with the technical skills required to install, configure, administer and troubleshoot Microsoft SQL Server 2000. This curriculum maps to Microsoft Exam 70-228: Installing, Configuring and Administering Microsoft SQL Server 2000 Enterprise Edition, which forms part of the MCDBA and MCSE Certification programs.

Audience

Candidates for this course will operate in a medium to large computing environment, associated server design and the networking infrastructure. They will already be using SQL Server 7.0 and plan to upgrade to SQL Server 2000, Windows 2000 Active Directory and Internet Information Server (IIS). This series provides such organisations with theoretical and practical instruction on how to make this transition successfully and effectively.

Installing SQL Server 2000

This course provides theoretical and practical instruction in the subject of installing SQL Server 2000, covering initial installs and upgrades from Exchange 7.0. The instruction featured relates to the issues of planning and executing a server install, which includes databases and database creation. This course also covers the complexities of upgrading from SQL Server 6.5 or SQL Server 7.0, whilst also including discussions on determining the hardware requirements for SQL Server 2000, SQL management tools, configuring SQL Server Enterprise Manager and troubleshooting installation

Upgrade to SQL Server 2000
Perform a custom upgrade
Upgrade to SQL Server 2000 from SQL Server 6.5
Upgrade to SQL Server 2000 from SQL Server 7.0
Create a linked server
Configure SQL Main and SQL Agent Mail
Configure network libraries
Troubleshoot failed installations

Managing Databases

The Database Management course in this series includes practical instruction dealing with the alteration of databases, and optimising databases by using hardware-based RAID, file groups and file groups with hardware-based RAID. This course also discusses performance considerations for managing database files and the management of objects

Create and alter databases
Add file groups
Configure file group usage
Expand and shrink a database
Set database options by using alter database or create database statements
Size and place the transaction log
Create and manage objects.
Optimise database performance.
Optimise data storage
Optimise files and file groups
Manage database fragmentation
Modify the database schema

Security and Data Recovery

These courses cover the backup and restore strategies for SQL Server 2000, before going on to discuss managing security and permissions with SQL Server 2000.

Perform disaster recovery operations
Perform backups
Recover the system state and restore data
Configure, maintain and troubleshoot log shipping
Configure mixed security modes or Windows Authentication
Create and manage
Create and manage database users
Create and manage security roles
Add and remove users from roles
Create roles in order to manage database security-SQL Profiler, SQL Trace and C2 auditing

Managing Data

This course deals with transferring data, managing replication services and maintaining high availability, including practical instruction on transforming data by using Data Transformation Services (DTS) and creating and editing a DTS package by using DTS Import and DTS Export Wizards

Set up Internet Information Services (IIS) virtual directories to support XML
Import and export data, including Bulk Insert task, the bulk copy program, Data Transformation Services (DTS) and heterogeneous queries
Develop and manage Data Transformation Services (DTS) packages
Manage linked servers
Manage OLE DB Providers
Configure security mapping
Covert data types
Configure, maintain and troubleshoot replication services

Maintaining SQL Server 2000

This course provides theoretical and practical instruction in monitoring SQL Server for performance. Here the learner is introduced to the tools for monitoring and the common monitoring and tuning tasks whilst also dealing with optimising and troubleshooting SQL Server system activity

Create, manage and troubleshoot SQL Server Agent jobs
Configure alerts and operators by using SQL Server Agent
Optimise hardware resource usage, including CPU, disk I/O and memory
Monitor hardware resource using the Windows System Monitor
Resolve system bottlenecks by using the Windows System Monitor
Optimise and troubleshoot SQL Server system activity, including cache hits, connections, locks, memory allocation, recompilation and transactional throughput
Monitor SQL Server system activity by using traces
Monitor SQL Server system activity by using the Windows System Monitor

Presumed Competencies and Prerequisites

There have been many enhancements made to the SQL Server product since version 7.0, which relate to a tight integration with Windows 2000 Active Directory. Therefore learners are expected to have successfully completed training around designing a Directory Services Infrastructure series prior to commencing this curriculum. In addition learners are expected to have at least one year's experience in administering SQL Server environments, which provides an essential knowledge base for these curricula. It is also beneficial that learners have an understanding of relational database concepts, basic transact-SQL syntax and the role of the database administrator.