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
- 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
- declare @IsHadrEnabled as sql_variant
- set @IsHadrEnabled = (select SERVERPROPERTY('IsHadrEnabled'))
- select @IsHadrEnabled as IsHadrEnabled,
- case @IsHadrEnabled
- when 0 then 'The Always On availability groups is disabled'
- when 1 then 'The Always On availability groups is enabled'
- else 'Invalid Input'
- 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.
- 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