Need to archive data off the server
-
Hi,
We have an archive database that holds a history of our larger tables. This database is growing rapidly and will soon be too big for the server (it has just passed 100Gb with approximately 70m rows). I need to find a way to move the data out of the database and store remotely and was wondering if anyone has a good way of doing this? The data needs to be kept for regulatory reasons, but does not need to be readily available.
I was thinking of using BCP to create a file containing a month’s worth of data and then compressing and storing the files on a network drive (or possibly on tape). When we need to retrieve the data, the file for the relevant month could be temporarily BCP’d back into the database.
cheers
Dave
-
I think you need to split the database into a couple database or into 2 database, because like you say, you don't need the data readily available.
You can create a job that check the date, if any data find in the specified criteria, you just move that data to the secondary database, so your database will not growth up in the production environment, but in the secondary database.
-
Hi,
The data is already in a separate database on the contingency server. The problem is that we need to keep the data for 7 years, and our contingency server will run out of space well before then!!
I was wondering if anyone else had experience of keeping archived data outside a database, and if there were any pitfalls to avoid.
thanks
dave
-
Like you sad before, the tape drive it's a good choice, but try to split the tape by year or month, because it will help you, in case you need a speific data.
-
Only pitfall that I can see is.. IF you need to keep information for 7 years I would ensure that your media:
1. Was not overwriteable
2. Was stored someplace safe
3. Was able to last for 7 years with ZERO or minimal degradation
4. Had a duplicate shipped someplace else (Just in case)
5. Was not forgotten....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I do 2 year archives.
I save all of the data to tape, then remove (delete) any data that meets the requirement for removal. Then I properly mark the tape and tape case, fill out two 'tape storage' letters. They are signed by me and the person who will be storing the tape. Both sheets list descriptions of the tape(s) along with what data is on them and where they will be stored. I keep one and the other is kept with the tape.
Issues......if you have to restore the tape, do you have the 'room' on your system to do so? In seven years, will you have a tape drive and software CAPABLE of restoring the data? For example, I am using Backup Exec 8.6 to make my copies. That is no longer supported and we are looking into getting the latest version. I have heard problems in the past that later versions of BE can't restore 8.6 version tapes. Plus my tapes are Ultrium LTO1 tapes for HP tape drives. I have plans to keep all versions of my backup software and two (of my six) tape drives for future restores.
-SQLBill
-
Does anyone really believe media or hardware that is used now will even be functional in 7 years?
The thing is, about at 2 to 3 years, you need to set some time aside to convert this data to something more usable.
The last 7 to 10 years look like,
floppy to hd or zip or dat to CD or dlt to DVD or SAN...just too many different schemes and media. i'ld do what works for now and then when you get close to running out of space, call an audible at the line and do what is plausable then. If we could see 7 years in the future most of us wouldn't be in IT - we'ld be at the racetrack or wall street.
-
So many responses, so many ideas. I've worked at small shops to fortune 100 shops and have seen at least 15-16 different archival strategies throughout my career. I've also fought the battles for money, equipment and consultants for this purpose as well ! It might be in your firms best interest to hire a consultant (this way management has already bought in to the idea) and once the options and cost are presented the business can make the choice. If this is not possible, then you are in for an uphill battle (as I stated earlier, been there and done that). If you come up with a semi bullet proof strategy you'll get shot at for the price. If you low-ball it, and then have a failed recovery ... bang ... shot again ! Besides, the consultant will evaluate your storage, network, backup/recovery/disaster plans as well if he's worth his salt. Management ought to love this (hint, hint, consultant selling point). Good luck !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I haven't heard of fortune 100 shops before... what's that like
? -
LOL, Fortune 100 is the downsized version of Fortune 500. The other 400 are being outsourced.
On the serious side, you can and should archive that db to tape and store it at a remote site. LT2 tapes can store 400 gb of compressed data.
Jules
Jules Bui
IT Operations DBA
Backup and Restore Administrator -
fortune 100/500/1000 ... all just rankings by size ... as for what it's like ... well corporate size and corporate b.s. are exponentially proportionate to one another. Presently I'm in healthcare, 1500 employees, 50 person IS staff and 19 facilities outside of the main hospitals and growing ... all in all a great envieonment, with great people.
Oh, Jules, love the outsourcing remark !!!
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
there ara SAN systems that have an archiving option, meaning datafiles not used for x time migrate to tape until called for.
I've only seen the concept-picture, but have never seen it working in conjunction with e.g. a partitioned sqlserver database.
Would be nice dough because you could have a partitioned db by month/year, have it automigrating/recaling when needed.
I've seen this concept working a couple years ago on a OS/390-DB2V5 datawarehouse system.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
SET IBM_AND_DB2_MODE ON
This type of storage is generically called "HSM" - Hierarchical Storage Management. Typically everything is on fast disks, then goes to slow disks, then tape or DVD (still on the unit), then then tape or DVD (no longer on the unit). Since it was on an IBM system, there were hooks into DB2 to allow DB2UNLOAD to create the archive datasets (IBM speak for files) to be archived to "HSM". Big bucks definitely !
SET IBM_AND_DB2_MODE OFF
There are smilar systems available for Windows based servers as well. However you probably will not find the 'hooks' into SQL Server to remove the data. That'll be either yours or the developers responsibility.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Depending on how you have it broken up, you could do your backups, then zip the .BAK files to save space. Then perhaps you could keep more of them on the server so they continue to get backed up and you're not dependant on hardware becoming obsolete. On a smaller scale, we need to archive certain tables, and we DTS them to text, then zip them, but that might be unmanageable in your case.
Buy some more disk, raid 0, dump your zip files there, back them up every night.
-
I like the new SET option
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply