Compare Availability Group Replicas (Jobs, Logins, Credentials, Proxies etc)

[SQLDBAWithABeard]

Feature Request

Following a discussion on the airplane home from Summit (and subsequent blog post in draft https://sqldbawithabeard.com/?p=8594&preview=1&_ppp=4a8b0f1a7f)

A group of commands and an overarching command to compare the items on all of the replicas within an Availability Group to test for any differences that could be run before invoking the copy commands to fix them

So

Compare-DbaAGReplicas to call Compare-DbaAgReplicaAgentJobs Compare-DbaAgReplicaLogins Compare-DbaAgReplicaOperators Compare-DbaAgReplicaCredentials

etc

This would need to include not just a compare for names but also the option to compare on LastDateModified

Problem to solve

Senior DBA checking that Junior DBA has performed changes on ALL replicas in AG Troubleshooting issues by checking all AGs have same jobs etc

A very rough function

Function Compare-AgentJobs {
    Param(
        $SQLInstances
    )
    ## remove jobs* variables from process
    Get-Variable jobs* | Remove-Variable
    ## Get the number of instances
    $count = $SQLInstances.Count
    ## Loop through instances
    $SQLInstances.ForEach{
        # Get the jobs and assign to a new dynamic variable
        $Number = [array]::IndexOf($SQLInstances,$_)
        $Job = Get-DbaAgentJob -SqlInstance $_
        New-Variable -Name "Jobs$Number" -Value $Job
    }
    $i = $count -1
    $Primary = $SQLInstances[0]
    While($i -gt 0){
        ## Compare the jobs with Primary
        $Compare = $SQLInstances[$i]
        Write-Output "Comparing $Primary with $Compare "
        Compare-Object (Get-Variable Jobs0).Value (Get-Variable "Jobs$i").Value
        $i --
    }
}

A very simple date modified compare

`

Get the Agent Jobs

$Default = Get-DbaAgentJob -SqlInstance rob-xps $Dave = Get-DbaAgentJob -SqlInstance rob-xps\dave

Create a custom object array with the date instead of the datetime

$DaveJobs = @() $Dave.ForEach{ $DaveJobs +=[pscustomobject]@{ Server = $.OriginatingServer Name = $.Name Date = $_.DateLastModified.Date } }

Create a custom object array with the date instead of the datetime

$DefaultJobs = @() $Default.ForEach{ $DefaultJobs +=[pscustomobject]@{ Server = $.OriginatingServer Name = $.Name Date = $_.DateLastModified.Date } }

Perform a comparison

$Difference = Compare-Object $DefaultJobs $DaveJobs -Property date -PassThru

Sort by name and display

$Difference | Sort-Object Name | Select-Object Server,Name,Date`

[wsmelton]

Only thing in Compare-Object is cases with null values it does not handle well from my understanding. So in cases if you are comparing an operator and making sure junior set a particular property or value, if it is null on both sides it can't handle it.

I've seen Don recommend the below alternative that we might see about including in dbatools as an internal command. It is found under alx9r/ToolFoundations repo: code and even a test for the code

[github-actions[bot]]

🚧🚨 This issue is being marked as stale due to 90 days of inactivity. If you would like this issue to remain open:

• Verify the issue/bug is reproduced in the latest version of the module
• Verify the environmental info provided is still accurate
• Add any additional steps you followed to reproduce if necessary 🚨🚧 ⌛️ This issue will be closed in 30 days ⌛️

[tboggiano]

I'd like to add DateLastModified to Get-DbaAgentJob to support the comparison of dates between jobs?

[tboggiano]

For comparing logins I would like to add the modifed date from sys.server_prinipals to Get-DbaLogin?