MCSE Training Kit (Exam 70-228) : Microsoft SQL Server 2000 System Administration

by Microsoft Corporation

• ISBN13: 9780735612471
• ISBN10: 0735612471
• Format: Trade Paper
• Copyright: 2001-04-28
• Publisher: Microsoft Pr

Summary

This official "MCSE Training Kit" teaches database administrators how to set up and support Microsoft SQL Server 2000 -- as they prepare for MCP Exam 70-228, a core exam for MCDBA certification and an elective on the Microsoft Windows "RM" 2000 MCSE track. Topics map directly to the objectives measured by the MCP exam, including installing and configuring SQL Server 2000, creating databases, optimizing database performance, extracting and transforming data, managing security, and monitoring and troubleshooting SQL Server system activity. Students learn through an integrated system of skill-building tutorials, case study examples, and self-assessment exercises. An economical alternative to classroom instruction, this kit enables students to set their own pace and learn by doing!

Table of Contents

About This Book	xvii
Overview of SQL Server 2000	1	(26)
About This Chapter	1	(1)
What Is SQL Server 2000?	1	(11)
The SQL Server 2000 Environment	2	(1)
SQL Server 2000 Components	2	(2)
SQL Server 2000 Relational Database Engine	4	(1)
SQL Server 2000 Analysis Services	4	(1)
Application Support	4	(1)
Additional Components	5	(2)
SQL Server 2000 Editions	7	(3)
Integration with Windows 2000 and Windows NT 4.0	10	(2)
Lesson Summary	12	(1)
What Are the SQL Server 2000 Components?	12	(7)
Server Components	13	(1)
Client-Based Administration Tools and Utilities	14	(1)
Client Communication Components	14	(4)
SQL Server Books Online	18	(1)
Lesson Summary	19	(1)
What Is the Relational Database Architecture?	19	(4)
System and User Databases	19	(1)
Physical Structure of a Database	20	(1)
Logical Structure of a Database	21	(1)
Lesson Summary	22	(1)
What Is the Security Architecture?	23	(3)
Authentication	23	(2)
Authorization	25	(1)
Lesson Summary	26	(1)
Review	26	(1)
Installing SQL Server 2000	27	(36)
About This Chapter	27	(1)
Before You Begin	28	(1)
Planning to Install SQL Server 2000	28	(4)
What Is the Minimum Hardware Required?	28	(3)
Exceeding Minimum Computer Hardware Requirements	31	(1)
Lesson Summary	32	(1)
Deciding SQL Server 2000 Setup Configuration Options	32	(7)
Determining the Appropriate User Account for the SQL Server and SQL Server Agent Services	32	(2)
Creating a Dedicated Windows 2000 User Account	34	(1)
Choosing an Authentication Mode	34	(1)
Determining Collation Settings	35	(2)
Determining Your Windows Collation	37	(1)
Selecting Network Libraries	37	(1)
Deciding on a Client Licensing Mode	38	(1)
Lesson Summary	39	(1)
Running the SQL Server 2000 Setup Program	39	(10)
Running the SQL Server 2000 Setup Program	40	(1)
Understanding Installation Types	41	(1)
Selecting a Setup Type	42	(6)
Installing a Default Instance of SQL Server 2000	48	(1)
Lesson Summary	49	(1)
Using Default, Named, and Multiple Instances of SQL Server 2000	49	(4)
Installing Multiple Instances of SQL Server 2000	50	(1)
Using Multiple Instances of SQL Server 2000 Effectively and Appropriately	51	(1)
Understanding Shared Components Between Instances	51	(1)
Understanding Unique Components Between Instances	51	(1)
Working with Default and Named Instances of SQL Server 2000	52	(1)
Lesson Summary	52	(1)
Performing Unattended and Remote Installations of SQL Server 2000	53	(5)
Performing an Unattended Installation of SQL Server 2000	53	(1)
Creating Setup Initialization Files for SQL Server 2000	53	(2)
Performing an Unattended Installation of a Named Instance of SQL Server 2000	55	(2)
Performing a Remote Installation of SQL Server 2000	57	(1)
Lesson Summary	58	(1)
Troubleshooting a SQL Server 2000 Installation	58	(3)
Reviewing SQL Server 2000 Setup Program Log Files	59	(1)
Accessing SQL Server 2000 Troubleshooting Information Online	59	(1)
Reviewing the SQL Server Error Log and the Windows Application Log	60	(1)
Reviewing the SQL Server Error Log and the Windows Application Log	60	(1)
Lesson Summary	61	(1)
Review	61	(2)
Preparing to Use SQL Server 2000	63	(32)
About This Chapter	63	(1)
Before You Begin	63	(1)
Reviewing the Results of Installation	64	(11)
What Files and Folders Were Added?	64	(3)
What Permissions Were Set in the NTFS File System	67	(1)
Reviewing the Files and Folders That Were Created	68	(2)
What Registry Keys Were Added	70	(1)
What Permissions Were Set on Registry Keys	71	(2)
Reviewing Permissions on Registry Keys	73	(1)
What Programs Were Added to the Start Menu	74	(1)
Lesson Summary	74	(1)
Starting, Stopping, Pausing, and Modifying SQL Server 2000 Services	75	(7)
What Is the Default Configuration for Each SQL Server Service?	75	(1)
Starting, Stopping, and Pausing SQL Server 2000 Services	76	(3)
Starting SQL Server Services	79	(1)
Changing the SQL Server or SQL Server Agent Service Account After Setup	80	(1)
Lesson Summary	81	(1)
Working with Osql, SQL Query Analyzer, and SQL Server Enterprise Manager	82	(12)
Working with Osql	82	(2)
Using Osql to Query SQL Server 2000 Instances	84	(1)
Working with SQL Query Analyzer	85	(2)
Using SQL Query Analyzer to Query SQL Server 2000 Instances	87	(2)
Working with SQL Server Enterprise Manager	89	(2)
Working with the SQL Server Enterprise Manager MMC Console	91	(2)
Lesson Summary	93	(1)
Review	94	(1)
Upgrading to SQL Server 2000	95	(26)
About This Chapter	95	(1)
Before You Begin	95	(1)
Preparing to Upgrade	96	(5)
Working with Multiple Versions of SQL Server on the Same Computer	96	(1)
Choosing the Appropriate Upgrade Process and Method	97	(2)
Determining Hardware and Software Requirements	99	(1)
Preparing for the Actual Upgrade	100	(1)
Lesson Summary	101	(1)
Performing a Version Upgrade from SQL Server 7.0	101	(3)
Performing a Version Upgrade	101	(2)
Performing Post-Upgrade Tasks	103	(1)
Manually Upgrading Meta Data Services Tables and the Repository Database	103	(1)
Lesson Summary	104	(1)
Performing an Online Database Upgrade from SQL Server 7.0	104	(7)
Performing an Online Database Upgrade	104	(6)
Performing Post-Upgrade Tasks	110	(1)
Lesson Summary	110	(1)
Performing a Version Upgrade from SQL Server 6.5	111	(8)
Performing a Version Upgrade	111	(7)
Troubleshooting a SQL Server 6.5 Upgrade	118	(1)
Specifying a Backward Compatibility Level for Upgraded Databases	119	(1)
Lesson Summary	119	(1)
Review	119	(2)
Understanding System and User Databases	121	(22)
About This Chapter	121	(1)
Before You Begin	121	(1)
Understanding the Database Architecture	122	(5)
Introducing Data Files	122	(1)
Viewing the Properties of a Data File	123	(1)
Allocating Space for Tables and Indexes	124	(1)
Storing Index and Data Pages	125	(1)
Lesson Summary	126	(1)
Understanding the Transaction Log Architecture	127	(7)
Introducing Transaction Log Files	128	(1)
How the Transaction Log Works	129	(3)
Introducing Recovery Models	132	(1)
Viewing the Properties of a Transaction Log and a Database	133	(1)
Lesson Summary	134	(1)
Understanding and Querying System and Database Catalogs	134	(8)
Introducing System Tables	134	(2)
Retrieving System Information	136	(1)
Querying System Tables Directly	136	(1)
Querying System Tables Using System Stored Procedures	137	(3)
Querying System Tables Using System Functions	140	(1)
Querying System Tables Using Information Schema Views	141	(1)
Lesson Summary	142	(1)
Review	142	(1)
Creating and Configuring User Databases	143	(36)
About This Chapter	143	(1)
Before You Begin	143	(1)
Creating a User Database	144	(17)
Creating a User Database	144	(3)
Using SQL Server Enterprise Manager to Create a User Database	147	(4)
Creating a Database Using the Create Database Wizard in SQL Server Enterprise Manager	151	(3)
Creating a Database Directly Using SQL Server Enterprise Manager	154	(1)
Using the Create Database Transact-SQL Statement to Create a User Database	155	(2)
Creating a Database in SQL Query Analyzer Using the Create Database Transact-SQL Statement	157	(1)
Scripting Databases and Database Objects Using SQL Server Enterprise Manager	158	(1)
Generating a Transact-SQL Script to Re-create the TSQLDB Database	159	(1)
Lesson Summary	160	(1)
Setting Database Options	161	(2)
Introducing Database Options	161	(1)
Viewing Database Option Settings	161	(1)
Modifying Database Options	162	(1)
Lesson Summary	163	(1)
Managing User Database Size Using Automatic File Growth Appropriately	163	(7)
Configuring Automatic Filegrowth Settings Using SQL Server Enterprise Manager	164	(1)
Using Automatic File Shrinkage Appropriately	165	(1)
Controlling Data File Size Manually	165	(1)
Modifying Data File Size Using SQL Server Enterprise Manager	166	(1)
Controlling Transaction Log File Size Manually	167	(1)
Creating Additional Data and Transaction Log Files	168	(1)
Lesson Summary	169	(1)
Placing Database Files on Multiple Disks	170	(7)
Introducing RAID	170	(1)
Introducing Filegroups	170	(2)
Configuring Your SQL Server 2000 Disk Subsystem for Performance, Fault Tolerance, and Recoverability	172	(2)
Moving Data and Transaction Log Files	174	(3)
Lesson Summary	177	(1)
Review	177	(2)
Populating a Database	179	(46)
About This Chapter	179	(1)
Before You Begin	179	(1)
Transferring and Transforming Data	180	(3)
Importing Data	180	(1)
DTS Data Transformations	181	(1)
Introducing the Data Transfer Tools	182	(1)
Lesson Summary	182	(1)
Introducing Microsoft Data Transformation Services (DTS)	183	(7)
Understanding a DTS Package	183	(5)
DTS Package Storage	188	(1)
Introducing DTS Tools	188	(2)
Lesson Summary	190	(1)
Transferring and Transforming Data with DTS Graphical Tools	190	(25)
Using the DTS Import/Export Wizard	190	(8)
Transferring Tables and Data from the Northwind Database Using the DTS Import/Export Wizard	198	(4)
Using DTS Designer	202	(6)
Creating a Data Transfer and Transform Package Using DTS Designer	208	(5)
Extending DTS Package Functionality	213	(1)
Lesson Summary	214	(1)
Working with DTS Packages	215	(3)
Understanding DTS Package Storage Options	215	(2)
Using DTS Package Execution Utilities	217	(1)
Using DTS Package Logs and Exception Files	217	(1)
Performing Disconnected Edits	218	(1)
Lesson Summary	218	(1)
Using the Bulk Copy Program (Bcp) and the Bulk Insert Transact-SQL Statement	218	(5)
Copying Data in Bulk Using Text Files	219	(1)
Using Bcp	219	(1)
Importing Data Using Bcp	220	(2)
Using the Bulk Insert Transact-SQL Statement	222	(1)
Optimizing Bulk Copy Operations	222	(1)
Lesson Summary	223	(1)
Review	223	(2)
Developing a Data Restoration Strategy	225	(18)
Understanding Data Restoration Issues	226	(5)
Understanding the Types of Database Backups	231	(4)
Understanding the Restoration Process	235	(5)
Review	240	(3)
Backing Up and Restoring SQL Server	243	(46)
About This Chapter	243	(1)
Before You Begin	243	(1)
Understanding Backup Terms, Media, and Devices	244	(5)
Defining Terms	244	(1)
Selecting Backup Media	244	(2)
Creating Permanent Backup Devices	246	(2)
Creating Backup Devices Using Transact-SQL	248	(1)
Lesson Summary	249	(1)
Backing Up Databases, Files, Filegroups, and Transaction Logs	249	(15)
Perform Backups Using SQL Server Enterprise Manager	249	(5)
Backing Up the master Database Using the Create Database Backup Wizard	254	(5)
Backing Up the msdb Database Directly Using SQL Server Enterprise Manager	259	(1)
Perform Backups Using Transact-SQL	260	(2)
Performing Backups Using Transact-SQL	262	(2)
Lesson Summary	264	(1)
Restoring a User Database	264	(21)
Determining the Data Restoration Sequence	265	(2)
Retrieving Backup Media Information	267	(1)
Performing Restorations Using SQL Server Enterprise Manager	268	(5)
Performing a Complete Database Restoration	273	(5)
Performing a Database Restoration to a Specified Point in Time	278	(3)
Performing Restorations Using Transact-SQL	281	(2)
Performing Restorations Using Transact-SQL	283	(2)
Lesson Summary	285	(1)
Restoring and Rebuilding System Databases	285	(3)
Restoring the Master Database	285	(2)
Rebuilding the System Databases	287	(1)
Lesson Summary	288	(1)
Review	288	(1)
Managing Access to SQL Server 2000	289	(40)
About This Chapter	289	(1)
Before You Begin	289	(1)
Understanding the Authentication Process	290	(8)
Understanding the SQL Server 2000 Authentication Process	290	(1)
Comparing Windows Authentication and SQL Server Authentication	291	(2)
Selecting a SQL Server 2000 Authentication Mode	293	(2)
Switching SQL Server 2000 Authentication Modes	295	(1)
Understanding Security Account Delegation	295	(3)
Lesson Summary	298	(1)
Understanding the Authorization Process	298	(4)
Understanding Server-Wide Permissions	298	(1)
Understanding Database-Specific Permissions	299	(2)
Lesson Summary	301	(1)
Creating and Managing Logins	302	(24)
Granting Access Using SQL Server Enterprise Manager	302	(4)
Creating a Login Using the Create Login Wizard	306	(5)
Creating a Login Directly Using SQL Server Enterprise Manager	311	(3)
Granting Access Using Transact-SQL Statements	314	(4)
Granting SQL Server 2000 and Database Access Using Transact-SQL	318	(1)
Viewing Access Information	319	(5)
Viewing SQL Server 2000 Access Information	324	(2)
Lesson Summary	326	(1)
Review	326	(3)
Managing SQL Server Permissions	329	(32)
About This Chapter	329	(1)
Before You Begin	329	(1)
Granting Database-Specific Permissions	330	(21)
Implementing Permissions	330	(2)
Creating and Testing Permission Conflicts	332	(1)
Managing Statement Permissions	333	(4)
Granting and Testing Statement Permissions	337	(3)
Managing Object Permissions	340	(6)
Viewing Permissions	346	(1)
Granting and Testing Object Permissions	346	(5)
Lesson Summary	351	(1)
Using Application Roles	351	(4)
Understanding Application Roles	351	(1)
Creating Application Roles	352	(1)
Activating and Using Application Roles	353	(1)
Creating and Testing Application Roles	353	(2)
Lesson Summary	355	(1)
Designing an Access and Permissions Strategy	355	(3)
Permitting Administrator Access	355	(1)
Using Windows Groups and SQL Server 2000 Server Roles	356	(1)
Providing SQL Server 2000 Access	356	(1)
Providing Database Access	357	(1)
Using Fixed Database Roles for Administrative Access	357	(1)
Providing Data Access	357	(1)
Lesson Summary	358	(1)
Review	358	(3)
Performing Administrative Tasks	361	(50)
About This Chapter	361	(1)
Before You Begin	361	(1)
Performing Configuration Tasks	362	(22)
Configuring Windows 2000 (and Windows NT 4.0)	362	(3)
Configuring the SQL Server Service	365	(9)
Configuring the Service Account	374	(1)
Configuring the SQL Server Agent Service	375	(3)
Registering SQL Server 2000 Instances with SQL Server Enterprise Manager	378	(5)
Sharing Registration Information	383	(1)
Lesson Summary	384	(1)
Setting Up Additional Features	384	(22)
Setting Up SQL Mail and SQLAgentMail	385	(4)
Setting Up Linked Servers	389	(4)
Setting Up and Testing a Linked Server Configuration	393	(3)
Creating an ODBC SQL Server Data Source	396	(3)
Configuring SQL Server XML Support in IIS	399	(5)
Creating an IIS Virtual Directory	404	(2)
Lesson Summary	406	(1)
Performing Maintenance Tasks	406	(3)
Updating Distribution Statistics	406	(1)
Maintaining Indexes	407	(1)
Maintaining Full-Text Indexes	407	(1)
Lesson Summary	408	(1)
Review	409	(2)
Automating Administrative Tasks	411	(64)
About This Chapter	411	(1)
Before You Begin	411	(1)
Defining Operators	412	(6)
Methods of Notification	412	(1)
Fail-Safe Operators	413	(1)
Creating Operators	413	(2)
Creating a Fail-Safe Operator	415	(1)
Creating Operators and Setting a Fail-Safe Operator	416	(2)
Lesson Summary	418	(1)
Creating Jobs	418	(21)
Implementing Jobs	418	(4)
Creating Jobs	422	(4)
Creating a Job Using the Create Job Wizard	426	(5)
Creating a Job Using SQL Server Enterprise Manager Directly	431	(2)
Using Transact-SQL	433	(1)
Reviewing Jobs and Job History	434	(3)
Reviewing and Modifying a Job and Viewing Job History	437	(2)
Lesson Summary	439	(1)
Configuring Alerts	439	(14)
Defining Alerts	440	(2)
Configuring Alerts	442	(4)
Creating an Event Alert Using the Create Alert Wizard	446	(4)
Creating a Performance Condition Alert Using SQL Server Enterprise Manager Directly	450	(2)
Reviewing Alerts and Alert History	452	(1)
Lesson Summary	453	(1)
Creating a Database Maintenance Plan	453	(9)
Using the Database Maintenance Plan Wizard	454	(6)
Viewing and Modifying Database Maintenance Plans	460	(2)
Using the Sqlmaint Utility	462	(1)
Lesson Summary	462	(1)
Creating Multiserver Jobs	462	(10)
Creating Master and Target Servers	463	(3)
Creating Jobs for Target Servers	466	(1)
Monitoring Jobs on Target Servers	467	(3)
Creating a Multiserver Job	470	(2)
Lesson Summary	472	(1)
Review	472	(3)
Monitoring SQL Server Performance and Activity	475	(38)
About This Chapter	475	(1)
Before You Begin	475	(1)
Developing a Performance Monitoring Methodology	476	(3)
Establishing Monitoring Goals	476	(2)
Determining Resources and Activities to Monitor	478	(1)
Lesson Summary	479	(1)
Choosing Among Monitoring Tools	479	(10)
Using System Monitor	480	(1)
Using Task Manager	481	(1)
Using SQL Profiler	482	(2)
Using SQL Query Analyzer	484	(1)
Using the SQL Server Enterprise Manager Current Activity Window	485	(1)
Using Transact-SQL	485	(3)
Using SNMP	488	(1)
Lesson Summary	488	(1)
Performing Monitoring Tasks	489	(23)
Monitoring Resource Usage	489	(9)
Monitoring System Resources Using System Monitor and Task Manager	498	(1)
Monitoring Stored Procedures, Transact SQL Batches, and User Activity	499	(4)
Monitoring SQL Batches and Stored Procedures	503	(1)
Monitoring Current Locking and User Activity	504	(5)
Monitoring Blocking Problems Using the Current Activity Window and System Stored Procedures	509	(2)
Lesson Summary	511	(1)
Review	512	(1)
Using SQL Server Replication	513	(80)
About This Chapter	513	(1)
Before You Begin	513	(1)
Introducing Replication	514	(9)
Describing Replication	514	(3)
Understanding the Types of Replication	517	(4)
Selecting a Physical Replication Model	521	(1)
Choosing Replication Implementation Tools	522	(1)
Lesson Summary	523	(1)
Planning for Replication	523	(3)
Planning for Replication Security	523	(1)
Filtering Published Data	524	(1)
Choosing Initial Snapshot Options	525	(1)
Lesson Summary	526	(1)
Implementing Replication	526	(47)
Configuring Distributor and Publisher Properties	526	(8)
Configuring a Distributor	534	(1)
Creating a Publication	535	(10)
Creating a Transactional Publication	545	(8)
Creating a Merge Publication	553	(3)
Configuring Push Subscriptions	556	(7)
Creating a Push Subscription	563	(2)
Configuring a Pull Subscription	565	(6)
Creating a Pull Subscription	571	(2)
Lesson Summary	573	(1)
Monitoring and Administering Replication	573	(18)
Monitoring with Replication Monitor	573	(4)
Monitoring Replication	577	(1)
Reviewing and Modifying Distributor Properties	578	(1)
Reviewing and Modifying Publication Properties	579	(1)
Reviewing and Modifying Replication Agent Profile	580	(2)
Reviewing and Modifying Subscription Properties	582	(7)
Resolving Conflicts Interactively Using Windows Synchronization Manager	589	(1)
Lesson Summary	590	(1)
Review	591	(2)
Maintaining High Availability	593	(28)
About This Chapter	593	(1)
Before You Begin	593	(1)
Using Standby Servers	594	(21)
Using a Standby Server	594	(2)
Automating Log Shipping	596	(8)
Creating a Standby Server	604	(3)
Monitoring Log Shipping	607	(4)
Monitoring Log Shipping	611	(1)
Bringing a Standby Server Online	612	(1)
Bringing a Standby Server Online as the Primary Server	613	(2)
Lesson Summary	615	(1)
Using Failover Clustering	615	(4)
Introducing MSCS	616	(1)
Creating a Virtual Server on a Server Cluster	617	(2)
Lesson Summary	619	(1)
Review	619	(2)
Appendix	621	(16)
Glossary	637	(30)
Index	667