SQL Server AlwaysOn讲解

SQL Server AlwaysOn讲解

一、概念

可用性组支持的复制环境适用于一组离散用户数据库，称为“可用性数据库” 。 可以创建可用性组以实现高可用性 (HA) 或读取缩放。 HA 可用性组是一组共同实现故障转移的数据库。 读取缩放可用性组是一组复制到其他 SQL Server 实例以实现只读工作负荷的数据库。 一个可用性组支持一组主数据库以及一至八组对应的辅助数据库。 辅助数据库 不是 备份。 应继续定期备份您的数据库及其事务日志。

Alwayson相对于数据库镜像最大的优势就是可读副本，带来可读副本的同时还添加了一个新的功能就是配置只读路由实现读写分离。

AlwaysOn技术集中了故障转移群集、数据库镜像和日志传送三者的优点，但又不相同。故障转移群集的单位是SQL实例，数据库镜像和日志传送的单位是单个用户数据库，而AlwaysOn支持的单位是可用性组，每个组中可以包括一个或者是多个用户数据库。也就是说，一旦发生切换，则可用性组中的所有数据组会作为一个整体进行切换。

AlwaysOn底层依然采用Windows故障转移群集的机制进行监测和转移，因此也需要先建立Windows Cluster，只不过可用性组中的数据库不一定非要再存放在共享存储上了。可以是存储在本地磁盘上。

各副本推荐使用单机模式的SQL Server，那么数据库副本就存放在该运行该实例节点的本地磁盘上；如果可用性副本是个群集实例，那么数据库副本就存放在共享磁盘上。

可用性组从Windows群集角度来看，就是一个SQL级别的群集资源，其中的所有数据库作为一个整体在节点间进行故障转移，当然这不包括系统数据库，系统数据库是不能加入高可用性组中的。

下图显示的是一个包含一个主要副本和四个次要副本的可用性组。 支持最多八个次要副本，包括一个主要副本和四个同步提交次要副本

1.1、限制

因为需要借助Windos群集实现监控和转移，所以AlwaysOn会受到一些限制：

一个可用性组中的所有可用性副本必须运行在单一的Windows群集上，跨不同Windows群集的SQL Server实例不能配置成一个AlwaysOn可用性组。

一个可用性组中的所有可用性副本必须运行在Windows群集的不同节点上。运行在同一个节点上的两个不同实例不能用作同一个可用性组的副本。

一个数据库只能属于一个可用性组。

AlwaysOn最多可以支持五个副本（同步提交模式），但只有一个可用性副本上运行的数据库是处于可读写状态。这个可读写的数据库被称为主数据库（PrimaryDatabase），同时这个可用性副本被称为主副本（primaryreplica）。其余的副本都被称为辅助副本（secondaryreplica），辅助副本上的数据库可能是不可访问的，或者是只能接受只读操作（取决于可用性组的配置），这些数据库被称为辅助数据库。一但发生故障转移，任何一个辅助副本都可以成为新的主副本实例。主副本会不断地将主数据库上的数据变化发送到辅助副本，来实现副本间的数据库同步。

Always On 可用性组 提供了一组丰富的选项来提高数据库的可用性并改进资源使用情况。 主要组件如下：

1. 支持最多九个可用性副本。 “可用性副本” 是可用性组的实例化，此可用性组由特定的 SQL Server 实例承载，该实例维护属于此可用性组的每个可用性数据库的本地副本。 每个可用性组都支持一个主副本和最多八个辅助副本。
2. 支持替代可用性模式，如下所示：

• 异步提交模式 。 此可用性模式是一种灾难恢复解决方案，适合于可用性副本的分布距离较远的情况。
• 同步提交模式 。 此可用性模式相对于性能而言更强调高可用性和数据保护，为此付出的代价是事务延迟时间增加。 一个给定的可用性组可支持最多 5 个同步提交可用性副本（包括当前主副本）。

SQL Server 2019 (15.x) 将同步副本的最大数目从 SQL Server 2017 (14.x) 中的 3 增加到了 5。 可以配置此组的 5 个副本在该组中进行自动故障转移。 有 1 个主要副本以及 4 个同步的次要副本。

1. 使您能够将给定的可用性副本配置为支持以下一种或两种活动辅助功能：

• 利用只读连接访问，与副本的只读连接可以在此副本作为辅助副本运行时访问和读取其数据库。
• 当副本作为辅助副本运行时，对副本的数据库执行备份操作。

通过使用活动辅助功能，可以更好地利用辅助硬件资源，从而提高 IT 效率并降低成本。 此外，通过将读意向应用程序和备份作业转移到辅助副本，有助于提高针对主副本的性能。

1. 支持每个可用性组的可用性组侦听器。 “可用性组侦听程序” 是一个服务器名称，客户端可连接到此服务器以访问 AlwaysOn 可用性组的主要副本或次要副本中的数据库。 可用性组侦听器将传入连接定向到主副本或只读辅助副本。 侦听器在可用性组故障转移后提供快速应用程序故障转移。

1.2、故障集群实例

FCI：Failover Cluster Instance故障集群实例，FCI是实例层面的而always on是数据库层面的，FCI的概念有点类似ORACLE的RAC，但是实际FCI只有一个实例具备读写的功能

FCI在实例层面运作，而AlwaysOn是在库层面运作。

FCI是迁移服务器硬件，不提供单个或多个数据库的迁移。需要搭配数据库镜像，但是镜像是“单库”、不可读，AlwaysOn可用组是可以以多个库为一个单位迁移，备库可读。

FCI在过去很长时间都是SQL Server的常用高可用技术。它可以在集群的任何可用节点之间进行故障转移。其唯一缺点就是存储。由于需要使用共享存储，所以存储子系统就成了单点故障的风险点。FCI是一个安装在WSFC上的SQL Server 实例，不管是默认实例还是命名实例。这个实例最少需要这几个资源：IP地址、网络名、共享硬盘（N个）、SQL Server服务、SQL Server代理服务。当然这些资源对于单独的实例而言也一样，只是IP地址和网络名是来自于本机，硬盘也属于本机，而FCI则不同。一个两节点的FCI中，SQL Server实例会使用WSFC节点都能可用的共享存储作为SQL Server的存储。通常这次存储是在SAN中划出来的LUN，FCI的部署粗略分为两步

1、在FCI的第一个节点上运行SQL Server安装向导，并选择“新的SQL Server 故障转移群集安装”。完成第一步之后，就可以开始第二步。

2、在WSFC的其他参与节点上运行SQL Server安装向导并选择“向SQL Server故障转移群集添加节点”并完成安装。

1.3、服务故障转移群集

WSFC：Windows Server Failover Cluster windows服务故障转移群集，纯粹的OS层面的东西。

它是微软高可用技术（HA）的核心组成部分。WSFC跟FCI、AlwaysOn相比，它更多的是Windows Server的一个功能，而后面两个则是SQL Server的功能，同时，WSFC更加底层，在创建SQL Server Failover Cluster Instance、SQL Server AlwaysOn等高可用技术之前，都需要部署和配置WSFC。

WSFC可以把多台计算机节点（纯物理机、纯虚拟机、物理机混合虚拟机）组合在一起并对外部应用程序提供高可用服务。服务器上的一个应用如SQL Server，可以运行在cluster的任何一个节点上，这种运行方式是通过cluster提供一个虚拟访问点（由一个唯一IP地址和一个唯一机器名组成，或者“虚拟网络名”）给客户端程序作为链接方式。地址和虚拟名作为一个应用程序的“资源组”，在多个参与节点之间像令牌形式地被传输。当活动节点出现严重故障时，会使得活动节点停止对外服务。这时候集群服务会自动尝试重启当前节点或伙伴节点的资源组。从高层次的角度来说，客户端的访问点是沿着故障转移伙伴节点中的所有硬盘和服务起源传输的。一个已集群的实例在发生故障转移时，会引发客户端连接的断开，然后在其他节点可用之后马上重连。

可用性组：就是指的DB级别的集群的组名称

每个可用性组定义一个包含两个或更多故障转移伙伴（称为可用性副本）的集合。 “可用性副本”是可用性组的组件。 每个可用性副本都承载可用性组中的可用性数据库的一个副本。 对于某个给定可用性组，可用性副本必须位于某一WSFC群集的不同节点上的单独SQL Server实例上。

二、AlwaysOn可用性组

2.1、可用性副本

可用性副本：就是DB级别的集群中的成员，包含主副本，辅助副本，每个副本由一些数据库组成。

对于每个可用性组，一个给定实例只能承载一个可用性副本。 但是，每个实例可用于多个可用性组。 给定的实例可以是独立实例或 SQL Server 故障转移群集实例 (FCI)。

每个可用性副本都被分配一个初始角色（“主角色”或“辅助角色”），角色由该副本的可用性数据库继承。 给定副本的角色确定它承载的是读写数据库还是只读数据库。 其中一个副本（称为“主副本”）被分配主角色，它承载读写数据库（称为“主数据库”）。 至少一个其他副本（称为“辅助副本”）被分配辅助角色。 辅助副本承载只读数据库（称为辅助数据库）。

2.2、侦听器

AlwaysOn创建后，客户端就需要进行连接，为了让应用程序能够透明地连接到主副本而不受故障故障转移的影响，我们需要创建一个侦听器，侦听器就是一个虚拟的网络名称，可以通过这个虚拟网络名称访问可用性组，而不用关心连接的是哪一个节点，它会自动将请求转发到主节点，当主节点发生故障后，辅助节点会变为主节点，侦听器也会自动去侦听主节点。

一个侦听器包括虚拟IP地址、虚拟网络名称、端口号三个元素，一旦创建成功，虚拟网络名称会注册到DNS中，同时为可用性组资源添加IP地址资源和网络名称资源。用户就可以使用此名称来连接到可用性组中。与故障转移群集不同，除了使用虚拟网络名称之外，主副本的真实实例名还可以被用来连接。

2.3、Always on的原理

1、任何一个SQL Server里都有个叫Log Writer的线程，当任何一个SQL用户提交一个数据修改事务时，它会负责把记录本次修改的日志信息先记入一段内存中的日志缓冲区，然后再写入物理日志文件（日志固化）,所以对于任何一个数据库，日志文件里都会有所有数据变化的记录。

2、对于配置为AlwaysOn主副本的数据库，SQL Server会为它建立一个叫Log Scanner的工作线程,这个线程专门负责将日志记录从日志缓冲区或者日志文件里中读出，打包成日志块，发送给各个辅助副本。由于它的不间断工作，才使主副本上的数据变化，可以不断地向辅助副本上传播。

3、在辅助副本上，同样会有两个线程，完成相应的数据更新动作，它们是固化（Harden）和重做（Redo）。固化线程会将主副本Log Scanner所发过来的日志块写入辅助副本的磁盘上的日志文件里（这个过程被称为"固化"）。而重做线程，则负责从磁盘上读取日志块，将日志记录翻译成数据修改操作，在辅助副本的数据库上完成。当重做线程完成其工作以后，辅助副本上的数据库就会跟主副本一致了。AlwaysOn就是通过这种机制，保持副本之间的同步。重做线程每隔固定的时间点，会跟主副本通信，告知它自己的工作进度。主副本就能够知道两边数据的差距有多远。这些线程在工作上各自独立，以达到更高的效率。Log Scanner负责传送日志块，而无须等待Log Writer完成日志固化；辅助副本完成日志固化以后就会发送消息到主副本，告知数据已经传递完毕，而无须等待重做完成。其设计目标，是尽可能地减少AlwaysOn所带来的额外操作对正常数据库操作的性能影响。

4、同步提交模式的维护方式：从客户端收到事务后，主副本会将事务的日志写入事务日志，同时将该日志记录发送到辅助副本。日志记录写入主数据库的事务日志后，事务将不能撤消，除非在此时故障转移到尚未收到该日志的辅助副本。主副本将等待来自同步提交辅助副本的确认。辅助副本将强制写入日志（固化），并将确认消息返回给主副本。收到来自辅助副本的确认后，主副本将完成提交处理并向客户端发送一条确认消息。在同步提交可用性模式下，副本联接到某个可用性组后，辅助数据库就会与对应的主数据库求得一致并进入 SYNCHRONIZED（已同步）状态。 只要一直在进行数据同步，辅助数据库就会保持 SYNCHRONIZED 状态。 这可确保对主数据库提交的每个事务也应用到对应的辅助数据库。在同步辅助副本上的每个辅助数据库之后，辅助副本的同步运行状态总体上将为 HEALTHY。

5、异步提交模式的维护方式：如果每个辅助副本都在异步提交模式下运行，则主副本不会等待任何辅助副本强制写入日志， 而会在将日志记录写入本地日志文件后，立即将事务确认发送到客户端。由于主副本不会等待来自辅助副本的确认，因而辅助副本上的问题从不会影响主副本，辅助数据库就会保持 SYNCHRONIZING（正在同步） 状态。对于主副本和辅助副本相隔很远而且您不希望小错误影响主副本的灾难恢复方案的情况，或性能比同步数据保护更重要的情况，异步提交模式将会很有用。异步提交辅助副本会尝试与接收自主副本的日志记录保持一致，但异步提交辅助数据库往往会保持未同步状态，通常异步提交辅助数据库和相应的主数据库之间的这个时间差会很小。但是，如果承载辅助副本的服务器的工作负荷过高或网络速度很慢，则这个时间差会变得较大。

6、会话超时机制：由于软错误不能由服务器实例直接检测到，因此，软错误可能导致一个可用性副本无限期等待会话中另一个可用性副本的响应。 为了防止发生这种情况， Always On 可用性组实施了会话超时机制，此机制基于以下条件：所连接的可用性副本会在每个打开的连接上按固定间隔发送 ping。 在超时期限内收到 ping 指示连接仍是开放的且服务器实例正在通过此连接进行通信。 收到 ping后副本将重置此连接上的超时计数器。主副本和辅助副本相互 ping 以指示它们仍处于活动状态， 会话超时限制是用户可配置的副本属性，默认值为 10 秒。如果在会话超时期限内没有收到来自另一个副本的ping，该连接将超时、连接将关闭；超时的副本进入 DISCONNECTED（已断开连接） 状态。 即使为同步提交模式的副本，事务也将不等待该副本重新连接暂时将该辅助副本切换到异步提交模式。在该辅助副本重新与主副本连接后，它们将恢复同步提交模式。

三、仲裁配置

3.1、仲裁配置的三种方式

1、不配置仲裁见证：就是少数服从多数，正常节点数量占多数的情况下，集群才会提供服务，否则就停止服务。例如5个节点的集群，其正常节点数量必须至少3个，集群才会提供服务；

2、配置磁盘见证：适用于偶数节点的集群，他在计算法定数量时会将仲裁磁盘计算进来，例如，4个节点+1个仲裁磁盘节点的集群，可以将其视为5个节点的集群，这时正常节点数量必须至少3个，集群才会提供服务；

3、配置共享文件见证：它和配置磁盘见证类似，不过磁盘改为共享文件夹内的文件。

四、Always on的搭建

4.1、配置流程

1、primary、secondary节点实例的所有服务器都必须先在os上安装好故障转移集群Failover clustering功能，一旦其中某台服务器没有安装，则创建故障转移集群时会报错the server ‘XX’ does not have the failover clustering feature installed。OS安装了故障转移集群功能的话Server Manager–Manage–add roles and feature–feature–failover clustering，才会出现server manager–tools–Failover cluster manager。

2、primary、secondary节点实例的服务器需要加入同一个域中。

3、创建Windows服务器故障转移集群(Windows Server Failover Cluster)时，只在其中某台服务器比如只在primary节点实例的服务器创建即可，给集群起个名字和分配一个ip，并把所有的节点服务器加入故障转移集群中即可(这些加入的服务器需要加上域名后缀)，此时千万不要勾选“将所有符合条件的存储添加到群集”，否则primary、secondary节点实例的服务器原来挂载的存储目录会消失。

4、配置集群仲裁选择共享文件仲裁时，不能使用任意节点服务器本地的目录（File share associated with file share witness resource cannot be hosted by this cluster or any of its nodes）

5、每个节点的sqlserver服务都要启用always on的功能，这个功能开启后需要重启sqlserver服务以便生效。

Sql Server Configuration Manager–SQL Server Serivces–SQL Server(MSSQLSERVER)–右键选择Properties–Awayson High Availability–Enable AlwaysOn Availability Groups。

6、在主节点数据库实例上配置always on，实例–Always On High Availability–右键选择New Availablity Group Wizard新建可用性组。

7、数据库加入always on可用性组时，右键高可用组名称–add database即可，但是必须对primary节点的实例的数据库进行full备份和log备份，并把full备份和log备份以norecovery模式恢复到secondary节点的实例。

4.2、扇区检查

需要留意主副本机器和各个辅助副本机器的扇区是否一致，如果扇区不一致有可能导致同步慢，那么最好不要搭建AlwaysOn。

例如：在服务器上运行下面命令，C盘为SQL Server数据文件，日志文件所在盘符

fsutil fsinfo ntfsInfo C:

如果每个扇区字节数和每个物理扇区字节数这两个值，各个副本显示不同，那么最好不要搭建AlwaysOn。

NTFS对于大于2GB的分区，默认簇大小为8个扇区（4KB），分配单元默认是4096字节是因为内存页是4kb；簇=分配单元，默认的分配单元是4096字节，那么一个6000字节的文件就需要两个分配单元，分配单元是windows读写文件的基本单位；逻辑扇区：512字节，操作系统将分配单元的读写请求划分为多个512字节大小，然后写入到磁盘，其实就是操作系统做了一层转换；物理扇区：512 ，物理磁盘读写的基本单位，旧磁盘是512字节，新磁盘是4kb。

五、Always ON的总结

1、primary节点数据库创建的表、索引，会自动同步到secondary节点的数据库。

2、always on要求各个节点对应的操作系统版本必须一致，但是数据库版本可以不一致，比如数据库一个是sqlserver2016 sp2，一个是sqlserver2016 sp3。

3、搭建always on时，各个节点的实例名称@@servername不需要一致。

4、关于IP，一个是windows故障转移集群ip，OS级别的IP，外部可以通过这个ip登录故障转移集群中中的任意一台服务器。一个是alwayson侦听IP，是数据库实例级别的IP，外部可以通过这个ip连上always on的任意一个数据库实例，类似oracle的scan ip.这两个ip对应的dns记录都不需要预先在dns服务器中创建，而是在建立windows故障转移集群ip(这个过程需要输入WFC名称和ip)和alwayson侦听IP(这个过程需要输入监听名称和ip)时会自动在dns服务器中创建。

5、primary或secondary节点的数据库都不能执行脱机/分离操作，执行脱机/分离操作会报错。

6、同步提交即AG的属性Availability Mode选择Synchronous commit时，primary节点的数据库后面状态显示(Synchronized)，secondary节点的数据库后面状态显示(Synchronized)，异步提交即AG的属性Availability Mode选择Asynchronous commit时，primary节点的数据库后面状态显示(Synchronized)，secondary节点的数据库后面状态显示(Synchronizing)。

7、primary节点的数据库新增一个数据文件，secondary节点的数据库也会新增一个数据文件，且路径和primary节点的数据库的一模一样，就算secondary节点的数据库设置了默认路径也会忽略，比如secondary节点的数据库的默认路径是G:\DEFAULT.DATA，primary节点的数据库新增文件的路径是L:\data1.dbf，secondary节点的数据库该文件路径也是L:\data1.dbf，而非G:\DEFAULT.DATA\data1.dbf，所以primary节点的数据库新增一个数据文件，secondary节点的数据库服务器没有一样的路径，secondary节点的数据库会报错，always on的同步会中断。

六、Always On的备份

问题：关于日志备份，我们都知道事物日志备份会截断日志链，假如我在任意副本上执行了日志备份，那么其他副本的日志是否也会一起截断？

答案：是的，一个副本执行日志备份，其他副本会自动截断，只要主节点和辅助节点直接正常通信，不管怎么设置，日志都是可以备份的，可以在主节点备份，也可以在辅助节点备份，只是不能同时备份，不管在哪个节点备份，都会截断所有节点的日志；

其实只要在“备份首选项”（可用性组，右键，属性，）指定的数据库实例上“备份事务日志”即

可将事务日志备份并截断。