Accelerated Training for Microsoft SQL Server 7.0
(Microsoft Training Course: 1634) - 5 days - £2640 exc VAT

THIS COURSE HAS NOW RETIRED. PLEASE SEE THE SUPPLEMENTARY INFORMATION FOR DETAILS OF ALTERNATIVE COURSES.
This course provides students with the knowledge and skills required to install, configure, administer, implement, and troubleshoot Microsoft SQL Server client/server database management system version 7.0. This course is not intended for individuals who have no prior SQL Server knowledge.

1. SQL Server Overview
2. Building Databases
3. Self-study: Overview of Transact-SQL
4. Querying Databases
5. Self-study: Advanced Text Queries
6. Managing Queries
7. Self-study: Implementing Triggers
8. Managing Databases
9. Replication
10. Self-study: Managing Replication

---

Supplementary Information This course has now retired and is no longer available. We suggest you look at Course 6317: Upgrading Your SQL Server 2000 Database Administration Skills to SQL Server 2008.

---

Module 1: SQL Server Overview

• What is Microsoft SQL Server?
• SQL Server architecture
• SQL Server security
• SQL Server databases
• SQL Server installation options
• Verifying the installation
• Preparing to use SQL Server
• Assigning login accounts to users and roles
• Assigning permissions to users and roles
• Planning security
• Lab: SQL Server overview
• Lab: Installing and configuring SQL Server
• Lab: Managing security Managing permissions
• Lab: Managing application security

Skills

• Determine minimum hardware and software requirements for 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 by using SQL Server Setup.
  
• Verify the installation of SQL Server.
  
• Configure SQL Server.
  
• Troubleshoot the installation and configuration.
  
• Implement Microsoft Windows NT Authentication Mode and Mixed Authentication Mode.
  
• 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.
  

top

---

Module 2: Building Databases

• How data is stored
• Creating databases
• Modifying databases
• Creating filegroups
• The library database
• Creating data types
• Creating tables
• Types of data integrity
• Enforcing data integrity
• Using constraints
• Using defaults and rules
• Deciding which enforcement method to use
• Index architecture
• Creating indexes
• Create index options
• Maintaining indexes
• Lab: Creating databases
• Lab: Creating database objects
• Lab: Implementing data integrity
• Lab: Creating indexes

Skills

• Evaluate database storage considerations.
  
• Create and configure a database.
  
• Manage a database and transaction log.
  
• Create a file group.
  
• Create and drop user-defined data types.
  
• Create and drop user tables.
  
• Define and use DEFAULT and CHECK constraints.
  
• Define PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.
  
• Describe and use defaults and rules.
  
• Data integrity methods.
  
• Determine when indexes are useful and decide on the types of indexes to create.
  
• Create clustered and nonclustered indexes with unique or composite characteristics.
  
• Use the CREATE INDEX options to expedite index creation and improve index performance.
  
• Apply the appropriate fillfactor value to accommodate the future growth of tables.
  
• Use various tools and verification features to maintain indexes and enhance their optimal performance.
  

top

---

Module 3: Self-study: Overview of Transact-SQL

• Microsoft SQL Server programming tools
• Transact-SQL programming language
• Elements of Transact-SQL
• Ways to execute Transact-SQL statements
• How queries are processed.

top

---

Module 4: Querying Databases

• Combining data from multiple tables
• Combining multiple result sets
• Creating a table from a result set
• Introduction to subqueries
• Nested subqueries
• Correlated subqueries
• Evaluating a correlated subquery
• Modifying data
• Using aggregate functions
• GROUP BY fundamentals
• Generating aggregate values within result sets
• Listing the TOP n values
• Lab: Querying multiple tables
• Lab: Advanced query techniques
• Lab: Summarizing data

Skills

• Use subqueries to break down and perform complex queries.
  
• Modify data using the INSERT… SELECT, DELETE, and UPDATE statements.
  
• Generate a single summary value using aggregate functions.
  
• Organize summary data for a column using aggregate functions with the GROUP BY and HAVING clauses.
  
• Generate summary data for a table using aggregate functions with the GROUP BY clause and the ROLLUP or CUBE operators.
  
• Generate control-break reports using the COMPUTE and COMPUTE BY clauses.
  
• Use the TOP n keyword to retrieve a list of the specified top values in a table.
  

top

---

Module 5: Self-study: Advanced Text Queries

• Microsoft Search Service
• Microsoft English Query

top

---

Module 6: Managing Queries

• Introduction to stored procedures
• Creating, executing, and modifying stored procedures
• Managing transactions
• SQL Server locking
• Introduction to distributed queries
• Defining views
• Introduction to stored procedures
• Creating, executing, and modifying stored procedures
• Using parameters in stored procedures
• Executing extended stored procedures
• Lab: Managing transactions and locks
• Lab: Working with distributed data
• Lab: Implementing views
• Lab: Creating stored procedures
• Lab: Creating stored procedures with parameters

Skills

• Describe transaction processing.
  
• Execute, cancel, or roll back a transaction.
  
• Identify locking concurrency issues.
  
• Identify resource items that can be locked and the types of locks.
  
• Describe lock compatibility.
  
• Set locking options and display locking information.
  
• Write ad hoc queries that access data that is stored in a remote SQL Server or in an OLE DB data source.
  
• Set up a linked server environment to access data that is stored in a remote SQL Server or in an OLE DB data source.
  
• Write queries that access data from a linked server.
  
• Execute stored procedures on a remote server.
  
• Use distributed transactions to modify distributed data.
  
• Define a view with the CREATE VIEW statement.
  
• Alter a view definition.
  
• Drop a view from a database.
  
• Describe the characteristics and implications of a broken ownership chain.
  
• Locate view definition information.
  
• Update a source table using a view.
  
• Describe how a stored procedure is processed.
  
• Create, execute, modify, and drop a stored procedure.
  
• Create stored procedures that accept parameters.
  
• Execute extended stored procedures.
  
• Create custom error messages.
  

top

---

Module 7: Self-study: Implementing Triggers

• Introduction to triggers
• Defining triggers
• Examples of triggers

top

---

Module 8: Managing Databases

• SQL Server backup
• Planning a backup strategy
• Preparing to restore a database
• Restoring backups
• Restoring databases from different backup types
• Restoring damaged system databases
• Introduction to SQL Server automation
• Automating routine maintenance tasks
• Tools for importing and exporting data in SQL Server
• Data Transformation Services (DTS)
• Tools for monitoring SQL Server
• Generating a query history
• Creating a maintenance plan for SQL Server
• Lab: Backing up databases
• Lab: Restoring databases
• Lab: Creating jobs and operators
• Lab: Creating alerts
• Lab: Transferring data
• Lab: Monitoring SQL Server

Skills

• 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 SQL Server 7.0 backup methods.
  
• Use the BACKUP LOG statement to back up and clear transaction logs.
  
• Design an appropriate backup strategy.
  
• 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 7.0 and restore a production server.
  
• Restore damaged system databases.
  
• Create and schedule jobs.
  
• Create alerts to respond to SQL Server 7.0 errors.
  
• Create operators to be notified when a job completes or when an alert is fired.
  
• Create SQL Server performance condition alerts to notify a system or database administrator of potential problems before they occur.
  
• Troubleshoot potential problems when jobs or alerts do not execute as anticipated.
  
• Automate administrative jobs within a multiserver environment.
  
• Transform data using DTS.
  
• Create and edit a DTS Package using the DTS Import and Export wizards.
  
• Describe factors that affect SQL Server performance.
  
• Monitor hardware performance using Windows NT Performance Monitor.
  
• Use Transact-SQL tools to monitor performance.
  
• Monitor SQL Server 7.0 activity with SQL Server Profiler.
  
• View the last 100 actions in SQL Server Profiler.
  
• Examine specific query performance in SQL Server Query Analyzer.
  
• Develop a database maintenance plan.
  

top

---

Module 9: Replication

• Planning replication
• Preparing the servers
• Publishing
• Subscribing
• Lab: Implementing replication

Skills

• List the various methods to distribute data.
  
• Describe the characteristics of SQL Server 7.0 replication.
  
• Explain the publisher-subscriber metaphor, including articles, publications, and subscriptions.
  
• Explain filtering data for replication.
  
• Describe SQL Server replication agents.
  
• Explain the SQL Server replication types.
  
• Describe the physical replication models.
  
• Address issues in planning a replication scenario.
  
• Identify the tasks that must be performed to configure SQL Server for replication.
  
• Set up and configure a distribution server.
  
• Set up publication and subscription servers.
  
• Create publications.
  
• Configure synchronization.
  
• Set up subscriptions.
  

top

---

Module 10: Self-study: Managing Replication

• Monitoring and troubleshooting replication
• Replicating in heterogeneous environments
• Publishing on the Internet

top

---

Before attending this course, students must have the following pre-requisites:

• Current working knowledge of Microsoft Windows NT 4.0.
• Course 922, Supporting Microsoft Windows NT 4.0 Core Technologies, or equivalent knowledge.
• An understanding of basic relational database concepts.
• Two years of experience with relational databases.
• Three to six months of SQL Server experience.
• Knowledge of basic Transact-SQL syntax (SELECT, UPDATE, and INSERT statements).
• Familiarity with the role of the database administrator.

After completing this course, students will be able to describe SQL Server architecture; plan for a SQL Server installation; install SQL Server; configure SQL Server; create and manage files and databases; choose a login security method; configure login security; plan and implement database permissions; back up databases; restore databases; automate administrative tasks; import, export, and transform data; monitor and maintain SQL Server performance; replicate data from one SQL server to another.

This course has now retired and is no longer available. We suggest you look at Course 6317: Upgrading Your SQL Server 2000 Database Administration Skills to SQL Server 2008.