SQL Database handles large records

Hi,

We have this potential that we are talking to.  They said they will need to insert about 1.4M records each month.  What is the setup is needed to handle this type of volume?

Currently, we have two boxes with failed-over.  thanks
mcrmgAsked:
Who is Participating?
 
8080_DiverCommented:
What I am trying to tell you is that you have to evaluatre the requirements (which I don't have) and the expectations regarding data retention and volume (which, again, I don't have) and the manner in which the data will be used (in addition to the number of inserts, you need to consider the number of updates and accesses, as well as the nature of the accesses) in order to determine the ammount of data storage and RAM, as well as the number of CPUs, CPU speeds, etc.

Once you figure out what youi think you need, I would double the data storage and RAM, if possible, and bump0 the clock speeds and number of CPUs to the next higher numbers to allow for your being a bit of a novice at spec'ing the system.
0
 
8080_DiverCommented:
Which version of SQL Server?

Please describe the boxes and, especially, the data storage devices and where your databases reside.
0
 
mcrmgAuthor Commented:
it is SQL 2008
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)   Sep 16 2010 19:43:16   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)


Xeon X5570 @ 2.93GHZ (2 processors)
RAM 64 G
64-bit

We store the db on the nas.  thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lcohanDatabase AnalystCommented:
"They said they will need to insert about 1.4M records each month." - aside of this statement what is the data retention? I recommend you look at partitioned tables in SQL 2008 for your task:

http://technet.microsoft.com/en-us/library/dd578580(SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms188730(v=SQL.100).aspx
0
 
8080_DiverCommented:
Follow up questions:
What is the size of a record?
Are these reords inserted as a bulk load or are they transactions during the day?
If transactions during the day, are they expected on a 24 x 7 x 365 basis or is thre a period (e.g. after 22:00 and prior to 05:00 or all day on Sunday) that can be considered a "Maintenance Window"?
What is their data retention policy for these records?
Are these records to be inserted in only one table or are they denormalized rows of data that need to be normalized as they are insrerted in the database?
0
 
mcrmgAuthor Commented:
I looked up "data retention", but still fuzzy on this.. can ee tell me more about this?  thx
0
 
8080_DiverCommented:
A Data Rentention Policy provides guidance regarding how long data is retained.  In other words, Data Retention Policies let you know which data you can delete at any point in time.  For instance, there are some data that, due to legal requirements, must be retained (i.e. kept available) for 7 years.  Other data may have a 10 year or 3 year retention requirement.  In some cases (e.g. certain medical records) the data is to be retained "indefinitely."

The longer the retention period, the more total data will be in the database.  Therefore, longer the retention periods, mean more data storage will be required.
0
 
mcrmgAuthor Commented:
I will need to find out more on that.  

In terms of partitioned tables, do I need a new box?  thanks
0
 
8080_DiverCommented:
I can't answer that without a lot more data . . . for instance, the answers to my previous questions.  

You do realize that you are, essentially, asking me to design your solution for you, right?  

You do also realize that what you are asking is exactly the sort of thing I get paid to do, right? ;-)
0
 
mcrmgAuthor Commented:
>>You do also realize that what you are asking is exactly the sort of thing I get paid to do, right? ;-)


understand.  if you dont mind sharing your knowledge.   thanks
0
 
mcrmgAuthor Commented:
okay, thanks for the tips.  At this point, I really dont have the answer for that.  Untill I got more info back.  I will open a new question.  Again.  thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.