AlwaysOn_2016_无域集群+负载均衡搭建与简单测试

--- 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
 
------------ 这部分代码是图形化工具中缺失的步骤 
: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

: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

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