--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect SQL2K16-BAG01

USE master; 
GO 
--Create the database Master Key, if needed.  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password';  
GO  
-- Make a certifcate on HOST_B server instance.  
CREATE CERTIFICATE BAG01_Cert   
   WITH SUBJECT = 'SQL2K16-BAG01 certificate for HADR',   
   EXPIRY_DATE = '12/31/2030';  
GO  
--Create a mirroring endpoint for the server instance on HOST_B.  
CREATE ENDPOINT [Hadr_endpoint]  
   STATE = STARTED  
   AS TCP (LISTENER_PORT=5022)   
   FOR DATA_MIRRORING (   
      AUTHENTICATION = CERTIFICATE BAG01_Cert  
      , ENCRYPTION = REQUIRED ALGORITHM AES  
      , ROLE = ALL );  
GO  
--Backup BAG01_Cert certificate.  
BACKUP CERTIFICATE BAG01_Cert TO FILE = 'C:\BAG01_Cert.cer';  
GO   

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

:Connect SQL2K16-BAG02

USE master; 
GO 
--Create the database Master Key, if needed.  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password';  
GO  
-- Make a certifcate on HOST_B server instance.  
CREATE CERTIFICATE BAG02_Cert   
   WITH SUBJECT = 'SQL2K16-BAG02 certificate for HADR',   
   EXPIRY_DATE = '12/31/2030';  
GO  
--Create a mirroring endpoint for the server instance on HOST_B.  
CREATE ENDPOINT [Hadr_endpoint]  
   STATE = STARTED  
   AS TCP (LISTENER_PORT=5022)   
   FOR DATA_MIRRORING (   
      AUTHENTICATION = CERTIFICATE BAG02_Cert  
      , ENCRYPTION = REQUIRED ALGORITHM AES  
      , ROLE = ALL );  
GO  
--Backup BAG02_Cert certificate.  
BACKUP CERTIFICATE BAG02_Cert TO FILE = 'C:\BAG02_Cert.cer';  
GO   

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

/*****************************************************

       COPY CERTIFICATES TO OTHER SERVERS

******************************************************/

:CONNECT SQL2K16-BAG01

USE master;  
--On BAG01, create a login for BAG02.  
CREATE LOGIN BAG02_login WITH PASSWORD = 'AStrongPassword!@#';  
GO  
--Create a user, BAG02_user, for that login.  
CREATE USER BAG02_user FOR LOGIN BAG02_login  
GO  
--Obtain BAG02 certificate. (See the note   
--   preceding this example.)  
--Asscociate this certificate with the user, BAG02_user.  
CREATE CERTIFICATE BAG02_cert  
   AUTHORIZATION BAG02_user  
   FROM FILE = 'C:\BAG02_cert.cer';  
GO  
--Grant CONNECT permission for the server instance on BAG02.  
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO BAG02_login  
GO  


:CONNECT SQL2K16-BAG02

USE master;  
--On BAG02, create a login for BAG01.  
CREATE LOGIN BAG01_login WITH PASSWORD = 'AStrongPassword!@#';  
GO  
--Create a user, BAG01_user, for that login.  
CREATE USER BAG01_user FOR LOGIN BAG01_login  
GO  
--Obtain BAG01 certificate. (See the note   
--   preceding this example.)  
--Asscociate this certificate with the user, BAG01_user.  
CREATE CERTIFICATE BAG01_cert  
   AUTHORIZATION BAG01_user  
   FROM FILE = 'C:\BAG01_cert.cer';  
GO  
--Grant CONNECT permission for the server instance on BAG01.  
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO BAG01_login  
GO  







:Connect SQL2K16-BAG01

USE [master]

GO

CREATE AVAILABILITY GROUP [SQL2K16-BAG1]
WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
BASIC,
DB_FAILOVER = ON,
DTC_SUPPORT = NONE)
FOR DATABASE [ConfigDB]
REPLICA ON N'SQL2K16-BAG01' WITH (ENDPOINT_URL = N'TCP://SQL2K16-BAG01:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
	N'SQL2K16-BAG02' WITH (ENDPOINT_URL = N'TCP://SQL2K16-BAG02:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

GO

:Connect SQL2K16-BAG01

USE [master]

GO

ALTER AVAILABILITY GROUP [SQL2K16-BAG1]
ADD LISTENER N'SQL2K16-BAGL1' (
WITH IP
((N'192.168.10.55', N'255.255.255.0')
)
, PORT=1433);

GO

:Connect SQL2K16-BAG02

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

