备注：判断是否是主副本命令和判断是否是备份主副本命令不一样，如果指定的数据库是非可用性组数据库判断是否是主副本返回的结果是NULL，而判断是否是备份主副本返回的是1。

总结

1.在可用性组的主数据库或辅助数据库上不允许 RESTORE 语句。

2.备份首选项只是给出一个备份的判断选项，无论你手动在哪个副本上备份都可以，唯一的限制就是辅助副本的完整备份只支持“复制”备份。由于辅助副本只支持仅复制备份，所以辅助副本无法进行差异备份。要进行差异备份那么首先备份副本应该选择主副本。

3.无论是在主副本上备份日志还是在辅助副本上备份日志最终都会截断所有副本上的日志链。

好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对小编的支持。

AlwaysOn 进阶 Level 1：What is "SQL Server AlwaysOn"?

By Perry Whittle，2016/02/24（首次发布：2014/09/24）

关于系列

本文属于进阶系列：Stairway to AlwaysOn

AlwaysOn是一套复杂的技术，往往被误解。在这个阶梯中，您将学习AlwaysOn技术，它们如何适应高可用性堆栈，以及如何充分利用它们。

欢迎来到“SQL Server AlwaysOn的阶梯”系列的第一个级别。在这篇1级文章中，我们将发现“AlwaysOn”，“故障转移群集实例”（FCI）和“Windows Server故障转移群集”技术。我们将详细介绍每个细节并总结它们所在的高可用性堆栈的位置。这将为我们提供一个良好的基础，这对上升的楼梯水平是必不可少的。较高级别的楼梯将研究AlwaysOn可用性组和FCI使用的所需基础架构和不同的存储要求和选项。

在每个楼梯层次之后，您将进一步了解AlwaysOn结构如何构建。尽管作为DBA，您可能没有与AlwaysOn和FCI下面的核心基础设施项目进行任何直接交互，但是有助于全面了解所有技术如何集成。最后的楼梯将导致一个功能AlwaysOn配置。

我们首先要看基础知识，其中包括对已经提到的3种技术的简要描述。

AlwaysOn描述中使用了许多首字母缩写词和缩写词。本文结尾处包含了一个常用术语表。

不用多说，让我们深入了解每种技术。

Windows服务器故障转移群集

Windows Server故障转移群集（WSFC）是位于所有Microsoft高可用性应用程序下的核心高可用性（HA）产品。 WSFC是Windows Server操作系统软件套件的一部分。在创建SQL Server的故障转移群集实例，AlwaysOn高可用性组或甚至Microsoft Exchange邮件服务器群集之前，您需要部署和配置WSFC。

Windows Server故障转移群集提供了将多个计算机节点（物理和/或虚拟）组合起来为一组应用程序提供高可用性的能力。应用程序是服务器软件，如SQL Server或Exchange，我们希望能够在任何节点上运行。通过向客户端呈现包括唯一IP地址和唯一计算机名称或“虚拟网络名称”的虚拟接入点来使应用高度可用。此地址和虚拟名称成为应用程序组中的资源，并在参与节点（如令牌）之间传递。活动计算机节点的严重硬件故障将导致在该节点上运行的组服务的丢失。群集服务将根据故障类型（硬件或软件）自动尝试重新启动当前节点或伙伴节点上的组。

在较高级别上，客户端访问点详细信息与任何磁盘和服务资源一起传输到故障转移伙伴节点。群集实例的故障转移会导致客户端连接断开;一旦服务在另一个节点上可用，则客户端可以重新连接。常见的故障通常是其中之一，但是应用程序的任何故障都可能导致服务移动到另一个节点：

• 公用NIC或网络故障
• 电源故障
• 主板故障
• CPU故障

使用WSFC时，群集应用程序被安装到单独的组或“应用程序”中，其中包含一组资源，如磁盘，服务，IP地址等。组及其资源在任何时候都由单个节点拥有，而除非有计划的交换机或故障转移到该节点，否则不能从任何其他伙伴节点访问资源。

下面显示了Windows Server故障转移群集的典型视图。群集节点全部通过网络连接，域控制器和DNS服务与WSFC一起工作，以允许客户端连接到虚拟IP或虚拟网络名称，无论服务在哪个节点上运行。

为了部署FCI，计算机节点必须使用共享存储，这些存储通常从SAN出现给每个节点。为了部署一个典型的AlwaysOn组，节点利用自己的本地存储，而不是与其他集群伙伴共享。

尽管群集节点可能具有不同的硬件，但通常最好将节点间的硬件保持一致，以避免功能较弱的节点无法处理超出其功能的负载。但是，节点必须使用相同的操作系统补丁级别和网络配置;在部署Windows Server故障转移群集之前验证您的配置时，这将变得清晰。 Windows Server版本（Windows 2003中的8个节点，Windows 2008中的16个节点和Windows 2012中的64个节点）的最大群集节点数量不同。

部署强大的Windows Server故障转移群集需要仔细的设计，支持的硬件和相应版本的Windows Server操作系统。地理分散的集群（跨多个WAN的集群）进一步增加了所需的设计和规划的数量，并显着增加了成本。

知道WSFC仅提供故障转移伙伴功能很重要。应用程序在节点之间没有负载平衡或扩展。每个服务都运行在一个且只有一个节点上。

通常，在大型多节点群集中，您可以在Windows Server故障转移群集节点的子集上安装群集应用程序。在所有节点上安装应用程序的错误都可能导致一些不希望的故障转移，我们将在后面看到，也违反了AlwaysOn组限制策略，这可以确保所有AlwaysOn实例驻留在集群中的不同节点上。

WSFC需要某种形式的中介来控制群集资源所有权。此仲裁以Cluster Quorum的形式提供。自Windows 2003 SP1以来，此Quorum采用节点投票系统的形式，维持Quorum所需的多数选票。您还可以使用磁盘形式的其他仲裁资源进行本地化群集，也可以使用多站点群集的远程文件共享。从Windows Server 2012开始，法定人数使用动态节点权重配置在计划中断期间动态平衡群集投票，以防止不必要的故障转移。我们将在未来的层面更详细地讨论法定人数。

故障转移群集实例

SQL Server的故障转移群集实例一直是SQL Server产品中流行的高可用性技术。 SQL Server高度可用的实例是集群化的，以减轻任何节点硬件故障和任何潜在的软件故障。 这里唯一的薄弱环节是存储; 存储子系统成为单点故障。

故障转移群集实例是默认或命名的SQL Server实例，已作为群集应用程序安装到WSFC上。 群集应用程序通常具有以下资源：

• IP地址
• 网络名字
• 共享磁盘
• SQL Server服务
• SQL Server代理服务

独立实例共享相同的基本要求，不同之处在于，使用独立实例时，IP地址和网络名称将从计算机节点本身获取，而磁盘存储由计算机的本地磁盘资源提供。

参考上面的图，我们看到了具有单个FCI的2节点集群的典型视图。 SQL Server的群集实例将使用已呈现给WSFC节点的任何共享存储。通常这种存储将采取从SAN提供的LUN的形式。 SQL Server的FCI部署在一个两步的过程中，这个过程将在稍后的阶梯中介绍。现在，下面是部署SQL Server的故障转移群集实例的两步过程的基本概述：

1. 在将参与FCI的第一个计算机节点上启动“新建SQL Server故障转移群集安装”向导。一旦完成并成功完成，您就可以进入第二阶段了。
2. 在希望加入新的SQL Server FCI的WSFC中的任何计算机节点上启动“将节点添加到SQL Server故障转移群集”向导。

注意：尽管标准版将FCI限制为2个节点，但并不指定有多少节点具有Windows群集的成员资格（您可能有任何数字，直到操作系统的最大值）。该限制是在SQL Server安装程序级别执行的。

FCI有点像一个跑道接力队的传球过程;计算机节点拥有群集的SQL Server应用程序及其资源，然后为客户端提供对SQL Server服务（持有接力棒）的访问权限。当活动的计算机节点失败（下落接力棒）时，合作伙伴节点进入并获得集群应用程序及其资源的所有权（接上接力棒）。

AlwaysOn可用性组

多年来，故障转移群集一直是为SQL Server提供高可用性的主要方法。当一个节点失败时，另一个节点接管向客户端提供SQL Server服务。 AlwaysOn与Windows Server故障转移群集技术集成，提供更具弹性的高可用性平台。

尽管群集在实例级别上工作，但AlwaysOn在数据库级别配置。 AlwaysOn可用性组是SQL Server 2012中引入的新技术，用于将预定义的数据库组复制到AlwaysOn中已知的一组只读伙伴实例或副本。多个节点各自托管一个AlwaysOn数据库的同步副本，并且最好通过监听器的配置来提供访问（稍后会详细介绍）。

AlwaysOn可用性组需要一个或多个辅助副本来托管高可用性数据库的副本。这些辅助数据库可能是可读或不可读的。它们也可以以异步或同步的方式进行更新。异步副本仅支持手动强制故障转移，而同步副本支持自动或手动故障转移。

次要只读副本可以配置为响应只读查询，您也可以将目标的次要目标作为备份/维护操作以减轻主数据库的压力。这种主从关系也是可逆的，以确保真正的高可用性。任何经过适当配置的只读伙伴在系统发生故障时都可能承担主角色。

AlwaysOn依靠WSFC核心功能实现AO提供的高可用性，但不需要与FCI相关的以下任何共享资源。

• 共享磁盘
• 共享的IP地址
• 共享网络名称
• 共享的SQL Server和SQL Server代理资源

这个共享资源规则有一个例外。创建AlwaysOn组侦听器时，将创建将由AO组副本共享的IP地址和网络名称资源。

正如我们所发现的，故障转移群集实例链中的薄弱环节是共享存储。这里有很多方法可以实现冗余，但是通常成本很高，而且安装和维护通常很困难。当然，如前所述，故障转移群集实例只能缓解服务器硬件。它不提供单个或甚至多个辅助数据库。我们在SQL Server 2012之前的SQL Server版本中有数据库镜像，但这些仅为单个不可读的辅助数据库提供了范围。

AlwaysOn仍然使用熟悉的SQL Server端点作为实例通信。端点在使用可用性组部署向导时自动配置。向导驱动的部署提供了最简单的部署路径，而手动部署需要大量的手动交互。尽管如此，一个基本的AlwaysOn组配置仍然非常容易部署和配置，并提供以前不可用的HA级别，而无需采用复杂的功能集成级别。

您也可以创建一个高可用的侦听器服务，您将使用该服务来接受到可用性组的传入连接。监听器由一个唯一的IP地址和一个唯一的虚拟网络名称组成。这是使组内数据库高度可用的最重大变化之一。

在创建AlwaysOn可用性组期间，将在Windows Server故障转移群集内创建一个群集角色，并包含一个资源。此资源在AlwaysOn组故障转移期间在伙伴节点之间进行故障转移，并标识AlwaysOn组的主副本。

AlwaysOn听众

监听程序在配置时将作为资源创建，并驻留在AlwaysOn可用性组的故障转移群集应用程序角色中。资源是：

• 虚拟IP地址
• 虚拟网络名称

侦听器使用TCP端口来接受传入的连接，并默认连接到主副本。当只读路由已配置时，指向只读意向连接的监听程序的连接将被路由到辅助伙伴而不是主要副本。这是我们可以减轻主副本负载的另一种方式。

在AlwaysOn组的故障转移期间，群集中的节点之间的群集应用程序及其资源将发生故障转移。群集应用程序的节点位置跟​​踪主副本及其底层节点，并根据需要在群集中移动。在主副本是SQL Server的群集实例的情况下，侦听器由该FCI 副本的主动节点拥有。

结论

这就是阶梯1的结尾，它提供了3个核心技术的快速介绍，用来使我们的SQL Server实例及其对象高度可用。 在我们的高可用性堆栈中，我们将WSFC作为基本级别，这是安装FCI或AlwaysOn可用性组的主要要求。 接下来，我们有了位于WSFC顶部的FCI，它依靠群集来服务和保护SQL Server实例。 最后，我们有AlwaysOn组，坐在SQL Server的独立实例和SQL Server的“故障转移群集实例”之上。

在2级中，我们将查看SQL Server High Availability中可用的存储类型及其典型用法。 这将帮助您了解系列中未来的阶梯级别。

词汇表

SQL SERVER 2012 AlwaysOn - 维护篇 03

搭建 AlwaysOn 是件非常繁琐的工作，需要从两方面考虑，操作系统层面和数据库层面，AlwaysOn 非常依赖于操作系统，域控，群集，节点等概念；

DBA 不但要熟悉数据库也要熟悉操作系统的一些概念；否则理解的会不深刻，一旦AlwaysOn 出现故障，首先根据错误日志，来排查是操作系统问题还是数据库问题；

部署AlwaysOn 分三部分：

1,操作系统层面； https://www.cnblogs.com/lvzf/p/10565298.html

2,数据库层面；    https://www.cnblogs.com/lvzf/p/10566598.html

3,维护AlwaysOn;   https://www.cnblogs.com/lvzf/p/10569857.html

本篇文章详细介绍- AlwaysOn 维护

接上文 SQL SERVER 2012 AlwaysOn - 数据库层面 02 下面开始对AlwaysOn 维护进行介绍；

1，新增数据库

可用性组添加数据库 Test

数据库初始化过程省略，详细请看上面【数据库层面 02 】 初始化过程

点击：AlwaysOn 高可用性 – 可用性组-可用性数据库-添加数据库

选中 Test 数据库 点击 【下一步】

选中 - 【仅联接】，点击 【下一步】

点击【全部联接】 联接 DB128，DB129 数据库实例；

下图显示 Test 数据库已经加入到可用性组 FlowCluster

2，可用性组故障转移

把可用性组从 DB124 转移到 DB128，可用性组的所有数据库做为一个整体转移 FlowCluster – 右键 – 故障转移 – 如下图：

选中 DB128，把主副本转移到 DB128 数据库服务器上，如下图：

故障转移成功；DB124 转移到 DB128
DB124 由原来的【主要角色】 转变成【辅助角色】
DB128 由原来的【辅助角色】转变成【主要角色】

Note:
-  前端程序写操作，如果直接联接【侦听器】， 那么故障转移后 会自动把连接转移到新的主角色；

-  前端程序写操作，如果直接联接主服务器角色，那么故障转移后，写操作字符连接配置文件需要修改到新的主角色；

-  如果前后端做了读写分离，直接联接【侦听器】，自动分发读写操作到指定的服务器，只读操作分发到只读数据库服务器；

部署AlwaysOn 分三部分：

1,操作系统层面； https://www.cnblogs.com/lvzf/p/10565298.html

2,数据库层面；    https://www.cnblogs.com/lvzf/p/10566598.html

3,维护AlwaysOn;   https://www.cnblogs.com/lvzf/p/10569857.html

SQL SERVER 2012 AlwaysOn– 数据库层面 02

搭建 AlwaysOn 是件非常繁琐的工作，需要从两方面考虑，操作系统层面和数据库层面，AlwaysOn 非常依赖于操作系统，域控，群集，节点等概念；

DBA 不但要熟悉数据库也要熟悉操作系统的一些概念；否则理解的会不深刻，一旦AlwaysOn 出现故障，首先根据错误日志，来排查是操作系统问题还是数据库问题；

部署AlwaysOn 分三部分：

1,操作系统层面； https://www.cnblogs.com/lvzf/p/10565298.html

2,数据库层面；    https://www.cnblogs.com/lvzf/p/10566598.html

3,维护AlwaysOn;   https://www.cnblogs.com/lvzf/p/10569857.html

本篇文章详细介绍- 数据库层面的部署；

接上文 SQL SERVER 2012 AlwaysOn - 操作系统层面 01 下面开始对AlwaysOn 数据库层面的部署

1，搭建AlwaysOn - 可用性组

1.1 安装 SQLSERVER 2012( - sp2)

点击【全新 SQLSERVER 独立安装…….】不要点击【新的 SQLSERER 故障转移群集安
装 】做 AlwaysOn 可用性组 不需要 SQLSERER 群集

安装 SP2 补丁包

1.2 配置 AlwaysOn

因为生产环境数据库较大，为了不影响业务运行，短时间内能创建 AlwaysOn ， 我们采用备份+还原方式配置 AlwaysOn ；

各个节点担当的角色如下：
主：   192.168.140.124
辅同：192.168.140.128
辅异：192.168.140.129

启用 AlwaysOn 可用性组 （三个节点做同样的操作）

右键 SQLSERVER 服务 – 属性-AlwaysOn 高可用性 – 启用 AlwaysOn 可用性组 ； 

- 重启 SQLSERER 服务（三个节点做同样的操作）

- AlwaysOn 初始化数据库

- DB124 创建数据库 Flow  

- 完整备份数据库[Flow] 

USE MASTER
GO
BACKUP DATABASE [Flow] TO DISK=''E:\Backup\Flow_full_20160122.bak'' WITH CHECKSUM,NOFORMAT,INIT,SKIP
GO

- 数据库[Flow]日志备份

USE MASTER
GO
BACKUP LOG [Flow] TO DISK=''E:\BackupLog\FlowLog_1.bak''
GO

- 把完整备份和日志备份拷贝到 DB128，DB129 服务器；

- DB128，DB129 还原数据库[Flow];( 注意：NORECOVERY 模式，非常重要 ) 

USE [master] 
GO
RESTORE DATABASE [Flow] FROM DISK = N''E:\Backup\Flow_full_20160122.bak'' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 
GO

- DB128，DB129 还原日志[UCardFlow];( 注意：NORECOVERY 模式，非常重要 )

USE MASTER
GO
RESTORE LOG [Flow] FROM DISK = N''E:\BackupLog\FlowLog_1.bak'' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 
GO

- 以 DB124 为主节点 创建 AlwaysOn

右键[AlwaysOn 高可用性] - 新建可用性组向导

点击 【下一步】,输入可用性组名称【FlowCluster】

点击【下一步】 选中 Flow 数据库

点击【下一步】添加副本，添加 DB128，DB129 副本

点击【端点】输入对应的 IP 地址

点击【备份首选项】 选中主副本

下一步，选中【仅联接】因为前期已经做完数据库初始化

下一步，出现警告（正在检查侦听器配置），不用理会，因为我们还没有配置侦听器；

下一步

点击【完成】
也可以点击【脚本】生成 SQL 脚本，下次利用脚本配置 AlwaysON 能快速完成

- 配置侦听器

DB124 – 可用性组侦听器-右键-添加侦听器

（因为我们前面没有配置侦听器，到这一步我们手动配置，其实道理一样）

输入侦听器 DNS 名称 DB150，端口 1433，IP 192.168.140.150

点击确定；

- 下图是整个 AODB 配置完成后 主服务器实例的情况；

查看 AlwaysOn 可用性组的详情

可用性组名称 – FlowCluster 右键 – 属性 如下图

我们是三个节点的 AlwaysOn ，其中 DB124 是主角色，DB128，DB129 是辅助角色
DB124 对外提供读写服务。DB128，DB129 对外提供只读服务，根据业务情况，选择具体
的只读服务器。其中 DB129 异步提交可以做为异地灾备服务器；

打开域控服务器 AD1074 DNS 记录，发现服务器 IP,以及群集，侦听器 IP 都记录在内

至此AlwaysOn 数据库层面配置完成；

部署AlwaysOn 分三部分：

1,操作系统层面； https://www.cnblogs.com/lvzf/p/10565298.html

2,数据库层面；    https://www.cnblogs.com/lvzf/p/10566598.html

3,维护AlwaysOn;   https://www.cnblogs.com/lvzf/p/10569857.html

SQL Server 2016 + AlwaysOn 无域集群

[========]

AlwaysOn

AlwaysOn 可用性组概述 (SQL Server)

搭建 WSFC

配置计算机的 DNS 后缀

1. 配置计算机的 DNS 后缀，注意在同个工作组
   
   
   
   

2. 每个节点的机器都要做域名解析，修改 host 文件 C:\Windows\System32\drivers\etc
   

安装故障转移集群

1. 节点服务器添加故障转移群集功能
   

2. 等待安装完成

验证集群

1. 打开故障转移集群管理工具
   
2. 点击验证配置
   
3. 添加集群节点
   
4. 运行所有测试
   
5. 可以点击查看验证报告，勾选立即使用经过验证的节点创建集群
   

创建集群

1. 配置集群的管理名称和管理 IP
   

2. 等待配置完成
   

3. 可以查看集群报告，稍后配置文件共享见证

创建文件共享见证

由于我们是两个节点的故障转移集群，所以需要加上共享文件夹，如果是奇数节点，这一步是不需要做的！

1. 配置集群仲裁
   
2. 选择文件共享见证
   
   
3. 在集群节点之外的一台服务器上创建共享文件夹 \\XIANGMU4TEST01\share，并设置 Everyone 完全控制的权限

配置 AlwaysOn

1. 开启 AlwaysOn
   
   

2. 需要重启 SQL Server 服务

3. 检查，如果 AlwaysOn 启用成功，在服务器属性里可以看到启用 HADR 为 True
   
   

4. 查看各节点的投票数

SELECT * FROM  sys.dm_hadr_cluster_members;

1. 查看 cluster

SELECT * FROM SYS.[dm_hadr_cluster]

新建可用性组

但是既然节点没有加入 AD，那么久不能用域认证，只能用证书认证，因此需要创建证书和端点。在配置可用性组前各节点进行证书认证信任。

1. 修改 SQL 服务登陆账号为本机管理员账号
   
   

2. 创建证书，两个节点都要创建证书，注意修改证书名称

--节点二：创建主密钥/证书，备份证书。
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''AAA111aaa'';
GO
CREATE CERTIFICATE Cert_server228
WITH SUBJECT = ''Cert_server228'',
START_DATE = ''2017-01-01'',EXPIRY_DATE = ''2099-12-30'';
GO
BACKUP CERTIFICATE Cert_server228
TO FILE = ''C:\Storage\Cert_server228.cer'';
GO

注意：备份证书的文件夹要先创建好，并且赋予权限

1. 将创建好的证书放到另一台节点服务器，并加入证书

--节点二：创建其他节点证书
USE master;
GO
CREATE CERTIFICATE Cert_server227
FROM FILE = ''C:\Storage\Cert_server227.cer'';
GO

证书创建好后如下

1. 重新创建端点，授权账号设置为本机管理员账号，验证方式使用上面创建的证书

--节点：创建端点
CREATE ENDPOINT [testag_endpoint]
    AUTHORIZATION [POSTEST2-2016\Administrator]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
    FOR DATA_MIRRORING
    (ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_server228, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

如果存在已有的端点，则需要将已有端点删除

1. 在主节点服务器操作新建可用性组。
   

2. 设置可用性组名称，建议勾选数据库级别运行状况检测。
   

• 在之前的 AlwaysOn 2012 和 2014 中，如果实例健康出现问题，将触发故障转移。如果有一个数据库有问题，只要实例 OK，可用性组就不会故障转移。
• 在 AlwaysOn 2016 中，勾选之后，不论是一个实例有问题，还是一个或多个数据库有问题，都会发生故障转移。

1. 选择数据库，要求数据库已经进行了完整备份
   

2. 添加副本节点，并勾选自动故障转移，设置辅助副本为可读副本
   

3. 将端点 URL 设置为 IP 方式，这里端点会显示为我们上面创建的端点，登陆账号为我们上面设置的账号
   

4. “备份首选项” 和 “侦听器” 不需要设置，保持默认就行，可用性侦听器我们后面再添加，可以直接点击 “下一步”

5. 数据同步这里建议使用完整的数据库和日志备份的方式更方便，需要有一个共享文件夹，并且节点服务器要有相同的数据库文件目录结构

6. 等待可用性组创建成功
   

7. 创建成功后数据库状态显示为已同步
   
   故障转移集群会显示拥有一个群集角色
   
   

8. 显示面板可以查看集群信息
   

创建侦听器

一个侦听器包括虚拟 IP 地址、虚拟网络名称、端口号三个元素，一旦创建成功，虚拟网络名称会注册到 DNS 中，同时为可用性组资源添加 IP 地址资源和网络名称资源。用户就可以使用此名称来连接到可用性组中。

1. 添加侦听器
   

2. 选择使用静态 IP
   

3. 创建成功后，在故障转移集群管理器里的角色节点，可以看到客户端访问名称和 IP 地址
   

4. 连接数据库时使用侦听器的地址
   

可读副本的负载均衡

SQL Server 2016 支持多个只读副本负载分担只读操作。

右键一个可用性副本可以查看副本的只读性设置：

主角色中的连接

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

• 允许所有连接（ALL）：主数据库同时允许读写连接和只读连接。这是主角色的默认行为。
• 仅允许读 / 写连接（READ_WRITE）：允许 ApplicationIntent=ReadWrite 或未设置连接条件的连接。不允许 ApplicationIntent=ReadOnly 的连接。仅允许读写连接可帮助防止客户错误地将读意向工作负荷连接到主副本。

修改脚本：

USE [master]
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON N''POSTEST1-2016\POSTEST'' WITH (PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE))
GO

可读辅助副本

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

• 无连接（NO）：不允许任何用户连接。辅助数据库不可用于读访问。这是辅助角色中的默认行为。
• 仅读意向连接（READ_ONLY）：辅助数据库仅接受连接参数 ApplicationIntent=ReadOnly 的连接，其它的连接方式无法连接。
• 允许任何只读连接（ALL）：辅助数据库全部可用于读访问连接。此选项允许较低版本的客户端进行连接。

修改脚本：

USE [master]
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON N''POSTEST1-2016\POSTEST'' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO

设置数据库实例监听端口

1. Sql Server 配置管理工具找到数据库实例的网络配置，
   

2. 右键 TCP/IP，默认配置的是动态端口
   

3. 这里修改为固定端口 1433
   

4. 最后需要重启 Sql Server 服务。

配置可用性组只读路由

执行下面 sql 获取可用性组名称：

select name,* from  sys.availability_groups

执行下面 sql 获取要配置的可用性副本名称（服务器实例）：

select replica_server_name,* from sys.availability_replicas

1. 为可用性副本（服务器实例）配置只读路由 URL，路由地址可以填 IP 或者计算机名，端口填数据库实例绑定的 TCP/IP 端口。
   执行脚本：

USE [master]
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON N''POSTEST1-2016\POSTEST'' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N''TCP://10.98.98.227:1433''))
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON N''POSTEST2-2016\POSTEST'' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N''TCP://10.98.98.228:1433''))
GO

也可以右键可用性组选择属性，通过 UI 方式更改

1. 每个自动转移副本配置只读路由列表

USE [master]
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON
N''POSTEST1-2016\POSTEST'' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((''POSTEST2-2016\POSTEST''),''POSTEST1-2016\POSTEST'')));
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON
N''POSTEST2-2016\POSTEST'' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((''POSTEST1-2016\POSTEST''),''POSTEST2-2016\POSTEST'')));
GO

使用 “()” 对只读路由列表进行分组，
List 1: ''POSTEST1-2016\POSTEST''
List 2: ''POSTEST2-2016\POSTEST''
按如下方式工作：

1. 路由到第一个列表中的副本，如果第一个列表存在多个副本，则对读取连接进行轮询分发。
2. 如果第一列表中任一副本不可用了，路由将继续到第一个列表中的其他可用副本。
3. 如果第一个列表中的所有副本都不可访问，将会路由到下一个列表

4. 如果第一个列表中的任一副本可用，将会恢复路由到第一个列表

5. 测试只读路由效果，sqlcmd 使用 Readonly 指定为只读连接，

sqlcmd -S 10.98.98.231 -K Readonly -d test -Q "select @@servername" -e

只读连接被连接到辅助副本

使用 SSMS 或者 EF 等需要在连接字符串设置参数 ApplicationIntent=ReadOnly

注意：
想要使用辅助副本负载读操作，必须要在连接字符串中配置为只读连接，然后在程序中指定使用只读连接进行数据库读取操作，AlwaysOn 自身不能自动负载均衡。

配置账号

为每个数据库节点配置登陆账号，然后通过监听器使用该账号登陆数据库。

1. 主副本创建登陆账号 satest

2. 查看主库上该账号的 sid 为库名

SELECT * FROM [test]..sysusers

1. 在副本数据库上创建对应账号，其中 sid 对应的值是主库上所查到的 sid 的值

CREATE LOGIN [satest] WITH PASSWORD=N''xxxxxxx'',
SID =0x99AD266AFD26F841B3E49EF9633B0D4B, DEFAULT_DATABASE=[test],
 CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

使用故障转移

建议使用 SSMS 中 AlwaysOn 进行手动故障转移。

今天关于SQL Server Alwayson创建代理作业的注意事项详解和sql server 新建代理作业步骤的介绍到此结束，谢谢您的阅读，有关AlwaysOn 进阶 Level 1：What is "SQL Server AlwaysOn"?、SQL SERVER 2012 AlwaysOn - 维护篇 03、SQL SERVER 2012 AlwaysOn– 数据库层面 02、SQL Server 2016 + AlwaysOn 无域集群等更多相关知识的信息可以在本站进行查询。