July 22, 2020 at 1:08 pm
Hello,
hopefully this can be a straight forward question, but at the same time, looking to get some advice and if possible experience from someone that tried this... but as the title says, My company has large databases, about 1.5 tb, and dont want to invest in software, but of course we do have good hardware, and trying to speed up the backup process.
so, someone from the group suggested to create a powershell script that utilizes runspace, which spins up multiple threads to do the process. I am testing that as we speak, but so far doesnt seem to increase the backup performance and speed of shrinking down backup time (usually takes 10-15 hours). is Powershell/runspace good or can it actually take backups faster?
Again, i am testing it now, and so far it seems to be stuck on 1 database at a time, though i do see multiple SPIDS on several databases, but, it seems using sp_whoisactive, i only see 1 database with percent_complete going up, again only 1 database, others are null, and are in suspended... very slow.
I tried Ola hallengren, and that seems to increase speed, but i have to make multiple jobs and use Parallel=Y, new i guess feature from Ola Hallengren.
So, the last question I have is, On Performance Monitor, what metrics can i use to see or should I use to measure to give me a good reading on which process works best?
Also any other backup ideas anyone can throw out for me to try and increase backup times?
thanks in advance 🙂
July 22, 2020 at 1:14 pm
Also any other backup ideas anyone can throw out for me to try and increase backup times?
I'm sure that there are lots of ways to do that (eg, play Unreal Tournament on the server during the backup window), but I reckon you're really after a decrease here 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 22, 2020 at 1:21 pm
lol I was going to host a counter strike server on our SQL server, but I decided to wait till my movies were downloaded 😛
but if you have any idea, if powershell is the best way to go, if and why i only see a percent for 1 database when running powershell/runspace, or anything else, it would be helpful 🙂
July 22, 2020 at 1:24 pm
I'll leave that one for the DBAs here. Counter Strike is a good call, that will do it!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 22, 2020 at 3:37 pm
The best way to speed up backups is to stripe them. The SQL engine can then spew out pages to multiple locations. If you have good hardware, I'd look at that, rather than trying to somehow run multiple backups jobs at once.
You mention 1.5TB, is that lots of dbs? Or a few?
July 22, 2020 at 3:38 pm
cool thanks Steve, Also we are on VM, so not sure if putting it on separate Drives would matter, but should i try doing it on configuring to backup separate backup files per database, or still use separate folder and files?
July 22, 2020 at 4:31 pm
Separate physical drives is mostly what you want. You want separate IO paths. Depending on your architecture, it may or may not help. The other place that diff files helps is with threading inside the OS.
You have to test.
July 22, 2020 at 10:09 pm
depending on how many vcpu's you got (if only 2 vCpu use 2 files instead), but most times splitting the backup onto 4 files does help even if the files all end up on the same drive.
just make sure it is not on the same drives where the databases themselves are.
July 31, 2020 at 7:25 pm
If you want to do this in Powershell, you could use dbatools: https://docs.dbatools.io/#Backup-DbaDatabase
But whether you use Ola's backup or Powershell, take a look at modifying the MaxTransferSize, BlockSize and BufferCount settings. I've been able to squeeze a little better performance out of backup with modifying those. Unfortunately, I don't have the links that I used as guidance when I first attempted this but I'm sure Google will come to your rescue for suggestions. You still have to play around with it to see what works best in your environment.
Perhaps a combination of those with striping as Steve mentioned.
August 1, 2020 at 4:03 pm
I would look at other areas to improve performance - I am able to backup a 2TB+ database in less than an hour using the native backup and compressed setting. If it is taking over 10 hours to backup your database the issue is most likely going to be the system, storage or network resources available.
Are you backing up across the network to a UNC path by chance? Are you backing up to the same volume (LUN) that contains the data files (in other words - are all drives just presented from the VM diskpool?).
I know a lot of systems are setup to just use the VM storage - but for larger systems you should present individual drives to the guest instead of using that shared storage for your different needs. For larger systems you should have at least 5 drives - one for the system databases, one for data files, one for transaction logs, one for tempdb - and a final one for backups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply