SQL Server 数据库镜像

故障转移群集技术 vs 日志传送技术

故障转移群集技术和日志传送技术都不完美，有各自的优缺点

数据库镜像 vs 故障转移群集技术

相对故障转移群集来说数据库镜像同样能够为客户端应用提供统一的连接方式，且同时具备故障转移群集所没有的抵御数据损失的能力

数据库镜像 vs 日志传送技术

相对日志传送，数据库镜像进行灾难切换要快的多，基本对客户端应用透明。

数据库镜像的设计目的

提供一个能实时同步数据的灾难恢复技术，即既能提供数据冗余备份，又能自动进行故障切换。

2.4.1 数据库镜像的基本概念

基本术语和角色

数据库镜像会为目标数据库创建一个副本数据库。这两个数据库分别运行在不同的 SQL Server 实例上，作为“伙伴”建立一个会话（Session）。通过这个对话，两个数据库互相进行通信和协作，扮演互补的角色：“主体角色” 和 “镜像角色”，以实现“镜像”的效果。在任何给定的时刻，都是一个伙伴扮演主体角色，而另一个伙伴扮演镜像角色。

• 主体数据库（Principal Database）
  扮演主体角色的数据库

• 镜像数据库（Mirror Database）
  扮演镜像角色的数据库

• 主体服务器
  运行主体数据库的 SQL Server 实例

• 镜像服务器
  运行镜像数据库的 SQL Server

镜像数据库的作用

镜像数据库作为主体数据库的一个副本，在主体数据库发生故障、不可访问时能迅速恢复数据库访问，即提供了灾难恢复的功能。

镜像技术的限制

1. 主体数据库的恢复模式必须为完整恢复模式

2. 主库不能执行一些操作（比如还原数据库备份）

3. 镜像数据库由于一直处于“恢复”状态，因此不能被访问，可通过建立数据库快照来访问

4. 只有用户数据库才能配置数据库镜像，所有的系统数据库都不能被镜像，对于系统数据库需通过备份的方式来保障安全

5. 由于镜像数据库相互独立，这些数据库不能作为一个组来进行故障转移

数据库镜像会话

上图说明了作为伙伴参与两个镜像会话的两个服务器实例，一个会话用于名为 Db_1 的数据库做镜像，另一个会话用于名为 Db_2 的数据库。

每个数据库镜像的会话都只针对一个用户数据库，而且是独立的。例如，一个应用程序需要同时访问一个 SQL 服务器实例上的两个数据库，那管理员需要配置两个镜像会话。如果其中一个数据库发生故障，它的镜像会将数据库转移到镜像服务器上，而另一个工作正常的数据库则继续在主体服务器上运行。这时对 SQL Server 来讲，两个数据库都能被使用。但是对应用程序来讲，连接哪个服务器都不能正常工作。由于镜像数据库相互独立，这些数据库不能作为一个组来进行故障转移

见证服务器的作用

见证服务器只作为一个中立的仲裁，无数据副本，其和主体/镜像服务器建立会话，判定数据库的健康状况，并在主库发生异常时，触发自动的故障转移，让镜像数据库和主库转换角色。

配置了见证服务器的数据库镜像兼备了高可用性和灾难恢复的功能，如果数据库镜像没有配置见证服务器，就无法实现自动的故障转移。一旦主体数据库发生异常，DBA需手动进行故障转移

见证服务器的的特点

• 必须独立于主体服务器和镜像服务器的实例

• 多个数据库镜像伙伴（无论它们是否使用相同的主体服务器和镜像服务器）可以共用一个见证服务器

主体、镜像、见证服务器实例通信原理

主体、镜像、见证服务器实例上都会建有一个叫作“端点”（Endpoint）的对象，每个端点都侦听在一个 TCP 的端口上，默认是 5022 端口。主体、镜像、见证服务器三者就是通过建立在实例上的端点向另两个服务器发送 TCP 数据包来进行“会话”。一个 SQL Server 实例上，无论配置有多少个数据库镜像会话，它们都只使用唯一的端点。若这三个实例中的某两个运行在同一台机器上，要确保这两个实例上的端点侦听在不同的 TCp 端口上。

主体数据库和镜像数据库数据同步原理

SQL Server 数据库中任何的数据变化都会先记录到事务日志中，然后才会真正更新数据页面。而事务日志是先保存在该数据库的日志缓存里（Log Buffer）里，然后将缓存中的日志块固化到磁盘上的 LDF 文件中。在数据库镜像中，主服务器在将主体数据库的日志从日志缓存固化到磁盘的同时，还会使用另一个线程来将日志块发送到镜像服务器的端点。当镜像服务器通过端点接收到日志快之后，它现将日志块放到镜像数据库的日志缓存里，然后将缓存里的日志块固化到磁盘上。一旦日志块被固化之后，镜像服务器会根据日志来对镜像数据库执行“重做”（Redo），最终更新数据页面。当镜像服务器重做日志时，镜像数据库实际就是在执行日志的前滚操作。如果重做失败，则镜像服务器通过将数据库置于 SUSPENDED 状态来暂停会话。DBA 必须找到问题的原因并解决问题才能继续会话。当主体服务器阶段或收缩数据库的日志时，镜像服务器也将在日志的同一点收缩日志。

数据压缩的优点

从 SQL Server 2008 开始，日志块在被主体服务器发送到网络之前会做压缩处理，这会带来以下好处

1. 提升日志发送和接受的效率

2. 降低日志块传输对网络链路和网络设备所带来的负载

3. 对异常繁忙的生产系统，降低了由于网络不胜负荷导致的镜像对话异常中断，也降低由于网络延迟导致的数据库镜像性能问题

发送队列

• 产生发送队列的原因
  主库上新日志生成的速率可能会快于日志发送的速率，有些日志记录来不及发送，而在主库上暂时“累计”起来。

• 发送队列标记
  发送队列不会占用主库上额外的存储空间和内存，其存在于主库的事务日志文件（LDF）上。事务日志里会有一个标记，指向所有没被发送到镜像服务器的日志记录中最早的那个。即从这个标记开始到最新的日志为止，就是“发送队列”。主体服务器就不断地从这个队列中取出最早的日志，并发送给镜像数据库

重做队列

• 产生重做队列的原因
  在镜像数据库中，如果日志重做的速率赶不上日志在镜像端固化的速度，那么也在镜像数据库的 LDF 文件中等待重做的事务也会产生一个队列。

• 重做队列标记
  重做队列也不实用额外的存储空间和内存，它仅是使用一个标记，指向镜像数据库的事务日志文件中最早的那个还未重做的日志。镜像服务器从这个标记开始按顺序重做日志，更新数据。重做队列中等待的为重做日志数量决定了故障转移到镜像数据库所要花费的时间。

数据库镜像技术和日志传送一样，其同步的信息也是日志记录，而不是数据操作命令。不同的是，日志传送是通过数据库日志备份和恢复。而数据库镜像更直接，其直接读取和标记日志文件里的日志记录。因此镜像技术的实时性会更好。

2.4.2 数据库镜像操作模式

数据库镜像有 3 种操作模式：高可用、高保护和高性能。使用哪一种模式取决于两点：

• 数据库镜像的 “事务安全性” 设置

• 是否有见证服务器

数据库镜像 3 种操作模式及它们之间的区别

事务安全性

事务安全性是一个数据库镜像的设置选项。若将它设置为 Full, 数据库镜像就处于“同步”传输模式。若将该选项设置为 Off, 数据库镜像就处于“异步”传输模式

如何设置事务安全性

在 SSMS 中打开已配置镜像的数据库的属性页，在镜像中可的运行模式区域，就能看到数据库镜像当前的事务安全性设置。可根据自己的需要进行修改

使用 T-SQL 语句修改事务安全性设置，以下语句将事务安全性设置为 Full:

alter database [<dbname>] set safety full;

同步传送和异步传送模式之间的区别

同步传送模式下，数据库镜像是这样处理事务的

• 从客户端收到事务请求时，主体服务器将事务的日志写入事务日志中。

• 主体服务器将事务写入数据库中，同时将日志发送到镜像服务器。这时这题服务器不是提交或回滚事务，而是等待镜像服务器完成第三步

• 镜像服务器将日志固化到磁盘，并向主体服务器返回确认

• 主服务器收到来自镜像服务器的确认后，结束事务操作，并向客户端发送事务完成的确认消息

用这样的方法，镜像服务器那里没有做完，主体服务器就不会结束事务。同步传送模式通过上述的过程来保证数据在主体数据库和镜像数据库上是始终保持完全同步的。所有被提交的事务都保证会被写入镜像服务器的磁盘上。这样，事务被认为是“安全”地在两个伙伴间传输，不会有两边数据不同步的现象。但是，同步传送模式会给主体服务器上事务的提交带来额外的延迟，即使延迟可能非常微小。所以数据安全是以牺牲一定的性能来达到的。为了尽量缩小延迟，需要给伙伴服务器之间配备高速可靠的网络，同时要确保伙伴服务器拥有良好的磁盘性能。一台 I/O 表现不佳的镜像服务器会因为日志固化的缓慢而拖累主体服务器的性能。一般情况下，推荐伙伴服务器具有相同的硬件配置。这既是为了保障较小的延迟，也是为了确保无论哪台机器扮演主体服务器都会有相同的性能表现。

在异步传送模式下，主体数据库向镜像服务器发送日志之后，不会等待镜像服务器的确认，而是直接向客户端发送事务完成的确认消息。主体数据库上提交事务不需要等待镜像服务器将日志写入磁盘。因此，主体数据库和镜像数据库可能不是完全同步的。镜像服务器会尽可能的“追赶”主体服务器，想与之同步。但由于网络、负载等因素的影响，镜像数据库还有可能在某种程度上滞后于主体数据库。尽管这两个数据库之间的同步差距通常很小，但一旦主体数据库突然出了问题，在镜像数据库上可能会有一部分数据丢失。所以这种方法性能好，但是不能完全保证数据同步。

三种操作模式

高可用操作模式

• 高可用操作模式的前提条件
  高可用模式要求数据库镜像的安全性设置必须设置为FULL,即日志传输模式是“同步”模式，以保证在主体数据库提交的事务必定在镜像数据库端也提交了。除此之外，必须配置见证服务器来保证在主体数据库发生异常时自动触发故障转移。

• 高可用操作模式下的自动故障转移场景
  见证服务器和镜像服务器保持会话，而主体服务器由于发生系统故障，中断了与镜像服务器和见证服务器的会话。在这种情况下，镜像服务器会将镜像数据库的角色转换成”主体“角色。除了由见证服务器触发的自动故障转移，DBA 也可执行手动故障转移

• 高可用操作模式适用条件

1. 主体服务器和镜像服务器之间具有高速且可靠的网络连接环境

2. 用户需要系统有自动故障转移的功能。

• 高可用操作模式的限制
  若数据库实例上有多个数据库都配置了镜像，而应用程序需要同时访问到所有这些数据库才能正常运行，那其中一个数据库发生自动故障转移并不能触发其他镜像数据库的自动转移。这时这些数据库可能会分别运行在不同的 SQL 实例上，从而导致整个应用程序不能同时找到所有数据库，最终无法使用。由于数据库镜像不能实现多个数据库一同转移，高可用模式只保证了数据库层面的可用，而不能保证应用层面的一定可用。

高保护操作模式

• 高保护操作模式的前提条件

1. 数据库镜像的安全性设置必须设置为 FULL

2. 高速可靠的网络

• 高保护操作模式下的故障转移

1. 高保护操作模式下的事务安全性设置为FULL，所以主体数据库和镜像数据库的日志是保持同步的。

2. 高保护操作模式下的数据库镜像没有配置见证服务器，因此没有一个仲裁的角色来判断哪个数据库出现了问题。

3. 主体服务器无法继续工作时，DBA 需手动操作才能切换数据库的角色。

4. 当主体数据库和镜像数据库保持着会话且两边数据库已同步时，数据库管理员可执行手动故障转移

5. 若镜像服务器实例出现故障，则主体服务器实例不会受到影响，主题数据库状态会变为“断开”连接的状态，不过依旧可被访问

6. 若主体服务器出现问题，则镜像对话就会被挂起，但可以将主体数据强制故障转移到哦原始的镜像服务器。

7. 强制切换可能造成一定量的数据丢失，因为原始的主体服务器上可能还有一些日志没有来得及发送到镜像数据库上。

高性能操作模式

下图显示了高性能操作模式的数据库镜像

• 高性能操作模式的前提条件

1. 事务安全性被设置为OFF
   这意味着镜像数据库的日志可能滞后于主体数据库（异步传输模式）。

• 高性能操作模式下的故障转移

1. 高性能操作模式下不需要见证服务器，唯一可以使用的切换方式是强制故障转移。
   这意味着高性能操作模式下的角色切换永远存在着损失数据的风险。

• 高性能操作模式适用场景

1. 性能要求非常高，不能忍受任何延迟的数据库环境。

2. 如果网络状况不稳定，或者镜像的两个伙伴相隔很远，或者用户可用接受一定量的数据丢失，也可以考虑使用高性能操作模式。

2.4.3 客户端连接重定向及超时控制

和故障转移群集不同，数据库镜像无法提供一个虚拟的服务器名给使用数据库的应用程序。即应用程序需要知道当前的主体服务器是哪一个，才能连接上正确的 SQL Server 以获取数据服务。一旦发生数据库镜像故障转移，用户需通过修改连接字符串，建立别名等方式来将应用重定向到新的主体服务器。这极大的影响了数据库镜像的高可用性：对于一个高可用操作模式的数据库镜像，虽然可以自动进行故障转移，但是在手动重定向应用的连接之前，实际上应用还是不工作的。

为增强数据库镜像的高可用性，必须能够使得应用程序意识到镜像服务器的存在，并且能够在主体服务器不存在时，自动重定向到那个镜像服务器。为此，微软为 ADO.NET 和 SQL Native Client 这两个数据库驱动程序添加一项特性：可在连接字符串中指定首选的服务器，同时也可以指定故障切换后的备选数据库。那些使用ADO.NET 或者 SQL Native来连接 SQL Server 的应用，通过这个特性可获得自动重定向连接的能力。

Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;

通常将大部分时间扮演主体角色的服务器赋给 Data Source 参数，把多数时间扮演镜像角色的服务器赋给 Failover Partner 参数

有了 Failover partner 参数后，应用程序连接 SQL Server 的行为就变得比较复杂

• 当应用程序尝试连接到 Data Source 所指定的主体服务器并且失败时，它就会去尝试连接 Failover Partner 指定的镜像数据库。因此，在数据库镜像发生故障转移（自动、手动或强制）之后，应用程序始终可以凭借这两个属性找到可以提供数据服务的服务器

• 如果应用程序尝试连接 Data Source 指定的主体服务器并且成功了，那么连接字符串中 Failover Partner 属性指定的服务器就会被忽略掉。此时驱动程序会通过主体服务器获得它的故障转移伙伴的名字，把这个名字缓存在应用程序的内存中。要注意的是，这个被缓存的名字会覆盖连接字符串中的 Failover Partner 属性值。也就是说，即使你在连接字符串中没有指定 Failover Partner，甚至你有意把 Failover Partner 设置为一个错误的、非镜像服务器的实例，只要应用程序第一次能成功连接到主体服务器，应用程序就能获得正确的镜像服务器名称。

无论是通过上面两个情况中的哪一种，只要应用程序得到了两个伙伴的名称，驱动程序就会遵循“连接重试算法”。不论是初次与会话建立连接，还是在中断已建立连接后重新连接，当应用程序成功无法连接到当时主体服务器后，就回去尝试连接故障转移伙伴。这时又会有两种情况：

• 若成功连接，则会读取新的主体服务器的镜像配置信息，得到新的镜像服务器名，并缓存在应用程序的内存中

• 若连接失败，那么驱动程序会继续尝试去访问 Data Source 中指定的首选服务器。驱动程序就会这样不断地尝试这两个服务器，直到达到了连接超时的阈值

这就是数据库镜像的“连接重试算法”。由于连接重试算法的存在，连接一个镜像数据库的时候，计算“超时”错误的方法也和连接普通的 SQL Server 数据库不同。当数据库是镜像数据库的时候，你在连接字符串中指定的 connection timeout 值和非镜像数据库的 connection timeout 值尽管大小一样，但实际上效果是不一样的。

假设指定 connection timeout 15 秒：

第一轮，连接 Data Source 指定的首选服务器，超时时间为总 timeout 的 8%，即 15*8%=1.2s。这时若连接失败，就试图连接故障转移伙伴（无论是从缓存中的还是从 Failover Partner 参数中获得伙伴的名字），超时时间也是 1.2 秒

第二轮，如果连接故障转移伙伴失败，此时，客户端数据库接口再试图连接首选服务器，超时时间设为总 timeout 的 16%， 即 15*16%=2.4 s。如果没有成功，就再次连接故障转移伙伴，超时时间也是 2.4 秒。

第三轮，超时时间是总 timeout 时间的 24%。在后续的每轮中，连接尝试的重试时间会逐渐变大。前八次连接尝试的重试时间如下：

8%, 8%, 16%, 16%, 24%, 24%, 32%, 32%

重试时间使用以下公式进行计算：

RetryTime = PreviousRetryTime+(0.08*TotalLoginTimeout)(PreviousRetryTime 初始值为 0)

对于 15 秒的 timeout 值，实际连接服务器的最大 timeout 值是 3.6 秒！如果前面 3 轮连接（1.2s, 2.4s, 3.6s）都不成功，在还没有用尽第4轮的 4.8s 时间之前（实际上此时第四轮只剩下 0.6 s）,15 秒超时时间就到了，你的应用程序就会报超时错误。

要增加连接服务器的真实 timeout 值，只能增加总的 connection timeout 值。例如，当我们将 timeout 设为 120 秒时，那么第一轮的重试时间是 120*8%=9.6 秒。即便如此，第一轮的尝试连接时间还是比非镜像数据库的默认 15 秒 timeout 时间要短。假设你现有的应用本就需要较长的连接超时，如果需要把数据库配置成镜像，则一定要把 connection timeout 增大，否则你的应用可能非常容易就得到超时错误。

对于不是ADO.NET 和 SQL Native的驱动，即使数据库是镜像数据库，在应用程序端也不会缓存故障转移伙伴的名称，同时也不支持 Failover Partner 参数，因此就必须要考虑其他的方法来实现连接重定向，例如在应用程序的代码中加入重试和重定向的逻辑。

镜像切换需要考虑的事情

数据库镜像切换只能保持用户数据库中数据的同步，而系统数据库时无法配置镜像的。即登录名、SQL Server 实例的配置、维护计划、作业等保存在 master、msdb 或 model 数据库里的信息在镜像的两台服务器上并不是同步的。一旦发生故障转移，即使数据库依旧可用，应用程序可能会因为各种原因无法正常工作，比如：

• 应用程序使用的登录名在新的主体服务器上不存在

• 新的主体服务器上虽然已经有同名的登录名，但是该登录名的 SID 和原始主体服务器上的登录名不同。于是在故障转移后形成了孤立用户。这时可用 sp_change_users_login存储过程来解决问题

• SQL 作业会使用一些 SQL Server 以外的资源（例如一些文件）,但是这些资源在两台服务器上存放的路径不同

• 两台服务器的配置不同，新的主体服务器由于不够优化而导致性能极差

2.4.5 数据库镜像的监控和故障排查

要了解数据库镜像的运行状态，对发生的问题进行故障排查，可收集以下信息

1. SQL Server 的 ERRORLOG
   在主体服务器、镜像服务器和见证服务器上，ERRORLOG 中都会记录每个镜像数据库所处的状态，每个数据库当时处于什么角色，以及服务器彼此间的连同状况

比如，下面这段日志表明镜像的端点已经成功监听在了端口 5022 上。

2018-12-04 17:26:38.04 spid10s     Starting up database 'tempdb'.2018-12-04 17:26:38.07 spid16s     The Service Broker protocol transport is disabled or not configured.2018-12-04 17:26:38.07 spid16s     Server is listening on [ 'any' <ipv6> 5022].2018-12-04 17:26:38.07 spid16s     Server is listening on [ 'any' <ipv4> 5022].

下面这段日志说明数据库处于镜像角色

2018-12-04 17:33:54.22 spid31s     Database mirroring is active with database 'test' as the mirror copy. This is an informational message only. No user action is required.

如果发生连接故障，ERRORLOG 中会记录镜像数据库的状态变化，以及连接问题的错误代码，这些代码对分析和定位连接会非常有用。举例如下：

(1) 由于发生了自动故障转移，数据库转换了角色

镜像服务器的 ERRORLOG

The mirrored database "XXX" is changing roles from "MIRROR" to "PRINCIPAL" due to AutoFailover

主体服务器的 ERRORLOG

The mirrored database "XXX" is changing roles from "PRINCIPAL" to "MIRROR" due to Role Synchronization

(2) 伙伴服务器之间发生网络连接错误

(3) 伙伴服务器不响应连接请求，可能是出现了严重的性能问题

2. 性能监视器
   数据库镜像在性能监视器中有一个专门的对象 SQL Server:Database Mirroring。这个对象下的计数器对于了解数据库镜像运行的状态，有着非常重要的作用

主体数据库

• Transaction Delay 计数器
  检查该计数器以确定数据库镜像是否影响主体服务器的性能

• Log Send Queue KB 计数器
  检查该计数器以确定发送队列的大小，若 send queue 很长，有可能是镜像数据库处理日志慢导致的，也可能是由于网络传输慢导致的。

• Log Bytes Sent/Sec
  该计数器显式每秒发送的日志量。可计算(Log Send Queue)/(Log Bytes Received /Sec)来估算镜像数据库需要多长时间才能追赶上主体数据库

镜像数据库

• Log Bytes Received/Sec
  该计数器显示了每秒接收到的日志量

• Redo Queue KB
  检查该计数器来确定重做队列的大小，就知道有多少日志没有被重做，若 Redo queue 很大,这意味着镜像数据库存在 I/O 瓶颈

• Redo Bytes/Sec
  该计数器显示了执行重做操作的速率，发生故障转移时，原始的镜像数据库需要把重做队列里的日志都重做完才能真正上线运行。可通过计算(Redo Queue)/(Redo Bytes/Sec)来估算一旦发生故障转移，需要多长时间才能完成全部重做操作

上述的计数器配合一些系统自带的计数器（例如，物理磁盘，网络等对象下的计数器），可以很直观地定位到数据库镜像的性能瓶颈

3. 数据库镜像监视器
   数据库镜像监视器是一个图形用户界面，可用来监视镜像伙伴以及镜像会话的状态，利用这些信息可评估在当前状况下完成故障转移所需的时间，和潜在的数据丢失量。也可为你认为很重要的指标（如 send queue, redo queue）配置警告阈值，一旦超过设置的阈值就触发警告来通知数据库管理员。

4. 系统存储过程和系统表
   可通过运行 sp_dbmmonitorresults 系统存储过程来查看当前数据库镜像的状态。该存储过程和镜像监视器都是从 msdb 数据库中的系统表 dbm_monitor_data 中获取数据的

use msdb
goexec sp_dbmmonitorresults test

数据库镜像两类问题

1. 诊断发生故障转移的原因（对于非高可用模式的镜像，就是诊断镜像停止工作的原因）

收集主体服务器，镜像服务器和见证服务器上的 ERRORLOG。如上所说，ERRORLOG 里会记录什么时候镜像发生了问题，发生了什么类型的问题，以及最终的结果如何。最重要的是，不能孤立地取分析各个 ERRORLOG，而是应该将他们彼此作为参考，互相印证。

2. 性能问题
   由于只有主体服务器才能响应应用端的请求，性能问题都会体现在主体服务器端。例如主体服务器上事务提交有严重的延迟，或者是发送队列很长等。但这类问题的最终症结往往却不在主体服务器上，而在镜像服务器上或者是在网络上。要诊断这类问题，需要在主体服务器和镜像服务器上打开性能监视器来记录各项镜像相关的数据，以及磁盘、网络、内存、CPU 相关的数据。此外数据库镜像监视器也能帮助你了解主体/镜像服务器的运行状态。通过这些数据，你可了解镜像服务器的处理事务的效率，网络传送事务的速度以及镜像端的磁盘负载和磁盘性能（往往是最大瓶颈）

打赏

支付宝微信扫一扫，打赏作者吧～

本文链接：https://www.kinber.cn/post/1707.html 转载需授权！

推荐本站淘宝优惠价购买喜欢的宝贝:

 您阅读本篇文章共花了：