November 17, 2009 at 3:15 pm
Ok Folks I have been saving files (20K to 250K) into varbinaries in a table. The number of records is relatively small (below 200K) but it is the size of the files themselves that drove DB file size into 60G territory (and growing fast). DB is accessed by both standalone app and web logic. Everything works and dandy but I am concerned about infinite expantion of my DB. Needless to say backups take over an hour (full backups).
I need a strategy that would allow me to
1. keep my existing code instact (too much of it already written)
2. help me mitigate future DB expantion
3. improve backup restore times
All of the BLOBS are in 1 single table. The rest of the DB is a peanut in terms of size and number of records.
I am on SQL Server 2005 but can switch to 2008.
Any advices...
November 18, 2009 at 8:07 am
Switching to 2008 and using Filestream may help.
You could also create a new filegroup and move your single table with the BLOB data to a file in that filegroup. Then you can do filegroup backups.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 20, 2009 at 2:28 am
If you are using Enterprise Edition, you could consider partitioning the LOB table.
Placing archive data on read-only file groups within the partitioning scheme would mean you could use the READ_WRITE_FILEGROUPS option of BACKUP DATABASE to create a partial backup. You can supplement these backups with Differential Partial Backups, as well as normal transaction log backups.
See:
http://msdn.microsoft.com/en-us/library/ms191539.aspx (Partial backups)
http://msdn.microsoft.com/en-us/library/ms190218.aspx (Differential partial backups)
Full file backups are also an option:
http://msdn.microsoft.com/en-us/library/ms189860.aspx (Full file backups)
http://msdn.microsoft.com/en-us/library/ms190939.aspx (Full file diffs)
If you don't need to keep all data online forever, you can also use partitioning to make archival simple.
Paul
December 5, 2009 at 5:00 pm
zaebis 88446 (11/17/2009)
I am concerned about infinite expantion of my DB.
How about a purge strategy?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply