Admin911 SQL Server 2000: A Survival Guide for System Administrators (2000)

by Brian Knight, Brian Knight (Conducted by)

Overview

This guide should allow DBAs to manage and troubleshoot SQL Server 2000. It contains a troubleshooting chart for ease of use when finding solutions to problems.

Product Details

ISBN-13:	9780072130973
Publisher:	McGraw-Hill Companies, The
Publication date:	01/01/2001
Series:	Admin911
Edition description:	2000 ed.
Pages:	388
Product dimensions:	7.50(w) x 9.20(h) x 0.80(d)

Table of Contents

	Acknowledgments	xiv
1	Architecture and Installation	1
	SQL Server's Roots	2
	Architecture Overview	3
	Net-Library	3
	Open Data Services	3
	The SQL Server Engines	4
	Installation Tips	4
	SQL Server 2000 Editions	7
	Preinstallation Decisions	9
	Removing SQL Server	12
	Starting SQL Server and Agent	12
	Upgrading from SQL Server 4.2	13
	Upgrading from SQL Server 6.0 or 6.5	14
	Upgrading from SQL Server 7.0	15
	Upgrading Stored Procedures	17
	Tricks and Tips for Upgrading	17
	Files and Directories	19
	Moving the Tools Directory	20
2	Managing SQL Server	21
	Managing the Server	22
	Managing Multiple Instances	22
	Renaming a Server	23
	Tweaking Configuration Options	24
	Managing the Database	31
	Tweaking Database Configuration Options	32
	Managing Security	38
	Common Security Concerns	39
	Avoiding Dangerous Settings	41
	Deploying Your Database	43
	Service Packs	44
3	Optimizing and Automating SQL Server Administration	45
	Startup Stored Procedures	46
	SQL Mail	47
	Configuring SQL Mail	47
	xp_sendmail	49
	Other Mailer Options	50
	Optimizing SQL Server Agent	51
	General Tab Options	51
	Advanced Tab Options	52
	Alert System Tab Options	52
	Job System Tab	53
	Connection Tab	53
	Operators	54
	Adding an Operator	54
	Scripting Operators	55
	Jobs	57
	Creating Jobs	58
	Job Categories	60
	Debugging Jobs	60
	Scripting Jobs	61
	Centralized Administration	61
	Managing Multi Server Jobs	61
	Creating a Multi Server Job	62
	Administering DTS	63
	DTS Connections	64
	Saving a Package	65
	Managing Package Versions	66
	Securing a Package	67
	Executing Packages	68
	Managing Package Locations	69
	Using DTSRunUI	70
	Scheduling a Package	70
	Optimizing Packages	71
	DTS Without SQL Server	74
	Troubleshooting DTS	75
4	Monitoring and Tuning SQL Server	79
	Managing Messages	80
	Error Message Severity Levels	81
	Raising an Error	82
	xp_logevent	83
	System Monitor	83
	Adding Counters	84
	Performance Counters to Watch	86
	System Monitor Logging	87
	Viewing Performance Logs	89
	sp_monitor	90
	Trace Flags	91
	Setting Trace Flags	91
	Alerts	94
	Creating an Alert	94
	Setting Performance Alerts	96
	Setting Job Notifications	96
	Forecasting Growth	97
	Database Growth Reports	97
	sqldiag	99
	Black Box	99
5	Automating Administrative Tasks	101
	Query Analyzer	102
	Command-Line Options	102
	Distributing Configuration Files	104
	Object Browser	104
	Getting Around the 256-Character Limit	104
	Shortcut Queries	105
	Finding Objects	106
	Templates	106
	Creating Your Own Templates	107
	Administrative T-SQL	108
	Simplifying Cursors	108
	Extended Stored Procedures	111
	Administrative DBCC Commands	119
	System Functions	127
	Working with Identities	130
	Extended Properties	135
	OLE Automation	140
	Alternate Method of Counting Records	144
6	Optimizing and Troubleshooting SQL Server	147
	Optimizing Windows 2000 Networks for SQL Server	148
	Optimizing Operating System Components	148
	Optimizing Network Settings	149
	Optimizing Name Resolution	151
	Registry Dependencies	154
	Rebuilding SQL Registry Entries After Reinstalling Windows	155
	Using Registry Server Listings	155
	Managing and Configuring the Database	156
	Monitoring Growth	156
	Disconnecting Users	157
	Model Database	159
	Database Standards	159
	Naming Conventions	160
	Numeric Data Type Decisions	162
	Character Data Types	163
	Performance Tuning Your Database Model	164
	Row Size	165
	Text, ntext, and image Fields	167
	Minimizing Wasted Column Space	168
	Summarizing Data	169
	Using Sample Data	169
	Using a Cross Join	169
	Optimizing Stored Procedures	172
	Determining Parameters for Stored Procedures	172
	Troubleshooting Cached Stored Procedures	173
	Tuning Stored Procedure Performance	174
	Building Dynamic Stored Procedures	174
	Using Temporary Tables	175
	Table Data Types for Temporary Tables	176
	Using the Tempdb Database	177
	Performance Tuning T-SQL	178
	Performance Tips for Queries	179
	Non-logged SQL Statements	180
	Returning Top Rows	180
	Ordering Views	181
	Quoted Identifiers	182
	Read-Only Databases	183
	Optimizing Indexes	184
	Optimizing Index Fill Factors	184
	Using DBCC SHOWCONTIG	185
	Defragging Indexes	189
	Using the Index Tuning Wizard	190
	SQL Server Profiler	191
	Web Troubleshooting Resources	195
7	Disaster Planning and Recovery	197
	Developing a Plan	198
	Third-Party Backup Programs	198
	Types of Backups	199
	Full Backups in Enterprise Manager	199
	Full Backups in T-SQL	204
	Configuring Devices with T-SQL	206
	Differential Backups	206
	Transaction Log Backups	207
	Transaction Log Backups in T-SQL	207
	Backing Up Individual Files and File Groups	208
	Backupset Table	209
	Optimizing Backup and Restore Performance	209
	Database Recovery Models	210
	Simple Recovery Model	211
	Full Recovery Model	212
	Bulk Recovery Model	212
	Recovery Options	213
	Maintenance Plans	213
	Using sqlmaint	214
	Using xp_sqlmaint	216
	Restoring	216
	Full Database Restores	217
	Full Restore in T-SQL	219
	Differential Database Restores	222
	Transaction Log Restores	223
	Point-In-Time Scenarios	224
	Fixing Broken Logins	224
	Using sp_change_users_login	225
	Recovering a Corrupt Master Database	226
	Rebuilding Other Databases	227
	Detaching and Attaching a Database	227
	Detaching a Database	228
	Other Methods of Copying Data Files	229
	Attaching a Database	230
	Uninstalling SQL Server or Upgrading from an Evaluation Edition	232
8	Scaling SQL Server	233
	Linked Servers	234
	Data Flow of Linked Servers	234
	Myriad Uses for Linked Servers	235
	Creating a Linked Server in Enterprise Manager	236
	Creating a Linked Server Through T-SQL	239
	Linked Server Security	241
	Configuring the Linked Server	245
	Configuring Linked Servers Through T-SQL	247
	Linked Servers Between Heterogeneous Databases	247
	Deleting a Linked Server	250
	Querying Linked Servers	250
	Joining Multiple Servers	251
	Executing Stored Procedures on Linked Servers	252
	Getting Verbose Error Messages	253
	Tuning and Supporting Linked Servers	256
	Distributed Partitioned Views	259
	DPV Architecture	259
	Preparing for a DPV	261
	Setting Up and Using the Partitioned Views	263
	Inserting Data into the Partitioned View	264
	Querying a Partitioned View	266
	Updating a Partitioned View	266
	DPV Limitations	267
	Indexed Views	268
	Indexed Views Requirements and Restrictions	268
	Creating an Indexed View	271
	Querying Indexed Views	273
9	SQL Server Replication	275
	SQL Server Replication	276
	Replication Considerations	276
	Replication Architecture	277
	Heterogeneous Support for Replication	279
	Understanding the Types of Replication	280
	Snapshot Replication	280
	Merge Replication	282
	Transactional Replication	283
	Bidirectional Replication	284
	Configuring SQL Server Agent Replication	285
	Preinstallation Concerns	285
	Data Types Concerns	286
	Dealing with Identity Columns	287
	Initial Replication Setup	289
	Customizing the Initial Installation	291
	Disabling Replication	294
	Snapshot/Transactional Replication Setup	295
	Merge Replication Setup	302
	Creating Subscriptions	305
	Merge Replication Subscriptions	306
	Configuring Replication After the Wizard	306
	Schema Changes in Replication	308
	sp_repladdcolumn	308
	sp_repldropcolumn	310
	Monitoring and Troubleshooting Replication	310
	Troubleshooting Agents	311
	Recovering from a Disaster	312
	Optimizing Replication	312
A	System Tables and Undocumented DBCC Commands	315
	System Tables	316
	Tables in Master Database Only	316
	Tables in Every Database	326
	Undocumented DBCC Commands	334
B	Extended Stored Procedures	341
	xp_availablemedia	342
	xp_cmdshell	342
	xp_dirtree	343
	xp_enum_activescriptengines	343
	xp_enum_oledb_providers	343
	xp_enumcodepages	344
	xp_enumdsn	344
	xp_enumerrorlogs	345
	xp_enumgroups	345
	xp_fileexist	345
	xp_fixeddrives	346
	xp_getfiledetails	346
	xp_getnetname	347
	xp_loginconfig	347
	xp_logevent	347
	xp_logininfo	348
	xp_ntsec_enumdomains	348
	xp_readerrorlog	348
	xp_regdeletekey	349
	xp_regdeletevalue	349
	xp_regenumvalues	349
	xp_regread	350
	xp_regwrite	350
	Index	353