什么是SQL Server灾难恢复？

A SQL Server disaster recovery plan (DRP) is a process to have SQL Server up and running, and to overcome data loss after a disaster. A disaster recovery plan must be planned and documented in order to prevent catastrophic data loss and incidents

SQL Server灾难恢复计划（DRP）是启动和运行SQL Server并克服灾难后数据丢失的过程。 必须计划并记录灾难恢复计划，以防止灾难性数据丢失和事件

High-Availability is the term that shows the percentage of time a system needs to be available. In some environments, it goes as high as 99.999% or just 5.26 minutes downtime per year

高可用性是表示系统需要可用时间的百分比。 在某些环境下，每年停机时间高达99.999％或仅5.26分钟

A good SQL Server disaster recovery plan must take into account numerous factors: sensitivity of data, data loss tolerance, required availability, etc. The plan can be based on few a solutions:

一个好SQL Server灾难恢复计划必须考虑许多因素：数据的敏感性，数据丢失的容忍度，所需的可用性等。该计划可以基于一些解决方案：

• Failover clustering
  故障转移群集
• Database mirroring
  数据库镜像
• Replication
  复写
• Log shipping
  日志传送
• Backup and restore
  备份还原

Each solution has its own advantages and cost of implementing. Based on the needs, a SQL Server disaster recovery plan should include on one or more available solutions

每个解决方案都有其自身的优势和实施成本。 根据需要，SQL Server灾难恢复计划应包括一个或多个可用解决方案

Failover clustering is a concept where a SQL Server instance is installed on the shared storage. It provides the infrastructure that supports high-availability and disaster recovery scenarios of hosted server applications. If a cluster node fails, the services that were hosted on that node can be automatically or manually transferred to another available node in a process known as failover. There is a short period of downtime while SQL Server is failing over

故障转移群集是在共享存储上安装SQL Server实例的概念。 它提供了支持托管服务器应用程序的高可用性和灾难恢复方案的基础结构。 如果群集节点发生故障，则可以在称为故障转移的过程中自动或手动将托管在该节点上的服务转移到另一个可用节点。 SQL Server故障转移时会有短暂的停机时间

Database mirroring is a solution for increasing availability of a SQL Server database. It maintains two exact copies of a single database. These copies must be on different SQL Server instances. Two databases form a relationship known as a database mirroring session. One instance acts as the principal server, while the other is in the standby mode and acts as the mirror server. Two SQL Server instances that act in mirroring environment are known as partners, the principal server is sending the active portion of a transaction log to the mirror server where all transactions are redone

数据库镜像是用于提高SQL Server数据库可用性的解决方案。 它维护单个数据库的两个精确副本。 这些副本必须位于不同SQL Server实例上。 两个数据库形成一种关系，称为数据库镜像会话。 一个实例充当主体服务器，而另一个实例处于待机模式并充当镜像服务器。 在镜像环境中起作用的两个SQL Server实例称为合作伙伴，主体服务器正在将事务日志的活动部分发送到镜像服务器，在该服务器上重做所有事务

There can be two types of mirror servers: hot and warm. A hot mirror server has synchronized sessions with quick failover time without data loss. A warm mirror server doesn’t have synchronized sessions and there is a possibility of data loss

镜像服务器可以有两种：热服务器和热服务器。 热镜像服务器具有同步会话，具有快速的故障转移时间，而不会丢失数据。 热镜像服务器没有同步的会话，并且可能会丢失数据

This solution will be removed in future versions of SQL Server

在将来SQL Server版本中将删除此解决方案

Replication can be used as a technology for coping and distributing data from one SQL Server database to another. Consistency is achieved by synchronizing. Replication of a SQL Server database can result in benefits like: load balancing, redundancy, and offline processing. Load balancing allows spreading data to a number of SQL Servers and distributing the query load among those SQL Servers. A replication consists of two components:

复制可用作一种将数据从一个SQL Server数据库分配到另一个数据库的技术。 通过同步来实现一致性。 复制SQL Server数据库可以带来以下好处：负载平衡，冗余和脱机处理。 负载平衡允许将数据传播到许多SQL Server，并在这些SQL Server之间分配查询负载。 复制包含两个组件：

• Publishers – databases that provide data. Any replication may have one or more publishers发布者 –提供数据的数据库。 任何复制都可以有一个或多个发布者
• Subscribers – databases that receive data from publishers via replication. Data in subscribers is updated whenever data the publisher is modified
• 订阅服务器 –通过复制从发布服务器接收数据的数据库。 只要修改发布者的数据，订阅者中的数据就会更新

SQL Server supports three types of replication:

SQL Server支持三种复制类型：

• Merge replication: publisher and subscriber independently make changes to the SQL Server database. The merge agent monitors the changes on the publisher and subscriber, if needed it modifies the databases. In case of a conflict, predefined algorithm determinates the appropriate data合并复制 ：发布者和订阅者独立地对SQL Server数据库进行更改。 合并代理监视发布者和订阅者上的更改，如果需要，它将修改数据库。 如果发生冲突，预定义算法将确定适当的数据
• Snapshot replication: the publisher makes a snapshot of the entire database and makes it available for all subscribers快照复制 ：发布者制作整个数据库的快照，并使其对所有订阅者可用

• Transactional replication: uses replication agents which monitor changes on the publisher and transmit these changes to the subscribers

  事务复制 ：使用复制代理来监视发布者上的更改并将这些更改传输给订阅者

  

• Log shipping is based on automated sending of transaction log backups from a primary SQL Server instance to one or more secondary SQL Server instances. The primary SQL Server instance is a production server, while the secondary SQL Server instance is a warm standby copy. There can be a third SQL Server instance which acts as a monitoring server. The log shipping process consists of three main operations: creating a transaction log backup on the primary SQL Server, copying the transaction log backup to one or more secondary servers, and restoring the transaction log backup on the secondary server

  日志传送基于将事务日志备份从一个主SQL Server实例自动发送到一个或多个辅助SQL Server实例的基础。 主SQL Server实例是生产服务器，而辅助SQL Server实例是热备份副本。 可以有第三个SQL Server实例充当监视服务器。 日志传送过程包含三个主要操作：在主SQL Server上创建事务日志备份，将事务日志备份复制到一个或多个辅助服务器，以及在辅助服务器上还原事务日志备份

  

  The Backup and restore technique should be used as basic option for assurance. There are two major concepts involved: backing up SQL Server data and restoring SQL Server data. Backed up data is moved to a neutral off-site location and restore is tested to assure data integrity. There are different types of backups available in SQL Server: a full backup, differential backup, transaction log backup, and partial backup. The backup strategy defines the backup type and frequency, how backups will be tested, and where and how backup media will be stored. The restore strategy defines who is responsible for performing restores and how restores should be performed to meet availability and data loss goals

  备份和还原技术应用作保证的基本选项。 涉及两个主要概念：备份SQL Server数据和还原SQL Server数据。 备份的数据将移至中性的异地位置，并对还原进行了测试，以确保数据的完整性。 SQL Server中有不同类型的备份：完全备份，差异备份，事务日志备份和部分备份。 备份策略定义了备份类型和频率，如何测试备份以及在何处以及如何存储备份媒体。 还原策略定义了谁负责执行还原以及应如何执行还原以满足可用性和数据丢失目标

  

翻译自: https://www.sqlshack.com/sql-server-disaster-recovery/