Skip to main content
commvault.com commvault.com
Solved

Options to improve large SQL DB restore performance?

  • August 10, 2021
  • 5 replies
  • 3126 views
T
Byte
Forum|alt.badge.img+2

Folks,

I believe the answer might be Intellisnap but figured I’d ask the Community. My DBAs are having a hard time being able to restore a 7TB database. We have restore timeouts set to 24 hours which is a lot of time. I understand that SQL restores will only use 1 thread. I am aware of some of the tuning params for SQL backups but is there anything we can do to speed up restores on the Commvault side for large DB restores? We get excellent read throughput on the storage side per mountpath (around 1500 MB/s).

I’ve been told Intellisnap is the way but I might not get support to go down that road.

 

Thank you

Best answer by ddwyer

Hi BackupGuy,

 

Thanks for your question, I can see Damian has provided an answer, but I just wanted to throw 2 cents in as well. Damian, feel free to correct anything I am wrong on below.

 

SQL Server streaming backup, by default, will use 2 streams, this is configurable from the subclient options and given the size of your DB, if network or storage is not the bottleneck here, then increasing streams could increase concurrency of the data transfer from SQL Server and result in faster backups. If you have enabled VSS for the instance it will definitely be a single streamed backup. Do you have VSS enabled for the instance? 

 

Can you give me more context on this? Why might you not get support?

 

“I’ve been told Intellisnap is the way but I might not get support to go down that road.”  

 

If I had to make a choice between IntelliSnap and Block-Level for a 7 TB database, I would choose IntelliSnap all the way. If only for the backup performance. Whilst Block-Level backup does perform a VSS Shadow operation, it still needs to stream data out. IntelliSnap on the other hand will take a hardware level snap which is much faster. Then at a later date you can perform a backup copy to move the data from the snapshot onto your library storage. This operation can be performed completely on the MediaAgents at any time you like, so you can schedule for times when the system is less utilised and it won’t impact the client.

 

Hope this helps.

 

David

View original
Did this answer your question?

5 replies

Damian Andre
Vaulter
Forum|alt.badge.img+23

One option might be to try block level backups, which would be able to multi stream. It has some caveats I believe so read up on that in the docs.

https://documentation.commvault.com/11.24/essential/115409_enabling_block_level_backups_for_sql_databases_from_command_center.html

https://documentation.commvault.com/11.24/expert/11731_block_level_backups.html

T
1 person likes this
  • T
    TheBackupGuy
T
Byte
Forum|alt.badge.img+2

Thank you so much! We’ve done that for large file server backups as well with great results. Will try that out.

Damian Andre
1 person likes this
  • Damian Andre
    Damian Andre
D
Vaulter
Forum|alt.badge.img+4
  • ddwyerVaulter
  • Vaulter
  • 13 replies
  • Answer
  • August 10, 2021

Hi BackupGuy,

 

Thanks for your question, I can see Damian has provided an answer, but I just wanted to throw 2 cents in as well. Damian, feel free to correct anything I am wrong on below.

 

SQL Server streaming backup, by default, will use 2 streams, this is configurable from the subclient options and given the size of your DB, if network or storage is not the bottleneck here, then increasing streams could increase concurrency of the data transfer from SQL Server and result in faster backups. If you have enabled VSS for the instance it will definitely be a single streamed backup. Do you have VSS enabled for the instance? 

 

Can you give me more context on this? Why might you not get support?

 

“I’ve been told Intellisnap is the way but I might not get support to go down that road.”  

 

If I had to make a choice between IntelliSnap and Block-Level for a 7 TB database, I would choose IntelliSnap all the way. If only for the backup performance. Whilst Block-Level backup does perform a VSS Shadow operation, it still needs to stream data out. IntelliSnap on the other hand will take a hardware level snap which is much faster. Then at a later date you can perform a backup copy to move the data from the snapshot onto your library storage. This operation can be performed completely on the MediaAgents at any time you like, so you can schedule for times when the system is less utilised and it won’t impact the client.

 

Hope this helps.

 

David

E
Byte
Forum|alt.badge.img+8
  • ETO
  • Byte
  • 30 replies
  • April 8, 2022

Hello Guys,
I have the same problem. I’m trying to restore SQL database about 5 TB. We have 10G/sec network on media agnet and on client. WE have tried enable/disable jumbo frame on switch and on servers. There is no firewall between client and MA. But we are unable to see good troughput. I see max 1.7 tb/h  truughput. 

SQL server is located on VMware ssd disks.

Windows Server 2012 64bit.

Is there anything to check or any suggestion ?

Thank you

D
Vaulter
Forum|alt.badge.img+4
  • ddwyerVaulter
  • Vaulter
  • 13 replies
  • April 9, 2022

Hi ETO,

 

Without seeing the logs, it’s hard to determine where the bottleneck is. The key places to be checking are media read speed, network performance and storage write speed on the destination. 

 

Are you using IntelliSnap, Block-Level or streaming here? If it’s snap, is it coming from the snap copy or from primary?

 

Assuming it’s streaming, you can see media read speed in the CVD.log on the MediaAgent, and the SQLiDA.log on the destination client will give you the SQL performance (SQL Writer Timer).

 

If you wanted to test the current network performance between the MA and the client, Commvault packages the CVNetworkTestTool.

 

You will need to run one side of the utility in Server Mode: 

https://documentation.commvault.com/commvault/v11/article?p=7584.htm

 

The other in Client Mode:

https://documentation.commvault.com/commvault/v11/article?p=7590.htm

 

And it will send data between the two utilities and let you know the throughput

 

Also make sure that CV and MSFT AV exclusions are in place where necessary:

 

CV: https://documentation.commvault.com/11.24/expert/8665_recommended_antivirus_exclusions_for_windows.html

 

MSFT: https://support.microsoft.com/en-us/topic/how-to-choose-antivirus-software-to-run-on-computers-that-are-running-sql-server-feda079b-3e24-186b-945a-3051f6f3a95b

 

Hope this helps.

 

Cheers,

David

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings