September 23, 2014 at 9:51 am
So I'm having some headaches with my backups, which are run via a third-party app managed by another admin. Apparently the 2x a week differentials are bumping into the start times for the bi-hourly transaction log backups and causing the whole schedule to (pardon the pun) get backed up...
😛
Right now, because of how the backup system is configured, the backups of the DBs is done in a "serial" fashion. DB A gets backed up, then DB B, and so on.
So, my question is, when you set up your backup, whether via SQL Agent Jobs, or a third party backup application, or 1000 trained monkeys, how do you do it?
Do you have each DB as a job step (serial) or do you do one job per DB (parallel)? Or do you do a hybrid approach, where all the DBs associated with one application or customer in one SQL Agent job (serial) and different jobs per application / customer?
Feel free to let this devolve into a discussion of which approach is better.
And yes, I do have multiple "customers" / "applications" all using one SQL Server instance...
😛
Thanks,
Jason
September 23, 2014 at 10:33 am
I use Ola Hallengren's scripts, never had a problem with them. https://ola.hallengren.com/
If your diff backups are taking longer, can you schedule them to run more than twice a week? They would then have to backup less changes and have a shorter run time. But I would have thought that you should be able to take log backups when a diff is running, or is that a "feature" of the third party app?
September 24, 2014 at 4:53 am
I experimented with doing backups simultaneously. It's possible with PowerShell to set up a threaded process to fire off all the backups at the same time. The results were pretty mixed. On a system with minimal load and smallish databases, it worked extremely well and we got improvements. For systems under load or bigger databases, it degraded the backup process in some places, causing stuff to run longer due to contention. We pretty much stuck to serial. And yeah, you might see contention on the processes sometimes. On some servers, we turned off log backups for an hour in order to get through the regular backups cleanly. It really depends.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2014 at 5:03 am
DBA From The Cold (9/23/2014)
....If your diff backups are taking longer, can you schedule them to run more than twice a week? They would then have to backup less changes and have a shorter run time....
This is not correct. A differential backup takes all changes since the last FULL backup. It doesn't take only the changes since the last DIFF backup. So scheduling the DIFF backup more frequently doesn't limit the size/duration of the final backup.
September 24, 2014 at 5:11 am
I've been in different companies and every company has it's own setup. Lately I see more and more third party software like TSM and the database backups done by a non-DBA department.
Personally I prefer to setup different backup jobs for each database. I try to schedule them as much as sequential as possible, but won't be alarmed when some run simultaniously. Periodically I run a report to show how much overlap these backups jobs have. But I experienced up to 4 or 5 backups could run at the same time without a large drop in performance. Of course all depends on your environment and configuration (server, network and storage performance).
September 24, 2014 at 6:07 am
HanShi (9/24/2014)
DBA From The Cold (9/23/2014)
....If your diff backups are taking longer, can you schedule them to run more than twice a week? They would then have to backup less changes and have a shorter run time....This is not correct. A differential backup takes all changes since the last FULL backup. It doesn't take only the changes since the last DIFF backup. So scheduling the DIFF backup more frequently doesn't limit the size/duration of the final backup.
D'oh my mistake.
September 24, 2014 at 7:04 am
I wrote my own backup process in a procedure. It isn't as robust as Ola's maintenance script, but it does everything I need and I can support it. There's a database job to run log backups and one to run full backups. The procedure accepts parameters to determine the backup type.
I haven't had the need to take it to this level yet, but if you take this route, you could write a table-based scheduler that determines when and how to backup each database. Just an idea.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply