Test-DbaAgPolicyState

Synopsis

Tests Availability Group health against Microsoft’s Always On predefined policies.

Description

Evaluates the health of SQL Server Availability Groups by checking them against
Microsoft’s predefined Always On policies from the Policy-Based Management framework.

Returns one object per policy evaluated, including whether the policy check passed
(IsHealthy), the policy name, category, facet, issue description, and details.

Based on the Microsoft documentation at:
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-policies-for-operational-issues-always-on-availability

Syntax

Test-DbaAgPolicyState
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-AvailabilityGroup] <String[]>]
    [[-Secondary] <DbaInstanceParameter[]>]
    [[-SecondarySqlCredential] <PSCredential>]
    [[-InputObject] <AvailabilityGroup[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Test-DbaAgPolicyState -SqlInstance sql2019 -AvailabilityGroup TestAG

Evaluates all predefined Always On policies for the availability group TestAG on sql2019.

Example: 2

PS C:\> Test-DbaAgPolicyState -SqlInstance sql2019

Evaluates all predefined Always On policies for all availability groups on sql2019.

Example: 3

PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2019 | Test-DbaAgPolicyState

Evaluates all predefined Always On policies for all availability groups on sql2019 using pipeline input.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-AvailabilityGroup

Specifies which availability groups to evaluate. If not specified, all availability groups are evaluated.

-Secondary

Specifies secondary replica endpoints when they use non-standard ports or custom connection strings.

-SecondarySqlCredential

Specifies credentials for connecting to secondary replica instances.

-InputObject

Accepts availability group objects from Get-DbaAvailabilityGroup via pipeline input.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

Outputs

PSCustomObject

Returns one object per policy evaluated. Properties:

• ComputerName: The computer name of the SQL Server instance (string)
• InstanceName: The SQL Server instance name (string)
• SqlInstance: The full SQL Server instance name (string)
• AvailabilityGroup: Name of the availability group (string)
• Replica: Name of the availability replica for replica-level policies, $null for server/AG-level policies (string)
• Database: Name of the availability database for database-level policies, $null for server/AG/replica-level policies (string)
• PolicyName: The name of the Always On policy being evaluated (string)
• Category: The severity of the policy - Critical or Warning (string)
• Facet: The object type the policy applies to (string)
• IsHealthy: Boolean indicating whether the policy check passed (bool)
• Issue: Description of the issue when the policy is not healthy, $null when healthy (string)
• Details: Additional detail about the current state (string)