Page is loading ...
Part I: SQL Server Basics 1
Chapter 1: Installing SQL Server 2008 3
Prepare for Installation 4
SQL Server in the Enterprise 4
SQL Server Editions 9
SQL Server 2008 Licensing 16
Installation Requirements 17
Install SQL Server 2008 20
Performing a Standard Installation 21
Verifying a Standard Installation 32
Performing an Unattended Installation 37
Chapter 2: Configuring SQL Server 2008 41
Configure the Server Platform 42
Configuring System Memory 42
Planning System Redundancy 46
Configure SQL Server 2008 50
Configuring the Server with the SSMS 50
Configuring the Server with TSQL 64
Configuring the Services 66
Chapter 3: Creating Databases, Files, and Tables 75
Perform Capacity Planning 76
SQL Server I/O Processes 77
Disk Capacity Planning 79
Memory Capacity Planning 82
Create and Alter Databases 83
Create a Database 84
Alter a Database 92
Drop a Database 96
Manage Database Files 98
Add Files to a Database 98
Add Files to a Filegroup 101
Contents
Introduction xiii
96602book.indb 7 7/23/09 4:04:16 PM
COPYRIGHTED MATERIAL
Contentsviii
Modify Database File Size 104
Deallocate Database Files 107
Create and Alter Tables 109
Understand SQL Server Data Types 109
Create a Table 112
Alter a Table 115
Drop a Table 117
Chapter 4: Executing Basic Queries 119
Use SQL Server Schemas 120
Understand Schemas 120
Create Schemas 123
Associate Objects with Schemas 125
Select Data from a Database 128
Use Basic Select Syntax 128
Group and Aggregate Data 133
Join Data Tables 136
Use Subqueries, Table Variables, Temporary Tables,
and Derived Tables 138
Modify Data 144
Insert Data 144
Delete Data 146
Update Data 146
Use the SQL Server System Catalog 148
Use System Views 148
Use System Stored Procedures 151
Use Database Console Commands 153
Part II: Data Integrity and Security 155
Chapter 5: Managing Data Integrity 157
Implement Entity Integrity 158
Implement Primary Keys 159
Implement Unique Constraints 163
Implement Domain Integrity 167
Implement Default Constraints 167
Implement Check Constraints 170
Implement Referential Integrity 174
Implement Foreign Keys 176
Implement Cascading References 179
Understand Procedural Integrity 182
Understand Stored Procedure Validation 183
Understand Trigger Validation 185
96602book.indb 8 7/23/09 4:04:16 PM
Contents ix
Chapter 6: Managing Transactions and Locks 187
Manage SQL Server Locks 189
Identify Lock Types and Behaviors 189
Identify Lock Compatibility 193
Manage Locking Behavior 194
Manage Transactions 199
Creating Transactions 200
Handling Transaction Errors 203
Using Savepoints in Transactions 205
Implement Distributed Transactions 208
Understanding Distributed Queries 209
Defining Distributed Transactions 215
Manage Special Transaction Situations 217
Concurrency and Performance 217
Managing Deadlocks 218
Setting Lock Time-Outs 220
Chapter 7: Managing Security 221
Implement User Security 222
Understand the Security Architecture 222
Implement Server Logins 229
Implement Database Users 232
Implement Roles 234
Manage Server Roles 234
Implement Database Roles 236
Implement Application Roles 237
Implement Permissions 240
Understand the Permissions Model 240
Manage Permissions through SSMS 241
Manage Permissions through a Transact SQL Script 244
Encrypt Data with Keys 245
Understand SQL Server Keys 246
Manage SQL Server Keys 247
Encrypt and Decrypt Data with Keys 253
Implement Transparent Data Encryption 255
Part III: Data Administration 259
Chapter 8: Implementing Availability and Replication 261
Implement Availability Solutions 262
Understand RAID 263
Understand Clustering 264
Implement Log Shipping 265
Implement Database Mirroring 276
96602book.indb 9 7/23/09 4:04:17 PM
Contentsx
Implement Replication 286
Understand Types of Replication 286
Configure the Replication Distributor 290
Configure the Replication Publisher 293
Configure the Replication Subscriber 298
Chapter 9: Extracting, Transforming, and Loading Data 303
Use the Bulk Copy Program Utility 304
Perform a Standard BCP Task 306
Use BCP Format Files 307
Use Views to Organize Output 309
Use the SQL Server Import and Export Wizard 309
Execute the Import and Export Wizard 310
Saving and Scheduling an Import/Export 314
Create Transformations Using SQL Server Integration Services (SSIS) 316
Create a Data Connection 320
Implement a Data Flow Task 322
Perform a Transformation 327
Implement Control of Flow Logic 332
Chapter 10: Managing Data Recovery 333
Understand Recovery Concepts 334
Understand Transaction Architecture 334
Design Backup Strategies 337
Perform Backup Operations 344
Perform Full Database Backups 344
Perform Differential Backups 349
Perform Transaction Log Backups 350
Perform Partial Database Backups 354
Perform Restore Operations 355
Perform a Full Database Restore 356
Use Point-in-Time Recovery 365
Part IV: Performance and Advanced Features 369
Chapter 11: Monitoring and Tuning SQL Server 371
Monitor SQL Server 372
Using the SQL Profiler 373
Using System Monitor 377
Using Activity Monitor 388
Implementing DDL Triggers 391
Using SQL Server Audit 394
Tune SQL Server 398
Using Resource Governor 398
Managing Data Compression 403
96602book.indb 10 7/23/09 4:04:17 PM
Contents xi
Chapter 12: Planning and Creating Indexes 407
Understand Index Architecture 408
Understand Index Storage 408
Get Index Information 411
Plan Indexes 416
Create and Manage Indexes 419
Create and Manage Clustered Indexes 419
Create and Manage Nonclustered Indexes 426
Use the Database Engine Tuning Advisor 428
Manage Special Index Operations 431
Cover a Query 432
Optimize Logical Operations 433
Optimize Join Operations 435
Chapter 13: Policy-Based Management 437
Understand Policy-Based Management 438
Create a Condition 441
Create a Policy 443
Evaluate a Policy 446
Policy Storage 448
Troubleshooting Policy-Based Management 449
Manage Policies in the Enterprise 451
Benefits of the EPM Framework 451
Chapter 14: Automating with the SQL Server Agent Service 455
Configure the SQL Server Agent Service 456
Understand Automation Artifacts 456
Perform Basic Service Configuration 459
Configure Database Mail 465
Create and Configure Automation Objects 470
Create and Configure Proxies 470
Create and Configure Schedules 473
Create and Configure Operators 477
Create and Configure Alerts 479
Create and Configure Jobs 482
Use the SQL Server Maintenance Plan Wizard 488
Index 497
96602book.indb 11 7/23/09 4:04:17 PM
/
