always on 完整方案

【Always On】完整文档

有道云笔记markdown文档。

概述

  • 发布订阅：alwayson之前的技术实现方案---SQL Server 2005,微软,查询分离。
    缺点：生产库和查询库的同步性能较差，存在性能问题，因此在大型生产环境为人诟病。
  • AlwaysOn:SQL Server 2012
    引入的一种新的技术架构，相比发布订阅性能明显提升;区别在于其充分利用内存高效读取的原理来实现日志的传递。

功能

原理

同步操作：

从客户端收到事务后，主副本会将事务的日志写入事务日志，同时将该日志记录发送到辅助副本。
日志记录写入主数据库的事务日志后，事务将不能撤消，除非在此时故障转移到尚未收到该日志的辅助副本。主副本将等待来自同步提交辅助副本的确认。
辅助副本将强制写入日志（固化），并将确认消息返回给主副本。
收到来自辅助副本的确认后，主副本将完成提交处理并向客户端发送一条确认消息。

客户端->>主副本: 提交事务
主副本->>主副本: 事务的日志写入事务日志
主副本->>辅助副本: 日志记录
辅助副本->>辅助副本:写入日志（固化）
辅助副本->>辅助副本:日志转换成修改操作，写入数据库（重做）
辅助副本->>主副本: 确认消息
主副本->>主副本: 完成提交
主副本->>客户端: 确认消息

线程介绍以及职责

LogWriter 任何一个SQL Server 数据修改事务 内存缓冲 日志固化

graph LR
日志信息-->内存日志缓冲区
内存日志缓冲区-->物理日志文件

Log Scanner 主副本 日志缓冲区或者日志文件 打包日志块 不间断工作 发送给各个辅助副本

graph LR
内存日志缓冲区/物理日志文件-->日志块
日志块-->辅助副本

Harden 写入辅助副本的磁盘上的日志文件

Redo 日志记录翻译成数据修改操作 每隔固定的时间跟主副本通信 告知自己的工作进度

graph LR
Harden日志块-->存放到辅助副本磁盘日志文件
存放到辅助副本磁盘日志文件-->Redo日志块
Redo日志块-->数据修改操作

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

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

  3. 在辅助副本上，同样会有两个线程，完成相应的数据更新动作，它们是固化（Harden）和重做（Redo）。

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

部署

所需资源

指标 域控服务器 DB1 DB2 共享文件夹or见证磁盘
IP 域控IP DB1 IP DB2 IP
网关 172.31.2.254 172.31.2.254 172.31.2.254
子网掩码 255.255.255.0 255.255.255.0 255.255.255.0
DNS 127.0.0.1 域控IP 域控IP
计算机名 Cluster01 DBserver01 DBserver02
域名 *.com *.com *.com
域账户 username
域密码 ******
仲裁见证文件共享地址 （\\share）共享地址 Tip：不能在域控上
VIP（故障转移集群虚拟IP） 172.31.1.E
SQL数据库版本 SQL Server 2016 Enterprise Edition SQL Server 2016 Enterprise Edition
SSMS Server Management Studio 17.4 Server Management Studio 17.4
服务器版本 Windows Server 2012 R2 Standand  64位 Windows Server 2012 R2 Standand  64位 Windows Server 2012 R2 Standand  64位
VIP（alwayson虚拟IP） Always On IP Always On IP
侦听器 侦听器名称
数据库 dbname
sa密码 ******

系统架构

  • windows 群集
graph TD

故障转移群集_VIP1--> 节点服务器1_IP1
故障转移群集_VIP1--> 节点服务器2_IP2
故障转移群集_VIP1--> 节点服务器X_IPX....
  • always on 集群
graph TD

数据库侦听器_VIP2--> DBServer01_IP1
数据库侦听器_VIP2--> DBServer02_IP2
数据库侦听器_VIP2--> DBServer0X_IPX....

搭建步骤

  1. Part1域控配置
  2. Part2故障转移集群配置
  3. Part3AlwaysOn集群配置
  4. Part4典型问题分析

扩展优化

技术要点

  • 四种集群仲裁配置

注意：
1. 仲裁配置：（多数节点） 和 （多数节点和文件共享）
2. 如果集群节点是奇数，那么使用多数节点。
3. 如果集群节点是偶数，那么使用多数节点和文件共享。
4. 需要配置一个共享文件夹，各个节点都能访问这个共享文件夹。
5. 共享文件夹所在机器不需要加入域。
6. 生产环境不要把共享文件夹放在域控上。
7. 一个节点和见证磁盘运行,可以运行。
8. 所有节点运行，见证磁盘不通信，停止运行。

  • 见证磁盘 VS 见证共享文件夹

  • 同步提交 VS 异步提交

  • 故障转移集群VIP VS AlwaysOn 的VIP

  • 故障转移模式

    可用性副本的主角色和辅助角色在称为“故障转移” 的过程中通常是可互换的。

      三种故障转移形式：
  1. 自动故障转移（无数据丢失）
  1. 计划的手动故障转移（无数据丢失）
  1. 强制手动故障转移（可能丢失数据）,通常称为“强制故障转移”

  • 自动故障转移所需条件

仅在以下条件下才发生自动故障转移：

注意：

  1. 在数据库级别，诸如因数据文件丢失而使数据库成为可疑数据库、删除数据库或事务日志损坏之类的数据库问题不会导致可用性组进行故障转移

  2. AlwaysOn 可用性组监视自动故障转移集中两个副本的运行状况。 如果任一副本失败，则该可用性组的运行状况状态将设置为“严重”。 如果辅助副本失败，则自动故障转移将不可行，因为自动故障转移目标不可用。 如果主副本失败，则可用性组将故障转移到辅助副本。 在之前的主副本进入联机状态之前，将不存在任何自动故障转移目标。 在任一情况下，为了在连续出现失败这种近乎不可能发生的情况下确保可用性，我们建议您将其他辅助副本配置为自动故障转移目标。

  3. 要设置故障转移模式为“自动”的前提是可用性模式是“同步提交”。

  4. 如果主要副本设置为手动故障转移，即使次要副本设置为自动故障转移，也无法发生自动故障转移。

  5. 只能设置一个自动故障转移辅助副本

  • 辅助角色支持的连接访问类型

  • 主角色支持的连接访问类型

注意：所有的限制只针对配置了可用性数据库，非可用性数据库不受这些连接的限制，配置读写分离至少得保证有`两个可读副本`，如果只有一个可读副本当可读副本变成了主副本之后会导致只读意向`无副本可连接`。

注意事项

  • 所有机器防火墙都关掉
  • 域控不需要安装故障转移集群服务和SQL Server.不需要加入到故障转移集群
  • 两个节点都需要安装相同的更新程序，建议不要开启自动更新功能，由系统管理员手动更新
  • SQL Server 2012 AlwaysOn只支持最多一个主副本和四个辅助副本.
  • 最多允许三个同步提交的可用性副本(包括主副本)
  • 最多允许两个自动故障转移副本(包括主副本)
  • 现在AlwaysOn可用性组已经完全支持 Windows Azure ，可以把辅助副本部署到 Windows Azure 上
  • 主副本机器和各个辅助副本机器的扇区是否一致，如果扇区不一致，或者环境不一样有可能导致同步慢或IP冲突问题
  • 每个扇区字节数和每个物理扇区字节数这两个值，各个副本显示不同，那么最好不要搭建AlwaysOn
  • 在AD用户和计算机管理界面 里的 域用户和故障转移集群用户的权限需要添加下面红框的权限，否则创建侦听器的时候有可能报错

跳坑记录

  • 原因分析：
    数据库加入可用性组后，正常状态下数据库为标注为“已同步”，当删除可用性后，数据库状态会变成“正在还原”该状态数据库将无法正常读写。
  • 解决方案：执行基本还原即可。
--修复正在还原数据库 
RESTORE DATABASE omsprod WITH RECOVERY
  • 原因分析：数据库备份还原后数据的登录名的用户映射关系丢失，数据库登录lcoms9999成为孤立用户，进而服务端连接数据库连接失败。
  • 解决方案：执行以下脚本数据库删除原有的数据库登录名修复数据的孤立用户。
--查询孤立用户 --需要选定对应的数据库
sys.sp_who @loginame ='LCOMS9999' -- sysname
--删除登录用户
KILL 71
--修复孤立用户 --需要选定对应的数据库
sp_change_users_login 'Auto_Fix', 数据库用户名, NULL, '密码'
  • 原因分析：数据库副本之间的sid不一致。
  • 修复方案：执行脚本获取sid，将主要副本的登录用户的sid 同步到服务副本中。
--查询登录名sid --主库查询sid --将结果在辅助库中执行
SELECT
  'create login [' +p.name + '] ' +
  case when p.type in('U','G') then 'from windows ' else '' end +
 'with ' +
 case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
 'sid = ' + master.sys.fn_varbintohexstr(l.sid) +
  ',check_expiration = ' + case when l.is_expiration_checked >0 then 'ON, ' else 'OFF, ' end +
  'check_policy= ' + case when l.is_policy_checked> 0 then 'ON, ' else 'OFF, ' end +
 case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
  else '' end +
 'default_database = ' + p.default_database_name+
 case when len(p.default_language_name) >0 then ',default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c ON  l.credential_id= c.credential_id
WHERE p.type in('S','U','G')
AND p.name<> 'sa'

  • 原因分析：AlwaysOn集群创建失败删除后，需要重新停用、启用alwaysOn功能

  • 解决方案：在每个数据库节点的数据库配置管理器上数据库服务的属性中重新停用、启用alwaysOn功能。

  • 原因分析：数据库连接字符串对应到当前主要副本数据库，发生故障转移时数据库连接无法自动转移到原辅助副本中。

  • 解决方案：更新数据的连接方式，将数据库连接到alwaysON的侦听器的虚拟IP上。当故障发生转移后数据库连接地址会自动的转移到新的主要副本数据库中。

  • 解决方案：修复数据库节点问题后，移除辅助副本的omsprod数据库，移除Alwayson集群的可用性组中的omsprod可用性数据库，重新添加该可用性数据库到可用性组中。

  • 解决方案：重新搭建一服务器虚拟机环境，使用服务器快照还原数据库节点。下一步按照步骤6执行。

  • 原因分析：AlwaysON集群创建的时候可以设置集群的“可读辅助副本”的状态。当状态为否时，数据将无法正常打开查看。该状态下数据库仅用于数据库备份。

  • 解决方案：设置“可读性辅助副本”的状态为是。

  • 现象描述：主库磁盘满了,故障转移异常,导致辅助副本同步异常

  • 修复步骤:

    1. 重新添加可用性组：失败
    1. 移除节点：重新添加:失败
    1. 重新添加到域：失败
    1. 重新添加到搭建，windows群集：失败
    1. 经检查群集添加失败的真实原因，主副本的防火墙个别端口被意外开启，由于前期排查时确定过防火墙已关闭，所以怀疑是公司的安全策略导致。 成功

引用

感谢前行者分享 不完全记录

SQL Server Alwayson概念总结

从0开始搭建SQL Server AlwaysOn系列

监控AlwaysOn可用性组的性能

Alwayson辅助副本上如何创建同步账号

posted @ 2018-09-03 13:39  过客非归  阅读(6727)  评论(1)  编辑  收藏  举报