AppAware backups of Mirrored MSSQL DB

Availability for the Always-On Enterprise

AppAware backups of Mirrored MSSQL DB

Veeam Logoby IHeartCats » Tue Oct 03, 2017 4:21 pm

Migrating some older msssql backups from our old backup platform into Veeam. I have done all the easy ones. I'm now dealing with ones that need log backups and one server that uses MSSQL mirroring.

Has anyone got any info or specific advice about Appaware back up MSSQL databases that are mirrored? I'm assuming there must be plenty of people out there using Veeam in this scenario but I can't find anything specific in the Veeam manuals/or on the forum.

We have 2 MSSQL DB servers which have automatic failover configured for both of the databases that reside on them.

Due to the auto fail over we don't know which of the mirrors will actually be running the database at backup time, would this cause backups to fail on one of the nodes?

Could the process of doing an app aware backup cause the database to fail over to the other nodes?

I need the benefit of your wisdom!

Thanks
IHeartCats
Influencer
 
Posts: 11
Liked: never
Joined: Tue Jul 18, 2017 1:58 pm
Full Name: Helen

Re: AppAware backups of Mirrored MSSQL DB

Veeam Logoby GarethUK » Tue Oct 03, 2017 9:33 pm 1 person likes this post

Hi,

It works without further code so long as you don't want to do say hourly transaction log backups.

But..... It isn't supported AFAIK by either Microsoft or Veeam - happy to be corrected.

If you need hourly transaction log backup you need to monitor what is happening and force a quick backup on the machine that becomes principle. Since both servers could have databases they are principle for it become a bit tricky.

I've coded a monitoring tool in PS and tested it, but I think the best solution is to move to Always On Availability Groups.

Regards,

Gareth
GarethUK
Influencer
 
Posts: 18
Liked: 2 times
Joined: Fri Mar 21, 2014 11:41 am
Full Name: Gareth

Re: AppAware backups of Mirrored MSSQL DB

Veeam Logoby IHeartCats » Fri Oct 06, 2017 9:46 am

GarethUK wrote:Hi,

It works without further code so long as you don't want to do say hourly transaction log backups.

But..... It isn't supported AFAIK by either Microsoft or Veeam - happy to be corrected.



Thanks for the info GarethUK

This would be good if someone from Veeam can confirm this. I'll log it as a sev4 on Monday if no one from Veeam can answer here.

Ideally yes we would like to be doing hourly log backups on it.

I guess the other approach would be to continue with Veeam Crash Consistent backups of it nightly and use a frequent maintenance plans to disk to get the hourly granularity but it's a bit of a grubby bodge.

Unless anyone else has an other work arounds?

H
IHeartCats
Influencer
 
Posts: 11
Liked: never
Joined: Tue Jul 18, 2017 1:58 pm
Full Name: Helen

Re: AppAware backups of Mirrored MSSQL DB

Veeam Logoby foggy » Fri Oct 06, 2017 12:47 pm 1 person likes this post

Gareth is spot on. Veeam B&R doesn't detect the switch and attempts to backup logs from the (former) active node until the next parent VM backup (image-level). The same for the mirrored DB, which is just skipped from processing until image-level VM backup is performed for it after it becomes principle.
foggy
Veeam Software
 
Posts: 15377
Liked: 1139 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: AppAware backups of Mirrored MSSQL DB

Veeam Logoby IHeartCats » Mon Oct 09, 2017 8:58 am

Hi Foggy,

Is there a recommended way to do get Mirrored SQL backups in Veeam?

Just use crash consistent backup and get MSSQL to do everything else with maintenance plans?

Thanks
H
IHeartCats
Influencer
 
Posts: 11
Liked: never
Joined: Tue Jul 18, 2017 1:58 pm
Full Name: Helen

Re: AppAware backups of Mirrored MSSQL DB

Veeam Logoby foggy » Mon Oct 09, 2017 2:41 pm

If you cannot afford losing logs saved from the switch till the next image-level backup, then yes, you need to look for workarounds.
foggy
Veeam Software
 
Posts: 15377
Liked: 1139 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Google [Bot], Songykhw and 1 guest