AlwaysON Commands/Scripts

 --Always ON Monitoring

select ag.name as "Availability Group"

 , ar.replica_server_name as "Source Replica"

 , ar2.replica_server_name as "Read-Only Destination"

 , ars.role_desc as "AG_Role"

 , al.dns_name "Listener"

 , ar.failover_mode_desc "Failover_Mode"

 , rl.routing_priority as "Routing Priority"

 , ar.secondary_role_allow_connections_desc as "Allowed Secondary Role"

 , ar2.read_only_routing_url as "Read-Only Routing Url"

 , ah.synchronization_health_desc "HealthStatus"

 , agl.ip_address "ListenerIPaddress"

 , ar.availability_mode_desc "Availability_Mode"

from sys.availability_read_only_routing_lists rl

 inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id

 inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id

 inner join sys.dm_hadr_availability_replica_states ars on ars.replica_id=ar.replica_id

 inner join sys.availability_groups ag on ar.group_id = ag.group_id

 inner join sys.dm_hadr_availability_group_states ah on ah.group_id = ag.group_id

 inner join sys.availability_group_listeners al on al.group_id = ag.group_id

 inner join sys.availability_group_listener_ip_addresses agl on agl.listener_id=al.listener_id

order by ag.name, ar.replica_server_name, rl.routing_priority

 

--Remove database from AG

ALTER DATABASE <SQLDBAHUB> SET HADR OFF; 

GO

 

POWER SHELL:

Remove-SqlAvailabilityDatabase ` 

-Path SQLSERVER:\Sql\SecondaryComputer\InstanceName\AvailabilityGroups\MyAg\Databases\MyDb8

 

Manual failover

ALTER AVAILABILITY GROUP <SQLDBAHUB-DB>  FAILOVER;

 

Manual failover with dataloss

ALTER AVAILABILITY GROUP [SQLDBAHUB] FORCE_FAILOVER_ALLOW_DATA_LOSS;

T-SQL to suspend data movement for the [SQLShackDemo] database.

ALTER DATABASE [SQLShackdemo] SET HADR SUSPEND;

GO

Resume data movement in SQL Server Always On Availability Groups

ALTER DATABASE [SQLShackdemo] SET HADR RESUME;

GO

--Listener Status with IP address

select dns_name ListenerName, ip_address ListernIPAddress, state_desc  from sys.availability_group_listener_ip_addresses A

Join sys.availability_group_listeners B  on A.listener_id=B.listener_id

--SUSPEND All databases involved into Always ON Group

-- Declare variables

DECLARE @DatabaseName NVARCHAR(255);

DECLARE @GroupName NVARCHAR(255) = 'FERACK_AG'; -- Replace 'YourAGName' with the name of your availability group

-- Declare cursor to iterate through all databases in the availability group

DECLARE db_cursor CURSOR FOR

SELECT database_name

FROM sys.dm_hadr_availability_replica_states ars

JOIN sys.availability_databases_cluster adc ON ars.group_id = adc.group_id

inner join sys.availability_groups ag on ars.group_id = ag.group_id

WHERE ars.is_local = 1  -- Only get databases for the local replica

AND ars.role_desc = 'PRIMARY'  -- Ensure it is the primary replica

AND ag.name = @GroupName;

 

-- Open the cursor

OPEN db_cursor;

 

-- Fetch the first database name

FETCH NEXT FROM db_cursor INTO @DatabaseName;

 

-- Loop through the databases and suspend them

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Print the database name being suspended

    PRINT 'Suspending data movement for database: ' + @DatabaseName;

   

    -- Suspend data movement for the database

       DECLARE @sql varchar(150)

       SET @sql ='ALTER DATABASE '+@DatabaseName+' SET HADR SUSPEND;'

       EXEC (@sql)

   

    -- Fetch the next database

    FETCH NEXT FROM db_cursor INTO @DatabaseName;

END

 

-- Close and deallocate the cursor

CLOSE db_cursor;

DEALLOCATE db_cursor;

GO

 

 

--RESUME All databases involved into Always ON Group

-- Declare variables

DECLARE @DatabaseName NVARCHAR(255);

DECLARE @GroupName NVARCHAR(255) = 'FERACK_AG'; -- Replace 'YourAGName' with the name of your availability group

 

-- Declare cursor to iterate through all databases in the availability group

DECLARE db_cursor CURSOR FOR

SELECT database_name

FROM sys.dm_hadr_availability_replica_states ars

JOIN sys.availability_databases_cluster adc ON ars.group_id = adc.group_id

inner join sys.availability_groups ag on ars.group_id = ag.group_id

WHERE ars.is_local = 1  -- Only get databases for the local replica

AND ars.role_desc = 'PRIMARY'  -- Ensure it is the primary replica

AND ag.name = @GroupName;

 

-- Open the cursor

OPEN db_cursor;

 

-- Fetch the first database name

FETCH NEXT FROM db_cursor INTO @DatabaseName;

 

-- Loop through the databases and suspend them

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Print the database name being suspended

    PRINT 'Suspending data movement for database: ' + @DatabaseName;

   

    -- Suspend data movement for the database

       DECLARE @sql varchar(150)

       SET @sql ='ALTER DATABASE '+@DatabaseName+' SET HADR RESUME;'

       EXEC (@sql)

   

    -- Fetch the next database

    FETCH NEXT FROM db_cursor INTO @DatabaseName;

END

 

-- Close and deallocate the cursor

CLOSE db_cursor;

DEALLOCATE db_cursor;

GO

 

--Remove All databases from Always ON Group

-- Declare a variable to store the availability group name

--DECLARE @AGName NVARCHAR(255);

DECLARE @GroupName NVARCHAR(255) = 'FERACK_AG'; -- Replace 'YourAGName' with the name of your availability group

-- Set the availability group name (replace 'YourAGName' with the actual name of your AG)

--SET @AGName = 'FERACK_AG';

 

-- Declare a variable to store database names

DECLARE @DatabaseName NVARCHAR(255);

 

-- Declare a cursor to iterate through all databases in the availability group

DECLARE db_cursor CURSOR FOR

SELECT database_name

FROM sys.dm_hadr_availability_replica_states ars

JOIN sys.availability_databases_cluster adc ON ars.group_id = adc.group_id

inner join sys.availability_groups ag on ars.group_id = ag.group_id

WHERE ars.is_local = 1  -- Only get databases for the local replica

AND ars.role_desc = 'PRIMARY'  -- Ensure it is the primary replica

AND ag.name = @GroupName;

 

 

-- Open the cursor

OPEN db_cursor;

 

-- Fetch the first database name

FETCH NEXT FROM db_cursor INTO @DatabaseName;

 

-- Loop through the databases in the availability group

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Alter the database to remove it from the availability group

    DECLARE @SQL NVARCHAR(500);

    SET @SQL = 'ALTER AVAILABILITY GROUP [' + @GroupName + '] REMOVE DATABASE '+ @DatabaseName + ';';

    PRINT 'Removing database ' + @DatabaseName + ' from Availability Group ' + @GroupName;

   

    -- Execute the SQL to remove the database from the Availability Group

   -- EXEC sp_executesql @SQL;

   Begin try

        exec (@SQL)

   end try

   begin catch

       print @DatabaseName

   end catch

 

    -- Fetch the next database

    FETCH NEXT FROM db_cursor INTO @DatabaseName;

END

 

-- Close and deallocate the cursor

CLOSE db_cursor;

DEALLOCATE db_cursor;

 

--ALTER AVAILABILITY GROUP [FERACK_AG]

REMOVE DATABASE [test];

 

 --Always ON Latency Check

SELECT  

       ar.replica_server_name,  

       adc.database_name,  

       ag.name AS ag_name,  

       drs.is_local,  

       drs.is_primary_replica,  

       drs.synchronization_state_desc,  

       drs.is_commit_participant,  

       drs.synchronization_health_desc,  

       drs.recovery_lsn,  

       drs.truncation_lsn,  

       drs.last_sent_lsn,  

       drs.last_sent_time,  

       drs.last_received_lsn,  

       drs.last_received_time,  

       drs.last_hardened_lsn,  

       drs.last_hardened_time,  

       drs.last_redone_lsn,  

       drs.last_redone_time,  

       drs.log_send_queue_size,  

       drs.log_send_rate,  

       drs.redo_queue_size,  

       drs.redo_rate,  

       drs.filestream_send_rate,  

       drs.end_of_log_lsn,  

       drs.last_commit_lsn,  

       drs.last_commit_time 

FROM sys.dm_hadr_database_replica_states AS drs 

INNER JOIN sys.availability_databases_cluster AS adc  

       ON drs.group_id = adc.group_id AND  

       drs.group_database_id = adc.group_database_id 

INNER JOIN sys.availability_groups AS ag 

       ON ag.group_id = drs.group_id 

INNER JOIN sys.availability_replicas AS ar  

       ON drs.group_id = ar.group_id AND  

       drs.replica_id = ar.replica_id 

ORDER BY  

       ag.name,  

       ar.replica_server_name,  

       adc.database_name;

 

 -- Query to get Always On Availability Group Endpoint Status

SELECT

    AGS.name AS [AG Name],

    AR.replica_server_name AS [Replica Server Name],

    DMENDPT.endpoint_id AS [Endpoint ID],

    DMENDPT.name AS [Endpoint Name],

    DMENDPT.state_desc AS [Endpoint State],

    DMENDPT.encryption_algorithm_desc AS [Encryption Algorithm]

FROM

    sys.availability_groups AS AGS

INNER JOIN

    sys.availability_replicas AS AR

    ON AGS.group_id = AR.group_id

INNER JOIN

    sys.database_mirroring_endpoints AS DMENDPT

    ON AR.replica_server_name = @@SERVERNAME  -- Matching replica with the current server

ORDER BY

    AGS.name, AR.replica_server_name;