Feature Request - SQL Log Backups

Availability for the Always-On Enterprise

Feature Request - SQL Log Backups

Veeam Logoby bsmall244 » Mon Apr 25, 2016 7:04 pm 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!
bsmall244
Lurker
 
Posts: 1
Liked: 1 time
Joined: Mon Apr 25, 2016 6:37 pm
Full Name: Brian Small

Re: Feature Request - SQL Log Backups

Veeam Logoby Gostev » Mon Apr 25, 2016 11:40 pm

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.
Gostev
Veeam Software
 
Posts: 21603
Liked: 2405 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

Re: Feature Request - SQL Log Backups

Veeam Logoby mkretzer » Tue Apr 26, 2016 4:44 am 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
mkretzer
Expert
 
Posts: 342
Liked: 74 times
Joined: Thu Dec 17, 2015 7:17 am

Re: Feature Request - SQL Log Backups

Veeam Logoby johannesk » Wed Apr 27, 2016 2:16 pm

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.
johannesk
Enthusiast
 
Posts: 30
Liked: 3 times
Joined: Tue Jan 19, 2016 1:28 pm
Full Name: Jóhannes Karl Karlsson

Re: Feature Request - SQL Log Backups

Veeam Logoby Gostev » Wed Apr 27, 2016 10:39 pm

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?
Gostev
Veeam Software
 
Posts: 21603
Liked: 2405 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

Re: Feature Request - SQL Log Backups

Veeam Logoby johannesk » Thu Apr 28, 2016 12:05 pm

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?
johannesk
Enthusiast
 
Posts: 30
Liked: 3 times
Joined: Tue Jan 19, 2016 1:28 pm
Full Name: Jóhannes Karl Karlsson

Re: Feature Request - SQL Log Backups

Veeam Logoby Gostev » Thu Apr 28, 2016 8:36 pm

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.
Gostev
Veeam Software
 
Posts: 21603
Liked: 2405 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

Re: Feature Request - SQL Log Backups

Veeam Logoby mkretzer » Thu Apr 28, 2016 9:04 pm 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.
mkretzer
Expert
 
Posts: 342
Liked: 74 times
Joined: Thu Dec 17, 2015 7:17 am

Re: Feature Request - SQL Log Backups

Veeam Logoby Harry.E » Fri Apr 29, 2016 12:08 pm

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.
Harry.E
Novice
 
Posts: 3
Liked: 1 time
Joined: Fri Apr 29, 2016 11:43 am

Re: Feature Request - SQL Log Backups

Veeam Logoby leonmahler » Mon May 02, 2016 4:31 am 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.
leonmahler
Lurker
 
Posts: 1
Liked: 1 time
Joined: Tue Feb 23, 2016 10:09 am
Full Name: Leon Mahler

Re: Feature Request - SQL Log Backups

Veeam Logoby CPCS » Mon May 02, 2016 6:00 am

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...
CPCS
Influencer
 
Posts: 12
Liked: 3 times
Joined: Tue Mar 01, 2016 1:49 pm

Re: Feature Request - SQL Log Backups

Veeam Logoby cousinsp » Tue May 03, 2016 10:17 am

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.
cousinsp
Novice
 
Posts: 9
Liked: 1 time
Joined: Fri Jan 15, 2016 11:20 am
Full Name: Pete Cousins

Re: Feature Request - SQL Log Backups

Veeam Logoby mkretzer » Tue May 03, 2016 11:12 am

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
mkretzer
Expert
 
Posts: 342
Liked: 74 times
Joined: Thu Dec 17, 2015 7:17 am

Re: Feature Request - SQL Log Backups

Veeam Logoby PAF » Wed Jul 20, 2016 8:50 am

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
Lurker
 
Posts: 2
Liked: never
Joined: Wed Jul 20, 2016 8:37 am
Full Name: Peter Almer Frederiksen

Re: Feature Request - SQL Log Backups

Veeam Logoby PAF » Wed Jul 20, 2016 9:06 am

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.
PAF
Lurker
 
Posts: 2
Liked: never
Joined: Wed Jul 20, 2016 8:37 am
Full Name: Peter Almer Frederiksen

Next

Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Bing [Bot], Google Feedfetcher and 5 guests