--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:CONNECT SQL2K16-AG01

USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint] 
	AS TCP (LISTENER_PORT = 5022)
	FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SQLskillsDemos\gMSAsqlservice$]
GO

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
GO

:CONNECT SQL2K16-AG02

USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint] 
	AS TCP (LISTENER_PORT = 5022)
	FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SQLskillsDemos\gMSAsqlservice$]
GO

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
GO

:CONNECT SQL2K16-AG03

USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint] 
	AS TCP (LISTENER_PORT = 5022)
	FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SQLskillsDemos\gMSAsqlservice$]
GO

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
GO



:CONNECT SQL2K16-AG01

USE [master]
GO

CREATE AVAILABILITY GROUP [SQL2K16-AG1]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
		DB_FAILOVER = ON,
		DTC_SUPPORT = NONE)
FOR DATABASE [ConfigDB]
REPLICA ON 
N'SQL2K16-AG01' WITH (ENDPOINT_URL = N'TCP://SQL2K16-AG01.SQLskillsDemos.com:5022', 
						FAILOVER_MODE = AUTOMATIC, 
						AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
						BACKUP_PRIORITY = 50, 
						SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'SQL2K16-AG02' WITH (ENDPOINT_URL = N'TCP://SQL2K16-AG02.SQLskillsDemos.com:5022', 
						FAILOVER_MODE = AUTOMATIC, 
						AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
						BACKUP_PRIORITY = 50, 
						SEEDING_MODE = AUTOMATIC, 
						SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'SQL2K16-AG03' WITH (ENDPOINT_URL = N'TCP://SQL2K16-AG03.SQLskillsDemos.com:5022', 
						FAILOVER_MODE = AUTOMATIC, 
						AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
						BACKUP_PRIORITY = 50, 
						SEEDING_MODE = AUTOMATIC, 
						SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

GO

ALTER AVAILABILITY GROUP [SQL2K16-AG1]
ADD LISTENER N'SQL2K16-AGL1' (
WITH IP ((N'192.168.10.35', N'255.255.255.0')), PORT=1433);
GO

:CONNECT SQL2K16-AG02

ALTER AVAILABILITY GROUP [SQL2K16-AG1] JOIN;
GO
ALTER AVAILABILITY GROUP [SQL2K16-AG1] GRANT CREATE ANY DATABASE;
GO

:CONNECT SQL2K16-AG03

ALTER AVAILABILITY GROUP [SQL2K16-AG1] JOIN;
GO
ALTER AVAILABILITY GROUP [SQL2K16-AG1] GRANT CREATE ANY DATABASE;
GO



