Comprehensive data protection for all workloads
Post Reply
dxhahn
Novice
Posts: 4
Liked: never
Joined: Feb 03, 2012 7:23 pm
Full Name: Dan Hahn
Contact:

Veeam v6 and SQL backups

Post by dxhahn »

I've spent a while reading the user guide and searching the forums and haven't found a clear answer to this question. So if anyone has expertise or can point me to another post or document explaining it then I'll be very thankful.

I'm a MSSQL DBA (2005/2008) and I'm working with our storage / systems admin to get Veeam backups working on the development SQL servers (and eventually production). I have no desire to replace our native SQL backups (full nightly + 30 minute tlogs) and use Veeam for SQL backups. I can't think of a good reason to take the extra steps to restore backups from a VM backup when I have a mature and fully functional process writing backups to an easily accessible disk every 30 minutes. It seems that everynight when Veeam runs it uses VSS to create a sql backup (shows up in MSDB along with the TSQL backup). Is it possible to have Veeam backup the entire VM without creating separate SQL backups of every database?

Regards.
chrisdearden
Veteran
Posts: 1531
Liked: 226 times
Joined: Jul 21, 2010 9:47 am
Full Name: Chris Dearden
Contact:

Re: Veeam v6 and SQL backups

Post by chrisdearden »

you'll need to change the advanced settings on the Veeam backup Job for your SQL boxes to not truncate transaction logs after the backup. This is part of our VSS integration. Of course if you need to grab a backup from beyond your local retained backups , then Veeam will be quite handy. That said depending on the size of those SQL backups, you could be creating a lot of additional data for your image level backups. Being a former DBA myself I understand where you are coming from on this :)
dxhahn
Novice
Posts: 4
Liked: never
Joined: Feb 03, 2012 7:23 pm
Full Name: Dan Hahn
Contact:

Re: Veeam v6 and SQL backups

Post by dxhahn »

I verified with our admin that we do have the log truncation turned off, anyone have any idea about completely disabling those VSS initiated SQL backups? or is that just part of the image backup that has to be done?
dxhahn
Novice
Posts: 4
Liked: never
Joined: Feb 03, 2012 7:23 pm
Full Name: Dan Hahn
Contact:

Re: Veeam v6 and SQL backups

Post by dxhahn »

After rereading your reply again Chris it seems you are telling me there's no way around it. We aren't "backing up the backups" as those are written to another drive that we've excluded from the backup, but it effectively doubles out maintenance window each night, since I do a TSQL backup at 10pm and then VEEAM does it again at 1am. It's not a big deal on smaller databases but on some of those pushing 100gb+ its a pain.
tsightler
VP, Product Management
Posts: 6009
Liked: 2843 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: Veeam v6 and SQL backups

Post by tsightler »

You can disable application aware processing for your SQL server altogether. This will give you crash consistent backups and you can then rely on your normal SQL process to restore SQL.

That being said, I'm curious, why is it a problem that the Veeam backups show in the logs? They are technically backups afterall. They should not interfere with the normal SQL backups as long as you follow the advice above and disable log truncation during Veeam backups.
Andreas Neufert
VP, Product Management
Posts: 6707
Liked: 1401 times
Joined: May 04, 2011 8:36 am
Full Name: Andreas Neufert
Location: Germany
Contact:

Re: Veeam v6 and SQL backups

Post by Andreas Neufert »

Dear dxhahn,

thanks for your sight to sql backup. Let me explain you our way of doing (sql) backups.


Veeam alway backups the whole VM with all of it´s application and databases.
In the first backup we read out all the datastore storage blocks and store them as a backup file.
After this step we use the vmware change block tracking funktionality to read out only the changed blocks (from a storage perspective).

In case you have this 1TB SQL database and there are only some litte changes 100MB each 30 minutes:
Normal agent based backups had to transfer 1TB of data in case of a full or read out logs.

If you backup this VM with Veeam, Veeam transfers only the changed blocks (~100MB) but from a restor perspective this backup is in every case a full backup of the database.

So if you backup your VM with Veeam every 30 minutes your have 48 restore points each day. And you can restore to this points without log file processing.
The amount of data that is backed up is ~1TB once + 48x ~100MB each day but deduplication and compression still reducese this dramatically.

With our instant restore technology you can bring one of this restore points back to live in ~5 minutes (amount of data doesn´t matter).

So this looking to me more attractive than (legacy backup) restore the database (1TB in some hours) and process log files for some hours.

If you need to restore to a time between 2 veeam backups you can process log files after our (instant) restore, too.

Our MS SQL U-AIR Wizard can help you to restore only database tables or just any iteam of your database whitout a full restore (Veeam Backup & Replication Enterprise Version).

Log File Processing:
If you use only Veeam for backups: Process logs in any case (Default job settings).
If you wan´t to backup the database with you tool. Disable log file processing but keep VSS processing in the job enabled.
This meens that you have a consistent state of your databases in our jobs. Yes this is logged in the Eventviewer and SQL Logs.

I recommend to use Veeam. I was responsible in my life before veeam for several huge SAP database installations.
In some crit sits I realy whished that these customers had used veeam. (Server up an running in 5 minutes => nothing to say more)


CU Andy
sunshineb
Novice
Posts: 6
Liked: never
Joined: Mar 16, 2011 7:18 pm
Contact:

Re: Veeam v6 and SQL backups

Post by sunshineb »

"With our instant restore technology you can bring one of this restore points back to live in ~5 minutes (amount of data doesn´t matter)."

I can not get the SQL restore to work half the time. When SQL restore does work it takes forever to boot up in my Virtual LAB (Surebackup). I am not using Veeam for SQL backups until this is resolved. I have a support case opened, which is 5168477. I will wait to see what happens.
Andreas Neufert
VP, Product Management
Posts: 6707
Liked: 1401 times
Joined: May 04, 2011 8:36 am
Full Name: Andreas Neufert
Location: Germany
Contact:

Re: Veeam v6 and SQL backups

Post by Andreas Neufert »

Hello Sunshineb,

thanks for the information.
In your case I saw that the support had done a webex with you last Friday. They working for you on a soution for your environment.
I saw that the SureBackup SQL Testscript didn´t detect your MS SQL Server. Did the SQL Server run on a (manual configured) special Network Port?
Are there any firewall or SQL Network Binding rules, so that our Veeam Server are not able to connect to the SQL Server over the network?
If you want to use the SQL Restore Wizard (not SureBackup itself) you can also turn of the SQL script for that temporarly.

Just a small addition:
I never sayed that a virtual server is up and running in a VirtualLab for the restore wizard in such time. I talked from a restore szenario if your loosing your whole server.
In the VirtualLab you normaly boot a Active Directory Server in Autorative Restore Modus. This take some time and the wizard starts after that the SQL Server.
So it take some minutes longer if you want to use the Universal Iteam Recovery Wizard vor MS SQL.

CU Andy
SUN999
Lurker
Posts: 1
Liked: never
Joined: Jan 19, 2012 7:38 am
Full Name: Soren Nielsen
Contact:

Re: Veeam v6 and SQL backups

Post by SUN999 »

Hi,

Disable the SQL VSS writer service on the database server, that will for sure kill Veeams ability to perform a SQL VSS snapshot.

//SUN
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Veeam v6 and SQL backups

Post by Gostev »

Might be easier to just disable application-aware processing for this VM completely in the job settings.
dxhahn
Novice
Posts: 4
Liked: never
Joined: Feb 03, 2012 7:23 pm
Full Name: Dan Hahn
Contact:

Re: Veeam v6 and SQL backups

Post by dxhahn »

Thank you all for your input, much appreciated. The point about VEEAM only writing the changed blocks and not interfering with the normal backup is a good one. I'm still only relying on my native backups, but will begin investigating using VEEAM as our secondary backup and possibly removing the nightly sync to secondary storage.
Post Reply

Who is online

Users browsing this forum: No registered users and 234 guests