New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
SQLDBAWithABeard opened this Issue Nov 8, 2017 · 1 comment

Comments

Projects
None yet
2 participants
@SQLDBAWithABeard
Contributor

SQLDBAWithABeard commented Nov 8, 2017

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

This comment has been minimized.

Show comment
Hide comment
@wsmelton

wsmelton Nov 8, 2017

Member

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

Member

wsmelton commented Nov 8, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment