January 23, 2008 at 10:32 am
I'm currently developing a database and am concerned about exceeding the limitations of SQL 05 capabilities. I'd like to get some input/ideas on how I can the growth of the data.
The application driving the database is a type of project management system. For the most part SQL Server should be able to handle what I need to do. However, I will have one table that will contain for example: 500k records this year (20ish columns wide). Next year, these records will be updated but the archived copy should still be accessible via my application. The new information could be identical to the old information but in some cases during projects we may need to reference the old information. The number 500k is variable and could be in the millions, so the next year this number would double.
Has anyone found themselves in this situation before? If so, how did you handle managing such large amounts of data? Is this Oracle territory?
January 23, 2008 at 10:58 am
Assuming that's the only growth you have in mind - that's actually not all that much.
What you're describing is actually LINEAR growth (you'd be doubling the number of records from year 1 to year 2, but adding about a constant amount each year)
Using your assumptions, that would be
500,000 x (20 columns) x (100 bytes per column) x (5 to account for data storage + log space + indexing) = 4.66 GB/year. That's assuming all columns being that wide (which would mean a bad design if you do have something that way).
That's certainly something SQL Server can handle, all other things being fairly normal (since you have not mentioned anything odd). You may need to look at partitioning the table, or the appropriate use of multiple files, etc..., but as stated early - it doesn't sound like something SQL Server can't handle. As of right now - SQL server 2005 can handle something around 524,000TB of data (give or take a few hundred TB's).
I might not be understanding your growth factor correctly - but try to put it into "regular math" and take a look at it. Also - take into account that the year 1 to year 2 growth is not necessarily indicative of long term growth.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 23, 2008 at 11:29 am
They will add documents to the projects and it will take a lot of space.
My recommedations will be to store attachments in the file system and path in SQL Server.
Regards,Yelena Varsha
January 24, 2008 at 12:52 pm
Yelena Varshal (1/23/2008)
They will add documents to the projects and it will take a lot of space.My recommedations will be to store attachments in the file system and path in SQL Server.
I have it configured to do so. Sounds like everything should be fine then.
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply