May 28, 2009 at 5:29 pm
Hi,
We're looking at archiving, and I came across this product from HP. We've got one large-ish database (is 400GB large?), which is performing fine transactionally, its just the maintenance tasks that I need to speed up (backup/restore, reindexing etc). There's also a perception that 400GB is too large, and that there are a lot of "old" records in the database, but these must be retained (there's a whole stack of reasons...).
So, has anyone used HP Database Archiving, and does it live up to the marketing hype?
TIA,
Steve
May 28, 2009 at 9:43 pm
sdoughty (5/28/2009)
Hi,We're looking at archiving, and I came across this product from HP. We've got one large-ish database (is 400GB large?), which is performing fine transactionally, its just the maintenance tasks that I need to speed up (backup/restore, reindexing etc). There's also a perception that 400GB is too large, and that there are a lot of "old" records in the database, but these must be retained (there's a whole stack of reasons...).
So, has anyone used HP Database Archiving, and does it live up to the marketing hype?
TIA,
Steve
Steve - First of all, I have no experience with HP Database Archiving. Hopefully there will be some others on the site that do.
Secondly, is 400 GB large? Well it depends on what you are used to but that is a fairly typically sized database in more and more environments. People like their data. When you start looking at archiving you need to be sure that all business requirements for data access are really on the table to be sure that after you "archive" the data there isn't some need for the data which is going to force you as the DBA to retrieve that data to a place where it is queryable again. So, with that being said I'd like to poke at the maintenance issues that you bring up.
In reality a 400 GB should be fairly manageable in the realm of backups. If you are not using some compression software for backups I would highly recommend that. You can reduce your backup / recovery times and you will certainly reduce the backup size on disk exponentially. There are huge savings in this area and the money that you will save in time and storage quickly offsets the cost of the software. RedGate and Quest both make good products.
In regards to index maintenance, you can do some intelligent maintenance by looking at fragmentation and only rebuilding those that are fragmented. If many of your tables are known to be static then you can avoid them and save time there.
You can also explore partitioning if you are on Enterprise edition which could allow you to partition your largest tables which could save you some time in the rebuilding index process as you could just rebuild the index on the partitions that have been modified and leave those alone that haven't been touched (i.e. the older data).
For consistency checking (DBCC) you can split this up manually as well to handle work through all the tables over a given period of time. Paul Randall has some great blog posts on his blog site at http://www.sqlskills.com Search it out on Google with DBCC and his name and you will get a "few" hits. 😉
To save disk you can use files / filegroups to move the older data to cheaper disk, especially if it is not going to be accessed often but still needs to be available. That can save some money on the storage end and allow for the high end disk to be used for the active data.
If you really find that you don't need that data and archiving is appropriate you could consider creating an archive database and dumping that data out that is not needed, take a backup to tape, (do a restore from the tape following to ensure that it is good) and them drop the archive database. Have some process defined to do that once a year. Chances are you will save a bunch of money doing something like this.
Again, hopefully others have had some experience with the product that you mention and I do hope that this gives you some ideas on ways to handle the issues that you are facing.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply