MSSQL 数据库Job在不同Server之间同步
在各种高可用方案中,我们常常面临考虑 SQL Login 或者 Job 脚本需要在不同Server之间同步。
这里提供几种方式:
1.SSIS Package 传输对象
2.Powershell 脚本部署。
3.可以设置触发器或者扩展事件,监控系统表中的改动,然后启动一个PS脚本,实时同步
这里做个简单的例子,加入我们每天同步一次job,主要思路就是监控系统表中的改动,然后获取job name,
然后通过SMO获得脚本对象,在目标服务器上执行
脚本如下:
param($souceSererIP='sourceServer',$targetServerIp='.') Import-Module sqlps -DisableNameChecking [system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null [system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")|Out-Null [system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")|Out-Null [string] $script ='' $server=New-Object Microsoft.SqlServer.Management.Smo.Server($souceSererIP) $dt=[System.DateTime]::Now.AddDays(-1).ToString("yyyy-MM-dd HH")+":00:00" foreach($job in $server.JobServer.Jobs) { if($job.Name -ne "test") { continue } if($job.DateLastModified -gt $dt) { $jobName=$job.Name $jobName=$jobName.Replace("/","_").Replace("'","_").Replace("[","_").Replace("]","_").Replace("(","_").Replace(")","_").Replace("&","_").Replace('"',"_").Replace("\","_").Replace(":","_").Replace("{","_").Replace("}","_").Replace("<","_").Replace(">","_").Replace("*","_").Replace("#","_") } $nl="`n" $script='if exists(select * from msdb.dbo.sysjobs where name=N'''+$jobName +''' )'+ $nl +'begin '+ $nl +'Exec msdb.dbo.sp_delete_job @job_name=N'''+$jobName+''' '+$nl+'end '+$nl +'GO ' $script=$script+$nl+$job.Script() #$script try { Invoke-Sqlcmd -ServerInstance $targetServerIp -query $script -ErrorAction Stop } catch { $ErrorMessage = $_.Exception.Message $FailedItem = $_.Exception.ItemName ##send email if have issue #Send-MailMessage -From ExpensesBot@MyCompany.Com -To WinAdmin@MyCompany.Com -Subject "HR File Read Failed!" -SmtpServer EXCH01.AD.MyCompany.Com -Body "We failed to read file $FailedItem. The error message was $ErrorMessage" Break } $output ='Execute success! Jobname:"{0}" from "{1}" to "{2}"' -f $jobName,$souceSererIP,$targetServerIp Write-Host $output }
我们将文件保存成syncjob.ps1,然后cmd中调用:
powershell c:\test\syncjob.ps1 sourceServer,targetServer .