January 15, 2012 at 5:47 am
I would like to get people's opinion on this.
My 1st instinct as a DBA is to want to schedule all db backups in the enterprise at the same time for consistency and maintenability. We are, however, being asked by our systems/storage team to stagger them, in order to alleviate increasing pressure on the IO load that we are experiencing on the SAN fabric and the SAN itself.
What are the pros and cons of staggering backups? What are people's opinions, practices and experiences on this? I would be very interested in this discussion.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 15, 2012 at 5:54 am
Easy answer (at least in my case), I have 3 customers (3 databases) & different time zones. We already knew the database workload distribution so we identified different times for their maintenance activities.
January 15, 2012 at 7:57 am
It really depends on where you are backing up to - and whether or not everything is on the same fabric, SAN, disks, etc...
It also depends on how much cache the SAN has, and a whole lot more.
If you are backing up a lot of databases at the same time, and those databases add up to quite a bit of storage usage then it doesn't do you any good to schedule them all at the same time. That will definitely push the SAN, the fabric, the cache, etc...
Stagger them as requested - this way you know you aren't going to cause contention which will cause your backups to take longer than they should.
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
January 15, 2012 at 1:36 pm
Difficult topic as modern tapedrives can take a lot of feed.
January 15, 2012 at 4:11 pm
I would stagger your backups. Hopefully, in production, you are running in full recovery and taking regular transaction log backups meaning that you can restore to any point in time. If this is the case then it doesn't really matter what time you perform the backup as you'll always be able to get all the databases back to a particular point.
January 26, 2012 at 1:56 pm
Thank you all for your replies.
Anyone else? I would be interested in a few more replies, just to get a better feel of what people are doing out there.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 26, 2012 at 2:09 pm
Marios Philippopoulos (1/26/2012)
Thank you all for your replies.Anyone else? I would be interested in a few more replies, just to get a better feel of what people are doing out there.
Okay, this is what I have..
The schedule is different for each of the major SQL Instances I administer, it gives better throughput to my network location (where the secondary backups are copied..)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 26, 2012 at 2:21 pm
We support multiple clients with multiple servers. In general we try do the backups in a reasonably resticted window so the flat file backups can get the files to tape. However we still stagger the backups where there are indications the infrastructure may not be able to handle the load.
There's no real value in having all the backups run at the same time and you may even be causing individual backups to run longer as the contend for resources.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 27, 2012 at 3:13 pm
My employer has 2 production database servers and a Compellent SAN. I am both the DBA and the SAN Administrator.
After monitoring both database server and SAN activities during backups, I came to some conclusions:
1. SQL Server database backup operations appear to be governed. That is, they do not execute as quickly as possible, like a large report query would. They seem to execute at a sedate pace that is not very taxing on the server. Using the Windows Performance Monitor program, I've observed that a single, full database backup operation performs about 5 logical disk accesses per second. This rate remains fairly constant for an entire backup operation.
2. Using the SAN's monitoring tools, I've noticed that the number of IOPS increases during a database backup operation, but not anywhere near the maximum number of IOPS our SAN will handle.
Because of this, I created our full database backup operations to take place almost simultaneously on each of our 2 database servers. That is, database backups are, at some time every night, executing simultaneously on both database servers, but only one full database backup operation is executing on each server at a time. I do not backup 2 databases simultaneously on a single server. Our SAN easily handles these simultaneous operations and more.
The complete set of serial database backup operations takes slightly over 3 hours to complete on one server, slightly over 4 hours on the other server. Concurrent with the database backups, and out of necessity (we have no predictable pattern of either light or heavy traffic) many other database maintenance and reporting operations, as well as high volumes of inbound traffic, take place with neither degradation of database server performance nor SAN performance.
That's my first hand experience.
LC
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply