MCSE Training Guide

I. EXAM PREPARATION.

1. Planning.


Introduction. Developing a Security Strategy for Microsoft SQL Server 7. SQL Server Authentication Methods. Planning the Use of Windows NT Groups for Security. Planning the Use of SQL Server Roles. Application Considerations in Planning Security. Planning SQL Server Service Startup Accounts. Linked Databases in Security Planning. Security Wrap Up. Developing a Microsoft SQL Server Capacity Plan. Planning SQL Server Data File Placement. Planning for Data File Growth. Performance versus Fault Tolerance. General Guidelines for File Placement. Communication and Network Requirements. Planning Communication Requirements. Developing a Data Availability Solution. Determining an Appropriate Backup Strategy. Assessing the Benefits of a Standby Server. Clustering Microsoft SQL Server 7. Data Availability Guidelines. Developing a Migration Plan. Planning an Upgrade from Previous Versions of SQL Server. Steps Required for Upgrading. Migrating Data from Other Sources. Summary Points on Migrating Other Source Data. Developing a Replication Strategy. Choosing the Appropriate Replication Model. Choosing a Replication Type. Designing and Planning Replication. Data Definition Considerations. Exercises. Review Questions. Exam Questions. Solutions to Exercises. Answers to Review Questions. Answers to Exam Questions.

2. Installation and Configuration.


Installing SQL Server 7. Installation Prerequisites. Installing SQL Server 7 Components. Installing the Database Server Component. Installing and Configuring SQL Server Clients. Performing an Unattended Setup. Upgrading from a SQL Server 6.x Database. Configuring Microsoft SQL Server 7. Using the Register Server Wizard. Configuring the Server. Configuring SQL Mail. Configuring Default ANSI Settings. Installing and Configuring the Full-Text Search Service. Installing the Full-Text Search Engine. Configuring Full-Text Search Service Properties. Configuring Full- Text Search Catalogs and Enabling Full-Text Search for Specific Columns and Tables. Exercises. Review Questions. Exam Questions. Answers to Review Questions. Answers to Exam Questions.

3. Configuring and Managing Security.


Introduction. SQL Server Security Basics. Planning SQL Server Security. Overview of the SQL Server Security Architecture. SQL Server Security Building Blocks. Barriers to Accessing SQL Server Data. User Authentication Methods. What is a Security Mode? Mixed Mode. Windows NT Authentication Mode. Authentication Methods. How Does a User Gain Access to SQL Server? Introduction to SQL Server Accounts. Creating and Managing Login IDs. Creating Login IDs Under Windows NT Authentication. Creating Login IDs Under SQL Server Authentication. Creating and Managing Database User IDs. Managing SQL Server Roles. Fixed Server Roles. Fixed Database Roles. The Public Role. User-Defined Database Roles. Application Roles. Managing Users with SQL Enterprise Manager. Using the SQL Server Create Login Wizard to Manage SQL Server Accounts. Manually Creating, Modifying, and Deleting SQL Server Login IDs and Database User IDs. Displaying SQL Server Account Information. Creating and Configuring Login IDs. Deleting a Login ID. Creating and Configuring Database User IDs. Creating and Configuring User-Defined and Application Database Roles. Deleting a Database User ID or Database Role. Configuring a Server Role. Creating and Managing IDs and Roles. Using System Stored Procedures to Manage Accounts. Permissions. Statement Permissions. Object Permissions. Implied Permissions. Understanding Permission Precedence. Using Views and Stored Procedures to Enforce Security. Understanding Ownership Chains. Non-Broken Ownership Chains. A Broken Ownership Chain. Preventing Broken Ownership Chains. Best Practices for Managing Permissions. Displaying Permissions Information. Viewing Statement Permissions. Viewing Object Permissions. Granting and Revoking Permissions. Granting and Revoking Statement Permissions Using SQL Enterprise Manager. Granting and Revoking Object Permissions Using SQL Enterprise Manager. Managing Permissions with Transact-SQL. Managing Permissions. Using SQL Server Profiler to Audit Server and Database Activity. Features of the SQL Server Profiler. Using the SQL Server Profiler. Exercises. Review Questions. Exam Questions. Answers to Review Questions. Answers to Exam Questions.

4. Managing and Maintaining Data.


Introduction. Creating a Database. Creating Data Files and Filegroups. Specify Growth Characteristics. Managing a Database. Shrinking a Database. Deleting Data and Log Files. Setting Database Options. Documenting and Scripting a Database. Setting Up a Database Maintenance Plan. Deleting a Database. Loading Data. INSERT and SELECT INTO. Bulk Copying Data. Data Transformation Services (DTS). Exercises. Review Questions. Exam Questions. Answers to Review Questions. Answers to Exam Questions.

5. Performing Backup and Restore.


Introduction. User Databases. Working with Backup Devices. Database Backups. Differential Backups. Transaction Log Backups. File or Filegroup Backups. Scheduling Your Backups. System Databases. The master Database. The model, msdb, and distribution Databases. Exercises. Review Questions. Exam Questions. Answers to Review Questions. Answers to Exam Questions.

6. Managing Replication.


Introduction. Replication Reviewed. Design Requirements. Types of Replication. Replication Agents. Configuring Servers. Distributors. Publishers. Subscribers. Creating Publications. Snapshot Publication. Transactional Publication. Merge Publication. Subscribing to Publications. Adding a Pull Subscription. Adding a Push Subscription. Adding an Anonymous Subscription. Exercises. Review Questions. Exam Questions. Answers to Review Questions. Answers to Exam Questions.

7. Automating Administrative Tasks.


Introduction. Configuring the SQL Server Agent. Startup Account. Connection Method. SQL Server Agent Mail. Implementing Jobs. Creating Jobs. Multiserver Jobs. Scripting Jobs. Managing Server Events. Defining Operators. Defining Alerts. Forwarding Events. Copying Operators and Alerts to Other Servers. Exercises. Review Questions. Exam Questions. Answers to Review Questions. Answers to Exam Questions.

8. Using Linked Servers.


Introduction. Linked Servers. Setting Up a Linked Server. Managing Security. Remote Servers. Setting Up a Remote Server. Managing Security. Exercises. Review Questions. Exam Questions. Answers to Review Questions. Answers to Exam Questions.

9. Monitoring and Optimization.


Introduction. Monitoring SQL Server. Monitoring Basics. Monitoring Engine Events. Monitoring Server Processes' Resource Usage. Monitoring with SQL Server Enterprise Manager. Monitoring the Error Logs. Monitoring with TSQL Statements. Monitoring. Optimizing Server Performance. Tuning Guidelines for SQL Server. Optimizing Memory Usage. Optimizing I/O Performance. Optimizing CPU Usage. Exercises. Review Questions. Exam Questions. Answers to Review Questions. Answers to Exam Questions.

10. Troubleshooting.


Introduction. Overview of Troubleshooting. SQL Server Startup Parameters. SQL Server Command-Line Utilities. Installing and Upgrading to SQL Server 7. Installation. Upgrading. Troubleshooting SQL Server Access Problems. Network Connectivity . NT Authentication. Troubleshooting SQL Server Permissions. Accessing Databases. Permissions. Troubleshooting Database Management Issues. Troubleshooting Backup and Restores. After a Database Restore. Troubleshooting Server Automation. Troubleshooting Replication. Troubleshooting Linked Server Issues. Exercises. Review Questions. Exam Questions. Answers to Review Questions. Answer to Exam Questions.

II. FINAL REVIEW.

Study and Exam Prep Tips.


Study Tips. Study Strategies. Pre-Testing Yourself. Exam Prep Tips. The MCP Exam. Exam Format. New Question Types. Putting It All Together. Final Considerations. Fast Facts. Planning. Develop a security strategy. Develop a SQL Server capacity plan. Develop a data availability solution. Develop a migration plan. Develop a replication strategy. Installation and Configuration. Install SQL Server. 604 Configure SQL Server. Implement full-text searching. Configuring and Managing Security. Assign SQL Server access through Windows NT accounts, SQL Server logins, and built-in administrator logins. Create and assign SQL Server roles Server roles include fixed server, fixed database, public, user-defined database, and application. Grant to database users and roles the appropriate permissions on database objects and statements. Audit server and database activity. Managing and Maintaining Data. Create and manage databases. Load data by using various methods Methods include the INSERT statement, the SELECT INTO statement, the BCP utility, Data Transformation Services (DTS), and the BULK INSERT statement. Back up system databases and user databases by performing a full database backup, a transaction log backup, a differential database backup, and a filegroup backup. Restore system databases and user databases from a full database backup, a transaction log backup, a differential database backup, and a filegroup backup. Manage replication. Automate administrative tasks. Enable access to remote data. Monitoring and Optimization. Monitor SQL Server performance. Tune and optimize SQL Server. Limit resources used by queries. Troubleshooting. Diagnose and resolve problems in upgrading from SQL Server 6.x. Diagnose and resolve problems in backup and restore operations. Diagnose and resolve replication problems. Diagnose and resolve job or alert failures. Diagnose and resolve distributed query problems. Diagnose and resolve client connectivity problems. Diagnose and resolve problems in accessing SQL Server, databases, and database objects. Practice Exam. Areas Tested by the SQL Server 7 Administration Exam. Exam Questions. Answers and Explanations.

III. APPENDIXES.

A. Glossary.
B. Overview of the Certification Process.


Types of Certification. Certification Requirements. How to Become a Microsoft Certified Professional. How to Become a Microsoft Certified Professional+Internet. How to Become a Microsoft Certified Professional+Site Building. How to Become a Microsoft Certified Systems Engineer. How to Become a Microsoft Certified Systems Engineer+Internet. How to Become a Microsoft Certified Solution Developer. Becoming a Microsoft Certified Trainer.

C. What's On the CD-ROM.


Top Score. Exclusive Electronic Version of Text. Copyright Information and Disclaimer.

D. Using the Top Score Software.


Getting Started. Instructions on Using the Top Score Software. Using Top Score Practice Exams. Using Top Score Study Cards. Using Top Score Flash Cards. Summary.

Index.