Professional SQL Server High Availability and Disaster Recovery电子书

Preface

About the Book

About the Authors

Objectives

Audience

Approach

Hardware Requirements

Software Requirements

Conventions

Installation

Installing the Code Bundle

Additional Resources

Lesson 1

Getting Started with SQL Server HA and DR

Introduction

What is High Availability and Disaster Recovery?

High Availability

Disaster Recovery

HA and DR Terminologies

Availability

Recovery Time Objective

Recovery Point Objective

SQL Server HA and DR Solutions

Windows Server Failover Cluster Installation

Log Shipping

AlwaysOn Availability Groups

Replication

Hybrid Scenarios

Introduction to SQL Server Replication

Replication Agents

Types of Replication

Transactional Replication

Merge Replication

Snapshot Replication

Configuring Snapshot Replication Using SQL Server Management Studio

Exercise 1: Creating a Publication

Exercise 2: Exploring the Distribution Database

Database Snapshot

Snapshot Agent Job

Replication Snapshot (snapshot.exe)

Modifying an Existing Publication

Exercise 3: Creating a Subscription

Exercise 4: Exploring the Distribution Database (Metadata)

Distribution Agent Job

Exercise 5: Running the Job

Exercise 6: Distribution Agent Process (distrib.exe)

Optimizing Snapshot Replication

Snapshot Replication Best Practices

Modifying Agent Parameters

Activity 1: Troubleshooting Snapshot Replication

Summary

Lesson 2

Transactional Replication

Introduction

Understanding Transactional Replication

Configuring Transactional Replication

Exercise 7: Creating the Publication

Exercise 8: Creating the Subscription

Transactional Replication Agents

Exercise 9: Modifying the Existing Publication

Stopping Transactional Replication

Exercise 10: Removing Transactional Replication

Azure SQL Database as a Subscriber in Transaction Replication

Exercise 11: Configuring and Verifying Azure SQL Database as a Subscriber for an On-Premises Transactional Replication

Understanding Peer-To-Peer Transactional Replication

Configuring Peer-To-Peer Transactional Replication

Exercise 12: Creating the Publication, Adding Articles, Adding the Subscription, and Pushing the Subscription Agent to the Publication on Server 1

Exercise 13: Taking the Backup of the Publication Database on Server 1

Exercise 14: Restoring the Database on the Subscriber Server

Exercise 15: Creating the Publication, Adding Articles, Adding the Subscription, and Pushing the Subscription Agent to the Publication on Server 2

Exercise 16: Verifying the Replication

Modifying an Existing Publication

Exercise 17: Removing an Article

Exercise 18: Adding an Article

Activity 2: Configuring Transactional Replication

Summary

Lesson 3

Monitoring Transactional Replication

Introduction

The Replication Monitor

Exercise 19: Setting Up the Replication Monitor

Monitoring Replication with the Replication Monitor

Exercise 20: Configuring Replication Alerts

Activity 3: Configuring an Agent Failure Error

Real-World Transactional Replication Problems and Solutions

Configuration Issues

Exercise 21: Problem 1 – Unable to Connect to Distributor

Exercise 22: Problem 2 – Inactive Subscriptions

Exercise 23: Problem 3 - Missing Replication Stored Procedures

Data Issues

Exercise 24: Problem 4 – Row Not Found at the Subscriber

Performance Issues

Exercise 25: Problem 5 – Log Reader Agent Takes Time to Scan the Transaction Log

Conflicts in Peer-to-Peer Transactional Replication

Exercise 26: Problem 6 – Conflicts in P2P Transactional Replication

Activity 4: Troubleshooting Transactional Replication

Summary

Lesson 4

AlwaysOn Availability Groups

Introduction

AlwaysOn Availability Group Concepts and Terminology

Availability Groups Concepts and Components

Data Synchronization

AlwaysOn Availability Groups

Prerequisites

Creating Hyper-V VMs

Exercise 27: Enabling Hyper-V

Exercise 28: Downloading Windows Server 2012 R2 Evaluation Edition and Creating the Base VM

Exercise 29: Installing the Windows Server 2012 R2 Evaluation Edition

Exercise 30: Provisioning VMs Using the Base VM

Active Directory Domain Controller

Exercise 31: Configuring Active Directory Domain Controller

Installing the SQL Server Developer Edition

Exercise 32: Downloading and Installing the SQL Server 2016 Developer Edition

Exercise 33: Restoring Sample Databases

Windows Server Failover Cluster

Exercise 34: Configuring Windows Server Failover Cluster

Configuring AlwaysOn Availability Groups

Exercise 35: Enabling the AlwaysOn AG Feature

Exercise 36: Creating the Availability Group

Exercise 37: Reviewing an AlwaysOn AG Configuration

AlwaysOn Availability Groups on Microsoft Azure

Exercise 38: Configuring an AlwaysOn Availability Group on Microsoft Azure

Exercise 39: Reviewing Our Availability Group Configuration

Exercise 40: Deleting the AlwaysOn Configuration

Summary

Lesson 5

Managing AlwaysOn Availability Groups

Introduction

AlwaysOn AG Failover

Automatic Failover

Exercise 41: Automatic Failover

Manual Failover

Exercise 42: Manual Failover without Data Loss

Exercise 43: Manual Failover with Data Loss

Managing AlwaysOn Availability Groups

Removing a Database from an Existing Availability Group

Exercise 44: Removing a Database from the Secondary Replica

Exercise 45: Removing a Database from the Primary Replica

Exercise 46: Adding a Database to an Availability Group

Exercise 47: Removing a Replica from an Availability Group

Exercise 48: Adding a Replica to an Availability Group

Exercise 49: Changing the Availability Mode of a Replica

Exercise 50: Changing the Failover Mode of a Replica

Exercise 51: Creating Multiple Listeners for the Same Availability Group

Exercise 52: Configuring Backups on the Secondary Replica

Exercise 53: Configuring Readable Secondaries

Exercise 54: Read-Only Routing

Exercise 55: Configuring the Flexible Failover Policy

Availability Database Synchronization States

Monitoring AlwaysOn Availability Groups

The AlwaysOn AG Dashboard

Exercise 56: Collecting Latency Data Using the AlwaysOn AG Dashboard

SQL Server Agent Job

Exercise 57: Configuring Reports

Monitoring AlwaysOn AG Using T-SQL

Monitoring an AlwaysOn AG Using PowerShell

Troubleshooting AlwaysOn Availability Groups

Exercise 58: Problem 1 - DDL Queries Block the Redo Thread on the Secondary Replica

Exercise 59: Problem 2 - Transaction Log is Growing

Exercise 60: Problem 3 - Replica Database in the Resolving State

Activity 5: Manual Failover

Activity 6: Adding a New Database to an Existing Availability Group

Summary

Lesson 6

Configuring and Managing Log Shipping

Introduction

Log Shipping Use Cases

Configuring Log Shipping

Exercise 61: Configuring Log Shipping

Managing and Monitoring Log Shipping

Exercise 62: Adding a New Secondary Instance to an Existing Log Shipping Configuration

Exercise 63: Removing a Secondary Instance from an Existing Log Shipping Configuration

Exercise 64: Adding a Database to a Log Shipping Configuration

Exercise 65: Removing a Log Shipped Database

Monitoring Log Shipping

Exercise 66: Configuring Email Alerts for the Log Shipping SQL Agent Jobs

Exercise 67: Using T-SQL to Set Up Custom Monitoring

Exercise 68: Changing the Database State at the Secondary

Exercise 69: Performing a Failover from the Primary Instance to the Secondary Instance

Troubleshooting Common Log Shipping Issues

Exercise 70: Problem 1 – Middle of a Restore Error

Exercise 71: Problem 2 – Directory Lookup for File Failed

Comparing AlwaysOn, Replication, and Log Shipping

Activity 7: Adding a New Data File to a Log Shipped Database

Activity 8: Troubleshooting a Problem – Could Not Find a Log Backup File that Could be Applied to Secondary Database 'Sales'

Summary

Appendix

Lesson 1: Getting Started with SQL Server HA and DR

Activity 1: Troubleshooting Snapshot Replication

Lesson 2: Transactional Replication

Activity 2: Configuring Transactional Replication

Lesson 3: Monitoring Transactional Replication

Activity 3: Configuring an Agent Failure Error

Activity 4: Troubleshooting Transactional Replication

Lesson 5: Managing AlwaysOn Availability Groups

Activity 5: Manual Failover

Activity 6: Adding a New Database to an Existing Availability Group

Lesson 6: Configuring and Managing Log Shipping

Activity 7: Adding a New Data File to a Log Shipped Database

Activity 8: Troubleshooting a Problem – Could Not Find a Log Backup File that Could be Applied to Secondary Database 'Sales'