February 15, 2011 at 2:43 am
Hi,
I am new to capacity planning in SQL Server 2005.
I know that the estimated size of the Data Files will be 200GB in the next year or two
What should the Size of my Backup Drive be when the estimated size of the Database is 200GB.
Cheers,
George
February 15, 2011 at 3:09 am
Not a trivial question...
How long do you intend to retain backups on disk before copying to tape/offsite storage?
Do you intend to do log backups (for point-in-time restores) and if so how often, how large do you estimate they will be and how long do you need to retain them?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2011 at 7:00 am
Hi,
Thanks for the response. We will be keeping 3 days worth of backups. The Transaction Log will be backed up every hour.
Not sure how big the Transaction Log will get. This is another thing I am not sure how to calculate.
Kind regards,
George
February 15, 2011 at 7:08 am
Calculating the transaction log is pretty difficult. Basically, the size of the data being sent into the system times the number of transactions. It's a tough one. Just remember that the data file size is not the size of the backup. It's the data (and log) that is being backup, so the space used, not the space allocated, needs to be your measuring point. And then, because you're keeping 3 copies on line, multiply it times 4. Why times 4? Because you want to complete a good backup before you delete the oldest file, so you need at least 4 times space on the system.
"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
February 15, 2011 at 7:16 am
So you're looking at the vicinity of 1 TB without any additional calculations.
Size of backup = size of data in database + size of portion of log needed for restore.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2011 at 7:21 am
...and you might try to lobby for some backup compression software in there somewhere as well. That will save a whole lot of disk space with 3 days of database and log backups on disk. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 15, 2011 at 7:23 am
Oh crud, and this is the point where I should have mentioned that my company has a couple of excellent backup compression products you might want to take a look at. I'm not very good at this sales stuff. I like discussing the technology a lot more.
But seriously, since Gail is right and you're looking at 1tb of backup storage, you really ought to look into compression.
"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
February 15, 2011 at 7:27 am
Grant Fritchey (2/15/2011)
Oh crud, and this is the point where I should have mentioned that my company has a couple of excellent backup compression products you might want to take a look at. I'm not very good at this sales stuff. I like discussing the technology a lot more.But seriously, since Gail is right and you're looking at 1tb of backup storage, you really ought to look into compression.
Yeah - I was going to give you a bit of a hard time about that. 😉
I use the Red-Gate backup compression software and really like it so I'll throw my .02 on that choice as well. High quality, reasonable cost, nice stuff!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 15, 2011 at 7:31 am
David Benoit (2/15/2011)
Grant Fritchey (2/15/2011)
Oh crud, and this is the point where I should have mentioned that my company has a couple of excellent backup compression products you might want to take a look at. I'm not very good at this sales stuff. I like discussing the technology a lot more.But seriously, since Gail is right and you're looking at 1tb of backup storage, you really ought to look into compression.
Yeah - I was going to give you a bit of a hard time about that. 😉
I use the Red-Gate backup compression software and really like it so I'll throw my .02 on that choice as well. High quality, reasonable cost, nice stuff!
yeah, I just saw your post. Maybe you should be doing this job. You're clearly spotting the opportunities better than I am.
"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
February 15, 2011 at 7:37 am
Grant Fritchey (2/15/2011)
David Benoit (2/15/2011)
Grant Fritchey (2/15/2011)
Oh crud, and this is the point where I should have mentioned that my company has a couple of excellent backup compression products you might want to take a look at. I'm not very good at this sales stuff. I like discussing the technology a lot more.But seriously, since Gail is right and you're looking at 1tb of backup storage, you really ought to look into compression.
Yeah - I was going to give you a bit of a hard time about that. 😉
I use the Red-Gate backup compression software and really like it so I'll throw my .02 on that choice as well. High quality, reasonable cost, nice stuff!
yeah, I just saw your post. Maybe you should be doing this job. You're clearly spotting the opportunities better than I am.
Not even close but thanks for the kind words. I am a fan of the products though.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 15, 2011 at 7:38 am
Thanks for all your replies.
They are most helpful and I really appreciate them.
The answers have really helped me to understand this area more.
I will definitely look into compression too.
Best wishes,
George
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply