Download Wiley SQL Server 2008 Administration Instant Reference

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