Host-based backup of VMware vSphere VMs.
Post Reply
Eluminare
Influencer
Posts: 15
Liked: 6 times
Joined: Dec 10, 2015 2:26 pm
Full Name: René Keller
Contact:

SQL Log Shipping of Server with > 1000 DBs

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

Re: SQL Log Shipping of Server with > 1000 DBs

Post by 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
Service Provider
Posts: 229
Liked: 19 times
Joined: Mar 29, 2016 3:37 pm
Full Name: Matt Sharpe
Contact:

Re: SQL Log Shipping of Server with > 1000 DBs

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

Re: SQL Log Shipping of Server with > 1000 DBs

Post by foggy »

Matt.Sharpe wrote: Oct 10, 2018 1:14 pm 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).
Matt.Sharpe wrote: Oct 10, 2018 1:14 pmI'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
Service Provider
Posts: 229
Liked: 19 times
Joined: Mar 29, 2016 3:37 pm
Full Name: Matt Sharpe
Contact:

Re: SQL Log Shipping of Server with > 1000 DBs

Post by Matt.Sharpe » 1 person likes this post

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
Service Provider
Posts: 189
Liked: 40 times
Joined: Mar 01, 2016 10:16 am
Full Name: Gert
Location: Denmark
Contact:

[MERGED] SQL Log Shipping server tweaks

Post by 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
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: SQL Log Shipping server tweaks

Post by 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
Veteran
Posts: 1143
Liked: 302 times
Joined: Apr 27, 2020 12:46 pm
Full Name: Natalia Lupacheva
Contact:

Re: SQL Log Shipping server tweaks

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

Re: SQL Log Shipping of Server with > 1000 DBs

Post by foggy » 1 person likes this post

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
Service Provider
Posts: 189
Liked: 40 times
Joined: Mar 01, 2016 10:16 am
Full Name: Gert
Location: Denmark
Contact:

Re: SQL Log Shipping of Server with > 1000 DBs

Post by 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
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Log Shipping of Server with > 1000 DBs

Post by veremin »

On a backup server. Thanks!
Post Reply

Who is online

Users browsing this forum: No registered users and 86 guests