Recommendations for backing up vCenter Database

[mzamesnik]

I have seen discussions surrounding this, but have not found the answer I was looking for. We are on Veeam 8.x, vSphere 5.5 with a separate vCenter server from our vCenter database server.

Currently, the vCenter database server is on Win2K12 R2 With MSSQL 2014. It is in full recovery mode, and the job properties in Veeam are using VM Tools quiescence. The job runs fine, SQL shows a backup occurring with a checkpoint, and subsequently, the logs are truncated.

If we were to switch to Application-Aware Image Processing, my understanding is that the vCenter database will be skipped automatically. If this is the case, does VM Tools quiescence actually make more sense if we wanted to keep the database in full recovery model?

What is best practices for backing up and protecting the vCenter database and server?

[tsightler]

My normal answer to your question is to put the vCenter DB into simple recovery mode, use AAIP, and know that you can restore the vCenter database to the point of your last Veeam backup, either by restoring the entire VM, or by restoring only the vCenter DB using SQL Explorer. Is there some specific reason you want to keep the DB in full recovery mode? If you not doing periodic log backups there's not really any reason to use full recovery mode and it doesn't seem like you are doing that even with your current configuration (using VM Tools quiescence).

[mzamesnik]

Thanks for the reply. My impression was that AAIP would skip the vCenter database altogether. So, if it were to remain in Full recovery mode, it would actually be better to backup with VM Tools quiescence since it would create a checkpoint and would truncate the logs, where as AAIP would skip the database and the logs would grow.

Personally, I would agree with you, and is what I plan to recommend. I simply stumbled on this issue because I had disabled VM tools quiescence on the job, and the logs eventually filled up. I had assumed we were using simple recovery mode, but I was wrong. It started a discussion on best practices, and I just wanted to get perspective from Veeam on their best practice recommendations, which it appears you have provided adequately. So, thank you!

[btmaus]

My impression was that AAIP would skip the vCenter database altogether. So, if it were to remain in Full recovery mode, it would actually be better to backup with VM Tools quiescence since it would create a checkpoint and would truncate the logs, where as AAIP would skip the database and the logs would grow.

My understanding is that AAIP would actually truncate the SQL logs.

[Vitaliy S.]

You can select different behavior for AAIP logs processing > Transaction Log Settings: Microsoft SQL

And btw, VM Tools quiescence does not truncate logs at all.

[mzamesnik]

You can select different behavior for AAIP logs processing > Transaction Log Settings: Microsoft SQL

And btw, VM Tools quiescence does not truncate logs at all.

Yes. I am aware of the different behavior for AAIP logs processing for SQL in general. But, I can tell you that in our case, I have hard evidence that when the Veeam backup is occurring with VM tools quiescence, it is showing the database being backed up in the SQL logs, and the transaction logs free space goes from whatever it is at at the time, to about 90% free space. This indicates to me that the logs are being truncated somehow, whether or not it is directly from the VM Tools quiescence directly I don't know. Further, when we stopped VM Tools quiescence, the logs filled, grew, and eventually used the entire drive available, causing vCenter to stop responding. The database is in Full recovery mode and is SQL 2014.

In the end, I plan on just switching to Simple recovery mode, and not worrying about it, unless our team lead requires it for some unknown reason. But, I was curious of the best practices from Veeam's perspective. From my knowledge, I was under the impression that AAIP skips the vCenter database automatically in more recent versions, where in earlier versions, it would cause a VSSControl timeout error. If you did want to use AAIP before, you needed to back it up directly from the host. I was curious if anything had changed.

[tsightler]

From my knowledge, I was under the impression that AAIP skips the vCenter database automatically in more recent versions, where in earlier versions, it would cause a VSSControl timeout error. If you did want to use AAIP before, you needed to back it up directly from the host. I was curious if anything had changed.

To be clear, it skips the freeze of that database by excluding it from the VSS writer freeze process, it is not excluded from the backup itself, it is simply backed up in crash consistent mode without the freeze. Since the full image backup include both the MDF and LDF file at the point of snapshot, SQL will simply perform a media recovery of the database on the next startup. However, since Veeam is not freezing the database, and thus it is not tagging it as being backed up we cannot perform log backups or point-in-time restores and it is not really safe for us to truncate the logs as the primary reason for the database to be in "Full" or "Bulk" is to allow those types of restores (there's really no reason to run in these modes if you're not also performing log backups).

So, with AAIP enabled and the vCenter database in simple mode, Veeam will still backup the vCenter database in a crash consistent manner, and the database will automatically perform recovery if the VM is restored in full, or if the database is restored using SQL Explorer.

[HendersonD]

We run a SQL 2014 server that houses about 20 databases. This server is virtualized under ESXi 6.0 Update 2. One of the SQL databases is our Virtual Center database. When Veeam was installed the Virtual Center server database was excluded from application aware processing.

I logged into the SQL server, launched SQL Management Studio, and right clicked on a database to view its properties. One of the properties is when the database was last backed up. I backup the SQL server each evening so for nearly all of my databases this field in SQL Managements studio shows the database was backed up the previous evening.

For my Virtual Center database the "Last Backup Date" is listed as None. This leads me to one of two conclusions:

1. The Virtual Center database is not being backed up at all. I thought that just excluding it from application aware processing would still allow it to be backed up
2. Veeam does not change the field that displays in Management Studio to show the database is actually backed up

I have an outside consultant I want to hire to do some work on this SQL server. He refuses to touch this server since it shows the my Virtual Center server database has never been backed up

[HendersonD]

I opened a ticket on this and support said that the Virtual Center database is NEVER backed up by Veeam. They told me to contact VMWare to find a way to backup the Virtual Center database

[tsightler]

I hate to say it, but support is incorrect. As noted above, the vCenter database is included as part of the full VM image, so any time you backup the VM the database is backed up and, if you run SQL Explorer, you should see the database listed as one of the databases that can be restored.

However, Veeam does not update the "backed up" field in the database. That's because applications don't update this field directly as it is automatically updated as part of the normal calls to the VSS/VDI APIs to tell SQL that a backup process has started and completed. However, since we exclude the vCenter database from this function, the field will never be updated. From the perspective of the SQL server, and this database, this is totally correct as it is completely unaware of the VM snapshot and image being taken since, without VSS, how could it be? However, it's lack of awareness that it's being backed up does not mean that the database is not included in the VM image.

If you'd like to make your consultant happy, just run a nice, native SQL backup of the vCenter database to some disk location temporarily.

[HendersonD]

I sent another inquiry off to support and you are correct. The MDF and LDF files are backed up but without VSS the backup date is not written. I just made a SQL maintenance job to backup the vCenter database each evening and place the backup file on a folder on the C: drive of the SQL Server. These backup files will also be caught by the Veeam Backup. This will at least put a date stamp on the last backup and make my consultant happy.

Thanks for the expertise!