SQL VSS writer integration

[pigbloke]

Hi

I have been trialling Veeam 4.1.1 and I have to say I'm very impressed and I am hoping to get the green light for purchase. From my point of view as administrator of the virtual infrastructure and all of our VMs it does exactly what I want it to do. Our SQL DBAs on the other hand are not yet fully convinced.

Our plan is to back up our whole virtual infrastructure with a single backup solution and all backup/restore jobs are controlled by the VI admins. Currently our DBAs operate the SQL backups using native SQL backup on our SQL server VMs - Full backup every night and transaction log backups every 15 minutes. From this they can recover to any point in time using the transaction log backups and they say we are not getting the same level of flexibility with the Veeam backups.
The server I am backing up is Windows Server 2008 x64 with SQL Server 2008 x64. The SQL VSS Writer service is installed and running and "Enable Veeam VSS integration" is ticked in the backup job and the backup job is running every 15 minutes.

The issues I am having are:

• 1. The SQL transcation logs are not being truncated after backups. I understand this is a function of the SQL VSS writer but I don't understand why this is not happening.
  2. I/O is frozen on the databases for about 10 seconds during the backup. I suspect this is also down to the SQL VSS writer but our sw dev guys insist that this will cause problems going forward so I really need to find a way to reduce this freeze time.
  3. Is there any way of performing differential SQL backups? The SQL VSS writer includes this functionality but is there any way to get the Veeam VSS integration to tap into this, using powershell scripts for example?
  4. If differential SQL backups are not possible will there be any provision for this in Veeam version 5?
  5. Is there a way to restore to a point in time in between the Veeam full backups, perhaps by backing up the drive containing the transaction logs seperately?

Sorry for the long post
Thanks in advance

Nick

[Gostev]

Hello Nick,

1. See here for some troubleshooting steps: Exchange 2003 no flushing of logs
2. I guess this comes down to how VSS works. SQL has to stop accepting new requests for a short period of time to process any pending transactions activities before it can be quiesced. If a few second timeouts inflicted by using VSS are totally unacceptable, you disable both Veeam VSS and VMware Tools quiescence (this is very important!) in the advanced backup job settings, and perform crash-consistent of your SQL.
3. No, we only implement incremental backups.
4. No, differentials are not planned for v5. Frankly speaking, we have only had 1-2 requests for this feature in past 2 years.
5. Sure, this is exactly how our customers are doing this. Restore Veeam backup, then apply transaction logs. I believe some have even posted about this on forums, if you are interested you may try to find these discussions using forum's search.

Hope this helps!

[tsightler]

My suggestion is to trust your DBA's. Veeam is a great tool, and it can be very useful for restoring your SQL VM's to a working state, but they're right, it simply does not offer as much protection as running SQL backups with 15 minute transaction log backups.

We basically combine Veeam with our native SQL backups. Our SQL backups run to disk on a central server. We backup both the SQL servers, and this central server with Veeam. For a DR scenario we can restore the SQL servers to a running state of the last Veeam backup, and from this point the DBA's can determine if this state is "good enough" (in many cases it is) or if they need to preform a full, transactional, up to the minute recovery from their backups, which for some databases can be very important.

I also don't think Veeam truncates SQL transaction logs, at least I've never seen it do this. Not sure about the frozen I/O, it may be VSS it may be the snapshot removal, but I'm amazed that there are applications that would fail from only 10 seconds of freeze time. That sounds like poorly designed applications since, if they can't survive 10 seconds of frozen I/O, they'll also likely never survive catastrophic SAN events that require failover due to path failures.

I've been wanting to test a point in time restore with Veeam, but I'm not sure if it would be possible or not. I think you could maybe do something like use the FLR restore utility to restore the MDB file without the transaction logs, then grab the transaction log from the following day, but that's pretty sloppy as far as I'm concerned.

The honest truth is, databases need special treatment that Veeam VSS simply doesn't provide right now.

[pigbloke]

Excellent stuff Gostev, thanks for the prompt feedback

1. I already ran the vssadmin commands to check that the SQL VSS writer was running. I guess the problem is something to do with our SQL server config or VSS config so I'll look into it.
2. I understand this is due to the VSS. Personally, I can live with the timeouts, but alas other people involved in this project have reservations. I'll see if I can dig up anything about reducing the duration of the I/O freeze.
3. OK
4. OK
5. I'll do some more searching on this to see if I can find a definitive way to do it. Would it be possible to backup the whole server once a day and then a seperate job to back up the transaction log vmdk every 15 mins. For a restore, use the latest full image backup and then apply the transaction logs from the other backup? Maybe this method would reduce the I/O freezes as well. I'll have to test it.

Thanks again

Nick

[pigbloke]

Tom, thanks for your input.

This is all up for discussion at the moment and it may well be that we do continue with the native SQL backups alongside Veeam or some other backup solution. From my point of view, I like Veeam and would like to do all our backups from one place AND have the ability to restore servers or perform DR without requiring input from other teams. If I can't do this reliably then so be it, I'm just ensuring I explore all avenues.

I though the SQL VSS writer would truncate the transaction logs as opposed to Veeam but I may be mistaken. I'll have to revisit some resources to ensure I haven't misunderstood.

The I/O freezes won't kill the applications, some people are just worried that a 10 second freeze every 15 minutes would be unacceptable to some of our customers. I don't necessarily agree but it's not really my decision.

Cheers

Nick

[Gostev]

Tom, could you please confirm what version of SQL are you using (where you do not observe transaction logs flushing) - I would like to have our QC to try to reproduce this, and then see if this can be addressed in v5. May be there is a way to configure SQL VSS writer to flush those logs. I would expect SQL logs to flush during our backup, just like this happens with Exchange logs.

[tsightler]

We're using SQL 2005 almost exclusively, although we still have a small smattering of SQL 2000 for a few special purpose databases, mostly vendor provided. We get flushes with Exchange, but I've never seen transaction logs flushed on a SQL 2005 server, I just assumed it was a technical decision not to do that since, in many cases, this is not what you want to happen. Perhaps there's some conflict with the native SQL backup however. Unfortunately that would make it exceptionally difficult to test. I'm not sure I even like the idea of Veeam flushing those logs as I simply don't think Veeam is 100% up to the task of performing point-in-time restores of SQL databases, but I guess I could always just disable VSS and know that they're only crash consistent.

[tsightler]

The I/O freezes won't kill the applications, some people are just worried that a 10 second freeze every 15 minutes would be unacceptable to some of our customers. I don't necessarily agree but it's not really my decision.

Oh, I see. I was thinking 10 seconds a night during backups, but 10 seconds every 15 minutes, yep, I could see that as a problem.

[Gostev]

I'm not sure I even like the idea of Veeam flushing those logs as I simply don't think Veeam is 100% up to the task of performing point-in-time restores of SQL databases, but I guess I could always just disable VSS and know that they're only crash consistent.

This should not be a problem. We are working on adding pretty granular control in v5 around transaction logs handling, so you will be able to use VSS but disable logs flushing at the same time.

[tsightler]

This should not be a problem. We are working on adding pretty granular control in v5 around transaction logs handling, so you will be able to use VSS but disable logs flushing at the same time.

Sounds like a nice feature, seems like v5 will be almost perfect.

Restores of MS SQL database with Veeam are currently an issue because it appears that the database is not left in "non-recovered" state to allow the application of additional transaction logs backed up outside of Veeam. It would be nice to have this option as it would simply the point-in-time recovery.

[tsightler]

This is all up for discussion at the moment and it may well be that we do continue with the native SQL backups alongside Veeam or some other backup solution. From my point of view, I like Veeam and would like to do all our backups from one place AND have the ability to restore servers or perform DR without requiring input from other teams. If I can't do this reliably then so be it, I'm just ensuring I explore all avenues.

I understand the desire to put everything in one basket, but when I visited this back a year ago or so we simply decided that databases were too different. Yes, from an operations perspective I can preform a DR and have the databases backup, but that's actually a very rare event. What's far more common is the DBA's needing to preform restores because of application upgrade failures or clones for development and testing or other scenarios. In most of those cases the DBA's don't need to involve my group to perform restores. That leaves the DBA's empowered to handle the day-to-day issues that they face all on their own and, and since they're the one's with the training and expertise in running SQL that's how it should be.

Our DBA's are actually very comfortable with snapshot based backups because we've been doing it a long time, long before Veeam, but there are simply things that snapshots can't do. I actually don't know about MS SQL, but with Oracle, the RMAN backups actually also preform block checksumming to detect corrupted blocks during the backup. These can happen due to silent disk corruption, bugs in Oracle, database crashes with short writes, etc. It's important to find these early.

We recently had a major disaster with the complete lost of a 6+TB storage array hosting roughly 4TB's of VM's. We restored all of these systems via Veeam, however several had serious database level issues with the restore, including corrupt blocks. Soem were so damaged they would not start. One of the MS SQL databases that was backed up only with Veeam had serious damage to it "master" database which left us unable to perform some basic operations. We had to preform some "risky" operations to recover the "master" database to return the server to normal operation. We were wishing we had native SQL backups that day.

[pigbloke]

Thanks Tom, this is all food for thought and I'll share your experiences with my colleagues. We're also exploring the option of backing up the raw data before it's processed by the database but not sure how viable this could be for us yet.

I would expect SQL logs to flush during our backup, just like this happens with Exchange logs.

Does anyone have a definitive answer for this? Myself and Tom both seem to seeing results to the contrary but perhaps I am missing something on the VSS side.

This should not be a problem. We are working on adding pretty granular control in v5 around transaction logs handling

Gostev, could you perhaps expand on these new features or are you limited on what you can say about v5?
It could be that v5 could provide enough functionality to satisfy all parties. Is it still scheduled for release next Quarter?

On a completely unrelated note are there any plans for a Citrix Xen Server version of Veeam in the future?

[Gostev]

We will test the SQL log flushing internally to confirm, and I will let you know. But I totally trust Tom, because he is never wrong

Anyhow, our new v5 code around VSS just hit the QC, and I will make sure this gets tested well, and we will see if any changes need and can be made around transaction logs handling and SQL.

I prefer to start talking specifics about new features only after they are confirmed by QC to work as expected. I would hate to "sell" you something right now which will then appear not to work as expected . What I can say is that we have implemented a number of very major improvements around our VSS implementation which have been very highly requested by our customers on this forum.

Yes, the release is still scheduled for Q3 (towards the end) and I am sure we will make the originally planned date.

There are no plans to support Citrix XenServer in the close future. Hyper-V support will most likely come first.

[tsightler]

We will test the SQL log flushing internally to confirm, and I will let you know. But I totally trust Tom, because he is never wrong .

That just means you haven't known me long enough! Even I question myself on this after writing about it but I'm definitely not seeing log flushes on our primary SQL 2005 server. Could this be somehow related to the fact that we're running SQL native backups as well? I remember when we first started using Veeam that we weren't seeing Exchange log flushes and this issue seemed to go away once we stopped using our native Exchange backup tool. Perhaps it will not flush logs unless there as been no log flush since the last VSS backup? I need to test this some more.

[mdornfeld]

Funny, me and my DBA were just talking about this after one one of our SQL servers stopped working after the logs filled up recently. This is one of our new servers that didn't have the regular SQL jobs set up on it yet to do the truncating, and I was running a Veeam job on it every 4 hours. I was always under the impression that the SQL logs were flushed, but now I'm thouroughly confused. Before I saw this article, I opened up ticket ID#527184 with Veeam to see what support said... I'll let you all know what they come back with if Anton doesn't come back first with the answer.

[pigbloke]

Thanks for all the feedback Gostev, v5 could be perfect for us but I understand you can't elaborate on what you've already mentioned. The Xen server question was just an "I wonder if" moment as we have a couple of Xen hosts on a different network to our VMWare hosts.

At the moment we are still using native SQL backups on our production servers and running Veeam backups on a couple of test servers. We are still discussing a few options regarding our future backup plans and still looking into the VSS/transaction logs issue. I'll update you if we come up with anything concrete.

[mdornfeld]

Here is the official answer from Veeam Support when I asked:

"We have discovered that Veeam B&R 4.X.X doesn't trancate SQL Tlogs in some conditions.
This is a issue of the product. R&D is working on this. I hope it will be fixed soon.
You should use a MS SQL tool instead."

[Gostev]

Hmm, this answer from our support is incorrect - it looks like there was miscommunication between this support engineer and development. I will take this to support management for investigation.

I have personally discussed the results of the research with our lead developer earlier today, and was going to update this thread anyway. Sorry it took a while, but research was quite deep.

Here are the correct answers:
1. SQL transaction logs are not truncated in ANY conditions with SQL VSS writer.
2. This is NOT an issue with our product or Veeam VSS integration implementation, but current SQL VSS writer design.

According to our research, the reason is that Microsoft SQL VSS writer does not have transaction logs processing implemented (unlike Exchange VSS writer). Reversing of the Microsoft SQL VSS writer had shown that it is based on native SQL API, but only implements a handful of API calls around database backup. Logs truncation requires using leveraging API calls for transaction logs backup during VSS freeze, which clearly does not happen. Additionally, we have found some technical Microsoft documentation confirming that SQL VSS writer does not implement transaction log processing.

It is not clear whether SQL VSS Writer does not implement transaction logs processing "yet" or "by design". It appears that SQL only flushes transaction logs after you perform so called "transaction log backup", which is special type of SQL backup that you can initiate with Management Studio (please bare with me if this is obvious for you, because this is new knowledge for me). Regular SQL database backups do not trigger log flushing, and SQL VSS Writer only implements this part. I am still puzzled why Exchange VSS writer's behavior is different, most likely Exchange and SQL are using transaction logs differently - although overall the restore concept is the same (restore full backup, replay logs on it).

So right now, in order to flush SQL transaction logs, you need to specifically perform transaction log backup with native or other tools.

From our side (now that we are sure we cannot rely in SQL VSS writer to flush the logs), we will continue the research and see if we could provide our own, optional transaction logs management for SQL. We have some ideas based on our research, and if they prove to be working, we will include this new feature into v5.

[mdornfeld]

Thanks Anton.
The real problem was that I was confused as to whether Veeam was truncating SQL logs or not. IMHO, I don't necessarily want Veeam or think Veeam should be focusing in on trying to implement that feature. I'd prefer just to have my DBA use the native SQL tool to take care of it.
Thanks! Matt

[Gostev]

Agree... but our ultimate goal is to help you get rid of those other tools single pass image level backup is theoretically all what is needed. And, if it is not sufficient today, that means our product lacks some functionality which needs to be added.

[fieldswe]

Hello Gostev,

We have just started using Veeam 4.1.1 in our environment ( Windows 2008 and SQL Server 2008 ). I found this forum and the thread seem to hit on some of the issues we're seeing. I noticed in your reply to Nick at the top of this thread you said that customers are using Veeam to restore SQL Server databases and appying transaction logs. When we restore the Database files and start SQL Server, it recovers the database without putting it into RESTORING state. So I'm not able to apply transaction logs. I've search the forum, but so far haven't found any post specifically stating how this is accomplished. We have restored entire VM and just the DB files and the result has been the same. Any pointers you or anyone else reading this thread can give would be greatly appreciated. I'll continue to search the forum and I'll update this thread if I find a solution.

Thanks,

William

[Gostev]

Hello, apparently that was incorrect statement on my side, sorry. In fact, as you can see from the later statement from Tom in this thread, it is impossible to apply transaction logs to restored SQL Server, because this requires putting SQL Server in a special state at restore time. Sorry about any confusion I may have caused. We are currently investigating our options around this feature request.

[tsightler]

I have not had a chance to try this, but I believe that it may be possible to apply transaction log backups if you manually restore the MDF file, but not the LDF file.

Also, there are third party tools like Apex SQL Log that can access LDF files as well as TRN backup files and selectively restore transactions.

[pigbloke]

From our side (now that we are sure we cannot rely in SQL VSS writer to flush the logs), we will continue the research and see if we could provide our own, optional transaction logs management for SQL. We have some ideas based on our research, and if they prove to be working, we will include this new feature into v5.

Gostev, the statement above is very encouraging. Would you be able to keep the community updated with Veeam's progress in this area. If this is indeed included in v5 we could be onto a winner

Thanks

Nick

[Gostev]

Hello Nick, we have already added functionality to optionally flush SQL transaction logs into v5 (implemented by leveraging native SQL API). By the way, v5 is a winner with or without this functionality included - trust me on this one

[pigbloke]

That's great news Anton. I look forward to the release

[sqldba]

I have been looking for the resolution to the issue where we are using the VSS/VEEAM backup solution and I am backing up the .trn logs through sql. I would like to be able to do a restore of the VSS/VEEAM backup and somehow use the .trn logs so that I can have point in time after the full VSS/VEEAM backup. I see some discussion of that here, but I do not see a resolution. Is this included in the V5 of VEEAM?

We currently use the VSS/VEEAM solution for all of our Development and Test databases because we use the Simple backup solution. We would love to use the Full backup solution with transaction log backups (.trn) do do a restore at a specific time.

Thanks for any help.

[Gostev]

AFAIK this is not possible today, because to do that SQL needs to be restored in a special mode (with a special flag), and we do not do this today. Thanks.