| Acknowledgments |
|
xvii | |
| Introduction |
|
xix | |
|
|
|
1 | (28) |
|
Preinstallation Considerations |
|
|
2 | (2) |
|
|
|
3 | (1) |
|
|
|
4 | (1) |
|
Installation---The Important Choices |
|
|
4 | (13) |
|
|
|
7 | (1) |
|
|
|
8 | (2) |
|
|
|
10 | (1) |
|
|
|
11 | (3) |
|
The SQL Server Logon Account |
|
|
14 | (3) |
|
|
|
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) |
|
|
|
28 | (1) |
|
System Architecture of SQL Server |
|
|
29 | (52) |
|
|
|
30 | (10) |
|
|
|
30 | (4) |
|
|
|
34 | (6) |
|
The Relational Engine and the Storage Engine |
|
|
40 | (18) |
|
|
|
40 | (1) |
|
The Optimization Component |
|
|
40 | (3) |
|
|
|
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) |
|
|
|
52 | (4) |
|
|
|
56 | (1) |
|
Other Managers in SQL Server |
|
|
57 | (1) |
|
|
|
58 | (9) |
|
The Buffer Manager and Memory Pools |
|
|
58 | (1) |
|
Access to In-Memory Pages |
|
|
58 | (1) |
|
Access to Free Pages (Lazywriter) |
|
|
59 | (3) |
|
|
|
62 | (1) |
|
Accessing Pages via the Buffer Manager |
|
|
63 | (1) |
|
|
|
64 | (2) |
|
|
|
66 | (1) |
|
The SQL Server Kernel and Interaction with the Operation System |
|
|
67 | (13) |
|
Threading and Symmetric Multiprocessing |
|
|
68 | (1) |
|
|
|
69 | (1) |
|
|
|
70 | (2) |
|
|
|
72 | (1) |
|
|
|
73 | (1) |
|
|
|
74 | (1) |
|
Referring to SQL Server Objects |
|
|
75 | (2) |
|
Understanding the Important of System Tables |
|
|
77 | (3) |
|
|
|
80 | (1) |
|
|
|
81 | (48) |
|
SQL Server 7's Innovative Answers to Security Issues |
|
|
83 | (2) |
|
Understanding SQL Server Logins |
|
|
85 | (4) |
|
Administering Database Users |
|
|
89 | (1) |
|
|
|
90 | (7) |
|
|
|
91 | (1) |
|
|
|
92 | (1) |
|
|
|
93 | (1) |
|
|
|
93 | (1) |
|
|
|
94 | (1) |
|
|
|
94 | (1) |
|
|
|
95 | (2) |
|
|
|
97 | (8) |
|
|
|
98 | (1) |
|
Predefined Database Roles |
|
|
98 | (6) |
|
Understanding Custom Database Roles |
|
|
104 | (1) |
|
Exploring Database Permissions |
|
|
105 | (8) |
|
|
|
105 | (4) |
|
|
|
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) |
|
|
|
127 | (2) |
|
The Structure of Data in SQL Server |
|
|
129 | (48) |
|
|
|
130 | (1) |
|
|
|
131 | (1) |
|
Understanding the Impact of Locks on Databases |
|
|
131 | (2) |
|
Understanding the Default Fill Factor Specification |
|
|
133 | (5) |
|
|
|
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) |
|
|
|
150 | (3) |
|
Introducing Database Specifications |
|
|
153 | (11) |
|
|
|
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) |
|
|
|
166 | (1) |
|
Setting the DBO Use Only Flag |
|
|
167 | (1) |
|
|
|
168 | (1) |
|
Truncate Log On Checkpoint |
|
|
169 | (1) |
|
|
|
169 | (1) |
|
|
|
170 | (1) |
|
|
|
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) |
|
|
|
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) |
|
|
|
179 | (1) |
|
|
|
180 | (1) |
|
|
|
180 | (1) |
|
Tools for Transferring Data in SQL Server |
|
|
181 | (3) |
|
Considering the Implementation of DTS |
|
|
184 | (5) |
|
|
|
185 | (1) |
|
|
|
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) |
|
|
|
195 | (1) |
|
Transforming Data with DTS |
|
|
196 | (3) |
|
|
|
199 | (5) |
|
Creating a Package with DTS Designer |
|
|
204 | (5) |
|
Excuting and Scheduling a DTS Package |
|
|
209 | (1) |
|
Introduction to Linked Servers |
|
|
210 | (7) |
|
|
|
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) |
|
|
|
222 | (1) |
|
Introduction to Distributed Queries |
|
|
222 | (3) |
|
|
|
223 | (1) |
|
Executing an Ad Hoc Query on a Remote Data Source |
|
|
224 | (1) |
|
|
|
225 | (2) |
|
Implementing and Managing a Backup Solution |
|
|
227 | (44) |
|
The New Administrator and Data Protection Responsibilities |
|
|
228 | (1) |
|
|
|
229 | (3) |
|
|
|
231 | (1) |
|
|
|
232 | (5) |
|
Backing Up System Databases |
|
|
232 | (5) |
|
Designing a Backup Solution |
|
|
237 | (7) |
|
Understanding Backup Types |
|
|
238 | (2) |
|
|
|
240 | (4) |
|
|
|
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) |
|
|
|
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) |
|
|
|
263 | (1) |
|
|
|
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) |
|
|
|
266 | (1) |
|
|
|
267 | (1) |
|
Advanced Backup Solutions |
|
|
268 | (2) |
|
|
|
270 | (1) |
|
|
|
271 | (30) |
|
Understanding the SQL Server Restore Process |
|
|
272 | (2) |
|
|
|
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) |
|
|
|
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) |
|
|
|
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) |
|
|
|
296 | (1) |
|
|
|
297 | (2) |
|
|
|
299 | (2) |
|
Monitoring and Optimization |
|
|
301 | (64) |
|
|
|
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) |
|
|
|
317 | (2) |
|
|
|
319 | (6) |
|
|
|
325 | (1) |
|
|
|
326 | (2) |
|
Startup Parameters on SQLSERVR.EXE |
|
|
328 | (1) |
|
|
|
329 | (1) |
|
Monitoring System Behavior |
|
|
330 | (1) |
|
|
|
330 | (13) |
|
|
|
332 | (4) |
|
Gathering Diagnostic Information |
|
|
336 | (5) |
|
Tuning SQL Server Profiler |
|
|
341 | (2) |
|
|
|
343 | (14) |
|
Performance Monitor Counters |
|
|
343 | (6) |
|
|
|
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) |
|
|
|
363 | (2) |
|
|
|
365 | (48) |
|
|
|
366 | (1) |
|
Understanding the SQL Server Agent |
|
|
367 | (2) |
|
Configuring E-Mail Services |
|
|
369 | (4) |
|
|
|
373 | (13) |
|
|
|
374 | (9) |
|
Managing Operators Using Enterprise Manager |
|
|
383 | (3) |
|
Creating and Managing Jobs |
|
|
386 | (13) |
|
Managing Jobs Using Enterprise Manager |
|
|
388 | (1) |
|
|
|
388 | (4) |
|
|
|
392 | (4) |
|
Creating and Managing Schedules |
|
|
396 | (2) |
|
|
|
398 | (1) |
|
Example: Automating the Backup to Testdb |
|
|
399 | (3) |
|
|
|
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) |
|
|
|
412 | (1) |
|
|
|
413 | (44) |
|
|
|
414 | (3) |
|
|
|
415 | (1) |
|
|
|
415 | (1) |
|
|
|
415 | (1) |
|
|
|
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) |
|
|
|
428 | (5) |
|
|
|
428 | (1) |
|
What Data Needs to Be Replicated? |
|
|
428 | (1) |
|
|
|
429 | (1) |
|
How Often Must You Replicate? |
|
|
429 | (1) |
|
What Will You Use for the Remote Connection? |
|
|
430 | (1) |
|
|
|
430 | (1) |
|
|
|
431 | (1) |
|
Where Is the Distribution Agent? |
|
|
431 | (1) |
|
Is the Distribution Database Shared? |
|
|
431 | (1) |
|
|
|
432 | (1) |
|
What Hardware Will You Use? |
|
|
432 | (1) |
|
Completing the Replication Planning |
|
|
432 | (1) |
|
|
|
433 | (15) |
|
Configurating a Distributor |
|
|
434 | (7) |
|
Modifying the Publisher and Distributor |
|
|
441 | (7) |
|
|
|
448 | (7) |
|
Navigating the Create Publication Wizard |
|
|
448 | (3) |
|
Exploring the Publication Properties Dialog Box |
|
|
451 | (4) |
|
|
|
455 | (1) |
|
|
|
456 | (1) |
|
|
|
457 | (42) |
|
Designing Merge Replication |
|
|
458 | (3) |
|
|
|
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) |
|
|
|
475 | (6) |
|
Using the Replication Monitor |
|
|
476 | (5) |
|
|
|
481 | (1) |
|
Troubleshooting Replication |
|
|
481 | (4) |
|
|
|
482 | (1) |
|
Configure and Monitor Replication Alerts |
|
|
482 | (1) |
|
Verify SQL Server Services |
|
|
483 | (1) |
|
|
|
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) |
|
|
|
491 | (2) |
|
|
|
492 | (1) |
|
|
|
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) |
|
|
|
498 | (1) |
|
Planning, Maintaining, and Troubleshooting with SQL Server |
|
|
499 | (44) |
|
|
|
500 | (5) |
|
Estimating the Minimum Size of a Database |
|
|
500 | (5) |
|
|
|
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) |
|
|
|
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) |
|
|
|
523 | (2) |
|
Troubleshooting Replication |
|
|
525 | (3) |
|
Items to Check for Resolving Replication Problems |
|
|
525 | (3) |
|
Debugging Stored Procedures and Triggers |
|
|
528 | (5) |
|
|
|
533 | (8) |
|
|
|
533 | (1) |
|
Nullability and ANSI Compliance Settings |
|
|
534 | (7) |
|
Locale-Specific SET Options |
|
|
541 | (1) |
|
|
|
541 | (2) |
| Appendix: Stored Procedure Summary |
|
543 | (2) |
| Standard Stored Procedures |
|
545 | (91) |
| Extended Stored Procedures |
|
636 | (21) |
| Index |
|
657 | |
