Host-based backup of VMware vSphere VMs.
Post Reply
redhorse
Expert
Posts: 219
Liked: 12 times
Joined: Feb 19, 2013 8:08 am
Full Name: RH
Location: Germany
Contact:

Veeam SQL Database

Post by 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
Product Manager
Posts: 9849
Liked: 2606 times
Joined: May 13, 2017 4:51 pm
Full Name: Fabian K.
Location: Switzerland
Contact:

Re: Veeam SQL Database

Post by 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.
Product Management Analyst @ Veeam Software
bernd.dausch
Service Provider
Posts: 21
Liked: 1 time
Joined: May 27, 2016 7:17 pm
Full Name: Bernd Dausch
Contact:

Re: Veeam SQL Database

Post by bernd.dausch »

Are there any Plans To Switch the database to a free one. MySQL, PostgreSQL?
redhorse
Expert
Posts: 219
Liked: 12 times
Joined: Feb 19, 2013 8:08 am
Full Name: RH
Location: Germany
Contact:

Re: Veeam SQL Database

Post by 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
Veteran
Posts: 3077
Liked: 455 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Veeam SQL Database

Post by 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
Veeam Software
Posts: 21138
Liked: 2141 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Veeam SQL Database

Post by foggy »

redhorse wrote: Feb 25, 2020 3:30 pm Are these tables also included in the Veeam configuration backup?
Configuration backup contains the entire database, so yes, they are included.
PetrM
Veeam Software
Posts: 3625
Liked: 608 times
Joined: Aug 28, 2013 8:23 am
Full Name: Petr Makarov
Location: Prague, Czech Republic
Contact:

Re: Veeam SQL Database

Post by PetrM » 1 person likes this post

Hello,
redhorse wrote: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.

Mildur wrote: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
Expert
Posts: 219
Liked: 12 times
Joined: Feb 19, 2013 8:08 am
Full Name: RH
Location: Germany
Contact:

Re: Veeam SQL Database

Post by 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
Veteran
Posts: 3077
Liked: 455 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Veeam SQL Database

Post by wishr » 1 person likes this post

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
Expert
Posts: 219
Liked: 12 times
Joined: Feb 19, 2013 8:08 am
Full Name: RH
Location: Germany
Contact:

Re: Veeam SQL Database

Post by 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
Veteran
Posts: 3077
Liked: 455 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Veeam SQL Database

Post by wishr »

Could you please clarify your question?
redhorse
Expert
Posts: 219
Liked: 12 times
Joined: Feb 19, 2013 8:08 am
Full Name: RH
Location: Germany
Contact:

Re: Veeam SQL Database

Post by 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
Veteran
Posts: 3077
Liked: 455 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Veeam SQL Database

Post by 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
Expert
Posts: 219
Liked: 12 times
Joined: Feb 19, 2013 8:08 am
Full Name: RH
Location: Germany
Contact:

Re: Veeam SQL Database

Post by 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
Veteran
Posts: 3077
Liked: 455 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Veeam SQL Database

Post by 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
Expert
Posts: 219
Liked: 12 times
Joined: Feb 19, 2013 8:08 am
Full Name: RH
Location: Germany
Contact:

Re: Veeam SQL Database

Post by 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
Veteran
Posts: 3077
Liked: 455 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Veeam SQL Database

Post by 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
Novice
Posts: 7
Liked: 1 time
Joined: Aug 14, 2017 12:16 pm
Contact:

Re: Veeam SQL Database

Post by 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
Expert
Posts: 219
Liked: 12 times
Joined: Feb 19, 2013 8:08 am
Full Name: RH
Location: Germany
Contact:

Re: Veeam SQL Database

Post by 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
Veeam Software
Posts: 3625
Liked: 608 times
Joined: Aug 28, 2013 8:23 am
Full Name: Petr Makarov
Location: Prague, Czech Republic
Contact:

Re: Veeam SQL Database

Post by PetrM »

Hello,

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

Thanks!
wishr
Veteran
Posts: 3077
Liked: 455 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Veeam SQL Database

Post by 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.
Post Reply

Who is online

Users browsing this forum: Egor Yakovlev and 74 guests