Compare-DbaAvailabilityGroup

Synopsis

Compares configuration across Availability Group replicas to identify differences in Jobs, Logins, Credentials, and Operators.

Description

Compares multiple object types across all replicas in an Availability Group to identify configuration differences. This comprehensive command checks SQL Agent Jobs, SQL Server Logins, SQL Server Credentials, and SQL Agent Operators to ensure consistency across AG replicas.

This is the main command for comparing AG replica configurations. It can run all comparison checks or specific ones based on the Type parameter.

Use this to verify that junior DBAs have applied changes to all replicas, troubleshoot issues where configurations have drifted, or perform routine audits of AG replica consistency.

Syntax

Compare-DbaAvailabilityGroup
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-AvailabilityGroup] <String[]>]
    [[-Type] <String[]>]
    [-ExcludeSystemJob]
    [-ExcludeSystemLogin]
    [-IncludeModifiedDate]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Compare-DbaAvailabilityGroup -SqlInstance sql2016 -AvailabilityGroup AG1

Compares all object types (Jobs, Logins, Credentials, Operators) across replicas in AG1.

Example: 2

PS C:\> Compare-DbaAvailabilityGroup -SqlInstance sql2016 -AvailabilityGroup AG1 -Type AgentJob

Compares only SQL Agent Jobs across replicas in AG1.

Example: 3

PS C:\> Compare-DbaAvailabilityGroup -SqlInstance sql2016 -AvailabilityGroup AG1 -Type AgentJob, Login

Compares SQL Agent Jobs and Logins across replicas in AG1.

Example: 4

PS C:\> Compare-DbaAvailabilityGroup -SqlInstance sql2016 -AvailabilityGroup AG1 -IncludeModifiedDate

Compares all object types including DateLastModified timestamps for jobs and logins.

Example: 5

PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2016 | Compare-DbaAvailabilityGroup

Compares all object types for all Availability Groups on sql2016 via pipeline input.

Example: 6

PS C:\> Compare-DbaAvailabilityGroup -SqlInstance sql2016 -AvailabilityGroup AG1 -ExcludeSystemJob -ExcludeSystemLogin

Compares all object types excluding system jobs and system logins.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. Can be any replica in the Availability Group.

-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 one or more Availability Group names to compare across their replicas.

-Type

Specifies which object types to compare. Valid options are: AgentJob, Login, Credential, Operator, All.
Default is All which runs all comparison checks.

-ExcludeSystemJob

Excludes system jobs from the agent job comparison.
Only applicable when Type includes AgentJob or All.

-ExcludeSystemLogin

Excludes built-in system logins from the login comparison.
Only applicable when Type includes Login or All.

-IncludeModifiedDate

Includes DateLastModified comparison for jobs and modify_date comparison for logins.
Only applicable when Type includes AgentJob, Login, or All.

-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.