• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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
0
mcrmg
Asked:
mcrmg
  • 5
  • 5
5 Solutions
 
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
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
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
 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now