April 2, 2012 at 12:37 pm
Grant Fritchey (4/2/2012)
Lynn Pettis (4/2/2012)
MyDoggieJessie (4/2/2012)
Lian (4/2/2012)
Hi,we are running SQL Server 2005 Enterprise Edition 64 bit
I'd like to hear your suggestion anyway Lynn!
In SQL Server 2008 and newer when running Enterprise Edition, I would also look at the possibility of implementing page or row data compression. I was looking at it at a previous employer and if we implemented page compression on 6 tables we could have reduced the overall space used by the database by almost 50%. I left before being able to present this as an option to help reduce disk utilization.
Although, compressing storage won't help backups.
The worst thing in this situation is that if you have a catastrophic outage, your restore process will take about 36 hours.
I'm with most of the others, you need to get backup compression working for you. I have a preference for the Red Gate tools (since I work for them). You also need to be sure that, if you're going to use multiple files for the backups, that these are on multiple disks and through more than one backup path (meaning multiple NIC cards or multiple disk controllers) otherwise you're just jamming more stuff down the same pipe. If you don't have multiple storage locations AND multiple paths, don't use multiple files. You might look at using differential backups in addition to the full. It'll ensure you're getting more stuff backed up in shorter periods of time, but your restore process is still going to be highly problematic.
I'm with several others, you need to break up your storage to ensure the most flexibility on your system.
Another option, not perfect, and not as good as a backup (absolutely not a replacement for good, tested backups) is to set up mirroring so that you've got your database in a second location.
Compressed storage is expanded during backups? That doesn't make since, as the restore process would need to know to recompress it. Or am I misunderstanding what you are saying here, Grant.
It was my understanding that the compressed data was backed up as is, and by using page or row compression you would not be able to restore the backup to a lesser edition (Standard Edition, for instance) since you are using an Enterprise Edition only feature.
April 2, 2012 at 12:46 pm
Lynn Pettis (4/2/2012)
Grant Fritchey (4/2/2012)
Lynn Pettis (4/2/2012)
MyDoggieJessie (4/2/2012)
Lian (4/2/2012)
Hi,we are running SQL Server 2005 Enterprise Edition 64 bit
I'd like to hear your suggestion anyway Lynn!
In SQL Server 2008 and newer when running Enterprise Edition, I would also look at the possibility of implementing page or row data compression. I was looking at it at a previous employer and if we implemented page compression on 6 tables we could have reduced the overall space used by the database by almost 50%. I left before being able to present this as an option to help reduce disk utilization.
Although, compressing storage won't help backups.
The worst thing in this situation is that if you have a catastrophic outage, your restore process will take about 36 hours.
I'm with most of the others, you need to get backup compression working for you. I have a preference for the Red Gate tools (since I work for them). You also need to be sure that, if you're going to use multiple files for the backups, that these are on multiple disks and through more than one backup path (meaning multiple NIC cards or multiple disk controllers) otherwise you're just jamming more stuff down the same pipe. If you don't have multiple storage locations AND multiple paths, don't use multiple files. You might look at using differential backups in addition to the full. It'll ensure you're getting more stuff backed up in shorter periods of time, but your restore process is still going to be highly problematic.
I'm with several others, you need to break up your storage to ensure the most flexibility on your system.
Another option, not perfect, and not as good as a backup (absolutely not a replacement for good, tested backups) is to set up mirroring so that you've got your database in a second location.
Compressed storage is expanded during backups? That doesn't make since, as the restore process would need to know to recompress it. Or am I misunderstanding what you are saying here, Grant.
It was my understanding that the compressed data was backed up as is, and by using page or row compression you would not be able to restore the backup to a lesser edition (Standard Edition, for instance) since you are using an Enterprise Edition only feature.
It should help the backups indirectly by making the amount of data smaller. I'm not sure what Grant is referring to. However, enabling backup compression on top of data compression will not help much (since the data itself is already compressed and does not leave much to be compressed on backup).
Jared
CE - Microsoft
April 2, 2012 at 1:00 pm
Lynn Pettis (4/2/2012)
It was my understanding that the compressed data was backed up as is, and by using page or row compression you would not be able to restore the backup to a lesser edition (Standard Edition, for instance) since you are using an Enterprise Edition only feature.
Yes and yes.
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
April 2, 2012 at 1:01 pm
Hyperbac intercepts the data stream while it's backing up, correct? It's done on the fly which results in considerable time being saved during a back, the same thing applies during the restore.
Since the OP isn't using any kind of compression, surely they sh/would experience a considerable performance improvement by using a tool like this - am I wrong on this assumption?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 2, 2012 at 1:08 pm
MyDoggieJessie (4/2/2012)
Hyperbac intercepts the data stream while it's backing up, correct? It's done on the fly which results in considerable time being saved during a back, the same thing applies during the restore.Since the OP isn't using any kind of compression, surely they sh/would experience a considerable performance improvement by using a tool like this - am I wrong on this assumption?
Yes. I used HyperBac at a previous employer and our backup/restore times on the old hardware running SQL Server 2005 dropped about 50%, from 1 hour to 30 minutes. This was disk to disk on the server and the databases were approximately 70GB.
April 2, 2012 at 1:08 pm
GilaMonster (4/2/2012)
Lynn Pettis (4/2/2012)
It was my understanding that the compressed data was backed up as is, and by using page or row compression you would not be able to restore the backup to a lesser edition (Standard Edition, for instance) since you are using an Enterprise Edition only feature.Yes and yes.
Thank you, Gail. I least I remembered that part correctly.
April 2, 2012 at 1:09 pm
SQLKnowItAll (4/2/2012)
However, enabling backup compression on top of data compression will not help much (since the data itself is already compressed and does not leave much to be compressed on backup).
Actually it can help quite a bit.
Data compression is at the page or row level. Backup compression is at a higher level. So if you have data repeating within the database but not within a page, backup compression can reduce that duplication, row or page cannot.
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
April 2, 2012 at 1:11 pm
Doe it still hold true that on 2008 (not r2), backup compression can only be initiated on Enterprise edition and restored to any edition? This is in contrast to data compression...
Jared
CE - Microsoft
April 2, 2012 at 1:13 pm
GilaMonster (4/2/2012)
SQLKnowItAll (4/2/2012)
However, enabling backup compression on top of data compression will not help much (since the data itself is already compressed and does not leave much to be compressed on backup).Actually it can help quite a bit.
Data compression is at the page or row level. Backup compression is at a higher level. So if you have data repeating within the database but not within a page, backup compression can reduce that duplication, row or page cannot.
Ha! Thanks for that... I actually read the article that this one was referring back to.
Jared
CE - Microsoft
April 2, 2012 at 1:14 pm
SQLKnowItAll (4/2/2012)
Doe it still hold true that on 2008 (not r2), backup compression can only be initiated on Enterprise edition and restored to any edition?
Yes.
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
April 2, 2012 at 1:54 pm
SQLKnowItAll (4/2/2012)
Lynn Pettis (4/2/2012)
Grant Fritchey (4/2/2012)
Lynn Pettis (4/2/2012)
MyDoggieJessie (4/2/2012)
Lian (4/2/2012)
Hi,we are running SQL Server 2005 Enterprise Edition 64 bit
I'd like to hear your suggestion anyway Lynn!
In SQL Server 2008 and newer when running Enterprise Edition, I would also look at the possibility of implementing page or row data compression. I was looking at it at a previous employer and if we implemented page compression on 6 tables we could have reduced the overall space used by the database by almost 50%. I left before being able to present this as an option to help reduce disk utilization.
Although, compressing storage won't help backups.
The worst thing in this situation is that if you have a catastrophic outage, your restore process will take about 36 hours.
I'm with most of the others, you need to get backup compression working for you. I have a preference for the Red Gate tools (since I work for them). You also need to be sure that, if you're going to use multiple files for the backups, that these are on multiple disks and through more than one backup path (meaning multiple NIC cards or multiple disk controllers) otherwise you're just jamming more stuff down the same pipe. If you don't have multiple storage locations AND multiple paths, don't use multiple files. You might look at using differential backups in addition to the full. It'll ensure you're getting more stuff backed up in shorter periods of time, but your restore process is still going to be highly problematic.
I'm with several others, you need to break up your storage to ensure the most flexibility on your system.
Another option, not perfect, and not as good as a backup (absolutely not a replacement for good, tested backups) is to set up mirroring so that you've got your database in a second location.
Compressed storage is expanded during backups? That doesn't make since, as the restore process would need to know to recompress it. Or am I misunderstanding what you are saying here, Grant.
It was my understanding that the compressed data was backed up as is, and by using page or row compression you would not be able to restore the backup to a lesser edition (Standard Edition, for instance) since you are using an Enterprise Edition only feature.
It should help the backups indirectly by making the amount of data smaller. I'm not sure what Grant is referring to. However, enabling backup compression on top of data compression will not help much (since the data itself is already compressed and does not leave much to be compressed on backup).
Sorry I was unclear. This is what I meant. This is why I need a copy editor.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply