3.图形化创建会出现问题，导出脚本查看发现脚本不全

　　下面是创建AWO的脚本（前提是各个节点已经还原的备份文件）

　　注： 以下脚本可以通过SSMS工具切换到SQLCMD模式运行

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect sql16node1
IF (SELECT state FROM sys.endpoints WHERE name = N‘Hadr_endpoint‘) <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [NT Service\MSSQLSERVER]
GO
:Connect sql16node1
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=‘AlwaysOn_health‘)
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=‘AlwaysOn_health‘)
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect sql16node2
IF (SELECT state FROM sys.endpoints WHERE name = N‘Hadr_endpoint‘) <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [NT Service\MSSQLSERVER]
GO
:Connect sql16node2
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=‘AlwaysOn_health‘)
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=‘AlwaysOn_health‘)
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect sql16node3
IF (SELECT state FROM sys.endpoints WHERE name = N‘Hadr_endpoint‘) <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [NT Service\MSSQLSERVER]
GO
:Connect sql16node3
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=‘AlwaysOn_health‘)
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=‘AlwaysOn_health‘)
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect sql16node1
USE [master]
GO
CREATE AVAILABILITY GROUP [KK_AG_2016]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE)
FOR DATABASE [AWO_2016]
REPLICA ON N‘SQL16NODE1‘ WITH (ENDPOINT_URL = N‘TCP://SQL16NODE1:5022‘, FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
    N‘SQL16NODE2‘ WITH (ENDPOINT_URL = N‘TCP://SQL16NODE2:5022‘, FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
    N‘SQL16NODE3‘ WITH (ENDPOINT_URL = N‘TCP://SQL16NODE3:5022‘, FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO
------------这部分代码是图形化工具中缺失的步骤<br>
:Connect sql16node2
ALTER AVAILABILITY GROUP [KK_AG_2016] JOIN;
GO
ALTER DATABASE [AWO_2016] SET HADR AVAILABILITY GROUP = [KK_AG_2016];
GO
:Connect sql16node3
ALTER AVAILABILITY GROUP [KK_AG_2016] JOIN;
GO
ALTER DATABASE [AWO_2016] SET HADR AVAILABILITY GROUP = [KK_AG_2016];
GO<br><br><br>

创建监听

:Connect sql16node1
USE [master]
GO
ALTER AVAILABILITY GROUP [KK_AG_2016]
ADD LISTENER N‘L_KK_AWO2016‘ (
WITH IP
((N‘192.168.3.121‘, N‘255.255.255.0‘)
)
, PORT=8000);
GO

测试

高可用测试

　　简单的看起来与之前没有什么差别，依然依赖故障转移群集，仲裁的的方式与2012R2基本相同，可以使用仲裁磁盘、仲裁文件夹或动态节点投票（偶数个节点依然会动态分配投票权），值得关注的是在windows2016的集群中添加了云仲裁，这个云端配置先不说，真是对于之前配置的跨机房的集群的福音。

负载均衡测试

配置负载均衡（此处只是简单的做了一下主节点的负载）

alter availability group kk_ag_2016
modify replica on ‘sql16node1‘
with
(
primary_role
(
read_only_routing_list = ((‘sql16node2‘,‘sql16node3‘),‘sql16node1‘)
)
)
alter availability group kk_ag_2016
modify replica on N‘sql16node1‘
with
(
    secondary_role (read_only_routing_url = N‘tcp://sql16node1.kk.com:1433‘)
)
go
alter availability group kk_ag_2016
modify replica on N‘sql16node2‘
with
(
    secondary_role (read_only_routing_url = N‘tcp://sql16node2.kk.com:1433‘)
)
go
alter availability group kk_ag_2016
modify replica on N‘sql16node3‘
with
(
    secondary_role (read_only_routing_url = N‘tcp://sql16node3.kk.com:1433‘)
)
go

　　测试的例子比较简单，也没使用什么压力工具就直接用SSMS管理工具。

　　使用监听名称连接或监听IP加端口

　　另外必须在连接参数中指定ApplicationIntent=ReadOnly

　　开了5个窗口执行语句：

　　使用profiler抓取结果

　　只是看看能不能负载均衡，咋一看微软还真没骗人。但是要了解这只是只读副本的负载，而不是写入也可以负载！

　　另外要注意AlwaysOn的数据同步是有时间延迟的（就算是同步模式）！这点可以参见我另一篇的测试：AlwaysOn 同步时间的测试

--------------博客地址------------------------------------------------------------------------------