SQL Server - How well does it work for you within Veeam B&R?

[VanillaMastermind]

I am the DBA for a company currently looking into Veeam B&R. We have less than 1,000 machines. I have chatted with the Veeam reps and we may be setting up a POC soon. I have watched the SQL Server webinars, and everything looks great. I think we should be able to handle all backup and restore scenarios with Veeam and get rid of our native SQL Server backups.

I am interested in hearing from other DBAs (or others experienced in SQL Server backup and recovery) who are using Veeam B&R for SQL Server backup and recovery.

• Have you needed to do a file-level recovery of a system database? Did it work? (My understanding is that you can't recover a system database through the SQL Server Explorer)
• If I backed up a new 100 GB database yesterday, and only 50 MB changes today, my next backup should be nearly instantaneous, right? Even though VSS in SQL Server would register it as a FULL backup (not LOG), in reality, it's more like a DIFF backup. Is that correct? There wouldn't really be any need for a true DIFF backup. I would just kick off a full backup in Veeam. I think this would only be helpful with a database in Simple recovery mode.
• Is there a way to measure data change in Veeam Explorer? In the example above, can I see that only 50 MB was modified?
• Do I leave Backup Compression enabled on the SQL Server side?
• How has the overall experience been? Have you run into any scenarios that you couldn't handle?
• Have restores to a separate instance worked well? We currently do this for restore testing, but we may not need to with the Virtual Lab feature
• Do you use the Virtual Lab feature to do Service Pack/Cumulative Update/Windows Update installation tests?

Thanks in advance for any feedback and suggestions!

[alanbolte]

I'll let actual DBAs answer the rest but I thought I'd help out with this one:

If I backed up a new 100 GB database yesterday, and only 50 MB changes today, my next backup should be nearly instantaneous, right? Even though VSS in SQL Server would register it as a FULL backup (not LOG), in reality, it's more like a DIFF backup. Is that correct? There wouldn't really be any need for a true DIFF backup. I would just kick off a full backup in Veeam. I think this would only be helpful with a database in Simple recovery mode.

Veeam backups are image-level for a whole SQL server VM (ignoring the separate transaction log backups). You won't be backing up individual databases, just restoring individually. Both full and incremental backups will register as a full backup to MS SQL unless you use the 'copy only' option, in which case MS SQL won't know it has been backed up. Incremental backups can in some ways be faster than DIFF because they are recording only the changes since the last incremental backup, rather than all the changes since the last full. However, even if you had only 50MB change across all databases on a particular SQL server, the incremental backup might still be 100s of MB or even several GB because with an image-level backup you're reading more than just the databases, you're also backing up stuff like Windows temporary files that change frequently no matter what you do. DIFF backups in MS SQL won't work properly if you are also running Veeam backups (except if they are set to Copy Only).

[VanillaMastermind]

<BUMP> Anyone else out there have thoughts on SQL Server in Veeam? I am hoping I'm not getting a lot of responses because it works SO WELL that no needs to use the support site!

[wa15]

I have been using Veeam to back up SQL 2005, 2012 and 2014 instances as frequently as every hour and we love it. The only issue we ran into was when we were backing up a 2005 instance, the logs were not getting truncated but that ended up being something unique in our Windows 2003 Cluster configuration and we could only put about half the blame on Veeam. But other than that, it is backing up our (rather busy) SQL instances quite nicely and the many restores (database level) we have had to do have been successful. We have done database restores to a separate instance and those have not been an issue. We use the virtual lab feature as part of SureBackup to confirm that our backups are completing successfully.

We have left backup compression on default settings on SQL boxes, can't remember if that is disabled or enabled off hand. The best method in measuring data is looking at the backup job statistics and seeing how much data has changed. Remember that Veeam backups the entire VM.

Hope this answers some questions.

[Gostev]

By default, compression is ON, with level set to OPTIMAL.
And I highly recommend everyone keeps these settings intact

Thanks for your feedback!

[matt_778]

I believe that the SQL instance that runs Veeam is used somehow for the restores - so you cant back-end Veeam with a SQL Express DB and expect to do item restore on a DB larger than 10GB (SQL Express limit)
So, sounds like in your instance you want Veeam to be using SQL Std minimum.

Also don't backend Veeam on the SQL server you are backing up, so you don't get in a chicken and egg scenario if trying to recover that Server

Search for this file veeam_best_practices_vmware.pdf dated Sep 2015 (pg24 for you)

[Gostev]

Local SQL Server instance is only used for log parsing during the transaction-level recovery (when you are restoring a database to the specific unwanted transaction, aka fine tuning). Regular point-in-time recovery that is done by selecting the desired time to restore database to using the time slider does not leverage local SQL instance, as logs are replayed to the selected moment in time directly on the target SQL Server.

[KiwiJJ]

One issue I came across was when the SQL server was "stunned" during the commit phase of the snapshot all of our application servers lost connection to the database. Possibly due to the fact that the apps don't like losing connection and are unable to reconnect. We ended up sticking with native SQL backups. We have since upgraded to SQL Server 2008 R2 and have upgraded VMware to the latest 5.5 version so will attempt to try again to use Veeam in the near future.

[matt_778]

Local SQL Server instance is only used for log parsing during the transaction-level recovery (when you are restoring a database to the specific unwanted transaction, aka fine tuning). Regular point-in-time recovery that is done by selecting the desired time to restore database to using the time slider does not leverage local SQL instance, as logs are replayed to the selected moment in time directly on the target SQL Server.

Thanks Gostev - I've cut n pasted my reference:

Another consideration relates to disaster recovery plans. By default, Veeam Explorer for Microsoft SharePoint and Veeam Explorer for Microsoft SQL will use a local SQL Server instance as a staging system for database restore (for details, see
http://helpcenter.veeam.com/backup/80/explorers/) and SQL databases in production environment are likely to exceed 10 GB, so Express Edition will not be able to support them. Thus, it is necessary to use Standard or Enterprise Edition if you plan to do any of the following:

Process more than 4000 VMs (also recommended for 400 – 4000 VMs)
Use Veeam Explorer for Microsoft SQL Server or Veeam Explorer for Microsoft SharePoint with databases bigger than
10 GB. If using any special features of Microsoft SQL Server or Microsoft Sharepoint that are only available with Standard
or Enterprise SQL version, you have to use the corresponding version with Veeam. In other deployment cases, accepting the limitations, it is possible to use Microsoft SQL Server Express Edition

https://www.google.co.nz/url?sa=t&rct=j ... ew&cad=rja

[mkaec]

Does this mean that SQL Server standard needs to be installed on the same server as Veeam, or does Veeam user whatever database server that has been configured for its own database?

[skrause]

One issue I came across was when the SQL server was "stunned" during the commit phase of the snapshot all of our application servers lost connection to the database. Possibly due to the fact that the apps don't like losing connection and are unable to reconnect. We ended up sticking with native SQL backups. We have since upgraded to SQL Server 2008 R2 and have upgraded VMware to the latest 5.5 version so will attempt to try again to use Veeam in the near future.

The snapshot stun will happen pretty much with every version of SQL/Windows/VMware. The stun is caused by VMware committing the changes from the snapshot into the main VMDK file during consolidation. Applications that don't like losing connection at all can be problematic in pretty much every case I have had so far. We use SQL 2012/2014 Always-On to avoid as much of the snapshot removal stun issue as we can, but our applications seem to be able to reconnect without much problem (in process queries being the big exception).

Does this mean that SQL Server standard needs to be installed on the same server as Veeam, or does Veeam user whatever database server that has been configured for its own database?

Veeam can use an external SQL server for its database.

[mkaec]

I found this in the v9 "What's New" guide. It looks like the issue of using Veeam Explorer with large databases has been addressed.

"Remote staging SQL Server support for transaction-level recoveries, eliminating the need to license a copy of SQL Server installed on the Veeam Backup & replication server with the highest edition of SQL Server used in your environment"