The SQL Server 2000 Book

by Sequeira, Anthony; Alderman, Brian

• ISBN13: 9781932111675
• ISBN10: 1932111670
• Format: Paperback
• Copyright: 2003-02-01
• Publisher: Oreilly & Associates Inc

Summary

This book is designed for the system or database administrator, IT manager, and IT professional working with SQL Server because it covers four main topic areas: technologies and architecture; planning; administering and optimizing, and using SQL Server 2000. Some of the critical topics featured in the book include backing up and restoring databases, optimizing SQL Server, using replication, configuring XML and Internet support, and automating administrative tasks.

Table of Contents

Introduction
Part I SQL Server 2000 in Your Organization
What Is SQL Server 2000?	3	(18)
A Relational Database Management System	3	(1)
A Key Element in the Microsoft .NET Strategy	4	(2)
SQL Server 2000's Key Features	6	(6)
Integration with Other Core .NET Servers	6	(1)
XML Support	6	(1)
Web-Enabled Analysis	7	(1)
Web Access to Data	7	(1)
Multiple Instances	7	(1)
Disk Imaging Support	8	(1)
Clickstream Analysis	8	(1)
Security	8	(1)
Full-Text Search	8	(1)
High Availability	8	(1)
Scalability	9	(1)
Distributed Partitioned Views	10	(1)
Indexed Views	10	(1)
Virtual Interface System Area Network (VI SAN)	10	(1)
Replication	11	(1)
Simplified Database Administration	11	(1)
Improved Developer Productivity	11	(1)
Data Transformation Services (DTS)	11	(1)
Extended Applications	11	(1)
English Query	12	(1)
Data Mining and Analysis (OLAP) Services	12	(1)
SQL Server's Competition	12	(2)
Oracle9i	13	(1)
Sybase Adaptive Server Enterprise 12	13	(1)
DB2 Universal Database version 7	13	(1)
SQL Server 2000 Editions	14	(5)
Enterprise Edition	14	(1)
Standard Edition	14	(1)
Personal Edition	15	(1)
Developer Edition	16	(1)
Evaluation Edition	16	(1)
Windows CE Edition	17	(1)
Desktop Engine	17	(2)
Summary	19	(2)
SQL Server 2000 Architectures and Components	21	(24)
SQL 2000 Architectures	21	(15)
Database Architecture	21	(5)
Relational Database Engine Architecture	26	(2)
Administration Architecture	28	(6)
Data Warehousing and OLAP Architecture	34	(1)
Application Development Architecture	34	(2)
SQL Server 2000 Components	36	(8)
Server Components	36	(1)
Client-based Administration Tools	37	(4)
Client Communication Components	41	(1)
Books Online	42	(2)
Summary	44	(1)
Transact-SQL	45	(34)
The Transact-SQL Language	45	(19)
Data Definition Language Statements	46	(7)
Data Manipulation Language Statements	53	(3)
Data Control Language Statements	56	(1)
Identifiers	57	(1)
Variables	58	(1)
Functions	58	(4)
Data Types	62	(1)
Expressions	63	(1)
Control-of-Flow Language Elements	63	(1)
Comments	64	(1)
Executing Transact-SQL Statements	64	(2)
Single Statements	64	(1)
Batches	64	(1)
Stored Procedures and Triggers	65	(1)
Scripts	66	(1)
Transact-SQL Tools	66	(10)
Query Analyzer	66	(4)
isqlw	70	(2)
isql	72	(1)
osql	73	(3)
Summary	76	(3)
Part II Planning and Installing SQL Server 2000
Installation Planning	79	(20)
Selecting the Appropriate Operating System	79	(1)
Planning Your Hardware	79	(9)
Hardware Requirements	81	(4)
Optimizing Hardware	85	(3)
Installation Considerations	88	(8)
Licensing	88	(1)
Security Context for Services	88	(3)
Using Named and Multiple Instances	91	(2)
Selecting a Security Mechanism	93	(1)
Collations and Sort Rules	94	(1)
Selecting Network Libraries	95	(1)
Summary	96	(3)
Installing SQL Server 2000	99	(32)
Methods of Installation	99	(9)
Standard Installations	99	(5)
Unattended Installations	104	(4)
Remote Installations	108	(1)
Post-Installation Tasks	108	(6)
Reviewing Results	108	(3)
Stopping, Starting, and Pausing Services	111	(2)
Connecting to the Server	113	(1)
Modifying the Services Account	114	(1)
Configuring SQL Server Enterprise Manager	114	(2)
Registering Additional Servers	115	(1)
Troubleshooting Installations	116	(13)
Troubleshooting Logs	116	(5)
Starting SQL Server from the Command Line	121	(1)
Troubleshooting Network Connections	122	(1)
Troubleshooting Standard Installations	122	(5)
Troubleshooting Remote Installations	127	(1)
Troubleshooting Unattended Installations	127	(1)
Rebuilding the Registry	128	(1)
Troubleshooting Information Online	128	(1)
Removing SQL Server	129	(1)
Summary	130	(1)
Upgrading to SQL Server 2000	131	(18)
Planning for an Upgrade	131	(7)
Running Multiple Versions of SQL Server	131	(1)
Selecting an Appropriate Upgrade Method	132	(3)
Replication Issues with Upgrading	135	(1)
Additional Hardware and Software Requirements	136	(2)
Preparing for the Upgrade	138	(1)
Upgrading from SQL Server 6.5	138	(6)
Performing the Upgrade	139	(3)
Backward Compatibility Factors	142	(1)
Post-Upgrade Tasks	143	(1)
Upgrading from SQL Server 7	144	(2)
Performing the Upgrade	144	(1)
Post-Upgrade Tasks	144	(2)
Performing an Online Database Upgrade from SQL Server 7	146	(2)
Performing the Online Database Upgrade	146	(2)
Post-Upgrade Tasks	148	(1)
Summary	148	(1)
Creating and Maintaining Databases	149	(50)
What Is a SQL Server Database?	149	(2)
Database Architectures	151	(7)
Database Storage	151	(1)
SQL Server Transactions	152	(2)
The SQL Server Transaction Log	154	(1)
Database Recovery Models	155	(1)
Viewing the Architectural Properties of a Database	155	(3)
Database Planning	158	(1)
Creating and Configuring Databases	159	(7)
Creating Databases	159	(1)
Transact-SQL	159	(3)
SQL Enterprise Manager	162	(1)
The Create Database Wizard	162	(4)
Setting Database Options	166	(12)
Database Options	166	(4)
Database Compatibility Levels	170	(3)
Database Collation	173	(1)
Modifying Database Options	174	(1)
Viewing Database Options Settings	175	(3)
Maintaining Databases	178	(8)
Scripting a Database	178	(2)
Renaming a Database	180	(1)
Deleting a Database	180	(1)
Copying or Moving a Database	181	(2)
Managing Database Size	183	(3)
Filegroups and other Database Optimizations	186	(2)
Filegroups	186	(2)
The Transaction Log	188	(1)
Tempdb	188	(1)
Viewing Database Information	188	(11)
System Tables	188	(1)
Querying System Tables Directly	189	(3)
Stored Procedures	192	(1)
System Functions	193	(1)
Information Schema Views	194	(1)
Summary	194	(5)
Part III Administering and Optimizing SQL Server 2000
Securing SQL Server 2000	199	(42)
An Overview of SQL Server Security	199	(1)
SQL Server Authentication	199	(15)
Auditing Authentication	200	(2)
Windows Authentication	202	(1)
Mixed Mode	202	(1)
Security Account Delegation	203	(1)
Impersonation	203	(1)
SQL Server Encryption	204	(1)
Managing Logins	204	(10)
Database Users	214	(4)
Permissions	218	(12)
Mechanisms for Granting User Permissions	218	(1)
Statement Permissions	219	(5)
Object Permissions	224	(6)
Predefined Permissions	230	(1)
Roles	230	(8)
Fixed Server Roles	230	(3)
Fixed Database Roles	233	(2)
User-defined Roles	235	(2)
Application Roles	237	(1)
Summary	238	(3)
Reasons for Performing Backups	241	(1)
Disaster Recovery	241	(1)
Backing Up and Restoring SQL Server 2000	241	(82)
Fault Tolerance	242	(1)
Copying Data From One SQL Server to Another	242	(1)
Monitoring Your Database	242	(1)
Read-only Databases	242	(1)
Understanding Backup and Restore Terminology	243	(1)
Backup Devices	243	(1)
Backup Media Terms	243	(1)
Securing Your Backup Operations	244	(1)
Using the Password Option on a Media Set	245	(1)
Using the Password Option on a Backup Set	245	(1)
Ownership and Permissions	245	(1)
Backup Device Types	245	(3)
Physical and Logical Devices	246	(1)
Disk Devices	246	(1)
Tape Devices	247	(1)
Managing Your Backup Devices	248	(10)
Understanding the sp_addumpdevice System Stored Procedure	248	(1)
Creating a Local Disk Backup Device Using Enterprise Manager	249	(2)
Creating a Local Disk Backup Device Using Transact-SQL	251	(1)
Creating a Network Disk Backup Device Using Enterprise Manager	251	(1)
Creating a Network Disk Backup Device Using Transact-SQL	252	(1)
Creating a Tape Backup Device Using Enterprise Manager	253	(1)
Creating a Tape Backup Device Using Transact-SQL	254	(1)
Deleting a Logical Backup Device Using Enterprise Manager	254	(1)
Deleting a Logical Backup Device Using Transact-SQL	254	(1)
Setting the Media Retention Option	255	(3)
Knowing Your Recovery Models	258	(3)
What Is a Recovery Model?	259	(2)
Switching Between Recovery Models	261	(1)
Understanding the Types of Backups in SQL Server 2000	261	(4)
Database Backups (Full or Normal)	262	(1)
Differential Database Backups	263	(1)
Transaction Log Backups (Incremental)	263	(1)
File and Filegroup Backups	264	(1)
File and Filegroup Differential Backups	264	(1)
Snapshot Backups	265	(1)
Understanding the Effects of a Backup	265	(2)
Understanding the Backup Process	266	(1)
Activities Restricted During a Backup	266	(1)
System Tables Updated	267	(1)
Performing Database Backups	267	(12)
Understanding the BACKUP Command	268	(1)
Creating a Full or Differential Database Backup Using Transact-SQL	269	(1)
Creating a Full or Differential Database Backup Using Enterprise Manager	270	(3)
Creating a Full, Differential, or Transaction Log Backup Using the Create Database Backup Wizard	273	(4)
Backing Up Files and Filegroups Using Transact-SQL	277	(1)
Backing up Files and Filegroups Using Enterprise Manager	277	(2)
Backing up Your System Databases	279	(3)
Backing up the Master Database	279	(1)
Backing up the Msdb Database	280	(1)
Backing up the Model Database	281	(1)
Backing up the Distribution Database	282	(1)
Backing Up Your Transaction Logs	282	(4)
Creating a Transaction Log Backup Using Transact-SQL	283	(1)
Creating a Transaction Log Backup Using Enterprise Manager	284	(1)
Backing Up the Transaction Log When Your Database is Damaged	285	(1)
Creating a Backup Strategy	286	(3)
Analyzing Availability and Recovery Requirements	287	(1)
Planning for Disaster Recovery	288	(1)
Selecting a Recovery Model	289	(1)
Optimizing Your Backups	289	(1)
Viewing Information about Your Backups	289	(2)
Listing Database and Transaction Log Files on a Backup Set	290	(1)
Viewing the Backup Header Information	291	(1)
Understanding the Restore Operation	291	(2)
RECOVERY versus NORECOVEREY When Restoring a Backup	291	(1)
Automatic Recovery Process	292	(1)
Manual Recovery Process	292	(1)
Securing Your Restore Operations	293	(1)
Understanding the Types of Restores in SQL Server 2000	294	(2)
Full Database Restore	295	(1)
File or Filegroup Restore with Full Recovery	295	(1)
Recovery to a Point in Time	295	(1)
Recovery to a Named Transaction	296	(1)
Understanding the Effects of a Restore	296	(1)
Restoring Your Databases	297	(11)
Understanding the RESTORE Command	297	(2)
Restoring a Full or Differential Database Backup Using Transact-SQL	299	(1)
Restoring a Full or Differential Database Backup Using Enterprise Manager	300	(2)
Restoring Files and Filegroups	302	(6)
Restoring Your System Databases	308	(3)
Restoring the Master Database	308	(2)
Restoring the Model Database	310	(1)
Restoring the Msdb Database	310	(1)
Restoring the Distribution Database	311	(1)
Restoring Your Transaction Logs	311	(7)
Restoring Your Transaction Log Backup Using Transact-SQL	312	(1)
Restoring Your Transaction Log Backup Using Enterprise Manager	313	(1)
Restoring Your Transaction Log to a Specific Point in Time Using Transact-SQL	314	(1)
Restoring Your Transaction Log to a Specific Point in Time Using Enterprise Manager	315	(1)
Specifying Named Marks in Your Transaction Log	316	(1)
Restoring Your Transaction Log to a Specific Named Transaction Using Transact-SQL	316	(1)
Restoring Your Transaction Log to the Point of Failure Using Transact-SQL	317	(1)
Creating a Restore Strategy	318	(1)
Optimizing Your Restore Operation	319	(2)
Improving Performance of Your Tape Backup Devices	320	(1)
Improving Performance of Your Disk Backup Devices	320	(1)
Troubleshooting Your Backup and Restore Operations	321	(1)
Summary	322	(1)
Automating Administrative Tasks	323	(80)
Reasons to Automate	323	(1)
Understanding the SQL Server Automation Process	324	(1)
Preparing to Automate	325	(12)
Understanding the SQL Server Agent Service	325	(1)
Choosing the Appropriate SQL Server Agent Service Logon Account	325	(3)
Configuring SQL Server Agent to Start Automatically	328	(5)
Using Email with SQL Server	333	(4)
Understanding Job Characteristics	337	(1)
Creating and Scheduling a Job Using Transact-SQL	337	(2)
Creating and Scheduling a Job Using Enterprise Manager	339	(10)
Creating a Job Using Enterprise Manager	339	(1)
Creating Your Job Steps Using Enterprise Manager	340	(6)
Scheduling Your Job Using Enterprise Manager	346	(1)
Defining a Schedule for a Job Using Enterprise Manager	346	(3)
Creating and Scheduling a Job Using the Create Job Wizard	349	(4)
Creating a Transact-SQL Script to Re-create Your Existing Jobs	353	(1)
Deleting a Job Using Enterprise Manager	354	(2)
Creating Job Categories	356	(4)
Creating a Job Category Using Transact-SQL	356	(1)
Creating a Job Category Using Enterprise Manager	356	(2)
Assigning a Job to a Job Category Using Transact-SQL	358	(1)
Assigning a Job to a Job Category Using Enterprise Manager	358	(1)
Changing the Membership of a Job Category Using Enterprise Manager	359	(1)
Reviewing the History of Your Jobs	360	(1)
Configuring the Size of Your Job History Log	361	(1)
Introducing SQL Server Operators	362	(5)
Understanding the Operator Notification Process	363	(1)
Creating Your SQL Server Operators	364	(3)
Creating a Transact-SQL Script to Re-create Your Existing Operators	367	(1)
Updating and Viewing Operator Information	367	(1)
Configuring SQL Server to Notify Your Operators of a Job's Status	368	(2)
Configuring a Fail-Safe Operator Using Enterprise Manager	370	(1)
Deleting an Operator Using Enterprise Manager	370	(1)
Introduction to Alerts	371	(3)
Types of Alerts	372	(1)
Understanding the Alert Process	373	(1)
Creating Your Alerts	374	(8)
Creating an Alert Using Transact-SQL	374	(1)
Creating an Alert Using an Error Number in Enterprise Manager	374	(1)
Creating an Alert Using a Severity Level in Enterprise Manager	375	(2)
Configuring a SQL Server Database Alert Using System Monitor	377	(3)
Assigning Alerts to an Operator Using Enterprise Manager	380	(2)
Creating an Alert Using the Create Alert Wizard	382	(3)
Creating a Transact-SQL Script to Re-create Your Existing Alerts	385	(1)
Editing and Viewing Alerts Using Enterprise Manager	386	(1)
Deleting an Alert Using Enterprise Manager	387	(1)
Introducing the Database Maintenance Plan Wizard	387	(9)
Introducing Multiserver Administration	396	(6)
Defining Your Master Server	397	(3)
Defining Your Targets Servers	400	(2)
Troubleshooting SQL Server Automation	402	(1)
Summary	402	(1)
Configuring SQL Server for Availability	403	(20)
Failover Clustering	403	(12)
Active/Passive versus Active/Active Configurations	405	(1)
Planning Issues	406	(1)
Installing Failover Clustering Support	406	(4)
Administering Failover Clusters	410	(1)
Removing a Node from a Failover Cluster	411	(1)
Recovering from Failures within Failover Cluster	411	(2)
Failover Cluster Troubleshooting	413	(2)
Standby Servers	415	(7)
Automated Log Shipping	416	(3)
Monitoring Log Shipping	419	(1)
Bringing the Standby Server Online	420	(2)
Summary	422	(1)
Monitoring and Optimizing SQL Server 2000	423	(42)
Strategies and Approaches to Optimization	423	(1)
Common Monitoring and Optimization Tasks	424	(1)
Establishing a Baseline and Detecting Bottlenecks	424	(1)
System-Level Monitoring	425	(1)
SQL Server Monitoring	425	(1)
Query Monitoring	425	(1)
Monitoring Tools	425	(39)
Windows 2000 Event Viewer	426	(3)
Windows System Monitor	429	(8)
Current Activity Window	437	(1)
Transact-SQL Tools	438	(10)
SNMP	448	(2)
SQL Profiler	450	(11)
SQL Query Analyzer	461	(3)
Summary	464	(1)
English Query	465	(28)
Installing English Query	465	(3)
English Query Components and Terminology	468	(3)
Building a Sample English Query Application	471	(16)
Building the Basic Model	471	(4)
Expanding the Basic Model	475	(3)
Using the Model Test Window to Make Modifications	478	(2)
Advanced Query Model Modification Techniques	480	(5)
Building your Application	485	(1)
Deploying your Application	485	(2)
Guidelines for English Query Usage	487	(3)
Summary	490	(3)
Part IV Using SQL Server 2000
Transferring and Transforming Data	493	(58)
BCP	493	(17)
Interactive BCP	498	(4)
BCP Examples	502	(5)
Modes of Operation	507	(1)
BCP and Triggers, Rules, Defaults, Constraints, and Unique Indexes	508	(1)
BCP Best Practices	509	(1)
BULK INSERT	510	(4)
DTS Overview	514	(1)
DTS Components	515	(7)
DTS Package	515	(1)
Connections	516	(1)
Tasks	516	(4)
Steps	520	(1)
Global Variables	521	(1)
A Simple Package Example	522	(1)
Using the DTS Wizards	522	(4)
Import/Export Wizard	522	(3)
Copy Database Wizard	525	(1)
Using the DTS Designer	526	(4)
DTS Packages	530	(8)
Saving Packages	530	(2)
Executing Packages	532	(4)
Scheduling your Package	536	(2)
Logging DTS Activity	538	(2)
Advanced DTS Tasks	540	(6)
Transform Data Task	540	(5)
Data Driven Query Task	545	(1)
Message Queue Task	546	(1)
Advanced Package Options	546	(2)
DTS Interoperability	548	(1)
Summary	549	(2)
Replication Benefits	551	(1)
Distributing Data Using Replication	551	(80)
Deciding When to Use Replication	552	(1)
Replication Terms and Concepts	552	(14)
Publisher-Subscriber Metaphor	553	(1)
Types of Subscriptions	553	(2)
Types of Replication	555	(3)
Replication Topologies	558	(2)
Replication Options	560	(3)
Replication Agents	563	(3)
Replication Features Supported by SQL Server 2000 Editions	566	(1)
Stages of Replication in SQL Server 2000	567	(1)
Configuring Replication and Distribution	568	(10)
Configuring Publishing and Distribution Using Transact-SQL	568	(1)
Configuring Publishing and Distribution Using Enterprise Manager	569	(6)
Getting to Know the Distribution Database	575	(3)
Creating Your Publications and Articles	578	(14)
Creating a Publication and Articles Using Transact-SQL	578	(1)
Creating a Publication and Articles Using Enterprise Manager	579	(13)
Managing Your Subscriptions	592	(19)
Creating a Push Subscription Using Transact-SQL	592	(1)
Viewing and Modifying a Push Subscription Using Transact-SQL	593	(1)
Deleting a Push Subscription Using Transact-SQL	593	(1)
Creating a Push Subscription Using Enterprise Manager	594	(4)
Viewing and Modifying a Push Subscription Using Enterprise Manager	598	(1)
Deleting a Push Subscription Using Enterprise Manager	599	(1)
Creating a Pull Subscription Using Transact-SQL	600	(1)
Viewing or Modifying a Pull or Anonymous Subscription Using Transact-SQL	601	(1)
Deleting a Pull Subscription Using Transact-SQL	601	(1)
Creating a Pull or Anonymous Subscription Using Enterprise Manager	602	(8)
Viewing and Modifying a Pull or Anonymous Subscription Using Enterprise Manager	610	(1)
Deleting a Pull or Anonymous Subscription Using Enterprise Manager	610	(1)
Applying the Initial Snapshot	611	(6)
Modifying Your Snapshot Files Working Directory	612	(1)
Viewing Your Snapshot Files Using Transact-SQL	613	(1)
Viewing Your Snapshot Files Using Enterprise Manager	613	(1)
Manually Applying Your Initial Snapshot	614	(1)
Automatically Applying Your Initial Snapshot	614	(1)
Executing Scripts When Applying Your Snapshot	615	(2)
Understanding Synchronization	617	(5)
Snapshot Replication Synchronization	617	(1)
Transactional Replication Synchronization	618	(2)
Merge Replication Synchronization	620	(2)
Using Heterogeneous Data Sources With Replication	622	(4)
Publishing to Heterogeneous Subscribers	622	(3)
Subscribing to Heterogeneous Publishers	625	(1)
Replication Features of SQL Server 2000 Windows CE	625	(1)
Monitoring Your Replication Process	626	(3)
Monitoring Replication Agent Performance	627	(1)
Monitoring Replication Agent Session Details	628	(1)
Using Alerts to Automate Replication Notification	628	(1)
Optimizing Replication	629	(1)
Summary	629	(2)
Configuring XML and Internet Support	631	(54)
Introducing the Web Assistant Wizard	632	(1)
Introducing XML Support in SQL Server 2000	632	(1)
Becoming Familiar with XML	633	(15)
Exploring HTML Sample Code	634	(1)
Exploring XML Sample Code	634	(2)
Exploring an XML Document	636	(2)
Using Xpath to Navigate Your XML Document	638	(1)
Specifying Your XPath Location	639	(2)
Specifying Criteria in Your Location Paths	641	(1)
Introducing XSL Style Sheets	642	(2)
Applying Your Style Sheets	644	(1)
Validating Your XML Data Using XML Data Schemas	645	(1)
Introducing an XDR Schema	645	(1)
Specifying the Content of Your Schema	646	(1)
Specifying Valid Data Types	647	(1)
Validating Your XML Document	648	(1)
Understanding XML and Relational Databases	648	(2)
Configuring an IIS Virtual Directory	650	(6)
Accessing SQL Server Data Using HTTP	656	(4)
Using the Virtual Directory Information	656	(1)
Choosing Your Access Method	657	(3)
Retrieving and Writing XML Data	660	(4)
Retrieving Data Using the FOR XML Clause	660	(1)
Guidelines for Using the FOR XML Clause	661	(1)
Writing XML Data Using OPENXML	661	(1)
Using XML in Meta Data Services	662	(2)
Installing and Using XML Sample Files	664	(2)
Running the XML Demo	665	(1)
Using the XML Startup Exercises	666	(1)
Introducing the Web Assistant Wizard	666	(2)
Preparing to Use the Web Assistant Wizard	668	(1)
Choosing a Database to Publish	668	(1)
Creating the Required Queries	668	(1)
Running the Web Assistant Wizard	669	(14)
Summary	683	(2)
Glossary	685	(38)
Index	723