Veeam SQL Database

[redhorse]

Hello,

we had recently an issue with the Veeam DB which was full (SQL Express) and therefor all services were interrupted. The support worked on that case and truncated some tables to reduce the DB size by 3 GB.

The support recommended us to migrate to SQL Standard which was very surprising for my because we have a small environment with less than 50 VMs. Are there any official recommendations or sizing best practices?

If we migrate to SQL Standard which CAL licenses should we buy? Obviously we can’t buy core licenses as the we don’t want to pay more for SQL than for Veeam. We have a SQL Server in a VM but I suppose we should have it directly on the backup server to avoid dependencies in worst case scenarios.

Thanks.

[Mildur]

You can find Veeam Best Practice on this website (written by veeam folks)

https://www.veeambp.com/backup_server_i ... r_database

i find it strange, that you need a SQL Server Standard. On the Veeam BP Guide, they write you will need a SQL Standard above 500 vms.

[bernd.dausch]

Are there any Plans To Switch the database to a free one. MySQL, PostgreSQL?

[redhorse]

Now I got the feedback that these tables had to be truncated:

[VeeamBackup].[dbo].[Tape.file_versions]
[VeeamBackup].[dbo].[Tape.file_parts]
[VeeamBackup].[dbo].[Tape.files]

Now there the database size is about 3 GB which is totally fine. I still wonder which tape information were stored in the database as we use file to tape just for rare exceptions.

Are these tables also included in the Veeam configuration backup?

[wishr]

Hi everyone,

@bernd.dausch, currently, there are no short-term plans to move off Microsoft SQL Server.
@redhorse, could you please share with us your support case ID you had open on this matter? By any chance, do you use Backup to Tape and/or File to Tape jobs?

Thanks

[foggy]

Are these tables also included in the Veeam configuration backup?

Configuration backup contains the entire database, so yes, they are included.

[PetrM]

Hello,

I still wonder which tape information were stored in the database as we use file to tape just for rare exceptions

These tables list all files written to tape:
[dbo].[Tape.files] - names of files and their id. If a file is not in this table, it will be put on tape as “new” file.
[dbo].[Tape.file_parts] - same, but important when a file is split in two between two tapes – then it will be mentioned twice.
[dbo].[Tape.file_versions] - tracks last modified date and size. If the file is changed, it will be put on the tape again.

Please keep in mind that all direct interactions with Veeam configuration database can be performed by support team only.

On the Veeam BP Guide, they write you will need a SQL Standard above 500 vms.

In most cases, the number of VMs plays a key role for database sizing however any case which requires to process many objects makes database to grow.
For example, when you use file to tape jobs extensively or backup many files.

Thanks!

[redhorse]

Good evening,

@wishr
The case number is #04019561, actually we have only backup to tape jobs. There some tapes which were used for file to tape after our migration von BackupExec to Veeam but we've removed them all from catalog directly after the backup because we new that this has an affect to the database size.

So, I can't imagine which information were stored in the mentioned tables as I can't image that the file size was reached by the backup to tape jobs.

Just another question: Is it possible to only delete a catalog from the database without removing the tape?

[wishr]

Hi RedHorse,

As far as I see our engineers have cleaned the file to tape jobs data, so there is no harmful impact at all keeping in mind you do not use the file to tape jobs anymore. Please refer to the comment by Petr above for details regarding the сontents of the tables.

Thanks!

[redhorse]

Hi wishr,

we still have a few old file to tape jobs, so we just have to catalog them to get the content information?

I'd like to ask another question again as it whould be convenient for us to remove a catalog via the GUI without removing the tape from Veeam: Is it possible to only delete a catalog from the database without removing the tape?

[wishr]

Could you please clarify your question?

[redhorse]

Yes:

Question 1: I have some old tapes which are not in the catalog anymore, maybe these were the information in the affected tables. Do we just have to catalog these tapes to get the information back again?
Question 2: Is it possible to remove catalog data of file to tape jobs from Veeam without removing the tape entry itself?

[wishr]

Thank you. A few additional questions to get a better understanding of your needs.

Q1: What kind of information you are talking about? Why would you need to get it back?
Q2: What do you call a "catalog data" and what is "tape entry"? What you'd like to achieve by doing so, what's the main goal?

Thanks in advance.

[redhorse]

Q1: After truncating the three tables, the list of files stored on tapes are not available anymore. Therefor I need to catalog a tape to get the information which files are stored on a particular tape, am I right? So, if I want to know if the file "important.xlsx" is stored on tape 47 I need to insert that tape and start a "Catalog Trape"-Job so that the file information appears again? I hope it's clear, now?

Q2: As already said we have a few old tapes with file to tape jobs and I don't want to leave the file index in the database as these increases the database and I need these tapes very rare. In case I need a tape I can catalog it. Now, I'd like to know if I can just remove the catalog information of a tape and not the tape itself from Veeam. That ensures that the tape is still known and aware for the administrators and I can catalog it on demand.

[wishr]

Thanks for clarifying.

Q1: Correct. You will require to catalog a tape if you need to search through the file index, but that may potentially overflow the SQL database again. So in the long term, you may want to resolve the root cause itself and migrate to SQL standard.
Q2: That has already been done by our support engineer - they have cleaned the file metadata manually. The only thing you may want to do additionally is to make sure there are no jobs targeting these tapes now or even move them to a separate media pool to make sure the existing on tapes data stays intact.

Another option to clear up the catalog information is to mark a tape as free, but this definitely requires to move all the marked as free tapes to a separate media pool that is not targeted by any jobs, otherwise, the data on tapes may be overwritten as B&R will treat them as free. Please also note that starting from v10 the "Mark as free" operation cleans the database gradually in order to decrease the load on it, so it may take some time before you'll notice the outcome.

Thanks and let me know if you have any additional questions.

[redhorse]

Okay, thanks. We can migrate to SQL Standard but I'm still not sure which CALs we need.

Do we need a SQL Device CAL for all devices where the Veeam Console is installed? Do we need licenses for devices with agent, services or anything else related to Veeam installed?

I know that Microsoft licensing is annoying but I wonder if there is not an official recommendation by Veeam?

[wishr]

Unfortunately, we cannot comment on Microsoft licensing, but from our perspective, to resolve the current situation you just need a SQL server standard license for each server hosting the configuration database of Veeam B&R. If you have just a single backup server, then it's just a single SQL Server license.

Thanks

[LazyLoneLion]

What is the simplest way to monitor the size of the DB? Is there a counter -- windows performance counter or maybe Veeam ONE monitor/Report counter?

Or do you simply look at the size of SQL DB files?

[redhorse]

We just monitor the SQL DB size to avoid an increase beyond 10 GB, I don't know if there are other ways to monitor it.

[PetrM]

Hello,

I think you can track DB files size or get this information using SQL query.

Thanks!

[wishr]

Hi everyone,

You may use the following SQL query to determine the size of a database:

Code: Select all

use yourdatabasename
exec sp_spaceused

Replace "yourdatabasename" with the name of the desired database.

I hope it helps, thanks.