List of Figures

Chapter 2: The Basics of Achieving High Availability

Figure 2-1: A good example of servers arranged neatly in racks.

Figure 2-2: The proper amount of clearance above the racks as well as room for the cabling.

Figure 2-3: A good example of neat cabling.

Figure 2-4: An analog phone connection box in a data center, clearly labeled.

Chapter 3: Making a High Availability Technology Choice

Figure 3-1: An example of a server cluster.

Figure 3-2: An example of a Network Load Balancing cluster.

Figure 3-3: A failure detected on the node currently owning the SQL Server virtual server.

Figure 3-4: The new node after failover.

Chapter 4: Disk Configuration for High Availability

Figure 4-1: The results of a query designed to show the I/O statistics for all files comprising the pubs database.

Figure 4-2: Traditional system bandwidth pyramid.

Figure 4-3: Mirroring.

Figure 4-4: Striped mirrors.

Figure 4-5: Mirrored stripes .

Figure 4-6: Striping with parity, also known as RAID 5.

Figure 4-7: Block size in Computer Management.

Figure 4-8: The GUI for setting growth at database creation.

Figure 4-9: Configuring an alert for file size.

Figure 4-10: Shrinking a database in Enterprise Manager.

Chapter 5: Designing Highly Available Microsoft Windows Servers

Figure 5-1: SQL Server 2000 warning prior to installation.

Figure 5-2: Warning during the upgrade process.

Figure 5-3: Manage Your Server application.

Figure 5-4: Welcome To The Configure Your Server Wizard page.

Figure 5-5: Preliminary Steps wizard page.

Figure 5-6: Server Role wizard page.

Figure 5-7: Role Removal Confirmation wizard page.

Figure 5-8: Final confirming wizard page.

Figure 5-9: Windows Server 2003 Shut Down Windows dialog box.

Figure 5-10: Entry in Event Viewer.

Figure 5-11: Shutdown Event Tracker.

Figure 5-12: The Advanced tab of the Properties dialog box for a network card.

Figure 5-13: The General tab of the Properties dialog box for a network card.

Figure 5-14: The WINS tab of the Advanced TCP/IP Settings dialog box.

Figure 5-15: Advanced Settings dialog box.

Figure 5-16: Network Priority tab.

Figure 5-17: Using Cluster.exe to change the service account password under Windows Server 2003.

Figure 5-18: Starting DISKPART.

Figure 5-19: Selecting a disk to modify.

Figure 5-20: Selecting a volume.

Figure 5-21: Extending the capacity.

Figure 5-22: Expanded disk verification.

Figure 5-23: Starting NLB under Windows 2000 Server.

Figure 5-24: Starting NLB under Windows Server 2003.

Figure 5-25: Log Settings dialog box.

Figure 5-26: Removing NLB confirmation.

Figure 5-27: Network Load Balancing Manager after removing Network Load Balancing.

Chapter 6: Microsoft SQL Server 2000 Failover Clustering

Figure 6-1: N + 1 prior to failover.

Figure 6-2: N + 1 after two node failures.

Figure 6-3: One LUN with two drive letters as seen by SQL Server Setup.

Figure 6-4: Resource configuration in Cluster Administrator.

Figure 6-5: Cluster Administrator confirmation message.

Figure 6-6: Move Resources dialog box.

Figure 6-7: Modify Dependencies dialog box.

Figure 6-8: Verify the new dependency in the SQL Server Properties dialog box.

Figure 6-9: Output of the fn_servershareddrives function.

Figure 6-10: Drive letter available in SQL Server Enterprise Manager.

Figure 6-11: General tab of the SQL Server Network Utility dialog box.

Figure 6-12: Changing the port number.

Figure 6-13: Information confirmation message.

Figure 6-14: Modify Preferred Owners dialog box.

Figure 6-15: Resource group Properties Failback tab.

Figure 6-16: Advanced tab of the Properties dialog box for a cluster resource.

Figure 6-17: Failover tab for a cluster group.

Figure 6-18: Output of the fn_virtualservernodes function.

Figure 6-19: Installation Selection dialog box.

Figure 6-20: Advanced Options dialog box.

Figure 6-21: Failover Clustering dialog box.

Figure 6-22: Cluster Management dialog box.

Figure 6-23: Remote Information dialog box.

Figure 6-24: Existing Installation dialog box.

Figure 6-25: Information message confirming successful uninstall.

Figure 6-26: Setup Complete dialog box.

Figure 6-27: Security tab.

Figure 6-28: Configuration warning message.

Figure 6-29: SQL Server Agent Service Properties dialog box.

Figure 6-30: Restart Server message.

Figure 6-31: Error message 1.

Figure 6-32: Error message 2.

Figure 6-33: Error message 3.

Figure 6-34: Stopped cluster node.

Chapter 7: Log Shipping

Figure 7-1: The Active Directory Domains and Trusts tool of Windows 2000.

Figure 7-2: Diagram of the built-in log shipping features workflow.

Figure 7-3: How a database that is restored with the STANDBY option appears in Enterprise Manager.

Figure 7-4: The Options tab for a database.

Figure 7-5: Removing log shipping from a Database Maintenance Plan.

Figure 7-6: Removing a secondary from a Database Maintenance Plan.

Figure 7-7: The basic Log Shipping Monitor.

Figure 7-8: Detailed status information from the Log Shipping Monitor.

Figure 7-9: An error message indicating that the Event Viewer is full.

Figure 7-10: Log Shipping tab of a Database Maintenance Plan.

Figure 7-11: General tab of a Log Shipping secondary in a Database Maintenance Plan.

Figure 7-12: Initialize tab of a Log Shipping secondary in a Database Maintenance Plan.

Figure 7-13: Thresholds tab of a Log Shipping secondary in a Database Maintenance Plan.

Figure 7-14: Transaction Log Backup tab of a Database Maintenance Plan.

Figure 7-15: Source tab of the Log Shipping Monitor.

Figure 7-16: Destination tab of the Log Shipping Monitor.

Figure 7-17: Error message if the primary is not registered in Enterprise Manager.

Figure 7-18: Error message if the secondary is not registered in Enterprise Manager.

Figure 7-19: General tab when adding a new secondary.

Figure 7-20: Initialize tab when adding a new secondary.

Figure 7-21: Thresholds tab when adding a new secondary.

Figure 7-22: Changing server names in ODBC.

Chapter 8: Replication

Figure 8-1: Local Distributor in a replication topology.

Figure 8-2: Remote Distributor in a replication topology.

Figure 8-3: Using a Republisher.

Figure 8-4: Generating SQL Script For Replication dialog box.

Figure 8-5: Publisher And Distributor Properties dialog box.

Chapter 9: Database Environment Basics for Recovery

Figure 9-1: Duplexed and mirrored transaction logs.

Figure 9-2: Percent Log Used counter.

Figure 9-3: Performance of log file with autogrow.

Figure 9-4: Performance of log after recovery plan is started using backup.

Figure 9-5: Full backups do not block active transactions.

Figure 9-6: Frequent log backups minimize potential data loss.

Chapter 10: Implementing Backup and Restore

Figure 10-1: File-based backups offering significant flexibility.

Figure 10-2: The SQL Server Backup - Inventory dialog box to simplify creating a scheduled backup.

Figure 10-3: The Edit Schedule dialog box to define backup frequency.

Figure 10-4: The Edit Recurring Job Schedule dialog box to provide numerous scheduling possibilities.

Figure 10-5: The New Alert Properties dialog box to trigger transaction log backups when the transaction log is full.

Figure 10-6: The New Alert Properties dialog box set to trigger transaction log backups when the transaction log is 85 percent full.

Figure 10-7: Inventory Transaction Log Backup schedules.

Figure 10-8: SQL Server Agent job step detail.

Figure 10-9: SQL Server Agent backup job.

Figure 10-10: FileBasedBackupDB: A suspect database.

Figure 10-11: FileBasedBackupDB: Data file error.

Figure 10-12: FileBasedBackupDB: Specific file error.

Figure 10-13: File corruption of RWFile2 between points in time 12 and 13.

Figure 10-14: Windows Backup.

Figure 10-15: Selecting System State.

Figure 10-16: Backup job information.

Figure 10-17: The Advanced Backup Options dialog box.

Figure 10-18: Completed backup.

Figure 10-19: Restore And Manage Media tab.

Figure 10-20: Confirm Restore dialog box.

Figure 10-21: Advanced Restore Options dialog box.

Figure 10-22: Completed restore.

Chapter 12: Disaster Recovery Techniques for Microsoft SQL Server

Figure 12-1: The main Rebuild Master dialog box.

Figure 12-2: A successful Rebuild Master completion.

Chapter 13: Highly Available Upgrades

Figure 13-1: Automatic Updates screen in Windows.

Chapter 14: Administrative Tasks for High Availability

Figure 14-1: Security tab of Properties.

Figure 14-2: The General tab of SQL Server Agent Properties.

Figure 14-3: Modifying your port.

Figure 14-4: An example of a memory split.

Figure 14-5: Memory split with a paging file in use.

Figure 14-6: Freeing physical memory.

Figure 14-7: Changing your paging file settings.

Figure 14-8: Restart message.

Figure 14-9: Startup And Recovery dialog box from Computer Management.

Figure 14-10: Memory tab of an instances properties.

Figure 14-11: Prompt after selecting Reserve Physical Memory For SQL Server.

Figure 14-12: Nodes prior to failover.

Figure 14-13: Nodes after failover.

Figure 14-14: General tab of Generate SQL Scripts.

Figure 14-15: Formatting tab of Generate SQL Scripts.

Figure 14-16: Options tab of Generate SQL Scripts.

Figure 14-17: Generate SQL Scripts windows for SQL Server Agent Jobs.

Figure 14-18: DTS package with transfer tasks.

Figure 14-19: Transfer Jobs Task dialog boxJobs Tab.

Figure 14-20: Copy SQL Server Objects TaskCopy tab.

Figure 14-21: Save DTS Package dialog box with Locations expanded.

Chapter 15: Monitoring for High Availability

Figure 15-1: New Alert Properties dialog box General tab.

Figure 15-2: New Alert Properties dialog box Response tab.