March 23, 2012 at 7:02 am
So I have a rather large single database (660 GB) hosted on a SQL2008R2 Enterprise Server. It grows by about .85GB per day and contains 1 very large table of image data - its vendor supplied so there really isn't an option to change the design. I host backups on the same server on a separate drive and the backups are compressed so they grow by around .54 GB per day.
I'm running out of space for the backups and as an organization there is pressure to reduce the total amount of storage we are using. We have an EMC SAN that we are in the process of upgrading - or replacing and as part of that process the SAN admin would like me to start using AVAMAR (SQL client) for backups.
What do most others do in this situation, do people generally keep a large backup on the SQL Server? This is my preferred solution though I understand it consumes a lot of disk. I don't really like relying on a 3 party tool and having it off the server just means more time for restores and another level of complexity. It will likely mean that backups will take longer as well.
I'm trying to get a feel for what others are doing for backups so any thoughts or advice? Thanks!
March 23, 2012 at 9:17 am
personally I like to have atleast 2 full backups on local disk but thats just me
as you say it adds complexity to the solution and if you where to restore/backup from over the network how long extra would it take.
you have to take into account your RTO, I would test how long it takes to copy the backup to local disk then restore it (two factors, copy time and restore time), then test restoring from the network direct (one factor, restore time)
that way you can see the times taken to restore from local disk, copy to then restore from local disk and restore from network, say you have an RTO of 4 hours and the restore from network and copy and restore take longer than 4 hours how can you meet your RTO?
its the old backups are worthless, restores are priceless.
in a old role we faced the same constraints, where we had 1 hour to get the environment back up and running in the event of a failure, for every 5 minutes after that hour it was envisaged that the company would lose £500,000. the server guys where pushing to keep storage as low as possible but what they would of given us in the solution would of pushed us over the 1 hour recovery mark by a considerable amount of time. the price it would of cost the company would have been far more than the cost it was to just give us the extra space we needed to keep backups locally by a factor of millions of pounds so to the company it was spend more now, save more later which is a no brainer really
March 23, 2012 at 9:50 am
Hi,
U can take a weekly full backup and also choose for hourly (once in four hours) differential backups and schedule transaction logs for less interval of time for point-in time.
Its also a better option to use a 3rd party tool. Especially using Quest LiteSpeed or Red-gate. Both are good for compressing the backups and also for restores.
Considering on taking the full backups. How long will it take to backup the 650GB database and copying it over the network?? It will definetly take alteast the equivalant amount of time taken for backup to restore over other database on other server(say for Eg: DR Server).
March 23, 2012 at 10:13 am
I tend to agree with Anthony, but a few more things.
Don't delete old backups until the new one is made, so take that into account.
In terms of backups, are you doing any log backups? If so, you want to keep all log backups since the earliest full you have. That prevents issues if you have loss of a later full backup.
I dislike agents since they can impact restores. They work sometimes, but in a DR situation, you need things to work every time, so I'd lean on the SAN guys here. You can think about compression, either native or third party (like Red Gate's SQL Backup), or even a zip/tar type compression of the backup file itself once it's down. The advantage of the latter is you can use another machine, not your SQL Server, to run the CPU cycles. However that also depends on your setup.
RTO/RPO are important, so take those into account as you design the schema, and negotiate with your clients/customers about the impact of changes to the recovery and alter your plans or the RTO/RPO to match your resources.
RPO - http://en.wikipedia.org/wiki/Recovery_point_objective
RTO - http://voiceofthedba.wordpress.com/2011/09/28/rto-in-disaster-recovery/
March 26, 2012 at 7:09 am
Thanks for all the feedback. I think I have a good strategy in place, we have nightly full backup to local disk and then 30 days of backups (file level backups from the server) in another location. We are doing log backups every 15 minutes and keeping all log backups since the last full backup.
I was just trying to get a feeling for what others were doing. I have used RedGate SQL backup in the past and given the backup compression in SQL 2008 I've moved away from it (a few other reasons too).
It does make me think about the storage overhead for a Relational Database, log backups, install, TempDB, full backups. For every GB of data we probably need 3 to 4 times the number of GB (min) of storage to host the full solution. I know that people frequently say the cost of storage is going down but I don't really find this the case for enterprise class SAN storage (with new costly software, SSD's, replication) - granted I'm no SAN expert.
Thanks again.....
March 26, 2012 at 7:35 am
Thanks for all the feedback. I think I have a good strategy in place, we have nightly full backup to local disk and then 30 days of backups (file level backups from the server) in another location. We are doing log backups every 15 minutes and keeping all log backups since the last full backup.
I was just trying to get a feeling for what others were doing. I have used RedGate SQL backup in the past and given the backup compression in SQL 2008 I've moved away from it (a few other reasons too).
It does make me think about the storage overhead for a Relational Database, log backups, install, TempDB, full backups. For every GB of data we probably need 3 to 4 times the number of GB (min) of storage to host the full solution. I know that people frequently say the cost of storage is going down but I don't really find this the case for enterprise class SAN storage (with new costly software, SSD's, replication) - granted I'm no SAN expert.
Thanks again.....
March 26, 2012 at 9:13 am
Does the data change on this database or does it just get added to? If it is only added to, is it possible to partition (I know it is a vendor database, so I don't know what you can do to it)? If these are okay, you can partition it, make read only filegroups, back them up once and then store them somewhere while backing up the read-write filegroups regularly at a much smaller size.
Jared
CE - Microsoft
March 26, 2012 at 9:27 am
jackimo (3/26/2012)
It does make me think about the storage overhead for a Relational Database, log backups, install, TempDB, full backups. For every GB of data we probably need 3 to 4 times the number of GB (min) of storage to host the full solution. I know that people frequently say the cost of storage is going down but I don't really find this the case for enterprise class SAN storage (with new costly software, SSD's, replication) - granted I'm no SAN expert.
The cost goes down, but not in an absolute way. The cost/GB declines, so it costs less to store, but Enterprise level storage still requires a large investment. And the cost seems to decline for newer products, not existing ones.
MS estimated that you often needed 7-8 copies of a db in a large enterprise, which is a lot. You can do some work to move your copies to lower level storage, not Enterprise, but you have to get the whole IT group to buy in and make lower cost storage available in places where it fits.
March 27, 2012 at 4:27 am
In addition to backup compression, you could also consider compressing the data in your live database. Red Gate (should let you know that I work for Red Gate) offer SQL Storage Compress, which allows you to compress live data by up to 90%. Users have found this particularly useful when they're on expensive hardware such as SANs. You can find out more at http://www.red-gate.com/sqlstoragecompress or email us at dba.info@red-gate.com if you have any questions.
March 27, 2012 at 8:34 am
Jeff,
Thanks for mentioning Partitioning. I've never actually implemented it before. I'm now doing some research and reading. Here is my plan; just testing now....:-)
I have data for 6 years (and growing). There is no plan to purge data. I will create partitions for each year using a computed (persistent) year calculated column- right now there is a date column that stores the datetime the row was created. All years will be read only except the current year. I will backup the current year partition nightly and the rest only once (I guess).
I have a couple of questions;
Do I need to set this up in advance for years in the future? Do I need to create my function, scheme and file groups in advance?
At the end of the year would I just change the last file group to readonly and the next year to read/write?
Thanks
March 27, 2012 at 8:58 am
jackimo (3/27/2012)
Jeff,Thanks for mentioning Partitioning. I've never actually implemented it before. I'm now doing some research and reading. Here is my plan; just testing now....:-)
I have data for 6 years (and growing). There is no plan to purge data. I will create partitions for each year using a computed (persistent) year calculated column- right now there is a date column that stores the datetime the row was created. All years will be read only except the current year. I will backup the current year partition nightly and the rest only once (I guess).
I have a couple of questions;
Do I need to set this up in advance for years in the future? Do I need to create my function, scheme and file groups in advance?
At the end of the year would I just change the last file group to readonly and the next year to read/write?
Thanks
1. Don't compute year. Simply set the range on DATETIME and set the boundaries appropriately.
2. I would set the partition for as many years as you think this database is going to be in existence. It won't hurt anything to do so.
3. Not sure what you mean by asking if the filegroups, function, and scheme need to be created in advance. Of course, you cannot partition until these are created.
4. At the end of the year you can make the filegroup read only, then back it up, then do a full on the readwrite.
Jared
CE - Microsoft
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply