June 10, 2010 at 9:32 am
I've been asked to create a new database entirely devoted to storing PDF docs (as varbinary(max)). Our company policy is to do a complete backup of each database each night. But since these PDF's are stored almost exclusively as an archive, a nightly backup would be re-copying static data over and over. Is there a design technique so that only the current data (this year) would receive a daily backup, and then do backups of the entire database only quarterly or annually?
The PDF's will mostly be loaded in batches at quarter end. A very small number of them will receive modifications (updates), but any changes will probably occur within a few weeks, at most, of loading them into the archive database. After that, the data will just sit there with an occasional request to read it.
Thanks for any ideas,
Elliott
June 10, 2010 at 9:41 am
You could create seperate filegroups for current and archive data and place tables on the respective filegroups that contain current/archive data. Then do filegroup backups.
You'd probably need to create a trigger which would move data from current to archive.
June 10, 2010 at 9:49 am
Thanks for the filegroup idea.
In your concept, would I need to periodically (quarterly) create new filegroups to hold (new) archive tables, or could the archive table and filegroup just be allowed to grow over time?
Also, I need to consider what the select query (proc) would look like to allow users to search for PDF's sent to specific customers. Since the desired info could be in either the current table or the archive table, it would seem that the query would need to be a union of subqueries for the current and archived tables. Are there any issues with that?
Elliott
June 10, 2010 at 9:55 am
It's up to you if you want your archive table to grow over time. Only you can make the decision based upon disk space and query/index efficiency etc. Theoretically your trigger could create filegroups and tables dynamically based on date/time then move the data to the new tables.
As for a stored proc, a lot of it depends which route you want to take with regards the above.
June 10, 2010 at 10:25 am
I liked the Cowboy's idea of a separate database. I wouldn't use a trigger, since you could hang up a transaction, but I'd either use a Service Broker Q, which is more complex to setup, but could allow you to easily distribute things to another machine and has a lot of testing behind it.
Or you could build a process, T-SQL or SSIS, that would move over changes periodically, and you could decide on the period.
I wouldn't necessarily mess with filegroups if I didn't need to. If I could do this in another database, I'd do that for scalability. I could easily move that to a new server over time.
One thing to be aware of is that you still, I assume, need these PDFs for DR somewhere, correct? If you back them up too rarely, and you have a DR need, are you sure you can recover them? If you backed them up once a year, are you positive that tape/disk would be around in DR? I might back them up more frequently, weekly at least, keep a few copies, whack the rest as a side bar.
June 10, 2010 at 10:56 am
Steve
I was thinking that if I had an archive table on an archive filegroup, I could back up that filegroup fairly infrequently (and keep multiple backups in multiple locations for DR). Then I could perform the usual daily backups on the current table(s) on the "current" filegroup. (also kept in multiples for DR).
I've been directed to put this all in one new database, (no separate archive database), so that's why I'm looking for a mechanism that allows me to backup the current stuff without re-doing the static stuff over and over again.
I could write a job to transfer out of current and into archive once a month/quarter during a period of inactivity.
Does that sound feasible?
Elliott
June 10, 2010 at 11:01 am
Elliot,
That should work. Couple things to be aware of. If you have Enterprise edition, you should be able to restore one filegroup and get working while the next restores. IF you don't, you have to restore everything.
Be sure you practice, practice, practice the restores. Make sure you can do this and know what's required. Document it for others.
June 10, 2010 at 11:11 am
steve,
Excellent points. DR is my number one priority for this firm (and I should have mentioned that we have Enterprise Edition). I maintain a group of DR stored procedures and a rather extensive DR manual to assist the non-DBA's in IT to restore databases in the event of a major disruption when I wasn't available. I also hold annual drills with them (restore a test database) so that I know my instructions are understandable and effective.
All that said, I have not needed to perform backups and restores based on filegroups before, so I'll be practicing...
Thanks for everyone's helpful advice
Elliott
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply