'How to check if SQLAlwaysON is existing in server using powershell without a SQL DBA Admin access?

I want to check if SQLAlwaysON exists in server.

I already know that typing and getting the powershell commands Enable-SQLServerAlwaysON and Disabled-SQLServerAlwaysON are a great hint for that. But it won't work if there's no SQLAlwaysON tool and I don't want to Enable or Disable it.

Appreciate if someone can provide a simple but very good idea to check if SQLAlwaysON exists using powershell command. Thanks a lot!

SQLAlwaysON check

UPDATE:

I posted my solution below because the account that I use in checking is not a SQL DB Admin account and there's no way I could get an access inside the database server (security related).

sqldatabasepowershell


Solution 1:[1]

I think something like this can work for you:

if ( ( Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled" -ServerInstance "localhost" ).IsHadrEnabled ) {
    Write-Host "HADR is enabled."
}
else {
    Write-Host "HADR is NOT enabled."
}

References:

Invoke-Sqlcmd

SERVERPROPERTY (Transact-SQL) -> IsHadrEnabled

IsHadrEnabled

Applies to: SQL Server 2012 (11.x) and later.

Always On availability groups is enabled on this server instance.

0 = The Always On availability groups feature is disabled.

1 = The Always On availability groups feature is enabled.

NULL = Input is not valid, an error, or not applicable.

Base data type: int

For availability replicas to be created and run on an instance of SQL Server, Always On availability groups must be enabled on the server instance. For more information, see Enable and Disable AlwaysOn Availability Groups (SQL Server).

Note: The IsHadrEnabled property pertains only to Always On availability groups. Other high availability or disaster recovery features, such as database mirroring or log shipping, are unaffected by this server property.

Solution 2:[2]

A little late, but someone might find it useful, no SQL permissions required:

if (Get-ClusterResource -Cluster "CLUSTER ADDRESS" | `
    Where-Object { $_.ResourceType -eq "SQL Server Availability Group" }) {
  "SQL Server AlwaysOn Availability Group exists on this cluster"
} else {
  "NOPE"
}

Solution 3:[3]

I realized that I don't have a SQL Admin account so I cannot query inside the database. I only have a Local Server Admin account and cannot have any SQL Admin account due to security reasons.

I used the following to check if FailoverClusters module exists in server. Not encouraging to use as a checking but this module is a prerequisite to enable SQLAlwaysON.

It is still not enough to tell that SQLAlwaysON is existing in server. I suggest to double check too whenever you have a SQL Admin account using @BiNZGi's answer. HADR is critical component for AG.

PS script

if (Get-Module -ListAvailable -Name FailoverClusters) {

Write-Host "SQLServerAlwaysON may exists."

}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 BiNZGi
Solution 2 Tomas
Solution 3

Related Questions