VEB Config - why SQL?

[ChuckS42]

Just getting up to speed on Veeam Endpoint Backup, for potential use as a replacement for another vendor's backup software for protecting physical servers. I see the VEB client saves its configuration in a local SQL database, requiring the installation of SQL LocalDB. If I go this route, I'm going to get a lot of questions of why I'm installing what some will see as "yet another" SQL instance, with the extra management that will need. Also, I'd be using VEB to existing SQL servers; I trust the LocalDB install won't interfere with existing SQL instances.

So my questions are:

A) Why SQL? Seems an overly complex way to store a client configuration.
B) Is this a permanent decision, or will this be changed as the product matures?

[Gostev]

Hi

It is important to realize that LocalDB is not "yet another SQL instance" that requires "extra management", but rather lightweight runtime-only database engine. LocalDB is very similar to SQLite database engine used by Firefox or Chrome, the only difference is that SQLite runs in-proc (so you can't see it in Task Manager), while LocalDB runs out of proc.

By using LocalDB, we are able to re-use big chunks of code from our enterprise backup product that is in fact based on "full-blown" SQL Server. This significantly reduces the costs of development and testing, which is very important for a free product

On the other hand, we don't see any benefits in using some other database engine instead.

Thanks!

[ChuckS42]

Understood. Thanks for the clarification!

[mkaec]

I imagine some people might be skittish about LocalDB because of past bad experiences with applications that bundled the express edition of SQL server.

[Stefan121]

It is important to realize that LocalDB is not "yet another SQL instance" that requires "extra management", but rather lightweight runtime-only database engine.
...
By using LocalDB, we are able to re-use big chunks of code from our enterprise backup product that is in fact based on "full-blown" SQL Server. This significantly reduces the costs of development and testing, which is very important for a free product

Hi,
Thanks for the free version and I totally understand the reason, why a LocalDB instance is used. And I agree basically tor the re-use of code for the free version. But LocalDB is not "lightweight": 578MB private bytes and 378 MB working set size is too large for naming it lightweight.

As a real lightweight alternative - with more code reuse than SQLite - I want to mention: SQL Server CE
Moving data: http://erikej.blogspot.de/2012/02/migra ... erver.html

JM2c
Stefan

[Gostev]

But LocalDB is not "lightweight": 578MB private bytes and 378 MB working set size is too large for naming it lightweight.

I think your RAM consumption is worth investigating, as normally it should be 10 times less.

[mkaec]

Stefan121's stats are in line with what I am seeing.

Workstation: 414 MB Private Bytes, 187 MB Working Set
Server 1: 598 MB Private Bytes, 251 MB Working Set
Server 2: 579 MB Private Bytes, 236 MB Working Set
Server 3: 1157 MB Private Bytes, 627 MB Working Set
Server 4: 1156 MB Private Bytes, 624 MB Working Set
Server 5: 1177 MB Private Bytes, 633 MB Working Set

I agree that "lightweight" is not a good description. But, for me, I'd rather have this kind of usage than not have VEB at all.

The servers that have higher memory usage happen to have more installed memory. I wonder if LocalDB is adjusting its usage based on installed memory.

[Dima P.]

Hi Marc,

Just to be on the same page, can you make sure that VEB is the only application using SQL? Thanks.

[mkaec]

I believe it is. The 5 servers are actually dedicated Hyper-V hosts. When I check the process on the workstation, the command line is:
"C:\Program Files\Microsoft SQL Server\120\LocalDB\Binn\\sqlservr.exe" -c -SMSSQL12E.LOCALDB -sLOCALDB#EEC01435 -d"C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\VeeamEndPoint\master.mdf" -l"C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\VeeamEndPoint\mastlog.ldf" -e"C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\VeeamEndPoint\error.log"

[Mike Resseler]

Marc,

We truly believe it is too much so worth investigating. Can you create a support call (it is free, but comes without SLA) so our engineers can look at it?) For your information, my working set is at 100MB and private bytes at 77...

In the support call, please mention this thread (link to it) also.

Thanks

Mike

[Stefan121]

Hi,

thanks for taking the memory consumption into account.

Just to make clear: VEB itself uses only 100 MB Workingset and 70 MB Private bytes as you mentioned.
But the SqlServer instance for LocalDB is using the large memory 468 PB and 276 WS (right after reboot). VEB is the only user of MSSQL 2014.

All other tools are using the installed MSSQL 2012 or even 2005.

BTW: LocalDB was intended more for developer than for production: https://blogs.msdn.microsoft.com/sqlexp ... l-express/

[mkaec]

That blog article actually states "Moreover, if the simplicity (and limitations) of LocalDB fit the needs of the target application environment, developers can continue using it in production, as LocalDB makes a pretty good embedded database too."

[mkaec]

...We truly believe it is too much so worth investigating. Can you create a support call...

I opened support case 01761394 from within VEB on one of the servers I previously listed with a link to this thread in the description.

[mkaec]

I have some results to report from the support case.

The very first response I received was that "Generally MS SQL Express LocalDB uses 200-300MBs of RAM". I was asked to run an SQL command to limit LocalDB to 400 MB and it wouldn't start after that. I was able to use another instance of VEB to restore the LocalDB.

I was also asked to remove AV and test. After removing AV and rebooting, I found that LocalDB memory usage was lower than before the reboot. It was around 150 MB. However, I couldn't be sure if this was due to the AV removal or the reboot. I ran some backups and found that LocalDB memory usage increased a little bit after each backup. After 37 backups, it was comparable to what I saw before removing AV. After I put back AV and rebooted, I found the memory usage was actually 72 MB.

Normally, one might consider this a memory leak. But, I think it is just SQL being SQL and using more RAM for cache. I know my test was a bit unrealistic, but I didn't want to leave AV removed for long term.

What surprised me was the initial response that 200MB - 300MB is normal. That seems contrary to what's been discussed in this thread. Personally, I think it is normal and I wonder if that screenshot in the guide was just taken from a freshly booted system.

[Dima P.]

Hi Marc,

I found that LocalDB memory usage was lower than before the reboot. It was around 150 MB.

Could it be that your AV is somehow using the LocalDB as well?

I wonder if that screenshot in the guide was just taken from a freshly booted system

The screenshot was take from my laptop (although VEB was in idle state but that was mentioned in the blogpost), which well spoiled by different software

[mkaec]

Could it be that your AV is somehow using the LocalDB as well?

How so? It's clear from the command line that the instance of LocalDB is dedicated to VEB. If AV wanted to use LocalDB, then I think it would need to start a second process.

"C:\Program Files\Microsoft SQL Server\120\LocalDB\Binn\\sqlservr.exe" -c -SMSSQL12E.LOCALDB -sLOCALDB#E54A66C0 -d"C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\VeeamEndPoint\master.mdf" -l"C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\VeeamEndPoint\mastlog.ldf" -e"C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\VeeamEndPoint\error.log"

Also note that I was able to get the memory up to the same level when there was no AV installed.

Do you have VEB installed on any Windows Server 2012 R2 systems? I'd be interested in seeing a Task Manager screenshot of the LocalDB memory usage from one of those that has been running for 3 weeks.

[Dima P.]

Marc,

Sorry, forgot about your previous post. I am sure we got it in the QA lab – will update this thread with the results soon.

[Dima P.]

Marc,

Finally, got my hands on Windows 2016 TP5 with VEB running there since TP5 was published. Idle state 80MB / running state up to 200MB (although throttling was enabled but that should not take any effect of sql database at all).

[mkaec]

How much memory is in the system? How long had it been since the last reboot? Server 1 and Server 2 from my prior list have 48 GB of memory and the other 3 have 128 GB memory. I have a feeling that SQL sees this and decides it can use more. That could explain why my numbers were higher than typical.

[Dima P.]

Last was a week ago. It’s a VM with 8GB of RAM, so I can easily check your assumption. Let me double the memory and leave the VM running for a week or so.

[sixstorm1]

Coming from thread veeam-agent-for-windows-f33/feature-req ... 41900.html

I understand why you use SQL and for my part that is not the problem. Why don't you simply unload the SQL instance when the app is not backing up? This should be very simple to do and save ton of RAM. The client is backing up, like what, 10 minutes a day?

[Dima P.]

Olivier,

Backup settings (including scheduling options) are stored in SQL. If we keep DB disabled it's impossible to trigger the backup at the given time.

[mkaec]

Settings only need to be read during startup and when the user makes a change. Otherwise, they can be cached in memory.

Code: Select all

void LoadSettings()
{
    SQLServer.Start(); 
    try
    {
        _backupSettings = LoadBackupSettings();
    }
    finally
    {
        SQLServer.ShutDown();  // Reference counting could be used to ensure shutdown only actually occurs if something else is not using the database (like a backup job).
    }
}

[lucius_the]

Resurrecting the thread. Having had the same issues and questions as you guys and knowing that Veeam had adopted sqlite (at least in Linux version of the agent) I'm trying to make a little push to get it on windows agent as well. For those who are interested, have a look here: post408852.html#p408852