SQL log shipping

[sandrola]

We have a quiet large infrastructure (> 1500 VM) segmented into different subnet not directly routable. For VM in these segment the shipping of SQL Logs could be done over VIX (very slow) or over the network. Now to be possible to transfer the logs over the network we have opened the needed ports in the firewall and added "adhoc" routes to the VM's.

But in the GUI if the proxy selection is set to "autoselect" (default), Veeam cannot find any proxy that are in the same subnet as the SQL VM, so the Log's are still trasfered over VIX.
To be able to select a proxy (autoselect or manually) that work for this subnet, we must install a Veeam proxy (or choose a already present VM as proxy) for each subnet (>100).
To avoid the installation of these Veeam Proxy I have modified the "child job" with the SQL VM with no Proxy entry.

Code: Select all

$job = Read-Host "Enter master job name"
$subjob = Read-Host  "Enter Child Job name (SQL)"
 
if ((Get-PSSnapin -Name VeeamPSSnapIn -ErrorAction SilentlyContinue) -eq $null) {
  Add-PsSnapin -Name VeeamPSSnapIn
}
 
$o = New-VBRJobVSSoptions
$o.Enabled = $true
$o.VssSnapshotOptions.Enabled = $true
$o.IgnoreErrors = $false
#set sql options
$o.SqlBackupOptions.TransactionLogsProcessing.TruncateOnlyOnSuccessJob
$o.SqlBackupOptions.TransactionLogsProcessing = "Backup"
$o.SqlBackupOptions.BackupLogsFrequencyMin = 30
$o.SqlBackupOptions.UseDbBackupRetention = $true
$o.SqlBackupOptions.RetainDays = 15
$o.SqlBackupOptions.ProxyAutoSelect = $false
 
Get-VBRJob -Name $job | Get-VBRJobObject -Name $subjob | Set-VBRJobObjectVssOptions -Options $o

Now in the GUI the Proxy selection is grayed out, and the SQL Logs ships over the network to the selected repository without problems.

[Mike Resseler]

Sandro,

Great script and thank you for this! I am absolutely sure this is something others will benefit from also!

Thanks again
Mike

[LBegnaud]

yea we had some similar requirement in our infrastructure (SQL VMs unable to talk directly to Repositories) and came up with some similar code to actually assign SQL interaction proxies in the job. This is basically what you are doing, but with the added bonus of setting the list to be not null. Note that this is basically pseudo-code as it's copy/paste from various lines of a script we have to update SQL jobs.

Code: Select all

# set list of interaction proxies, adjust accordingly
$Gips = Get-VBRServer | ? name -like veeamguestprx* | sort name

# ensure $job variable points to parent job
$jobobjects = $job | Get-VBRJobObject
foreach($sqloij in $jobobjects){

	$OijGips = [Veeam.Backup.Core.COijProxy]::GetOijProxies($SqlOij.id) | ? type -eq BackupSql

	if($OijGips.count -eq 0){
		foreach($Gip in $Gips){
			# Add a server as a log shipping server for this object
			[Veeam.Backup.Core.COijProxy]::Create($SqlOij.Id, $Gip.Id, 0) > $null
			if($verbose -eq 1){"Adding log shipping proxy: $($Gip.name)"}
			sleep 1
		}
		# Save the VSS Options
		Set-VBRJobObjectVssOptions -Object $SqlOij -Options $SqlOij.VssOptions > $null
	}
}

[Mike Resseler]

I already like this thread

Thanks LBegnaud and Sandro!

[Gostev]

I was so curious I checked with QC folks, and we're actually quite confused about what OP's script allows to achieve... it would be very interesting for us to see the job logs with this "invalid" (from UI perspective) settings applied.