1

I am doing a manual comparison of the logins and jobs between Principal and Secondary server, when doing database failover from P to S. I copy both the logins and jobs from one server to excel and compare them to those on the other.

Could someone please suggest a way to automate at least part of this check?

Note - I am doing this before doing a database failover as a pre-check, just to ensure that the secondary isn't missing anything as compared to the principal, so as to avoid a break after mirroring database failover.

1 Answer 1

1

Using Power Shell worked for me. I have this as part of bigger script, I am only providing sections of it. Hopefully you can put it together.

FUNCTION report-status-primary 
    {
        PARAM (
                $Check,
                $Variable,
                $Primary,
                $Secondary
              )

        WRITE-HOST "Checking $Check on $Primary..." -ForegroundColor Yellow
        IF ($Variable)

            {

                WRITE-HOST "$Check on $primary not on $secondary" -ForegroundColor Red
                $Variable
            }
        ELSE
            {
                WRITE-HOST "Match" -ForegroundColor Green
            }     
        WRITE-HOST "------------------------------------------------------------" -ForegroundColor Yellow
    }

  FUNCTION report-status-secondary 
    {
        PARAM (
                $Check,
                $Variable,
                $Primary,
                $Secondary
              )

        WRITE-HOST "Checking $Check on $Secondary..." -ForegroundColor Yellow
        IF ($Variable)

            {

                WRITE-HOST "$Check on $Secondary not on $primary" -ForegroundColor Red
                $Variable
            }
        ELSE
            {
                WRITE-HOST "Match" -ForegroundColor Green
            }     
        WRITE-HOST "------------------------------------------------------------" -ForegroundColor Yellow
    }

Variable:

$SourceSQLServer = 'Primary Server'
$TargetSQLServer = 'Secondary Server'

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$SourceServer = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($SourceSQLServer)
$TargetServer  = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($TargetSQLServer)

# -- Jobs
$JobsSNT = $SourceServer.JobServer.Jobs.name | where {($TargetServer.JobServer.Jobs.name -notcontains $_) -and $_ -notlike "*]__*" }
$JobsTNS = $TargetServer.JobServer.Jobs.name | where {($SourceServer.JobServer.Jobs.name -notcontains $_) -and $_ -notlike "*]__*" }

# -- Logins
$LoginsSNT = $SourceServer.Logins.name | where {$TargetServer.Logins.name -notcontains $_}
$LoginsTNS = $TargetServer.Logins.name | where {$SourceServer.Logins.name -notcontains $_}

Reporting:

report-status-primary -check 'Jobs' -variable $JobsSNT -primary $SourceServer -secondary $TargetServer 
report-status-secondary -check 'Jobs' -variable $JobsTNS -primary $SourceServer -secondary $TargetServer 

report-status-primary -check 'Logins' -variable $LoginsSNT -primary $SourceServer -secondary $TargetServer 
report-status-secondary -check 'Logins' -variable $LoginsTNS -primary $SourceServer -secondary $TargetServer 

Here is a sample output I get running this script.

enter image description here

4
  • thanks for the script. Since i am not that good in Powershell, getting hard time in putting this together. I tried using this but keep getting errors here there. Not sure if there is SQL query to do this without using linked server. Appreciate you taking time and answering, Commented Aug 18, 2017 at 1:44
  • I literally ran the code that I posted by replacing server name and I get an output as expected. Edited answer with a sample output. Commented Aug 18, 2017 at 2:08
  • Perfect, i got that working. But is there a way to send this as results. Like what i f i want to automate this? Commented Aug 18, 2017 at 21:11
  • You can run this as a SQL Agent scheduled job and email the report. There are many examples, search in google. If this answers your original question please accept the answer by so other users do not think that you are still looking for an answer plus others can also benefit from this answer. Commented Aug 18, 2017 at 22:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.