Professional SQL Server High Availability and Disaster Recovery

Professional SQL Server High Availability and Disaster Recovery

by Ahmad Osama
Released January 2019
Publisher(s): Packt Publishing
ISBN: 9781789802597

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Leverage powerful features of the SQL Server and watch your infrastructure transform into a high-performing, reliable network of systems.

Key Features

  • Explore more than 20 real-world use cases to understand SQL Server features
  • Get to grips with the SQL Server Always On technology
  • Learn how to choose HA and DR topologies for your system

Book Description

Professional SQL Server High Availability and Disaster Recovery explains the high availability and disaster recovery technologies available in SQL Server: Replication, AlwaysOn, and Log Shipping. You'll learn what they are, how to monitor them, and how to troubleshoot any related problems. You will be introduced to the availability groups of AlwaysOn and learn how to configure them to extend your database mirroring. Through this book, you will be able to explore the technical implementations of high availability and disaster recovery technologies that you can use when you create a highly available infrastructure, including hybrid topologies. Note that this course does not cover SQL Server Failover Cluster Installation with shared storage.

By the end of the book, you'll be equipped with all that you need to know to develop robust and high performance infrastructure.

What you will learn

  • Configure and troubleshoot Replication, AlwaysOn, and Log Shipping
  • Study the best practices to implement HA and DR solutions
  • Design HA and DR topologies for the SQL Server and study how to choose a topology for your environment
  • Use T-SQL to configure replication, AlwaysOn, and log shipping
  • Migrate from On-Premise SQL Server to Azure SQL Database
  • Manage and maintain AlwaysOn availability groups for extended database mirroring

Who this book is for

Professional SQL Server High Availability and Disaster Recovery is for you if you are a database administrator or database developer who wants to improve the performance of your production environment. Prior experience of working with SQL Server will help you get the most out of this book.

Table of contents

  1. Preface
    1. About the Book
      1. About the Authors
      2. Objectives
      3. Audience
      4. Approach
      5. Hardware Requirements
      6. Software Requirements
      7. Conventions
      8. Installation
      9. Installing the Code Bundle
      10. Additional Resources
  2. Lesson 1
  3. Getting Started with SQL Server HA and DR
    1. Introduction
    2. What is High Availability and Disaster Recovery?
      1. High Availability
      2. Disaster Recovery
    3. HA and DR Terminologies
      1. Availability
      2. Recovery Time Objective
      3. Recovery Point Objective
    4. SQL Server HA and DR Solutions
      1. Windows Server Failover Cluster Installation
      2. Log Shipping
      3. AlwaysOn Availability Groups
      4. Replication
      5. Hybrid Scenarios
    5. Introduction to SQL Server Replication
      1. Replication Agents
    6. Types of Replication
      1. Transactional Replication
      2. Merge Replication
      3. Snapshot Replication
    7. Configuring Snapshot Replication Using SQL Server Management Studio
      1. Exercise 1: Creating a Publication
      2. Exercise 2: Exploring the Distribution Database
      3. Database Snapshot
      4. Snapshot Agent Job
      5. Replication Snapshot (snapshot.exe)
      6. Modifying an Existing Publication
      7. Exercise 3: Creating a Subscription
      8. Exercise 4: Exploring the Distribution Database (Metadata)
      9. Distribution Agent Job
      10. Exercise 5: Running the Job
      11. Exercise 6: Distribution Agent Process (distrib.exe)
    8. Optimizing Snapshot Replication
      1. Snapshot Replication Best Practices
      2. Modifying Agent Parameters
      3. Activity 1: Troubleshooting Snapshot Replication
    9. Summary
  4. Lesson 2
  5. Transactional Replication
    1. Introduction
    2. Understanding Transactional Replication
    3. Configuring Transactional Replication
      1. Exercise 7: Creating the Publication
      2. Exercise 8: Creating the Subscription
      3. Transactional Replication Agents
      4. Exercise 9: Modifying the Existing Publication
      5. Stopping Transactional Replication
      6. Exercise 10: Removing Transactional Replication
    4. Azure SQL Database as a Subscriber in Transaction Replication
      1. Exercise 11: Configuring and Verifying Azure SQL Database as a Subscriber for an On-Premises Transactional Replication
    5. Understanding Peer-To-Peer Transactional Replication
    6. Configuring Peer-To-Peer Transactional Replication
      1. Exercise 12: Creating the Publication, Adding Articles, Adding the Subscription, and Pushing the Subscription Agent to the Publication on Server 1
      2. Exercise 13: Taking the Backup of the Publication Database on Server 1
      3. Exercise 14: Restoring the Database on the Subscriber Server
      4. Exercise 15: Creating the Publication, Adding Articles, Adding the Subscription, and Pushing the Subscription Agent to the Publication on Server 2
      5. Exercise 16: Verifying the Replication
    7. Modifying an Existing Publication
      1. Exercise 17: Removing an Article
      2. Exercise 18: Adding an Article
      3. Activity 2: Configuring Transactional Replication
    8. Summary
  6. Lesson 3
  7. Monitoring Transactional Replication
    1. Introduction
    2. The Replication Monitor
      1. Exercise 19: Setting Up the Replication Monitor
      2. Monitoring Replication with the Replication Monitor
      3. Exercise 20: Configuring Replication Alerts
      4. Activity 3: Configuring an Agent Failure Error
    3. Real-World Transactional Replication Problems and Solutions
      1. Configuration Issues
      2. Exercise 21: Problem 1 – Unable to Connect to Distributor
      3. Exercise 22: Problem 2 – Inactive Subscriptions
      4. Exercise 23: Problem 3 - Missing Replication Stored Procedures
      5. Data Issues
      6. Exercise 24: Problem 4 – Row Not Found at the Subscriber
      7. Performance Issues
      8. Exercise 25: Problem 5 – Log Reader Agent Takes Time to Scan the Transaction Log
    4. Conflicts in Peer-to-Peer Transactional Replication
      1. Exercise 26: Problem 6 – Conflicts in P2P Transactional Replication
      2. Activity 4: Troubleshooting Transactional Replication
    5. Summary
  8. Lesson 4
  9. AlwaysOn Availability Groups
    1. Introduction
    2. AlwaysOn Availability Group Concepts and Terminology
      1. Availability Groups Concepts and Components
      2. Data Synchronization
    3. AlwaysOn Availability Groups
      1. Prerequisites
    4. Creating Hyper-V VMs
      1. Exercise 27: Enabling Hyper-V
      2. Exercise 28: Downloading Windows Server 2012 R2 Evaluation Edition and Creating the Base VM
      3. Exercise 29: Installing the Windows Server 2012 R2 Evaluation Edition
      4. Exercise 30: Provisioning VMs Using the Base VM
    5. Active Directory Domain Controller
      1. Exercise 31: Configuring Active Directory Domain Controller
    6. Installing the SQL Server Developer Edition
      1. Exercise 32: Downloading and Installing the SQL Server 2016 Developer Edition
      2. Exercise 33: Restoring Sample Databases
    7. Windows Server Failover Cluster
      1. Exercise 34: Configuring Windows Server Failover Cluster
    8. Configuring AlwaysOn Availability Groups
      1. Exercise 35: Enabling the AlwaysOn AG Feature
      2. Exercise 36: Creating the Availability Group
      3. Exercise 37: Reviewing an AlwaysOn AG Configuration
    9. AlwaysOn Availability Groups on Microsoft Azure
      1. Exercise 38: Configuring an AlwaysOn Availability Group on Microsoft Azure
      2. Exercise 39: Reviewing Our Availability Group Configuration
      3. Exercise 40: Deleting the AlwaysOn Configuration
    10. Summary
  10. Lesson 5
  11. Managing AlwaysOn Availability Groups
    1. Introduction
    2. AlwaysOn AG Failover
      1. Automatic Failover
      2. Exercise 41: Automatic Failover
      3. Manual Failover
      4. Exercise 42: Manual Failover without Data Loss
      5. Exercise 43: Manual Failover with Data Loss
    3. Managing AlwaysOn Availability Groups
      1. Removing a Database from an Existing Availability Group
      2. Exercise 44: Removing a Database from the Secondary Replica
      3. Exercise 45: Removing a Database from the Primary Replica
      4. Exercise 46: Adding a Database to an Availability Group
      5. Exercise 47: Removing a Replica from an Availability Group
      6. Exercise 48: Adding a Replica to an Availability Group
      7. Exercise 49: Changing the Availability Mode of a Replica
      8. Exercise 50: Changing the Failover Mode of a Replica
      9. Exercise 51: Creating Multiple Listeners for the Same Availability Group
      10. Exercise 52: Configuring Backups on the Secondary Replica
      11. Exercise 53: Configuring Readable Secondaries
      12. Exercise 54: Read-Only Routing
      13. Exercise 55: Configuring the Flexible Failover Policy
      14. Availability Database Synchronization States
    4. Monitoring AlwaysOn Availability Groups
      1. The AlwaysOn AG Dashboard
      2. Exercise 56: Collecting Latency Data Using the AlwaysOn AG Dashboard
      3. SQL Server Agent Job
      4. Exercise 57: Configuring Reports
      5. Monitoring AlwaysOn AG Using T-SQL
      6. Monitoring an AlwaysOn AG Using PowerShell
    5. Troubleshooting AlwaysOn Availability Groups
      1. Exercise 58: Problem 1 - DDL Queries Block the Redo Thread on the Secondary Replica
      2. Exercise 59: Problem 2 - Transaction Log is Growing
      3. Exercise 60: Problem 3 - Replica Database in the Resolving State
      4. Activity 5: Manual Failover
      5. Activity 6: Adding a New Database to an Existing Availability Group
    6. Summary
  12. Lesson 6
  13. Configuring and Managing Log Shipping
    1. Introduction
      1. Log Shipping Use Cases
    2. Configuring Log Shipping
      1. Exercise 61: Configuring Log Shipping
    3. Managing and Monitoring Log Shipping
      1. Exercise 62: Adding a New Secondary Instance to an Existing Log Shipping Configuration
      2. Exercise 63: Removing a Secondary Instance from an Existing Log Shipping Configuration
      3. Exercise 64: Adding a Database to a Log Shipping Configuration
      4. Exercise 65: Removing a Log Shipped Database
      5. Monitoring Log Shipping
      6. Exercise 66: Configuring Email Alerts for the Log Shipping SQL Agent Jobs
      7. Exercise 67: Using T-SQL to Set Up Custom Monitoring
      8. Exercise 68: Changing the Database State at the Secondary
      9. Exercise 69: Performing a Failover from the Primary Instance to the Secondary Instance
    4. Troubleshooting Common Log Shipping Issues
      1. Exercise 70: Problem 1 – Middle of a Restore Error
      2. Exercise 71: Problem 2 – Directory Lookup for File Failed
    5. Comparing AlwaysOn, Replication, and Log Shipping
      1. Activity 7: Adding a New Data File to a Log Shipped Database
      2. Activity 8: Troubleshooting a Problem – Could Not Find a Log Backup File that Could be Applied to Secondary Database 'Sales'
    6. Summary
  14. Appendix
    1. Lesson 1: Getting Started with SQL Server HA and DR
      1. Activity 1: Troubleshooting Snapshot Replication
    2. Lesson 2: Transactional Replication
      1. Activity 2: Configuring Transactional Replication
    3. Lesson 3: Monitoring Transactional Replication
      1. Activity 3: Configuring an Agent Failure Error
      2. Activity 4: Troubleshooting Transactional Replication
    4. Lesson 5: Managing AlwaysOn Availability Groups
      1. Activity 5: Manual Failover
      2. Activity 6: Adding a New Database to an Existing Availability Group
    5. Lesson 6: Configuring and Managing Log Shipping
      1. Activity 7: Adding a New Data File to a Log Shipped Database
      2. Activity 8: Troubleshooting a Problem – Could Not Find a Log Backup File that Could be Applied to Secondary Database 'Sales'

Product information

  • Title: Professional SQL Server High Availability and Disaster Recovery
  • Author(s): Ahmad Osama
  • Release date: January 2019
  • Publisher(s): Packt Publishing
  • ISBN: 9781789802597