--Pa$$w0rd
--P@$$w0rd
\\192.168.71.31\c$
DAGLAB01
AG01LAB01 - 192.168.71.31
AG01LAB02 - 192.168.71.32
AG01LAB01 - 192.168.71.61
AG02LAB02 - 192.168.71.62
-Don't forget the gateway on all networks

------------------------------------------------------------------------------
--Regular AGs
--on primary
--Primary AG
USE [master]
GO
CREATE AVAILABILITY GROUP [LocalAG01]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = ON,
DTC_SUPPORT = NONE,
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0)
FOR 
REPLICA ON N'AG01LAB01' WITH (ENDPOINT_URL = N'TCP://AG01LAB01.:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
	N'AG01LAB02' WITH (ENDPOINT_URL = N'TCP://AG01LAB02.:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO

--on secondary
alter AVAILABILITY GROUP [LocalAG01] join

--Secondary AG

CREATE AVAILABILITY GROUP [LocalAG02]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = ON,
DTC_SUPPORT = NONE,
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0)
FOR 
REPLICA ON N'AG02LAB01' WITH (ENDPOINT_URL = N'TCP://AG02LAB01.:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
	N'AG02LAB02' WITH (ENDPOINT_URL = N'TCP://AG02LAB02.:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO

--on secondary
alter AVAILABILITY GROUP [LocalAG02] join

-----------------------------------------------------------------------------
--Listeners

USE [master]
GO
ALTER AVAILABILITY GROUP [LocalAG01]
ADD LISTENER N'LocalAG01Listen' (
WITH IP
((N'192.168.71.111', N'255.255.255.0')
)
, PORT=7022);
GO

--Listeners

USE [master]
GO
ALTER AVAILABILITY GROUP [LocalAG02]
ADD LISTENER N'LocalAG02Listen' (
WITH IP
((N'192.168.71.112', N'255.255.255.0')
)
, PORT=7022);
GO
-------------------------------------------------------------------------------
--Add Databases
--Prepare database 
alter database AdventureWorksSmall set recovery FULL;
go
backup database AdventureWorksSmall to disk = 'NUL'



-----------------------------------------------------------------------------
--Distributed AGs
--on primary AG:
drop AVAILABILITY GROUP DAGLAB


CREATE AVAILABILITY GROUP DAGLAB  
   WITH (DISTRIBUTED)   
   AVAILABILITY GROUP ON  
      'LocalAG01' WITH    
      (   
         LISTENER_URL = 'tcp://LocalAG01Listen:5022',    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'LocalAG02' WITH    
      (   
         LISTENER_URL = 'tcp://LocalAG02Listen:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
GO


--on secondary AG:

drop AVAILABILITY GROUP DAGLAB


alter AVAILABILITY GROUP DAGLAB  
   join
   AVAILABILITY GROUP ON  
      'LocalAG01' WITH    
      (   
         LISTENER_URL = 'tcp://LocalAG01Listen:5022',    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'LocalAG02' WITH    
      (   
         LISTENER_URL = 'tcp://LocalAG02Listen:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
GO
alter availability group LocalAG02 grant create any database --all Replicas


--if needed:
alter database Adventureworks set hadr availability group = [DAGLAB]

alter database AdventureworksSmall set hadr availability group = LocalAG02 --all replicas

------------------------------------------------------------------------------------------------------------------------------------
--add monitoring queries here:

-- shows replicas associated with availability groups
SELECT 
   ag.[name] AS [AG Name], 
   ag.Is_Distributed, 
   ar.replica_server_name AS [Replica Name]
FROM sys.availability_groups AS ag 
INNER JOIN sys.availability_replicas AS ar       
   ON ag.group_id = ar.group_id
GO

-- shows sync status of distributed AG
SELECT 
   ag.[name] AS [AG Name], 
   ag.is_distributed, 
   ar.replica_server_name AS [Underlying AG], 
   ars.role_desc AS [Role], 
   ars.synchronization_health_desc AS [Sync Status]
FROM  sys.availability_groups AS ag
INNER JOIN sys.availability_replicas AS ar 
   ON  ag.group_id = ar.group_id        
INNER JOIN sys.dm_hadr_availability_replica_states AS ars       
   ON  ar.replica_id = ars.replica_id
WHERE ag.is_distributed = 1
GO

-- shows underlying performance of distributed AG
SELECT 
   ag.[name] AS [Distributed AG Name], 
   ar.replica_server_name AS [Underlying AG], 
   dbs.[name] AS [Database],
   ars.role_desc AS [Role],
   drs.synchronization_health_desc AS [Sync Status],
   drs.log_send_queue_size,
   drs.log_send_rate, 
   drs.redo_queue_size, 
   drs.redo_rate
FROM sys.databases AS dbs
INNER JOIN sys.dm_hadr_database_replica_states AS drs
   ON dbs.database_id = drs.database_id
INNER JOIN sys.availability_groups AS ag
   ON drs.group_id = ag.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ars
   ON ars.replica_id = drs.replica_id
INNER JOIN sys.availability_replicas AS ar
   ON ar.replica_id = ars.replica_id
WHERE ag.is_distributed = 1
GO

-- displays sync status, send rate, and redo rate of availability groups, including distributed AG
SELECT 
       ag.name AS 'AG Name', 
       ag.is_distributed, 
       ar.replica_server_name AS 'AG', 
       dbs.name AS 'Database',
   ars.role_desc, 
	drs.synchronization_health_desc, 
	drs.log_send_queue_size, 
	drs.log_send_rate, 
	drs.redo_queue_size, 
	drs.redo_rate,
	drs.suspend_reason_desc,
	drs.last_sent_time,
	drs.last_received_time,
	drs.last_hardened_time,
	drs.last_redone_time,
	drs.last_commit_time,
	drs.secondary_lag_seconds
FROM sys.databases dbs 
INNER JOIN sys.dm_hadr_database_replica_states drs 
   ON dbs.database_id = drs.database_id
INNER JOIN sys.availability_groups ag 
   ON drs.group_id = ag.group_id
INNER JOIN sys.dm_hadr_availability_replica_states ars 
   ON ars.replica_id = drs.replica_id
INNER JOIN sys.availability_replicas ar 
   ON ar.replica_id =  ars.replica_id
--WHERE ag.is_distributed = 1
GO

-- shows endpoint url and sync state for ag, and dag
SELECT
   ag.name AS group_name,
   ag.is_distributed,
   ar.replica_server_name AS replica_name,
   ar.endpoint_url,
   ar.availability_mode_desc,
   ar.failover_mode_desc,
   ar.primary_role_allow_connections_desc AS allow_connections_primary,
   ar.secondary_role_allow_connections_desc AS allow_connections_secondary,
   ar.seeding_mode_desc AS seeding_mode
FROM sys.availability_replicas AS ar
JOIN sys.availability_groups AS ag
   ON ar.group_id = ag.group_id
GO

-- shows current_state of seeding 
SELECT
   ag.name AS aag_name,
   ar.replica_server_name,
   d.name AS database_name,
   has.current_state,
   has.failure_state_desc AS failure_state,
   has.error_code,
   has.performed_seeding,
   has.start_time,
   has.completion_time,
   has.number_of_attempts
FROM sys.dm_hadr_automatic_seeding AS has
JOIN sys.availability_groups AS ag
   ON ag.group_id = has.ag_id
JOIN sys.availability_replicas AS ar
   ON ar.replica_id = has.ag_remote_replica_id
JOIN sys.databases AS d
   ON d.group_database_id = has.ag_db_id
GO

--check the thruput of the SEEDING_MODE
IF OBJECT_ID('tempdb..#Seeding') IS NOT NULL DROP TABLE #Seeding;

SELECT  GETDATE() AS CollectionTime,
        instance_name,
        cntr_value
INTO    #Seeding
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Backup/Restore Throughput/sec';

WAITFOR DELAY '00:00:05'

SELECT  LTRIM(RTRIM(p2.instance_name)) AS [DatabaseName],
        (p2.cntr_value - p1.cntr_value) / (DATEDIFF(SECOND,p1.CollectionTime,GETDATE())) AS ThroughputBytesSec
FROM    sys.dm_os_performance_counters AS p2
        INNER JOIN #Seeding AS p1
            ON p2.instance_name = p1.instance_name
WHERE   p2.counter_name LIKE 'Backup/Restore Throughput/sec%'
ORDER BY
        ThroughputBytesSec DESC;


--to failover
--alter to sync

-- sets the distributed availability group to synchronous commit 
 ALTER AVAILABILITY GROUP [DAGLAB] 
 MODIFY 
 AVAILABILITY GROUP ON
 'ag01' WITH 
  ( 
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
  ), 
  'ag02' WITH  
  ( 
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
  );

 -- verifies the commit state of the distributed availability group
 select ag.name, ag.is_distributed, ar.replica_server_name, ar.availability_mode_desc, ars.connected_state_desc, ars.role_desc, 
 ars.operational_state_desc, ars.synchronization_health_desc from sys.availability_groups ag  
 join sys.availability_replicas ar on ag.group_id=ar.group_id
 left join sys.dm_hadr_availability_replica_states ars
 on ars.replica_id=ar.replica_id
 where ag.is_distributed=1
 GO

ALTER AVAILABILITY GROUP distributedag SET (ROLE = SECONDARY); 

--test failover readiness

SELECT ag.name, 
    drs.database_id, 
    drs.group_id, 
    drs.replica_id, 
    drs.synchronization_state_desc, 
    drs.end_of_log_lsn 
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id; 



ALTER AVAILABILITY GROUP DAGLAB FORCE_FAILOVER_ALLOW_DATA_LOSS;