非域需要在Windos Server 2016
ip配置：
192.168.238.150 WIN-150
192.168.238.151 WIN-151
192.168.238.152 WIN-152
192.168.238.153 hzmc.noad.com（故障转移群集）
192.168.238.154 aglistener（侦听）
alwayson可用性组名叫 SQLAG
1.首先3台机子都用administrator账号登陆且密码相同
2.分别配置好网卡设置
内网网卡：只能用自动获取，不能设置静态IP（如果不是用云主机 实际上最好用静态IP），ipv6去掉，禁用TCP/IP上的NetBIOS，设置DNS后缀 wlh.com
3.3台机子全部添加故障转移群集功能
4.每个节点的计算机不需要加入域，但需要添加DNS后缀，且每个节点的后缀必须要相同
5.在每个节点上都添加一个用户，且用户名以及密码每个节点都一致（在计算机管理里设置）
6.在每个机子的hosts文件里添加解析（host文件在windows/system32/drivers/etc/host）
192.168.238.150 WIN-150.wlh.com
192.168.238.151 WIN-151.wlh.com
192.168.238.152 WIN-152.wlh.com
192.168.238.150 WIN-150
192.168.238.151 WIN-151
192.168.238.152 WIN-152
192.168.238.153 hzmc.noad.com
192.168.238.153 hzmc.noad.com.wlh.com
192.168.238.154 aglistener
192.168.238.154 aglistener.wlh.com
7.防火墙设置
8.故障转移群集创建
在1节点上，用以管理员方式运行Power Shell ，使用脚本创建Windows群集。因为无域的群集没有图形化界面可设置，所以只能使用PowreShell 。
注意：如果登陆Windows Server 2016服务器的账户不是Administrator，需要先以管理员方式运行PowerShell，执行下面的命令：
new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
因为我登陆Windows Server 2016用的是Administrator，所以跳过执行上面的命令，直接运行下面的命令：
Power Shell 脚本： New-Cluster -Name hzmc.noad.com -Node WIN-150,WIN-151,WIN-152 -StaticAddress 192.168.238.153 -AdministrativeAccessPoint DNS（需要修改的，具体情况具体分析，跑之前记得确认前面改完机器名有没有重启）
--获取集群名
Get-Cluster
--群集详情
Get-ClusterResource
9.连接故障转移群集（手动）
10.安装SQL Server和SSMS
步骤见其他文档
下面三个取消
11.创建一个共享文件夹，dcadmin给权限
12.开启alwayson，登陆身份为administrator
13.重启服务后，查看服务器属性，确保 HADR 为 True
14.在每台机子上创建证书和端点
既然节点没有加入域，那么就不能用域认证，只能用证书认证，因此需要创建证书和端点。
因此在配置可用性组前先在各节点配置证书认证信任。
结点1：创建主密钥/证书/端点，备份证书。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Hzmc321#'; ----密码
GO
CREATE CERTIFICATE Cert_DB01
WITH SUBJECT = 'Cert_DB01',
START_DATE = '2019-07-18',EXPIRY_DATE = '2099-12-31';
GO
BACKUP CERTIFICATE Cert_DB01
TO FILE = '\\WIN-150\aa\Cert_DB01.cer';
GO
CREATE ENDPOINT [SQLAG_Endpoint]
AUTHORIZATION [WIN-150\administrator]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING
(ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_DB01, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
结点2：创建主密钥/证书/端点，备份证书。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Hzmc321#'; ----密码
GO
CREATE CERTIFICATE Cert_DB02
WITH SUBJECT = 'Cert_DB02',
START_DATE = '2019-07-18',EXPIRY_DATE = '2099-12-31';
GO
BACKUP CERTIFICATE Cert_DB02
TO FILE = '\\WIN-150\aa\Cert_DB02.cer';
GO
CREATE ENDPOINT [SQLAG_Endpoint]
AUTHORIZATION [WIN-151\administrator]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING
(ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_DB02, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
结点3：创建主密钥/证书/端点，备份证书。
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Hzmc321#'; ----密码
GO
CREATE CERTIFICATE Cert_DB03
WITH SUBJECT = 'Cert_DB03',
START_DATE = '2019-07-18',EXPIRY_DATE = '2099-12-31';
GO
BACKUP CERTIFICATE Cert_DB03
TO FILE = '\\WIN-150\aa\Cert_DB03.cer';
GO
CREATE ENDPOINT [SQLAG_Endpoint]
AUTHORIZATION [WIN-152\administrator]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING
(ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_DB03, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
节点1上执行：创建节点2,3的证书（备份从共享文件夹拉到了本地的ZS）
CREATE CERTIFICATE Cert_DB02
FROM FILE = 'C:\ZS\Cert_DB02.cer';
GO
CREATE CERTIFICATE Cert_DB03
FROM FILE = 'C:\ZS\Cert_DB03.cer';
GO
节点2上执行：创建节点1,3的证书
CREATE CERTIFICATE Cert_DB01
FROM FILE = 'C:\ZS\Cert_DB01.cer';
GO
CREATE CERTIFICATE Cert_DB03
FROM FILE = 'C:\ZS\Cert_DB03.cer';
GO
节点3上执行：创建节点1,2的证书
CREATE CERTIFICATE Cert_DB01
FROM FILE = 'C:\ZS\Cert_DB01.cer';
GO
CREATE CERTIFICATE Cert_DB02
FROM FILE = 'C:\ZS\Cert_DB02.cer';
GO
15.备份还原好库，然后配置可用性组
16.AlwaysOn完成
17.添加侦听器
18.读写分离
主、辅助副本改主角色中的连接：
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'WIN-150' WITH (PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'WIN-151' WITH (PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'WIN-152' WITH (PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO
等于把这个允许读/写连接改成允许所有连接
主、辅助副本改可读辅助副本：
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'WIN-150' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'WIN-150' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'WIN-150' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO
等于把仅读意向改成是
配置端口
Sql Server 配置管理工具找到数据库实例的网络配置，若是动态端口，则改成固定端口1433
为可用性副本（服务器实例）配置只读路由 URL，路由地址可以填 IP ，端口填数据库实例绑定的 TCP/IP 端口。（先加完只读路由URL点了确定 再来加只读路由列表）（可负载分担）
2016开始支持使用路由列表让多个只读副本同时分担只读操作
语句改：
只读路由URL：
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'WIN-150' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://192.168.238.150:1433'))
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'WIN-151' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://192.168.238.151:1433'))
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'WIN-152' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://192.168.238.152:1433'))
只读路由列表：
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'WIN-150' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('WIN-151','WIN-152'),'WIN-150')));
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'WIN-151' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('WIN-150','WIN-152'),'WIN-151')));
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'WIN-152' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('WIN-150','WIN-151'),'WIN-152')));
查询优先级关系：
SELECT ar.replica_server_name ,
rl.routing_priority ,
( SELECT ar2.replica_server_name
FROM sys.availability_read_only_routing_lists rl2
JOIN sys.availability_replicas AS ar2 ON rl2.read_only_replica_id = ar2.replica_id
WHERE rl.replica_id = rl2.replica_id
AND rl.routing_priority = rl2.routing_priority
AND rl.read_only_replica_id = rl2.read_only_replica_id
) AS 'read_only_replica_server_name'
FROM sys.availability_read_only_routing_lists rl
JOIN sys.availability_replicas AS ar ON rl.replica_id = ar.replica_id
图形化界面改：
2616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
