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