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" } } |
