List of Figures

Chapter 1: Optimizing the Performance of Databases and Database Servers

Figure 1.1: System Monitor

Figure 1.2: SQLServer:Databases performance object counters

Figure 1.3: SQL Server Profiler

Figure 1.4: sys.dm_os_memory_objects DMV

Figure 1.5: Server Dashboard report

Figure 1.6: Scheduler Health report

Figure 1.7: Memory Consumption report

Figure 1.8: Log File Viewer

Figure 1.9: Activity Monitor

Figure 1.10: Output of sp_monitor

Figure 1.11: Output of fn_virtualfilestats

Figure 1.12: Recompilation

Figure 1.13: Disk Usage report

Figure 1.14: All Transactions report

Figure 1.15: Schema Changes History report

Figure 1.16: Output of DBCC SHOWCONTIG

Figure 1.17: The Database Engine Tuning Advisor

Chapter 2: Optimizing the Performance of Queries

Figure 2.1: Client statistics

Figure 2.2: Query plan

Figure 2.3: Additional query plan operation information

Figure 2.4: SQL Server index (B-tree)

Figure 2.5: Doubly linked list at the leaf level of the index (B-tree)

Figure 2.6: Physical location of pages that make up the doubly linked list in Figure 2.5

Figure 2.7: Physical location of pages after the index has been rebuilt

Figure 2.8: Activity Monitor, Process Info page

Figure 2.9: sp_who

Figure 2.10: sp_who2

Figure 2.11: Output of sys.dm_tran_locks

Figure 2.12: Resolving a deadlock

Figure 2.13: Deadlock graph in SQL Server Profiler

Chapter 3: Optimizing and Implementing a Data Recovery Plan for a Database

Figure 3.1: RAID-0

Figure 3.2: RAID-1

Figure 3.3: RAID-5

Figure 3.4: RAID-10

Figure 3.5: RAID 0+1

Figure 3.6: Failover cluster

Figure 3.7: Multi-Instance cluster

Figure 3.8: Disconnect Users in the Database When Restoring Backups check box

Figure 3.9: Replication architecture

Figure 3.10: Peer-to-peer transactional replication

Figure 3.11: Scripting DBA changes

Chapter 4: Designing a Strategy to Monitor a Database Solution

Figure 4.1: SQL Server Agent alert response options

Figure 4.2: SQL Server Agent alert options

Figure 4.3: SQL Server Event Alert

Figure 4.4: SQL Server Performance Condition Alert

Figure 4.5: WMI alert architecture

Figure 4.6: WMI alert

Figure 4.7: Trigger execution order

Figure 4.8: Event notification components in the Service Broker service

Chapter 5: Designing a Strategy to Maintain a Database Solution

Figure 5.1: Multiserver administration

Figure 5.2: Report Manager

Figure 5.3: Reporting Services Configuration Manager

Figure 5.4: Report History options in Report Manager

Figure 5.5: Report Snapshot options in Report Manager

Figure 5.6: An example of an email subscription in Report Manager

Figure 5.7: Configuring report caching in Report Manager

Figure 5.8: Configuring schedules in Report Manager

Figure 5.9: Managing encryption keys in the Reporting Services Configuration Manager

Figure 5.10: Reconfiguring the reportserver database in the Reporting Services Configuration Manager

Chapter 6: Designing and Managing SSIS Packages

Figure 6.1: Install options for SQL Server 2005/SSIS

Figure 6.2: Connecting to SSIS in SSMS

Figure 6.3: SSIS view in SSMS

Figure 6.4: Execute Package Utility dialog box

Figure 6.5: Scheduling an SSIS package

Figure 6.6: Viewing the ProtectionLevel property

Figure 6.7: Changing connection managers

Chapter 7: Designing a Database Data Management Strategy

Figure 7.1: The life cycle for data quality

Figure 7.2: The inner join of two data sets

Figure 7.3: The left outer join of two data sets

Figure 7.4: The right outer join of two data sets

Figure 7.5: The full outer join of two data sets

Figure 7.6: Setting the CHECKSUM page verify option

Figure 7.7: Addition of the uniqueidentifier column in the New Publication Wizard

Figure 7.8: The uniqueidentifier column in the published table

Figure 7.9: The conflict resolution policy for updatable subscriptions

Figure 7.10: The Resolver options page for a merge article

Figure 7.11: A peer-to-peer replication solution

Figure 7.12: The Production.ProductModel and Production.Product tables

Figure 7.13: The Production.Product.ProductModelID column properties

Chapter 8: Designing a Strategy to Manage Replication

Figure 8.1: Linked servers

Figure 8.2: A linked server configuration

Figure 8.3: SSMS provider options

Figure 8.4: Linked Server properties sheet, Server Options page

Figure 8.5: Active Directory Delegation tab

Figure 8.6: In delegation, the user running a distributed query logs into a SQL Server, which impersonates that user at other connected SQL Servers

Figure 8.7: Connections for OLE DB data sources

Figure 8.8: The publisher-distributor-subscriber metaphor

Figure 8.9: Snapshot replication components

Figure 8.10: The Sysreplicationalerts table in msdb

Figure 8.11: Warnings and Agents tab in Replication Monitor

Figure 8.12: Configuring replication alerts in Replication Monitor

Figure 8.13: Conflict resolution policy for updatable subscription

Figure 8.14: Merge replication components

Figure 8.15: Resolver tab of the Article Properties dialog box

Chapter 9: Protecting Your SQL Server from Attack

Figure 9.1: Results of auditing with a DDL trigger

Chapter 10: Designing a Strategy to Manage and Maintain Database Security

Figure 10.1: Basic encryption of data

Figure 10.2: SQL Server Login Properties dialog box

Figure 10.3: SQL Server Security Properties dialog box

Figure 10.4: Users in a database