搭建 WSFC

1、配置计算机的 DNS 字尾，注意在同个工作组

2、每个节点的机器都要做域名解析，修改 host 档案C:WindowsSystem32driversetc

安装故障转移丛集

2、等待安装完成

验证丛集

2、点选验证配置

3、新增丛集节点

4、执行所有测试

5、可以点选检视验证报告，勾选立即使用经过验证的节点建立丛集

建立丛集

2、等待配置完成

3、可以检视丛集报告，稍后配置档案共享见证

建立档案共享见证

1、配置丛集仲裁

2、选择档案共享见证

3、在丛集节点之外的一台服务器上建立共享资料夹\XIANGMU4TEST01share，并设定 Everyone 完全控制的许可权

配置 AlwaysOn

2、需要重启 SQL Server 服务

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

4、检视各节点的投票数

SELECT * FROM sys.dm_hadr_cluster_members;

检视 cluster

SELECT * FROM SYS.[dm_hadr_cluster]

新建可用性组

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:StorageCert_server228.cer';

GO

注意：备份证书的资料夹要先建立好，并且赋予许可权

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

--节点二：建立其他节点证书

USE master;

GO

CREATE CERTIFICATE Cert_server227

FROM FILE = 'C:StorageCert_server227.cer';

GO

证书建立好后如下

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

--节点：建立端点

CREATE ENDPOINT [testag_endpoint]

AUTHORIZATION [POSTEST2-2016Administrator]

STATE=STARTED

AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)

FOR DATA_MIRRORING

(ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_server228, ENCRYPTION = REQUIRED ALGORITHM AES)

GO

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

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

设定可用性组名称，建议勾选数据库级别执行状况检测。

在之前的 AlwaysOn 2012 和 2014 中，如果例项健康出现问题，将触发故障转移。如果有一个数据库有问题，只要例项OK，可用性组就不会故障转移。在 AlwaysOn 2016 中，勾选之后，不论是一个例项有问题，还是一个或多个数据库有问题，都会发生故障转移。选择数据库，要求数据库已经进行了完整备份

新增副本节点，并勾选自动故障转移，设定辅助副本为可读副本

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

“备份首选项”和“侦听器”不需要设定，保持预设就行，可用性侦听器我们后面再新增，可以直接点选“下一步”

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

等待可用性组建立成功

建立成功后数据库状态显示为已同步

故障转移丛集会显示拥有一个群集角色

显示面板可以检视丛集资讯

建立侦听器

1、新增侦听器

2、选择使用静态 IP

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

4、连线数据库时使用侦听器的地址

可读副本的负载均衡

右键一个可用性副本可以检视副本的只读性设定：

主角色中的连线

主角色中支援的连线访问型别：

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

USE [master]

GO

ALTER AVAILABILITY GROUP [posTestAg]

MODIFY REPLICA ON N'POSTEST1-2016POSTEST' 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-2016POSTEST' 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

为可用性副本（服务器例项）配置只读路由 URL，路由地址可以填 IP 或者计算机名，埠填数据库例项系结的 TCP/IP 埠。执行指令码：USE [master]

GO

ALTER AVAILABILITY GROUP [posTestAg]

MODIFY REPLICA ON N'POSTEST1-2016POSTEST' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://10.98.98.227:1433'))

GO

ALTER AVAILABILITY GROUP [posTestAg]

MODIFY REPLICA ON N'POSTEST2-2016POSTEST' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://10.98.98.228:1433'))

GO

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

每个自动转移副本配置只读路由列表USE [master]

GO

ALTER AVAILABILITY GROUP [posTestAg]

MODIFY REPLICA ON

N'POSTEST1-2016POSTEST' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('POSTEST2-2016POSTEST'),'POSTEST1-2016POSTEST')));

GO

ALTER AVAILABILITY GROUP [posTestAg]

MODIFY REPLICA ON

N'POSTEST2-2016POSTEST' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('POSTEST1-2016POSTEST'),'POSTEST2-2016POSTEST')));

GO

使用“()”对只读路由列表进行分组，

List 1: 'POSTEST1-2016POSTEST'

List 2: 'POSTEST2-2016POSTEST'

按如下方式工作：

路由到第一个列表中的副本，如果第一个列表存在多个副本，则对读取连线进行轮询分发。如果第一列表中任一副本不可用了，路由将继续到第一个列表中的其他可用副本。如果第一个列表中的所有副本都不可访问，将会路由到下一个列表如果第一个列表中的任一副本可用，将会恢复路由到第一个列表测试只读路由效果，sqlcmd 使用 Readonly 指定为只读连线，sqlcmd -S 10.98.98.231 -K Readonly -d test -Q "select @@servername" -e

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

使用 SSMS 或者 EF 等需要在连线字串设定引数ApplicationIntent=ReadOnly

注意：

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

配置账号

主副本建立登陆账号 satest检视主库上该账号的 sid 为库名SELECT * FROM [test]..sysusers

在副本数据库上建立对应账号，其中 sid对应的值是主库上所查到的sid的值CREATE LOGIN [satest] WITH PASSWORD=N'xxxxxxx',

SID =0x99AD266AFD26F841B3E49EF9633B0D4B, DEFAULT_DATABASE=[test],

CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

使用故障转移

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

后面会分享更多devops和运维方面的内容，感兴趣的朋友可以关注一下~