Post
Ask Question

SQL Server - Check If Always On Availability Of Groups Is Enabled Or Disabled

In this blog, we will simply see how to check if the "Always On" availability of Groups is enabled or disabled using "SERVERPROPERTY" in SQL Server.

What is "SERVERPROPERTY"?

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

"SERVERPROPERTY" Syntax

  1. SERVERPROPERTY ('propertyname')

ISHADRENABLED

Use ISHADRENABLED property to check the status of "Always On" availability of groups.
 0 The Always On availability groups is disabled
 1  The Always On availability groups is enabled

Example

  1. declare @IsHadrEnabled as sql_variant  
  2. set @IsHadrEnabled = (select SERVERPROPERTY('IsHadrEnabled'))  
  3. select @IsHadrEnabled as IsHadrEnabled,  
  4. case @IsHadrEnabled  
  5. when 0 then 'The Always On availability groups is disabled'  
  6. when 1 then 'The Always On availability groups is enabled'  
  7. else 'Invalid Input'  
  8. end as 'Hadr'  

 

Output

Check if the "Always On" availability of groups is enabled or disabled, using PowerShell

You can use Windows PowerShell to invoke 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.
  1. PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('IsHadrEnabled') as 'IsHadrEnabled'" -ServerInstance "epm\epmdb" -Username sa -Password *****

Applies To

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

Reference

See Also
Next Recommended Reading Enabling & Disabling xp_cmdshell in SQL SERVER