In the previous article of this series on the SQL Server transaction log, we discuss the SQL Server database recovery models, Full, Simple and Bulk-Logged, and the how the recovery model option of the database affects the way the SQL Server Engine works with the transaction logs. In this article, we will discuss the different types of high availability and disaster recovery solutions and the role of the SQL Server transaction log in these technologies.
在本系列的上一篇有关SQL Server事务日志的文章中，我们讨论了SQL Server数据库恢复模型（完整，简单和批量记录），以及数据库的恢复模型选项如何影响SQL Server Engine的工作方式。交易记录。 在本文中，我们将讨论不同类型的高可用性和灾难恢复解决方案以及SQL Server事务日志在这些技术中的作用。
高可用性和灾难恢复概述 (High Availability and Disaster Recovery Overview)
We live now in an IT based world, in which the success and the growth of the business depends mainly on the availability of the critical systems that run it. You will probably not be surprised to hear during Service Level Agreement (SLA) discussions, topics including Recovery Time Objective (RTO) and recovery Point Objective (RPO), and that a second of downtime in financial or tracking systems will not be accepted.
我们现在生活在一个基于IT的世界中，在这个世界中，业务的成功与成长主要取决于运行它的关键系统的可用性。 在服务水平协议（SLA）讨论期间，您可能不会感到惊讶，包括恢复时间目标（RTO）和恢复点目标（RPO）在内的主题，以及财务或跟踪系统中一秒钟的停机时间将不被接受。
The availability of business applications depend also on the availability of the data that these applications process and store. What is the need for an application that is available without being able to reach to the databases that this application uses?
业务应用程序的可用性还取决于这些应用程序处理和存储的数据的可用性。 对于无法访问该应用程序使用的数据库的可用应用程序有什么需求？
Working as a database administrator in an international company or a company that provides financial services, you will be asked to provide a reliable high availability and disaster recovery solution that ensures the minimum possible downtime for the database systems in case or any crash, failure or disaster situation. Microsoft provides us with number of technologies that can be used easily to design a reliable high availability and disaster recovery solution.
在国际公司或提供财务服务的公司中担任数据库管理员时，系统将要求您提供可靠的高可用性和灾难恢复解决方案，以确保在发生任何崩溃，故障或灾难时，数据库系统的停机时间尽可能短情况。 Microsoft为我们提供了许多可轻松用于设计可靠的高可用性和灾难恢复解决方案的技术。
高可用性概念 (High availability concepts)
Before going through the different high availability and disaster recovery technologies, we should understand the difference between the high availability and disaster recovery concepts. High Availability means that the SQL Server instances or databases will be available and reachable, with the least possible downtime, in case of any server crash or failure. The availability of the SQL Server that is provided by the solution is measured by the number of 9’s in the availability percentage that this solution provides, and the total period of annual downtime, as per the Microsoft table below:
在介绍不同的高可用性和灾难恢复技术之前，我们应该了解高可用性和灾难恢复概念之间的区别。 高可用性意味着在任何服务器崩溃或故障的情况下，SQL Server实例或数据库将可用且可访问，并且停机时间最少。 该解决方案提供SQL Server的可用性由该解决方案提供的可用性百分比中的9个数字以及每年停机的总时间来衡量，如下面的Microsoft表所示：
Number of 9’s | Availability Percentage | Total Annual Downtime |
2 | 99% | 3 days, 15 hours |
3 | 99.9% | 8 hours, 45 minutes |
4 | 99.99% | 52 minutes, 34 seconds |
5 | 99.999% | 5 minutes, 15 seconds |
9个 | 可用性百分比 | 年度总停机时间 |
2 | 99％ | 3天15小时 |
3 | 99.9％ | 8小时45分钟 |
4 | 99.99％ | 52分34秒 |
5 | 99.999％ | 5分15秒 |
On the other hand, Disaster Recovery means that, when the primary datacenter experiences a catastrophic event, such as an earthquake, flood or war, the system should be brought online and serve the users in another secondary site.
另一方面， 灾难恢复意味着，当主数据中心遇到灾难性事件（例如地震，洪水或战争）时，该系统应上线并为另一个辅助站点中的用户提供服务。
Let us now discuss the different high availability technologies provided by Microsoft SQL Server and the role of the SQL Server transaction log in these technologies.
现在让我们讨论Microsoft SQL Server提供的不同的高可用性技术，以及这些技术中SQL Server事务日志的作用。
日志传送 (Log Shipping)
The SQL Server Log Shipping is a database level high availability solution, that fits the less mission critical databases with flexible recovery point and recovery time. It consists of one primary database server and one or more standby secondary servers that can be used for reporting purposes.
SQL Server日志传送是数据库级别的高可用性解决方案，它适合具有关键任务的数据库，并具有灵活的恢复点和恢复时间。 它由一台主数据库服务器和一台或多台备用辅助服务器组成，可用于报告目的。
The operation of the Log Shipping feature requires the database to be in Full recovery model as it depends highly on the Transaction Logs. It starts by restoring the primary database to the secondary servers then the secondary database will be synchronized by the cumulative SQL Server transaction log backups that are taken from the primary database. This will be performed automatically using three SQL Server Agent jobs. The first job will take transaction log backup from the primary database, the second job will copy that transaction log backup to the secondary servers and the third job will restore these transaction log backups to the secondary databases, without performing the recovery process on the secondary databases. This means that, the secondary databases will not be brought online. Instead, it will be in restoring or standby mode, waiting for the next SQL Server transaction log backup from the primary server, as shown below:
日志传送功能的操作要求数据库处于完全恢复模式，因为它高度依赖于事务日志 。 首先将主数据库还原到辅助服务器，然后将通过从主数据库获取的累积SQL Server事务日志备份来同步辅助数据库。 这将使用三个SQL Server代理作业自动执行。 第一个作业将从主数据库中获取事务日志备份，第二个作业将将该事务日志备份复制到辅助服务器，第三个作业将这些事务日志备份还原到辅助数据库，而无需在辅助数据库上执行恢复过程。 这意味着辅助数据库将不会联机。 相反，它将处于还原或备用模式，等待从主服务器进行下一次SQL Server事务日志备份，如下所示：
The failover between the primary and the secondary servers in Log Shipping, known as role change, should be performed manually using a T-SQL command. Check What is the SQL Server Log Shipping and How to Create SQL Server Log Shipping for more information.
日志传送中主服务器和辅助服务器之间的故障转移（称为角色更改）应使用T-SQL命令手动执行。 有关更多信息，请检查什么是SQL Server日志传送和如何创建SQL Server日志传送。
事务复制 (Transactional Replication)
SQL Server Transactional Replication is a real time, database level, high availability solution, that consists of one primary server, known as Publisher, that distributes all the database tables, or selected tables known as articles, to one or more secondary servers, known as Subscribers, that can be also used for reporting purposes.
SQL Server事务复制是一种实时的数据库级别的高可用性解决方案，它由一个称为Publisher的主服务器组成，该主服务器将所有数据库表或选定的称为项目的表分发到一个或多个辅助服务器，这些服务器称为订户 ，也可以用于报告目的。
As the name indicates, Transactional Replication depends on a synchronization process with the SQL Server Transaction Logs.
顾名思义，事务复制取决于与SQL Server事务日志的同步过程。
It works with three agents;
它与三个代理一起工作；
- SQL Server Snapshot Agent that prepares the starting snapshot file that contains the database objects to be replicated, you can imagine it as a full backup file SQL Server快照代理 ，它准备了包含要复制的数据库对象的起始快照文件，您可以将其想象为一个完整的备份文件
- Distribution Agent is responsible for copying the initial snapshot and the cumulative logs to the subscribers 分发代理负责将初始快照和累积日志复制到订阅服务器
- The Log Reader Agent is responsible for monitoring the SQL Server transaction log in the publisher database and copy these transactions from the transaction log file of that database to the distribution database, to be copied to the subscribers by the distribution agent later, as shown below:
- 日志读取器代理负责监视发布者数据库中SQL Server事务日志，并将这些事务从该数据库的事务日志文件复制到分发数据库，稍后由分发代理复制到订阅服务器，如下所示：
- Note: Please see the following articles on SQL Replication basics, SQL Server Transactional Replication and SQL Server Replication configuration for more information.
- 注意： 有关更多信息 ， 请参阅以下有关 SQL复制基础知识 ， SQL Server事务复制 和 SQL Server复制配置 的文章。
数据库镜像 (Database Mirroring)
SQL Server Database Mirroring is a database level high availability solution, that can be configured on databases with full recovery model. It consists of minimum two servers; the primary SQL Server, known as Principal server and the secondary server, known as the Mirror server, and optionally a third server, known as Witness server. The Witness server will monitor the connection between these two servers and its availability and perform the automatic failover, or the role change, between these two servers.
SQL Server数据库镜像是数据库级别的高可用性解决方案，可以在具有完整恢复模型的数据库上进行配置。 它至少由两台服务器组成； 主要SQL Server（称为主体服务器）和辅助服务器（称为Mirror服务器），以及可选的第三个服务器（称为Witness服务器）。 见证服务器将监视这两个服务器之间的连接及其可用性，并在这两个服务器之间执行自动故障转移或角色更改。
SQL Server Database Mirroring works in two synchronization mode;
SQL Server数据库镜像在两种同步模式下工作：
- High-safety mode, also known as synchronous mode, in which the transaction will be committed on the principal database after committing it and writing it to the transaction log file on the mirror database, which increases the possibility of transactions latency
- 高安全性模式 ，也称为同步模式，其中在提交事务并将其写入镜像数据库上的事务日志文件之后，将在主体数据库上提交事务 ，这增加了事务等待时间的可能性
- high-performance synchronization mode, also known as Asynchronous mode, the transaction will be committed on the principal database without waiting for it to be committed on the mirror server, decreasing the possibility of transactions latency but increasing the possibility of data loss 高性能同步模式下 （也称为异步模式），事务将在主体数据库上提交，而无需等待它在镜像服务器上提交，从而减少了事务等待时间的可能性，但增加了数据丢失的可能性
Database Mirroring is started by restoring a full backup and a SQL Server Transaction Log backup from the principal database to the mirror server, without bringing the mirror database online. After configuring Database Mirroring, the mirror database will be synchronized by sending the active Transaction Log records to the mirror database and redoing all these operations on the mirror database, as shown below:
通过从主体数据库到镜像服务器还原完整备份和SQL Server事务日志备份来启动数据库镜像，而无需使镜像数据库联机。 配置数据库镜像后，将通过将活动事务日志记录发送到镜像数据库并在镜像数据库上重做所有这些操作来同步镜像数据库，如下所示：
Check the SQL Server mirroring concept, How to configure SQL Server Mirroring and Configuring the SQL Server Mirroring on TDE encrypted database, for more information.
有关详细信息，请检查SQL Server镜像概念 ， 如何配置SQL Server镜像和在TDE加密数据库上配置SQL Server镜像 。
始终在故障转移群集上 (Always on Failover Cluster)
SQL Server Always on Failover Cluster is an instance level high availability solution, that is built over the Windows Server Failover Clustering functionality. It consists of number of servers, known as cluster nodes, that has the same hardware and software components, in order to provide high availability for the failover cluster instance through that redundancy.
SQL Server始终在故障转移群集上是实例级高可用性解决方案，它是基于Windows Server故障转移群集功能构建的。 它由许多具有相同硬件和软件组件的服务器（称为群集节点）组成，以便通过该冗余为故障转移群集实例提供高可用性。
When the SQL Server Failover Cluster is configured and started, the SQL Server services and the resource groups, including the shared storage, the network name and the virtual IPs, can be owned only by one of the cluster nodes at a given time.
配置并启动SQL Server故障转移群集后，SQL Server服务和资源组（包括共享存储，网络名称和虚拟IP）在给定时间只能由群集节点之一拥有。
If any failure, such as operating system, hardware or service failure, occurred on the active node that owns the resource groups and has the SQL Server service online, or simply a planned reboot or upgrade for that active node will be performed, the ownership of the resource group will be completely moved to another cluster node, where the SQL Server instance will be taken offline on the previous active node then brought online on the new node that is owning the resource group, as shown below:
如果在拥有资源组并使SQL Server服务联机的活动节点上发生任何故障（例如操作系统，硬件或服务故障），或者仅执行该活动节点的计划的重新引导或升级，则该节点的所有权资源组将完全移到另一个群集节点，在该群集节点上，SQL Server实例将在先前的活动节点上脱机，然后在拥有该资源组的新节点上联机，如下所示：
You can see that, in the SQL Server Always On Cluster, there is no role for the SQL Server Transaction Log, as it will be configured at the SQL Server instance level, without the need to synchronize the changes between the different nodes. In other words, the SQL Server database will be located and online on one node at a given time, with no replica for that database on the other cluster nodes.
您可以看到，在SQL Server Always On群集中，SQL Server事务日志没有任何作用，因为它将在SQL Server实例级别进行配置，而无需同步不同节点之间的更改。 换句话说，SQL Server数据库将在给定时间在一个节点上定位并联机，而在其他群集节点上没有该数据库的副本。
- Note: See the articles What is SQL Server Failover Clustering and How to Install a Clustered Instance for more information.
- 注意： 有关 更多信息， 请参阅文章 什么是SQL Server故障转移群集 和 如何安装群集实例 。
始终处于可用性组 (Always on Availability Groups)
The SQL Server Always on Availability Group feature, introduced the first time in SQL Server 2012, is a database level high availability solution, that is built over the Windows Server Failover Clustering functionality. It consists of number of one primary server, known as Primary replica, in which the databases will be available to serve read-write connections, and up to eight secondary servers, known as Secondary replicas, that can be used to serve read-only connections for reporting purposes.
SQL Server始终可用性组功能是在SQL Server 2012中首次引入的，它是基于Windows Server故障转移群集功能构建的数据库级高可用性解决方案。 它由一台主要服务器（称为“主要副本”）和数据库（最多八台辅助服务器）组成，其中一台主要服务器（称为“主要副本”）可在其中服务于数据库的读写连接，这些服务器可用于“只读”连接。用于报告目的。
The Availability Group is a set of user databases that failover together between the availability replicas. In other words, a database level issue, such as database corruption or data loss, will not force the availability group to be failed over to another replica, where a database failure will not affect other databases in the same availability group.
可用性组是在可用性副本之间一起进行故障转移的一组用户数据库。 换句话说，数据库级别的问题（例如数据库损坏或数据丢失）将不会强制将可用性组故障转移到另一个副本，在该副本中数据库故障不会影响同一可用性组中的其他数据库。
To make it easier for the database users to connect to the availability group without the need to know which SQL Server instance is hosting the primary replica, an Availability Group Listener can be created. An availability group listener is a virtual network name, that consists of a unique DNS name, one or more virtual IP addresses and a TCP port number, that provides a direct connection to the appropriate replica of the availability group, by routing the read-write workload to the primary replica and route the read-only workload to the secondary replica, if the read-only routing list is configured on that availability group.
为了使数据库用户更容易地连接到可用性组，而无需知道哪个SQL Server实例承载了主副本，可以创建一个可用性组侦听器 。 可用性组侦听器是一个虚拟网络名称，它由一个唯一的DNS名称，一个或多个虚拟IP地址和一个TCP端口号组成，该虚拟网络名称通过路由读写操作提供与可用性组的适当副本的直接连接。如果在该可用性组上配置了只读路由列表，则将工作负载分配给主副本并将只读工作负载路由到辅助副本。
- Note: See the article 注意：有关更多信息，请参阅文章How to Configure Read-only Routing List for an Availability Group for more information.如何为可用性组配置只读路由列表 。
The data synchronization between the primary replica and the secondary replicas occurs at the database level and depends highly on the SQL Server Transaction Log of that database. The primary replica sends the transaction log records from each database participating in the availability group to all secondary replicas. In the other side, the secondary replicas will write these transaction logs in the database transaction log file for caching, then redo these logs in the corresponding secondary database. If the secondary replica is configured for asynchronous-commit mode, the primary replica will not wait for the secondary replica to write incoming transaction log records to disk. Take into consideration that, the server level changes or changes that are not written to the SQL Server transaction log file, such as the logins, linked servers and the SQL Agent jobs, will not be synchronized automatically between the availability group replicas and require you to synchronize it manually in the secondary replicas.
主副本和辅助副本之间的数据同步发生在数据库级别，并且高度依赖于该数据库SQL Server事务日志。 主副本将事务日志记录从参与可用性组的每个数据库发送到所有辅助副本。 另一方面，辅助副本将这些事务日志写入数据库事务日志文件中以进行缓存，然后在相应的辅助数据库中重做这些日志。 如果将辅助副本配置为异步提交模式，则主副本将不等待辅助副本将传入的事务日志记录写入磁盘。 请注意，服务器级别的更改或未写入SQL Server事务日志文件的更改（例如登录名，链接服务器和SQL Agent作业）将不会在可用性组副本之间自动同步，并且要求您执行以下操作：在辅助副本中手动对其进行同步。
- Note:注意： See the articles 有关更多信息，请参阅文章 Always On Availability GroupsAlways On可用性组, ， Always On Availability Groups with Direct Seeding具有直接播种的Always On可用性组, and 和 Synchronizing the SQL Server Instance Objects in Always On Availability Groups同步Always On可用性组中SQL Server实例对象以 for more information. 获取更多信息。
For this point, we should understand the importance of the SQL Server Transaction Log for the different types of the high availability and disaster recovery solutions. In the next article, we will show how to manage and monitor the growth of the SQL Server Transaction Log file. Stay tuned!
对于这一点，我们应该了解SQL Server事务日志对于不同类型的高可用性和灾难恢复解决方案的重要性。 在下一篇文章中，我们将展示如何管理和监视SQL Server事务日志文件的增长。 敬请关注！
目录 (Table of contents)
翻译自: https://www.sqlshack.com/sql-server-transaction-log-and-high-availability-solutions/
1801
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
