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: 18295
Liked: 1569 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: 166
Liked: 13 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: 18295
Liked: 1569 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: 166
Liked: 13 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.

Post Reply

Who is online

Users browsing this forum: No registered users and 13 guests