SQL Log Shipping of Server with > 1000 DBs

[Eluminare]

Hello @ all,

we have a MS SQL Server with over 1000 active databases. Due to this quite huge amount of databases, transaction logshipping (we're using a different server for this) takes 1-2 hours.

Is there an option to tweak session handling or in general to increase performance?

In the logs I can see, that the bottleneck seems to be the serial session handling. (Connection to DB Server, Connection to Backup Repo)

Greetings
René

[foggy]

Hi René, you could try to increase the number of parallel threads for logs collection via the SqlBackupMaxParallelThreads registry value on Veeam B&R server. The default number is 4, you can set it up to 10.

[Matt.Sharpe]

Hi Foggy, Does this key mean that the SQL databases will be backed up 10 at a time instead of 1 at a time? Is the value a decimal or hexadecimal value? I've implemented the key with a max value of 10 but i'm seeing no improved RPO times, they're pretty much the same as without the key.

[foggy]

Hi Foggy, Does this key mean that the SQL databases will be backed up 10 at a time instead of 1 at a time? Is the value a decimal or hexadecimal value?

This is the number of threads for logs collection only (decimal value).

I've implemented the key with a max value of 10 but i'm seeing no improved RPO times, they're pretty much the same as without the key.

This means that the bottleneck is somewhere else.

[Matt.Sharpe]

For anyone else having this issue. With help from the Veeam engineer Kirill. The following key got a SQL log shipper job from a 30-35 minute RPO to 5-10 minutes:

Name: UseSqlNativeClientProvider
Path 1: HKLM\SOFTWARE\Veeam\Veeam Backup and Replication
Path 2: HKLM\SOFTWARE\Wow6432Node\Veeam\Veeam Backup and Replication
Value: 1 (default value 0)

This key is changed on the SQL guest and a restart of the Veeam services on the B&R server.

[spiritie]

Hi Veeam Forums

Does anyone know of any registry tweaks that can be done in order for the Log Shipping to process additional tasks?
It seems the standard is 4 tasks (?) when monitoring the the temp path where the .bak files is stored on the log shipping server.

We are running v10 P1

Regards.

[HannesK]

Hello,
yes, 4 is the default

Code: Select all

SqlBackupMaxParallelThreads
Type: REG_DWORD
Default value: 4
Path: HKLM\SOFTWARE\Veeam\Veeam Backup & Replication\

the maximum value is 10. If you search for the key name, you also find another tweak, but I'm not sure whether this one is still useful as we improved log shipping with V10.

It would be interesting to hear what kind of environment you have and how more tasks improved the situation

Best regards,
Hannes

[Natalia Lupacheva]

Hi Gert,

In addition, if you increase this key value, please also note:
- Setting the number of threads more than the number of databases would be pointless. Extra threads will just be idle
- Increasing the number of threads would increase the load on server. If you have a lot of databases to back up, hundreds of databases would start back up at the same time, and this is the processor, RAM, disk loading. It would be an attempt to take all data to the repository at the same time.
In general, if you increase the number of parallel threads, the server should have a sufficient supply of resources (processor, RAM).

Thanks!

[foggy]

Also, the value above controls the max number of parallel guest OS threads used to process databases/write data to the repository. The number of tasks/slots on the Log Shipping Server is controlled by the SqlBackupProxySlots value (same type, same location, same default, not limited by 10 though). These slots are common for all the jobs using the particular shipping server.

[spiritie]

Hi Foggy

The "SqlBackupProxySlots", should this be implemented on the VBR server itself, or on the server hosting the Log shipping role?

Regards.

[veremin]

On a backup server. Thanks!