Synchronizing Logins, Jobs, and Dependent Objects between Replicas with PowerShell

Here’s a simple PowerShell Script that I developed to synchronize logins, credentials, proxies, operators, and jobs between AlwaysOn replicas using PowerShell, dbatools, and a little bit of T-SQL.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
#Powershell Script to copy logins, credentials, agent proxies, agent operators, and agent jobs between replica servers
#Author: Bennett Scharf
 
 
$server = $env:COMPUTERNAME
 
#debug variable assignment - in case we are not running the script on a replica server
$server = "node4"
 
$Commands = @()
 
$cmdlets = @("Copy-DbaLogin", "Copy-DbaCredential", "Copy-DbaAgentProxyAccount", "Copy-DbaAgentOperator", "Copy-DbaAgentJob" )
 
foreach ($cmdlet in $cmdlets)
#note: we're essentially using SQL and the system view sys.availability_replicas to generate the dbatools commands
{
    $query = "select distinct '$cmdlet -source ' + t1.replica_server_name + ' -destination ' +  t2.replica_server_name + '  -ErrorVariable oops'
    FROM sys.availability_replicas t1
    CROSS JOIN sys.availability_replicas t2
    WHERE t1.replica_server_name <>  t2.replica_server_name
    order by 1
    "
    $Commands += invoke-sqlcmd –ServerInstance $server -Database 'master'-Query $query | SELECT -ExpandProperty Column1
}
 
foreach ($command in $Commands)
{
 
    try
    {
        $command
        #comment out next line to only view command strings
        Invoke-Expression $command 
    }
 
    Catch
    {
        $to = "DBA Bob <bob@denvergov.org>"
        $from = "DBAMailAccount <alerts@denvergov.org>"
        $message = "An error was encountered while executing command: $command  Please check SQL Agent job log. `n Error: $oops"
        $subject = $env:COMPUTERNAME + ': Error replicating objects'
        Send-MailMessage -To $to -From $from -Subject $subject  -Body $message -SmtpServer "somerelay.domain.org" 
    }
}
This entry was posted in AlwaysOn, Powershell. Bookmark the permalink.

Leave a Reply