Skip to Content
View all events

Professional SQL Server High Availability and Disaster Recovery

Published by Packt Publishing

Advanced content levelAdvanced

Preventing and handling data loss scenarios of various severity

This course introduces several SQL Server high-availability solutions that improve the availability of servers or databases. It will cover some of the best recommended techniques to ensure the high availability of customer database servers. It will show you how to address pain-points and how to build a failover cluster, to avoid even the slightest downtime. By the end, you will be equipped with all the tools and techniques to ensure your SQL server is always on.

To many organizations, the availability of their applications is of the utmost importance. Without their applications, e.g. web shops, ERP, CRM applications, their business would grind to a halt, costing thousands or even millions of dollars. Most applications rely on databases for storing their data and many organizations use Microsoft’s SQL Server database.

The learning outcomes for this course include:

  • Understand downtime and availability factors.
  • Know what high availability technologies are available in SQL Server.
  • Know when to choose which high availability solution.
  • Understand data loss and disaster recovery in SQL Server.
  • Determine backup and restore strategies.
  • Know how to implement backup and restore strategies.

After an introduction that explains the high availability and disaster recovery situations and solutions, this training course will walk you through the following:

  • Setting up a Database Mirroring solution with Log Shipping.
  • Designing a Windows Cluster with an Always On Availability Group.
  • Designing a Windows Cluster with an Always On Failover Cluster.
  • Designing and Implementing Backup and Recovery strategies.

By the end of the course, you will be able to understand the different SQL Server high availability and disaster recovery technologies, how they work, and be able to properly implement the right solution to address recovery objectives and service level agreements.

What you’ll learn and how you can apply it

  • The meaning of high availability and disaster recovery
  • Data loss scenarios of various severity
  • Configuring a Log Shipping Solution
  • Designing a Windows Cluster with an Always On Availability Group
  • Designing a Windows Cluster with an Always On Failover Cluster
  • Designing and implementing backup and recovery strategies

This live event is for you because...

You’re a Database Engineer, Database Administrator and/or IT Professional who wants to learn how to maintain high availability of your SQL Server deployments.

Prerequisites

  • Attendees should have a basic understanding of managing and administering SQL Server databases.

Materials and downloads needed in advance

In the demos/labs, the following will be used:

  • Four laptops that each have a VMware Workstation Player virtual machine (VM).

Laptops/Servers

Laptop/Server 1

  • Configuration: VM with Windows Server 2016 and a >10GB extra hard drive

Laptop/Server2

  • Configuration: VM with Windows Server 2016,
  • SQL Server 2017 installation media and
  • SQL Server 2017 standalone installation

Laptop/Server3

  • Configuration: VM with Windows Server 2016,
  • SQL Server 2017 installation media an
  • SQL Server 2017 standalone installation

Laptop/Server4

  • Configuration: VM with Windows Server 2016 and
  • SQL Server 2017 installation media (not installed)

Windows Server:

Recommended Follow Up:

SQL Server 2017 Administrator's Guide

Course GitHub Repo:

https://github.com/squirreltraining/PacktProfessionalSQLServerHADR

Schedule

The time frames are only estimates and may vary according to how the class is progressing.

DAY 1

Section 1: Introduction to SQL Server High Availability and Disaster Recovery (20 min)

  • Course Introduction
  • How to Design a High Availability Solution

Section 2: Log Shipping(15 min)

  • How to Architect Log Shipping

Demo/Lab: Configuring and Monitoring Log Shipping (35 min)

  • Configuring Log Shipping from a primary server to a single secondary server
  • Monitoring Log Shipping

Break: 10 min

Section 3: Always On Availability Group(20 min)

  • How to Architect an Always On Availability Group

Demo/Lab: Implementing an Availability Group(50 min)

  • Configuring Windows Clustering
  • Creating a Windows Failover Cluster
  • Creating an Availability Group
  • Configuring Read-Only Routing
  • Monitoring Availability Groups
  • Performing a manual failover

Break: 10 min

Section 4: Implement Failover Clustering (20 min)

  • How to Architect failover clustering

Demo/Lab: Failover Cluster(60 min)

  • Configuring failover clustering
  • Configuring Quorum configuration
  • How to manage shared disks
  • Configuring cluster shared volumes

DAY 2

Section 5: Design a Disaster Recovery Solution (15 min)

  • Day 2 Introduction
  • How to design a Disaster Recovery Solution

Section 6: Database Backup (15 min)

  • How to design a Database Backup Strategy

Demo/Lab: Performing Database Backups(50 min)

  • Creating Database Backup Media Set with Full, Differential and Incremental Backup
  • Performing Database Snapshots
  • How to manage Transaction Log Backups
  • Configuring Backup Automation

Break 10 min

Section 7: Database Restore (20 min)

  • How to design a Database Restore Strategy
  • How to develop a plan to automate and test restores

Demo/Lab: Performing Database Restores (50 min)

  • Restoring a database backup
  • Reverting a database snapshot
  • Performing piecemeal restores, page recovery, point-in-time recovery, filegroup restore

Break 10 min

Section 8: Database Integrity (40 min)

  • Implementing database consistency checks
  • Identifying database corruption
  • Recovering from database corruption

Wrap-up and remaining Q&A (30 min)

Your Instructor

  • Alex Toth

    Alex Toth has over 15 years’ experience as a trainer/consultant with a strong focus on databases. He has deep knowledge of designing, implementing, developing, administering, and querying databases and data warehouses (SQL Server, SSIS, SSAS, Oracle, MySQL), and creating business reports (SSRS). Alex designs and delivers training programs targeted at business professionals, IT professionals, database administrators and software end users in many business areas, including finance, government, healthcare, science and education. He explains complex matter in a clear and light-hearted way to both technical and non-technical people and stimulates interactivity in both theory sessions and exercises.

    Alex lives in Ireland with his wife and son. In his spare time he enjoys screen and stage acting and playing the piano.

    linkedinXlinksearch

Skills covered

  • Disaster Recovery
  • SQL