SQL Server 7 Administration Guide

by Linsenbardt, Mark A.; Stigler, M. Shane; Stigler, Shane

• ISBN13: 9780072121476
• ISBN10: 0072121475
• Edition: 1st
• Format: Paperback
• Copyright: 1999-09-01
• Publisher: Osborne McGraw-Hill

Table of Contents

Acknowledgments	xvii
Introduction	xix
Installation	1	(28)
Preinstallation Considerations	2	(2)
Making a Platform Choice	3	(1)
Hardware Requirements	4	(1)
Installation---The Important Choices	4	(13)
Character Set	7	(1)
Sort Order	8	(2)
Unicode Collation	10	(1)
Network Support	11	(3)
The SQL Server Logon Account	14	(3)
Unattended Installation	17	(3)
Files Used for Unattended Installation	17	(3)
Testing Your Installation	20	(8)
The SQL Server Installation Files	20	(1)
Windows NT Service That Perform SQL Server Processing	21	(1)
Ensuring a Successful Installatino	21	(5)
Troubleshooting the Installation	26	(2)
Summary	28	(1)
System Architecture of SQL Server	29	(52)
The SQL Server Engine	30	(10)
The Net-Library	30	(4)
Open Data Services	34	(6)
The Relational Engine and the Storage Engine	40	(18)
The Command Parser	40	(1)
The Optimization Component	40	(3)
The Manager Service	43	(1)
The Expression Manager Component	43	(1)
The Query Execution Component	44	(1)
Communication Between the Relational Engine and the Storage Engine	44	(1)
The Access Methods Manager	45	(1)
Additional Components of the Access Methods Manager	46	(5)
The Page Manager and the Text Manager	51	(1)
The Transaction Manager	52	(4)
The Lock Manager	56	(1)
Other Managers in SQL Server	57	(1)
Managing Memory	58	(9)
The Buffer Manager and Memory Pools	58	(1)
Access to In-Memory Pages	58	(1)
Access to Free Pages (Lazywriter)	59	(3)
Checkpoints	62	(1)
Accessing Pages via the Buffer Manager	63	(1)
Large Memory Issues	64	(2)
The Log Manager	66	(1)
The SQL Server Kernel and Interaction with the Operation System	67	(13)
Threading and Symmetric Multiprocessing	68	(1)
The worker Thread Pool	69	(1)
Active Versus Idle	70	(2)
Disk I/O on Windows NT	72	(1)
Types of Databases	73	(1)
Database Objects	74	(1)
Referring to SQL Server Objects	75	(2)
Understanding the Important of System Tables	77	(3)
Summary	80	(1)
Implementing Security	81	(48)
SQL Server 7's Innovative Answers to Security Issues	83	(2)
Understanding SQL Server Logins	85	(4)
Administering Database Users	89	(1)
Using Server Roles	90	(7)
Database Creators	91	(1)
Disk Administrators	92	(1)
Process Administrators	93	(1)
Security Administrators	93	(1)
Server Administrators	94	(1)
Setup Administrators	94	(1)
System Administrators	95	(2)
Using Database Roles	97	(8)
The Public Role	98	(1)
Predefined Database Roles	98	(6)
Understanding Custom Database Roles	104	(1)
Exploring Database Permissions	105	(8)
Statement Permissions	105	(4)
Object Permissions	109	(4)
Understanding the Application Role	113	(3)
Managing Security Creatively	116	(3)
Using System Stored Procedures to Manage Security	117	(1)
Using Views to Manage Security	118	(1)
Understanding Broken Ownership Chains	119	(2)
Using Aliasing to Avoid Broken Ownership Chains	119	(1)
Addressing Security Issues That Arise from Aliasing	120	(1)
Beyond Security Basics: Suggested Strategies	121	(6)
Group-Based Security Management	121	(1)
Using Views for Data Security	122	(3)
Using Stored Procedures for Data Security	125	(1)
Using Triggers for Audit Trails	126	(1)
Summary	127	(2)
The Structure of Data in SQL Server	129	(48)
Understanding Pages	130	(1)
Understanding Extents	131	(1)
Understanding the Impact of Locks on Databases	131	(2)
Understanding the Default Fill Factor Specification	133	(5)
Exploring Database Files	138	(12)
Understanding the Transaction Log	142	(1)
Understanding Transaction Types	143	(2)
Effectively Handling Locking Conflicts	145	(1)
Understanding Database Files	145	(2)
Understanding Extended Database Files	147	(3)
Creating a Database	150	(3)
Introducing Database Specifications	153	(11)
Database Name	153	(1)
Using the NAME Parameter with CREATIVE DATABASE	153	(1)
Managing Filenames with the FILENAME Parameter	154	(1)
File Group Specification During the Creation Process	155	(2)
Specifying the database's Initial SIZE	157	(1)
Limiting Database growth with MAXSIZE	158	(1)
Controlling the Nature of a Database's Expansion with FILEGROWTH	158	(3)
Specifying Information About the Transaction Log with the LOG Parameter	161	(3)
Exploring Database Options	164	(11)
Single-User Mode	166	(1)
Setting the DBO Use Only Flag	167	(1)
Auto-Shrink	168	(1)
Truncate Log On Checkpoint	169	(1)
Read Only Mode	169	(1)
Auto-Create Statistics	170	(1)
Auto-Update Statistics	171	(1)
Effectively Using the Auto-Close Option	171	(1)
Using the Select Into/Bulk Copy Option	172	(1)
Using the ANSI NULL Default Option	173	(1)
Determining When to Use the Recursive Triggers Option	173	(1)
Specifying the Use of Quoted Identifiers with Transact-SQL	174	(1)
Detecting the Existence of Torn Pages	174	(1)
Performance Considerations	175	(1)
Summary	176	(1)
Transferring Data and Managing Distributed Data	177	(50)
Understanding Why You Will Transform Data in Many Environments	178	(3)
Change the Format of the Data	179	(1)
Restructure and Map Data	179	(1)
Make Data Consistent	180	(1)
Validate Data	180	(1)
Tools for Transferring Data in SQL Server	181	(3)
Considering the Implementation of DTS	184	(5)
Overview of DTS	185	(1)
The DTS Process	186	(3)
Working with the DTS Tools	189	(21)
The DTS Import and DTS Export Wizards	189	(1)
Importing and Exporting Data with the DTS Import and Export Wizards	190	(5)
DTS Designer	195	(1)
Transforming Data with DTS	196	(3)
Defining Workflows	199	(5)
Creating a Package with DTS Designer	204	(5)
Excuting and Scheduling a DTS Package	209	(1)
Introduction to Linked Servers	210	(7)
Adding Linked Servers	212	(1)
Security Considerations for Linked Servers	213	(1)
System Stored Procedures for Working with Linked Servers	214	(1)
Executing a Distributed Query	214	(3)
Copying Data with Bulk Copy (bcp) and the Distributed Transaction Coordinator (DTC)	217	(3)
Using the Bulk Copy Program	217	(3)
Distributing Data With SQL Server	220	(2)
Microsoft Distributed Transaction Coordinator	221	(1)
Replication	222	(1)
Introduction to Distributed Queries	222	(3)
Accessing Remote Data	223	(1)
Executing an Ad Hoc Query on a Remote Data Source	224	(1)
Summary	225	(2)
Implementing and Managing a Backup Solution	227	(44)
The New Administrator and Data Protection Responsibilities	228	(1)
The Backup Process	229	(3)
Restricted Activities	231	(1)
When to Back Up	232	(5)
Backing Up System Databases	232	(5)
Designing a Backup Solution	237	(7)
Understanding Backup Types	238	(2)
Backup Locations	240	(4)
Performing a Full Backup	244	(4)
Creating a Full Backup to Tape	247	(1)
Executing a Differential Backup	248	(4)
Reexamining Backup Solutions in the Context of SQL Server	250	(2)
Executing a Transaction Log Backup	252	(3)
Backup Options Available for Customizing and Controlling Performance	255	(5)
Verifying the Backup upon Completion	256	(1)
Ejecting the Tape After Backup	256	(1)
Removing Inactive Entries from the Transaction Log	256	(1)
Checking Media Set Name and Expiration	257	(1)
Specifying When Backup Set Will Expire	257	(1)
Initializing and Labeling Media	257	(1)
Append to Media	258	(1)
Overwriting Existing Media	258	(2)
Transact-SQL Specifications to Control Backup Performance	260	(8)
Using the Block Size Parameter	260	(1)
Adding Descriptions to the Backup	260	(1)
Setting a Data for the Media to Expire	261	(1)
Setting a Number of Days the Backup Must Be Maintained	261	(1)
Choosing to Format a Tape or Using the Existing Format	262	(1)
Rewriting Media Headers with Init or Leaving Them Alone with Noinit	263	(1)
Describing the Media	263	(1)
Naming the Media	264	(1)
Specifying the Backup Set's Logical Name	264	(1)
Using No_truncate to Protect the Transaction Log	264	(1)
Clearing the Transaction Log with the Truncate_only and No_log Parameters	265	(1)
Overriding Media Names with the Skip/Noskip Options	265	(1)
Controlling the Tape's Physical Location after the Backup with Unload/Nounload	266	(1)
Restart	266	(1)
Stats	267	(1)
Advanced Backup Solutions	268	(2)
Summary	270	(1)
Restoring Databases	271	(30)
Understanding the SQL Server Restore Process	272	(2)
Restoring a Database	274	(1)
Restoring a Database Using Enterprise Manager	274	(10)
Using the Database Radio Button	277	(1)
Using the Filegroups or Files Radio Button	278	(2)
Using the From Device Radio Buttom	280	(3)
Understanding the Backup History	283	(1)
Restoring a Database Using Transact-SQL	284	(13)
Restoring a Database	284	(2)
Using the DBO_ONLY Option with Restores	286	(1)
Choosing Media Sets Using the With File Option	287	(1)
Identifying Backup Sets with the Media Name Parameter	287	(1)
Restoring to a Specified Location with the Move To Option	288	(2)
Controlling How and When the Recovery Process Executes with the Recovery and Norecovery Options	290	(1)
Creating a Hot-Standby Server with the Standby Option	291	(1)
The Replace Option	291	(1)
Continuing a Restore After a Server Failure with the Restart Option	292	(1)
Controlling the Tape Device	293	(1)
Restoring Files and Filegroups	293	(2)
Restoring a Transaction Log	295	(1)
Restoring to a Specific Point in Time	296	(1)
Gathering Statistics	296	(1)
The Standby Server	297	(2)
Summary	299	(2)
Monitoring and Optimization	301	(64)
Configuration Issues	302	(28)
Windows NT Configuration Settings	303	(5)
SQL Server Configuration Settings	308	(1)
Memory Options for the SQL Server	309	(5)
Setting Scheduling Options	314	(3)
Disk I/O Options	317	(2)
Query Processing Options	319	(6)
Database Options	325	(1)
Buffer Manager Options	326	(2)
Startup Parameters on SQLSERVR.EXE	328	(1)
System Maintenance	329	(1)
Monitoring System Behavior	330	(1)
SQL Server Profiler	330	(13)
Defining a Trace	332	(4)
Gathering Diagnostic Information	336	(5)
Tuning SQL Server Profiler	341	(2)
Performance Monitor	343	(14)
Performance Monitor Counters	343	(6)
User-Defined Counters	349	(1)
Other Performance Monitor Counters	350	(6)
Other Performance Monitoring Considerations	356	(1)
Optimizing Backup and Restore Performance	357	(6)
Optimizing Database, Differential Database, and File Backup Performance	357	(1)
Optimizing Transaction Log Backup Performance	358	(1)
Optimizing Restore Performance	359	(1)
Optimizing Tape Backup Device Performance	360	(1)
Optimizing Disk Backup Device Performance	361	(1)
Optimizing Data Compression	361	(2)
Summary	363	(2)
Automating SQL Server	365	(48)
Why You Want to Automate	366	(1)
Understanding the SQL Server Agent	367	(2)
Configuring E-Mail Services	369	(4)
Working with Operators	373	(13)
Creating an Operator	374	(9)
Managing Operators Using Enterprise Manager	383	(3)
Creating and Managing Jobs	386	(13)
Managing Jobs Using Enterprise Manager	388	(1)
Creating a Job	388	(4)
Managing Job Steps	392	(4)
Creating and Managing Schedules	396	(2)
Managing Notifications	398	(1)
Example: Automating the Backup to Testdb	399	(3)
Understanding Alerts	402	(8)
Creating and Managing Alerts	403	(5)
Configuring Event Forwarding	408	(2)
Automating Jobs in a MultiServer Environment	410	(2)
Configuring the Master Server	410	(1)
Configuring Target Servers	411	(1)
Summary	412	(1)
Replication	413	(44)
The Metaphor	414	(3)
The Publisher	415	(1)
The Distributor	415	(1)
The Subscriber	415	(1)
The Process	416	(1)
The Reality of Replication	417	(11)
Understanding Replication Models	418	(4)
Understanding the Role of Agents	422	(2)
Understanding Replication of Methods	424	(4)
Planning Replication	428	(5)
Why Replicate?	428	(1)
What Data Needs to Be Replicated?	428	(1)
Who Are the Subscribers?	429	(1)
How Often Must You Replicate?	429	(1)
What Will You Use for the Remote Connection?	430	(1)
What Replication Method?	430	(1)
What Replication Model?	431	(1)
Where Is the Distribution Agent?	431	(1)
Is the Distribution Database Shared?	431	(1)
Push or Pull?	432	(1)
What Hardware Will You Use?	432	(1)
Completing the Replication Planning	432	(1)
Setting Up Replication	433	(15)
Configurating a Distributor	434	(7)
Modifying the Publisher and Distributor	441	(7)
Creating a Publication	448	(7)
Navigating the Create Publication Wizard	448	(3)
Exploring the Publication Properties Dialog Box	451	(4)
Creating a Subscription	455	(1)
Summary	456	(1)
Advanced Replication	457	(42)
Designing Merge Replication	458	(3)
Data Definition	458	(3)
Configuring Replication Agents	461	(10)
Examining the Configuration Choices for Replication Agents	463	(8)
Understanding the Distribution Database	471	(4)
Using Stored Procedures to Obtain Replication Information	472	(3)
Monitoring Replication	475	(6)
Using the Replication Monitor	476	(5)
Replication Alerts	481	(1)
Troubleshooting Replication	481	(4)
Check the Error Logs	482	(1)
Configure and Monitor Replication Alerts	482	(1)
Verify SQL Server Services	483	(1)
Test the Connectivity	483	(2)
Using Replication Scripts	485	(3)
Replicating to Foreign Hosts	488	(3)
Replicating to a Foreign Subscriber	489	(1)
Replicating from a Foreign Publisher	490	(1)
Usign SQL Server Replication Monitor	491	(1)
Maintaining Replication	491	(2)
Space Management	492	(1)
Backup Strategies	493	(1)
Monitoring SQL Server Replication Performance	493	(5)
Using Windows NT Performance Monitor	493	(3)
Using System Stored Procedures	496	(1)
Viewing Replication Agent Histories	497	(1)
Summary	498	(1)
Planning, Maintaining, and Troubleshooting with SQL Server	499	(44)
Capacity Planning	500	(5)
Estimating the Minimum Size of a Database	500	(5)
Maintaining SQL Server	505	(8)
Developing a Database Maintenance Plan	505	(4)
Automating the Database Maintenance Plan Tasks	509	(4)
Troubleshooting Issues That Arise After Using the SQL Server Upgrade Wizard	513	(6)
Removing SQL Server 6.5	513	(1)
Troubleshooting the Upgrade	513	(3)
Specifying Compatibility Levels	516	(1)
Understanding Backward Compatibility Details	517	(2)
Troubleshooting SQL Server Automated Administration	519	(6)
Troubleshooting Alerts	523	(2)
Troubleshooting Replication	525	(3)
Items to Check for Resolving Replication Problems	525	(3)
Debugging Stored Procedures and Triggers	528	(5)
Environmental Concerns	533	(8)
Case Sensitivity	533	(1)
Nullability and ANSI Compliance Settings	534	(7)
Locale-Specific SET Options	541	(1)
Summary	541	(2)
Appendix: Stored Procedure Summary	543	(2)
Standard Stored Procedures	545	(91)
Extended Stored Procedures	636	(21)
Index	657