--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

-- Build Second Availability Group in the DR site.
:CONNECT SQL2K16-DAG01

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-DAG02

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-DAG01


CREATE AVAILABILITY GROUP [SQL2K16-AG2]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
		DB_FAILOVER = ON,
		DTC_SUPPORT = NONE)
FOR REPLICA ON 
N'SQL2K16-DAG01' WITH (ENDPOINT_URL = N'TCP://SQL2K16-DAG01.SQLskillsDemos.com:5022', 
						FAILOVER_MODE = AUTOMATIC, 
						AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
						BACKUP_PRIORITY = 50, 
						SEEDING_MODE = AUTOMATIC, 
						SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'SQL2K16-DAG02' WITH (ENDPOINT_URL = N'TCP://SQL2K16-DAG02.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-AG2]
ADD LISTENER N'SQL2K16-AGL2' 
(WITH IP ((N'192.168.10.36', N'255.255.255.0')), PORT=1433);
GO

:CONNECT SQL2K16-DAG02

ALTER AVAILABILITY GROUP [SQL2K16-AG2] JOIN;
GO

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