Post
Ask Question

SQL Server - Check The "Always On" Availability Group Manager's Status

In this blog, we will simply show how to check the "Always On" availability group manager's status using "SERVERPROPERTY".

What's "SERVERPROPERTY"?

"SERVERPROPERTY" is a System-defined Function used to return the SQL Server instance information.

"SERVERPROPERTY" Syntax

  1. SERVERPROPERTY ('propertyname')

HADRMANAGERSTATUS

Use HADRMANAGERSTATUS property to check the "Always On" availability of group manager's status.

0Not started, pending
1Started and running
2Not started and failed

Example

  1. declare @HadrManagerStatus as sql_variant  
  2. set @HadrManagerStatus = (select SERVERPROPERTY('HadrManagerStatus'))  
  3. select @HadrManagerStatus as HadrManagerStatus,  
  4. case @HadrManagerStatus  
  5. when 0 then 'Not started, pending'  
  6. when 1 then 'Started and running'  
  7. when 2 then 'Not started and failed'  
  8. else 'Invalid Input'  
  9. end as 'HadrManager Status'  

Output

Check the "Always On" availability of group manager status using PowerShell?

You can use Windows PowerShell to invoke the SQL command on a reachable server within the network using Invoke-Sqlcmd cmdlet as the following.
  • Open Windows PowerShell as Administrator.
  • Type the Invoke-Sqlcmd with the below parameters.

    • -query: the SQL query that you need to run on the remote server.
    • -ServerInstance: the SQL server instance name.
    • -Username: the username that has sufficient permission to access and execute SQL query on the remote server.
    • -Password: the password of the elevated user.

      PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('HadrManagerStatus') as 'HadrManagerStatus'" -ServerInstance "epm\epmdb" -Username sa -Password *****

Applies To

  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

Reference

See Also