(Retired – no longer offered as a public class)

Overview

This 3/400-level, 5-day, SQL Server training class is designed to give a solid grounding for database administrators in topics related to high availability and disaster recovery.

The class starts by explaining the methodology for creating a sound HA/DR plan and then covers backup and restore operations in depth. After that, the four major multi-instance technologies (clustering, database mirroring, replication, and availability groups) are covered. Log shipping will be discussed, but not in any great depth. We also cover consolidation and virtualization, both of which play an important role in availability and cost-cutting. Finally we examine database snapshots, and cover consistency checking and recovering from corruptions in depth.

If you have previously attended IE3, you do not need to attend this course.

Availability is an area that is critical in today’s environment and all DBAs should be able to design and appropriate availability strategy and coordinate a successful recovery after a disaster.

Instructors: Paul S. Randal, Kimberly L. Tripp, Jonathan Kehayias

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.

Quotes From Past Attendees

Listed below are some verbatim quotes from recent attendees of this class:

• “Always a pleasure with SQLskills events. I learn so much with your team. You all are so knowledgeable and passionate about what you do. Great stories and real-life examples during the entire training event.”
  
• “I’m very happy with the information and content on the course.”
• “Very impressive course. Really enjoyed all teachers and was blown away by all of the knowledge.”
• “Gives me ‘ammo’ to go back and insist on a strategy as well as run book(s) from other DBAs on team as well as DBA managers.”
• “I can’t imagine a better use of my company’s training budget. What I learned was worth every penny. I took so many notes my pen caught fire!”
• “Great information and really effective delivery, as always.”
• “Recommend to other who are really interested in SQL Server deep dive topics.”
• “Fantastic sharing of knowledge.”

Curriculum

Module 1: Designing an Availability Strategy

This module describes a methodical approach to availability strategy planning, rather than the more common “let’s get a cluster” or “let’s try to use the incumbent technology” approaches. Topics covered include:

• Requirements gathering
• Non-technical imitations analysis
• Technical limitations analysis
• Technology evaluation
• Disaster recovery planning considerations

Module 2: Hardware and Architectures for HA/DR

It’s important to also consider hardware redundancy and troubleshooting when thinking about high availability, both for individual servers and multi-server architectures. Topics covered include:

• Basic hardware component redundancy for HA/DR
• HA/DR architecture effects on hardware choices
• HA/DR architecture effects on storage choices
• HA/DR effects on workload
• Hardware and storage sizing for HA/DR

Module 3: Consolidation

In this module we cover the pros and cons of each of the popular methods to put more databases on less hardware. Topics covered include:

• Consolidation methods
• Choosing a consolidation strategy
• Hardware and software considerations
• Post-consolidation tools

Module 4: Backup Internals and Strategy

Backups are the cornerstone of any availability and disaster recovery strategy. In this module we cover the internals and usage of the three types of backups along with how to combine them into a valid strategy. Topics covered include:

• Planning a backup strategy
• Backup types
  • Full/differential/log
  • VLDB strategy concerns
  • Filegroup/file backups
  • Filegroup/file differential backups
• Special features for backups
  • COPY_ONLY
  • Parallel striped backup/multi-file backups
  • Mirrored backups
  • Backup integrity
  • Backup compression
• Log shipping

Module 5: Restore Internals and Scenarios

Backups are only taken because you want to be able to perform a restore, for whatever reason, but restoring backups is one of the least practiced DBA skills. This module covers the major RESTORE options you’re likely to need when recovering from a disaster. Topics covered include:

• Phases of restore
• Recovery completion states
• Point-in-time recovery
• Restore types
• Restoring sequence and locations

Module 6: Database Snapshots

Database snapshots provide a very easy way to protect against user error as well as providing a static copy of the database for reporting etc. This module explains how database snapshots work, as well as some of the problems that can arise from their use. Topics covered include:

• How database snapshots work
• Usage patterns and anti-patterns
• Monitoring
• Troubleshooting

Module 7: Database Mirroring

Database mirroring has become immensely popular since its introduction in SQL Server 2005. This module covers configuring database mirroring, failure scenarios, and how to troubleshoot availability and performance. Topics covered include:

• Mirroring configuration
• Failure types and speed
• Failure scenarios
• Client redirection
• Monitoring
• Troubleshooting
• Other use cases for mirroring

Module 8: Failover Clustering

Clusters have a bad reputation, but since Windows 2008, they’ve turned the corner. They’ve gotten easier to deploy and manage, and we show you how to pull it off according to best practices. Topics covered include:

• Clustering concepts
• Planning for failover clustering
• How failover works
• Installation and patching SQL Server
• Troubleshooting cluster Issues

Module 9: Availability Groups

Availability Groups are the flagship HA technology introduced in SQL Server 2012 as a major upgrade of the capabilities of database mirroring. Topics covered include:

• Availability group overview
• Readable secondaries
• Application connection support
• Partial database containment
• Migrating from Database Mirroring
• Quorum model and node votes
• Asymmetric storage considerations
• Security
• Performance

Module 10: Corruption Detection and Recovery

This module addresses one of the most important areas for a DBA to know: how to detect (inevitable) corruption and how to recover from it, both with and without backups. Topics covered include:

• How does corruption occur
• Detecting corruption
• DBCC CHECKDB and related commands
• Interpreting DBCC CHECKDB output
• Recovering from corruptions without using repair
• Using repair
• Advanced techniques
• Real-life horror stories

Module 11: Virtualization

Whether you’re using Hyper-V, VMware, or Xen, virtualization changes a lot about how you deploy and manage SQL Server. We show how things are different, how you need to compensate, and dive into the new HA/DR options that virtualization offers. Topics covered include:

• Why virtualization is popular
• How virtualization works
• Considerations for SQL Server
• HA/DR options
• Monitoring
• Appendix: x86 virtualization types

Module 12: Appendix

This module is not taught. It presents a series of slides that compare the various HA technologies by various aspects.

Questions?

If you have any questions not answered by our F.A.Q., please contact us.