
:CONNECT SQL2K16-AGL1
ALTER AVAILABILITY GROUP SQL2K16-DAG 
MODIFY 
AVAILABILITY GROUP ON
'SQL2K16-AG1' WITH  
   ( 
    LISTENER_URL = 'TCP://SQL2K16-AGL1:5022',  
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
    FAILOVER_MODE = MANUAL, 
    SEEDING_MODE = MANUAL 
    ), 
'SQL2K16-AG2' WITH  
  ( 
  LISTENER_URL = 'TCP://SQL2K16-AGL2:5022', 
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
  FAILOVER_MODE = MANUAL, 
  SEEDING_MODE = MANUAL 
  );  



:CONNECT SQL2K16-AGL1
-- Check Status of synchronization 
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 AS drs
INNER JOIN sys.availability_groups AS ag ON drs.group_id = ag.group_id; 


    
-- Change Role to Secondary for current primary
ALTER AVAILABILITY GROUP SQL2K16-DAG SET (ROLE = SECONDARY); 



-- Test the failover readiness.. Synchronized and end_of_log_lsn matches
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 AS drs
INNER JOIN sys.availability_groups AS ag ON drs.group_id = ag.group_id; 


-- Failover the AG
ALTER AVAILABILITY GROUP SQL2K16-DAG FORCE_FAILOVER_ALLOW_DATA_LOSS; 