Discussions specific to the VMware vSphere hypervisor
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 » Aug 03, 2018 5:42 am

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: 19039
Liked: 1693 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 » Aug 03, 2018 10:28 am

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: 171
Liked: 14 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 » 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? 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: 19039
Liked: 1693 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 » Oct 11, 2018 5:07 pm

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 pm
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
Service Provider
Posts: 171
Liked: 14 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 » Oct 12, 2018 12:23 pm 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
Enthusiast
Posts: 96
Liked: 10 times
Joined: Mar 01, 2016 10:16 am
Full Name: Gert van Niekerk
Location: Denmark
Contact:

[MERGED] SQL Log Shipping server tweaks

Post by spiritie » May 19, 2020 12:20 pm

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
Veeam Software
Posts: 5428
Liked: 738 times
Joined: Sep 01, 2014 11:46 am
Location: Austria
Contact:

Re: SQL Log Shipping server tweaks

Post by HannesK » May 20, 2020 5:27 am

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
Veeam Software
Posts: 33
Liked: 12 times
Joined: Apr 27, 2020 12:46 pm
Full Name: Natalia Lupacheva
Contact:

Re: SQL Log Shipping server tweaks

Post by Natalia Lupacheva » May 20, 2020 9:04 am

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: 19039
Liked: 1693 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 » May 20, 2020 11:42 am

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.

Post Reply

Who is online

Users browsing this forum: Majestic-12 [Bot] and 17 guests