Comprehensive data protection for all workloads
Post Reply
sandrola
Influencer
Posts: 16
Liked: 10 times
Joined: Feb 27, 2017 11:49 am
Full Name: Sandro Lanfranchi
Location: Zürich
Contact:

SQL log shipping

Post by sandrola » 8 people like this post

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
Product Manager
Posts: 8044
Liked: 1263 times
Joined: Feb 08, 2013 3:08 pm
Full Name: Mike Resseler
Location: Belgium
Contact:

Re: SQL log shipping

Post by 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
Service Provider
Posts: 19
Liked: 7 times
Joined: Jan 24, 2018 12:08 am
Contact:

Re: SQL log shipping

Post by LBegnaud » 2 people like this post

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
Product Manager
Posts: 8044
Liked: 1263 times
Joined: Feb 08, 2013 3:08 pm
Full Name: Mike Resseler
Location: Belgium
Contact:

Re: SQL log shipping

Post by Mike Resseler »

I already like this thread :-D

Thanks LBegnaud and Sandro!
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL log shipping

Post by 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.
Post Reply

Who is online

Users browsing this forum: No registered users and 261 guests