This article describes the data synchronization process on SQL Server Always On Availability Groups in both Synchronous, and Asynchronous data commit mode.
本文介绍了在同步和异步数据提交模式下,SQL Server Always On可用性组上的数据同步过程。
High data availability is an important aspect of every DBA’s life. We need to minimize and mitigate the issues related to database unavailability.
高数据可用性是每个DBA生命的重要方面。 我们需要最小化和减轻与数据库不可用有关的问题。
In the production environment, we want to achieve RPO (Maximum allowed downtime) and RTO (Maximum acceptable data loss) goals of a production database service. Data should be available to business round the clock to avoid any business loss.
在生产环境中,我们希望实现生产数据库服务的RPO (最大允许停机时间)和RTO (最大可接受数据丢失)目标。 数据应全天候提供,以免造成业务损失。
In the following table, we can get a glimpse of the maximum allowed downtime based on availability percentage. We get a very strict maximum of downtime per year as we move towards a strict availability percentage.
在下表中,我们可以根据可用性百分比大致了解允许的最大停机时间。 随着我们朝着严格的可用性百分比迈进,我们每年都会获得非常严格的最大停机时间。
Availability percentage |
Maximum downtime per year |
99% |
3.65 Days |
99.9% |
8.77 Hours |
99.99% |
52.60 Minutes |
99.999% |
5.26 Minutes |
99.9999% |
31.56 seconds |
99.99999% |
3.16 seconds |
99.999999% |
315.58 Milliseconds |
可用性百分比 |
每年的最大停机时间 |
99% |
3.65天 |
99.9% |
8.77小时 |
99.99% |
52.60分钟 |
99.999% |
5.26分钟 |
99.9999% |
31.56秒 |
99.99999% |
3.16秒 |
99.999999% |
315.58毫秒 |
In SQL Server, we can achieve high availability using SQL Server Always On Availability Groups feature. Let’s get a quick overview of SQL Server Always on Availability Groups before we go in deeper.
在SQL Server中,我们可以使用SQL Server Always On可用性组功能实现高可用性。 在深入探讨之前,让我们快速了解一下SQL Server Always On可用性组。
In the following screenshot, we have three SQL instances participating in SQL Server Always On.
在下面的屏幕快照中,我们有三个SQL实例参与SQL Server Always On。
We can have a Primary instance and multiple secondary instances (depending upon SQL Version and Edition).
我们可以有一个主实例和多个辅助实例(取决于SQL版本和版本)。
Few important terms of SQL Server Always on Availability Groups are as follows.
SQL Server Always on可用性组的几个重要术语如下。
- Availability group: It is a logical group of user databases that should failover together on the secondary replica