| Acknowledgments | xi |
| Introduction | xi |
| 1 | SQL Server Overview | 1 |
| Defining SQL Server | 1 |
| SQL Server Communications | 2 |
| Transact-SQL: The Language of SQL Server | 3 |
| SQL Server and the Windows Family of Servers | 3 |
| SQL Server Editions and Components | 4 |
| SQL Server Components | 5 |
| SQL Server Services | 7 |
| SQL Server Databases | 8 |
| SQL Server Tools | 9 |
| 2 | Installing, Configuring, and Upgrading SQL Server | 13 |
| Hardware and Software Requirements | 13 |
| Preparing to Install SQL Server | 14 |
| Understanding Licensing | 15 |
| Character Sets, Sort Orders, and Collations | 17 |
| Multiple Instance Support in SQL Server 2000 | 20 |
| SQL Server Startup Account Selection | 22 |
| Authentication Mode Selection | 23 |
| Networking Library Selection | 25 |
| Installing SQL Server | 26 |
| SQL Server Attended Installation | 27 |
| Automating SQL Server Installation | 28 |
| Remote Installation of SQL Server | 30 |
| Testing the Installation | 31 |
| Upgrading to SQL Server 2000 | 31 |
| Troubleshooting SQL Server Installation | 32 |
| Applying SQL Server Service Packs | 34 |
| Completing Installation and Upgrade | 35 |
| 3 | SQL Server Security | 37 |
| SQL Server Security Model | 37 |
| Configuring SQL Server Authentication Modes | 38 |
| SQL Server 2000 and Kerberos | 39 |
| Managing SQL Server Logins | 42 |
| Managing Logins with Enterprise Manager | 42 |
| System Stored Procedures for Managing Logins | 44 |
| Default Logins | 46 |
| Getting Information on Logins | 46 |
| Database Access with Users and Roles | 46 |
| Assigning Logins to Database Users | 47 |
| Roles in SQL Server | 49 |
| Controlling Access with Permissions | 59 |
| Permission Management Commands | 59 |
| SQL Server Permission Categories | 60 |
| Obtaining Information on Permissions | 66 |
| Security Management Recommendations | 66 |
| 4 | Creating and Managing Databases | 69 |
| SQL Server Storage Structure | 69 |
| Transactions Explained | 70 |
| Devices in Previous Releases of SQL Server | 72 |
| Creating Databases | 73 |
| Planning File Placement | 75 |
| Managing Database Files | 79 |
| Managing File Growth and Numbers of Data Files | 79 |
| Filegroups in SQL Server | 81 |
| Shrinking Databases and Database Files | 82 |
| Retrieving File Information | 84 |
| Setting Database Options | 84 |
| Dropping Databases | 90 |
| The DBA Responsibility Truly Begins | 90 |
| 5 | Creating and Managing Database Objects | 91 |
| SQL Server Database Objects | 91 |
| Managing Tables and Data Types | 93 |
| SQL Server Scalar and User-Defined Data Types | 93 |
| Creating and Managing Tables | 98 |
| Managing Indexes | 103 |
| Why (Not to) Create Indexes? | 103 |
| SQL Server Data Retrieval Overview | 104 |
| Creating and Dropping Indexes | 106 |
| Managing Views | 108 |
| Creating, Altering, and Dropping Views | 109 |
| Programming in Transact-SQL | 111 |
| Transact-SQL Programming Language Elements | 112 |
| Stored Procedures | 112 |
| User-Defined Functions | 116 |
| Enforcing Integrity | 118 |
| Constraints | 118 |
| Triggers | 123 |
| SQL Server Defaults | 126 |
| SQL Server Rules | 128 |
| 6 | Database Backups | 131 |
| Understanding the SQL Server Recovery Models | 131 |
| Factors Affecting Your Choice of Recovery Model | 132 |
| Setting the Recovery Model | 133 |
| Understanding and Planning SQL Server Backups | 134 |
| Backup Types | 135 |
| The Backup Process | 140 |
| Creating Backup Devices | 140 |
| Performing Full Backups | 141 |
| Performing Differential Backups | 143 |
| Backing Up the Transaction Log | 144 |
| Backing Up Files and Filegroups | 145 |
| Backing Up a Database Using Enterprise Manager | 145 |
| Performing Dumps in SQL Server 6.5 | 146 |
| Automating SQL Server Backups | 148 |
| 7 | Restoring and Recovering Databases | 149 |
| Overview of SQL Server Recovery | 149 |
| Restoring and Recovering Databases | 151 |
| Pre-Restoration Tasks | 152 |
| Restoring a User Database | 156 |
| Restoring and Recovering System Databases | 163 |
| 8 | Loading and Extracting Data | 167 |
| SQL Server Tools for Extraction, Transformation, and Load | 167 |
| The Data Transformation Service | 170 |
| The DTS Import/Export Wizard | 173 |
| The DTS Designer | 175 |
| The Copy Database Wizard | 178 |
| Using DTS for ETL | 179 |
| The BCP Utility | 180 |
| Using Bulk Insert | 184 |
| Attaching and Detaching Databases | 186 |
| Using sp_detach_db and sp_attach_db | 187 |
| Using sp_certify_removable and sp_create_removable | 188 |
| Automating Data Loads | 189 |
| 9 | Automating SQL Server Administration | 193 |
| SQL Automation--An Overview | 193 |
| SQL Server Agent and Automating Administration | 194 |
| Configuring the SQL Agent Mail Service | 196 |
| Managing Operators | 197 |
| Defining a Fail-Safe Operator | 200 |
| Creating SQL Server Jobs | 201 |
| The Importance of the Job Owner | 203 |
| Defining Job Steps | 204 |
| Scheduling Jobs | 207 |
| Setting Notification | 210 |
| Monitoring Job History | 210 |
| Creating and Managing SQL Server Alerts | 211 |
| Creating Alerts on Events | 212 |
| Adding User-Defined Events | 214 |
| Creating Alerts on Performance Conditions | 215 |
| Defining Multi-Server Administration | 216 |
| 10 | Performance Tuning Part 1: Monitoring SQL Server Performance | 219 |
| Factors Affecting SQL Server Performance | 219 |
| The Pyramid of Performance | 220 |
| Defining a Performance Baseline | 224 |
| Tools for Monitoring SQL Server Performance | 225 |
| Windows Event Viewer | 226 |
| Windows System Monitor and Performance Logs and Alerts | 226 |
| SQL Profiler | 231 |
| Monitoring Real-Time SQL Server Activity | 233 |
| Automating SQL Performance Monitoring | 236 |
| 11 | Performance Tuning Part 2: Tuning SQL Server | 237 |
| Goals of Database Tuning | 237 |
| Tools for Tuning SQL Server | 238 |
| Windows System Monitor | 239 |
| Execution Plans and the Query Optimizer | 240 |
| SQL Server Query Optimizer | 240 |
| Determining the Execution Plan in Use | 242 |
| Optimizing Queries | 250 |
| Using Indexes to Cover a Query | 252 |
| Tuning Indexes | 254 |
| 12 | SQL Server Replication | 259 |
| Replication and Distributed Data | 259 |
| SQL Server Replication Architecture | 261 |
| SQL Server Replication Models | 262 |
| Replication Mechanics | 267 |
| SQL Server Replication Types | 269 |
| Designing and Planning Replication | 273 |
| What Data Should be Published | 273 |
| Who Receives the Data and How Often | 273 |
| Considering Physical Network Characteristics | 274 |
| Choosing the Type and Number of Subscribers | 275 |
| Considering Storage Space | 275 |
| Data Definition Considerations | 277 |
| Configuring SQL Server Replication | 279 |
| Configuring a Distributor | 279 |
| Configuring Publishing | 283 |
| Configuring Subscribers | 287 |
| Monitoring Replication | 289 |
| 13 | Managing Distributed Databases | 291 |
| Working with Distributed Data | 291 |
| Working with Ad Hoc Queries | 292 |
| Configuring and Managing Linked Servers | 294 |
| Securing Linked Server Connections | 297 |
| 14 | Clustering and Log Shipping | 305 |
| Clustering SQL Server | 305 |
| Installing SQL Server in a Clustered Environment | 307 |
| Administering SQL Server in a Cluster | 314 |
| Standby Servers and Log Shipping | 318 |
| Configuring a Production Database for Log Shipping | 318 |
| Monitoring Log Shipping | 323 |
| Switching Roles | 324 |
| Removing Log Shipping | 325 |
| Index | 327 |
