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

---------------------------
-- *** Add Endpoints *** --
---------------------------

:CONNECT SQL2K12-SVR1
USE [master];
GO

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

GO

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 [SQLSKILLSDEMOS\SQLServiceAcct]
GO

:CONNECT SQL2K12-SVR2

USE [master]

GO

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

GO

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 [SQLSKILLSDEMOS\SQLServiceAcct]
GO

:CONNECT SQL2K12-SVR3

USE [master]
GO

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

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 [SQLSKILLSDEMOS\SQLServiceAcct]
GO

:CONNECT SQL2K12-SVR4

USE [master]
GO

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

GO

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 [SQLSKILLSDEMOS\SQLServiceAcct]
GO


-------------------------------------------
-- *** ended event sessions are enabled ***
-------------------------------------------

:CONNECT SQL2K12-SVR1

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 SQL2K12-SVR2

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 SQL2K12-SVR3

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 SQL2K12-SVR4

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




-------------------------------------------
-- *** Create the AG ***
-------------------------------------------

:CONNECT SQL2K12-SVR1

USE [master]
GO

CREATE AVAILABILITY GROUP [SQL2K12-AG1]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY_ONLY)
FOR DATABASE [ConfigDB], [ERPDatabase]
REPLICA ON 
N'SQL2K12-SVR1' 
WITH (ENDPOINT_URL = N'TCP://SQL2K12-SVR1.sqlskillsdemos.com:5022', 
	  FAILOVER_MODE = AUTOMATIC, 
	  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	  BACKUP_PRIORITY = 0, 
	  SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'SQL2K12-SVR2' 
WITH (ENDPOINT_URL = N'TCP://SQL2K12-SVR2.sqlskillsdemos.com:5022', 
	FAILOVER_MODE = AUTOMATIC, 
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	BACKUP_PRIORITY = 100, 
	SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'SQL2K12-SVR3' 
WITH (ENDPOINT_URL = N'TCP://SQL2K12-SVR3.sqlskillsdemos.com:5022', 
	FAILOVER_MODE = MANUAL, 
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	BACKUP_PRIORITY = 50, 
	SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)),
N'SQL2K12-SVR4' 
WITH (ENDPOINT_URL = N'TCP://SQL2K12-SVR4.sqlskillsdemos.com:5022', 
	FAILOVER_MODE = MANUAL, 
	AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
	BACKUP_PRIORITY = 50, 
	SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY));

GO

-------------------------------------------
-- *** Create the AG Listener ***
-------------------------------------------

:CONNECT SQL2K12-SVR1

USE [master]
GO

ALTER AVAILABILITY GROUP [SQL2K12-AG1]
ADD LISTENER N'SQL2K12-AGList' 
(WITH IP
((	N'192.168.10.77', 
	N'255.255.255.0')
)
, PORT=1433);
GO

-------------------------------------------
-- *** Join each replica to the AG ***
-------------------------------------------

:CONNECT SQL2K12-SVR2

ALTER AVAILABILITY GROUP [SQL2K12-AG1] JOIN;
GO

:CONNECT SQL2K12-SVR3

ALTER AVAILABILITY GROUP [SQL2K12-AG1] JOIN;
GO

:CONNECT SQL2K12-SVR4

ALTER AVAILABILITY GROUP [SQL2K12-AG1] JOIN;
GO

-----------------------------------------------------------
-- *** Seed each Replica for each Availability Database ***
-----------------------------------------------------------

:CONNECT SQL2K12-SVR1

BACKUP DATABASE [ConfigDB] 
TO  DISK = N'\\SQL2K12-SVR1\Backup\ConfigDB.bak' 
WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO

:CONNECT SQL2K12-SVR2

RESTORE DATABASE [ConfigDB] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ConfigDB.bak' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

:CONNECT SQL2K12-SVR3

RESTORE DATABASE [ConfigDB] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ConfigDB.bak'
 WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

:CONNECT SQL2K12-SVR4

RESTORE DATABASE [ConfigDB] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ConfigDB.bak' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO


:CONNECT SQL2K12-SVR1

BACKUP LOG [ConfigDB] 
TO  DISK = N'\\SQL2K12-SVR1\Backup\ConfigDB.trn' 
WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

:CONNECT SQL2K12-SVR2

RESTORE LOG [ConfigDB] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ConfigDB.trn'
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:CONNECT SQL2K12-SVR3

RESTORE LOG [ConfigDB] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ConfigDB.trn'
 WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:CONNECT SQL2K12-SVR4

RESTORE LOG [ConfigDB] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ConfigDB.trn' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO


:CONNECT SQL2K12-SVR1

BACKUP DATABASE [ERPDatabase] 
TO  DISK = N'\\SQL2K12-SVR1\Backup\ERPDatabase.bak' 
WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

:CONNECT SQL2K12-SVR2

RESTORE DATABASE [ERPDatabase] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ERPDatabase.bak' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:CONNECT SQL2K12-SVR3

RESTORE DATABASE [ERPDatabase] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ERPDatabase.bak' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:CONNECT SQL2K12-SVR4

RESTORE DATABASE [ERPDatabase] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ERPDatabase.bak'
 WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO


:CONNECT SQL2K12-SVR1

BACKUP LOG [ERPDatabase] 
TO  DISK = N'\\SQL2K12-SVR1\Backup\ERPDatabase.trn' 
WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

:CONNECT SQL2K12-SVR2

RESTORE LOG [ERPDatabase] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ERPDatabase.trn' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:CONNECT SQL2K12-SVR3

RESTORE LOG [ERPDatabase] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ERPDatabase.trn' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

:CONNECT SQL2K12-SVR4

RESTORE LOG [ERPDatabase] 
FROM  DISK = N'\\SQL2K12-SVR1\Backup\ERPDatabase.trn' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO


----------------------------------------------------
-- *** Join the availability databases to the AG ***
----------------------------------------------------

-- Join Availability Databases
:CONNECT SQL2K12-SVR2
ALTER DATABASE [ConfigDB] SET HADR AVAILABILITY GROUP = [SQL2K12-AG1];
ALTER DATABASE [ERPDatabase] SET HADR AVAILABILITY GROUP = [SQL2K12-AG1];
GO

:CONNECT SQL2K12-SVR3
ALTER DATABASE [ConfigDB] SET HADR AVAILABILITY GROUP = [SQL2K12-AG1];
ALTER DATABASE [ERPDatabase] SET HADR AVAILABILITY GROUP = [SQL2K12-AG1];
GO

:CONNECT SQL2K12-SVR4
ALTER DATABASE [ConfigDB] SET HADR AVAILABILITY GROUP = [SQL2K12-AG1];
ALTER DATABASE [ERPDatabase] SET HADR AVAILABILITY GROUP = [SQL2K12-AG1];
GO

-- How do things look in Object Explorer?

