Comprehensive data protection for all workloads
Post Reply
bsmall244
Lurker
Posts: 1
Liked: 1 time
Joined: Apr 25, 2016 6:37 pm
Full Name: Brian Small
Contact:

Feature Request - SQL Log Backups

Post by bsmall244 » 1 person likes this post

After enabling SQL transaction log backups in Veeam instead of using SQL backup for our production SQL servers, we have discovered an issue with the way that the backups are processed on the source VM. It seems that Veeam streams the transaction log backup file to a temporary location on the C: drive of the source VM (C:\ProgramData\Veeam\Backup\SqlLogBackup), and then copies that file over to the backup repository. This almost caused a major issue with our production server, as the C: drive ran out of space, which caused the log backups to fail several times over a weekend, and in turn caused the transaction logs in SQL to swell and almost run that drive out of space as well. Our servers are very busy over the weekends and the transaction logs can swell to over 200GB in a day. It seems to me that there is an unnecessary duplication of data that happens when Veeam runs a backup to a temporary location, then copies that data to the repository. I know that a SQL Maintenance Plan can stream the backup file to a network drive, so why can't Veeam do the same thing and stream the backup file directly to the repository rather than placing it in a temporary location on the C: drive? It seems that this is a very dangerous method, as filling up the C: drive can crash the server. There has got to be a way to avoid duplicating the transaction log data on the source VM before copying it to the repository. I know that it is required to run a SQL transaction log backup to some location in order for this process to work, but it seems to me that someone can put some more thought to this and find a way to stream that file directly to the repository. Placing the temporary backup locally on the source VM is dangerous, and defaulting to the C: drive is even more so!
Gostev
Chief Product Officer
Posts: 31457
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Feature Request - SQL Log Backups

Post by Gostev »

Hi, where do you suggest we collect the SQL log backup data before backup process is completed? 200 GB is obviously too much to hold in RAM. We cannot stream directly to repository, because it may not be always available, resulting in missed RPOs.
mkretzer
Veeam Legend
Posts: 1140
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

Re: Feature Request - SQL Log Backups

Post by mkretzer » 1 person likes this post

I also think bsmall244 has a point here and i do not understand what Gostev is saying about RPOs.

In case of SQL logs if we back them up at a later time we can still restore every possible time in the logfile. And if the repo is not available it gets not backed up anyway. So in this situation we already have a problem somewhere (repo not available) and now Veeam causes a much bigger issue by filling up the SQL server drive.
Why should RPO be our biggest concern in this situation?? We had this issue and our SQL admins were NOT HAPPY.

One tip for bsmall244: What helped in our environment was to globally enable SQL server compression on the SQL servers. It might lead to a slightly increased CPU usage but it helped us keep the log backups small enough. It would be nice if Veeam could make this an option so we would not have to do this globally on the SQL.

Markus
johannesk
Expert
Posts: 146
Liked: 31 times
Joined: Jan 19, 2016 1:28 pm
Full Name: Jóhannes Karl Karlsson
Contact:

Re: Feature Request - SQL Log Backups

Post by johannesk »

I agree with bsmall244 and mkretzer. We have had problems just like those described by bsmall244 - and it takes down the credibility of the product among the DBA's.

I like the suggestions to be able to make veeam compress the log files. Also if it would be possible (and perhaps it is) to change the default log backup location to another drive than drive C:\.

I don't understand the argument of repository not being available. I would not like drive C: to be filled up waiting for a repository to go online. You would run your repository server to be highly available anyway.
Gostev
Chief Product Officer
Posts: 31457
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Feature Request - SQL Log Backups

Post by Gostev »

With the proposed architecture, if a repository goes down, Veeam will not be able to take log backups at all. This in turn means that SQL Server transaction logs will continue growing all this time (because they can only be truncated after successful backups), and will eventually overfill their volume, crashing SQL server. And you cannot restore it to the latest state because you have no log backups anywhere (unlike with the current approach) => missed RPO.

So ideally, we need to combine both approaches together somehow. But that's a lot of additional complexity which we like to avoid, because it directly impacts reliability. And this complexity also means we won't be able to add this functionality in a short time frame.
johannesk wrote:Also if it would be possible (and perhaps it is) to change the default log backup location to another drive than drive C:\
Yes, temporary log backup location is configurable.

From the issues described above, it sounds like quick and dirty fix might be for us to be automatically selecting a volume with most free disk space for temporary log backup location, instead of defaulting to %TEMP% location?
johannesk
Expert
Posts: 146
Liked: 31 times
Joined: Jan 19, 2016 1:28 pm
Full Name: Jóhannes Karl Karlsson
Contact:

Re: Feature Request - SQL Log Backups

Post by johannesk »

mkretzer wrote:
One tip for bsmall244: What helped in our environment was to globally enable SQL server compression on the SQL servers. ...... It would be nice if Veeam could make this an option so we would not have to do this globally on the SQL.

Markus
Does that work - If compression is enabled globally on the SQL, will the files sent to the by veem logshipper to "C:\ProgramData\Veeam\Backup\SqlLogBackup" be compressed?
Gostev
Chief Product Officer
Posts: 31457
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Feature Request - SQL Log Backups

Post by Gostev »

To add to that, after some research and discussion with the devs we've decided to add an option to use compression with native log backups to further reduce the disk space they take. Unfortunately though, this functionality seems to require SQL Server Enterprise edition.
mkretzer
Veeam Legend
Posts: 1140
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

Re: Feature Request - SQL Log Backups

Post by mkretzer » 1 person likes this post

johannesk wrote: Does that work - If compression is enabled globally on the SQL, will the files sent to the by veem logshipper to "C:\ProgramData\Veeam\Backup\SqlLogBackup" be compressed?
The info came initially from Veeam support. Case 01006690. I am 99 % sure it works as since we enabled it globally on all SQL servers we have less problems with the log backup disk space.
Harry.E
Novice
Posts: 3
Liked: 1 time
Joined: Apr 29, 2016 11:43 am
Contact:

Re: Feature Request - SQL Log Backups

Post by Harry.E »

Same issue here. C: drive 100% full due to Veeam transaction log backup. I would recommend to update the documentation:
(A) Emphasize that the logs are extracted from the DB and written temporary to the VM AND to make ensure that sufficient space is available to hold all transaction logs !!
(B) Clearly specify the directory where the temporary logs are stored in the documentation.

For us the solution was to map a separate disk with 100GB into this path.
leonmahler
Lurker
Posts: 1
Liked: 1 time
Joined: Feb 23, 2016 10:09 am
Full Name: Leon Mahler
Location: Breda, The Netherlands
Contact:

Re: Feature Request - SQL Log Backups

Post by leonmahler » 1 person likes this post

Hi Bsmall244,

How quickly does your log back-up grow? Is it 200GB per 30 minutes or is it 200GB per 24 hours?

Besides of the fact that Veeam copies the logs into the memory/ %TEMP% before a back-up/truncate, maybe you can create a schedule to run log backups every 4/2/1/0,5 hour(s)? This should reduce the risk op missing your RPO like Gostev mentioned and helps to achieve RTO.

This is how I configured an environment of 1400 Databases (653 Instances, 168 servers) which can grow really quick as well. I also configured alerts in SCOM so our Servicedesk can monitor the health status of the environment more proactively.
CPCS
Influencer
Posts: 17
Liked: 4 times
Joined: Mar 01, 2016 1:49 pm
Contact:

Re: Feature Request - SQL Log Backups

Post by CPCS »

Hi,

please veeam fix this! :?

At least and first it would be nice if the temp path would be selectable - but of course i would like to not waste a second partition with the same amount of space like the log files...Second update the documentation please, so that others know about this BEFORE running into this! This is the second time (first is/was that SQL restore is performed on local SQL express on the backup server and therefore space have to be available and not exceeding the 10gb express limit i think) i RANDOMLY STUMBLED over a major problem which can cause you serious problems which is not documented.

Is the compression not available in the Standard editions? :( If not, this "solution" would not even be applicable to all users...
cousinsp

Re: Feature Request - SQL Log Backups

Post by cousinsp »

As a SQL DBA, I think it would be best to backup the tran logs to the default backup location as set in SQL Server. I believe this location is stored in the registry.
mkretzer
Veeam Legend
Posts: 1140
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

Re: Feature Request - SQL Log Backups

Post by mkretzer »

The problem is that if you backup more often then the maximum time Veeam allows the backup to take reduces. If you backup every 15 minutes the max time the backup may take is one hour. That was an issue in our environment because some tasks lead to ~ 100 GB of logfile in a few minutes.

Gesendet von meinem Nexus 6P mit Tapatalk
PAF
Novice
Posts: 4
Liked: never
Joined: Jul 20, 2016 8:37 am
Full Name: Peter Almer Frederiksen
Contact:

Re: Feature Request - SQL Log Backups

Post by PAF »

Suggestion 1: Veeam should check if there is enough space on the C drive before using it, so avoid filling it and possibly crashing the SQL server. If there is not enough space the backup job should fail before writing to C.
Suggestion 2: If there is not enough space at C, Veeam could check if any other drive have enough space. SQL servers often have separate drives for db and db-log. WIndows Installer seems to use such a method.
Suggestion 3: Alternativly, Veeam should not use any local drive at all, but a shared folder at one of the Veeam servers.
PAF
Novice
Posts: 4
Liked: never
Joined: Jul 20, 2016 8:37 am
Full Name: Peter Almer Frederiksen
Contact:

Re: Feature Request - SQL Log Backups

Post by PAF »

Suggestion: The Veeam agent should enable the NTFS compression flag on the folder C:\ProgramData\Veeam\Backup\SqlLogBackup. The .bak files from SQL Server can often be compressed 50-75%.
Of course the flag can be enabled manually as well.
CPCS
Influencer
Posts: 17
Liked: 4 times
Joined: Mar 01, 2016 1:49 pm
Contact:

Re: Feature Request - SQL Log Backups

Post by CPCS »

I dont have the C:\ProgramData\Veeam\Backup\SqlLogBackup Folder on my SQL Server which is backuped? oO
Instead there is an empty Oracle folder?! oO
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Feature Request - SQL Log Backups

Post by foggy »

Buh95
Influencer
Posts: 21
Liked: 4 times
Joined: Nov 17, 2015 1:31 pm
Full Name: H.Schema.
Contact:

[MERGED] Veeam SQLServer Log Backup for large log volumes

Post by Buh95 »

Hi all,

We're studying the impact of migrating a big SQLServer (ERP/High transactional profile) from a physical server to a virtual one (Several TB of databases).

We've been happy Veeam users for years now, and we are already using Veeam to backup some small SQLServer Vmware VM, with periodic log backup (every hour).

All is working perfectly, but in case of our big sqlserver, I would have somme reserves, regarding the capacity of veeam to backup huge amount of logs.

In Veeam, the process of log backup uses a temporary local disk space to copy log files, before sending them to the veeam proxy server.
Today, we configure this temporary local disk space to redirect to another local disk (SQLTempLogPath in registry).

Our big sqlserver is able to generate between 1GB and 30GB per hour (in normal condition), and even 100GB/200GB of logs when DBA launch special REORGANIZE index operations...

So what would happen to my Veeam Log Backup :
=> Several long minutes to duplicate locally all the logs
=> and others several long minutes to send them by the network to the veeam server.

Does someone already experiment such a case in production ?
Do you know if there is a way to bypass the local log copy ?

Thanks in advance for your help.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Feature Request - SQL Log Backups

Post by foggy »

Thanks for the feedback, currently there's no way to bypass the local copy of log files. You can find some thoughts on this above.
Post Reply

Who is online

Users browsing this forum: bct44, Bing [Bot] and 165 guests