473,574 Members | 3,218 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Is this too much data for SQL?

I need to log data collected from instruments in a laboratory at a
very high rate. I need to log timestamps and values measured from
various devices at a rate as high as once a second.
This means my table will grow to tens or hundreds of gigabytes within
a year. I want to know if SQL server will be able to handle this much
data. In addition, is it better to separate this data into different
tables? Does a table become hard to search (search by the timestamp
field I am logging) once it gets to a certain size?

Does anyone have experience or advice aboutt his problem? Is there
possibly another product out there which is better suited for logging
time-based data like this than SQL Server?

Thanks
Jul 20 '05 #1
6 2183

"NewbieNewsGrou per" <ne************ ***@hotmail.com > wrote in message
news:1e******** *************** ***@posting.goo gle.com...
I need to log data collected from instruments in a laboratory at a
very high rate. I need to log timestamps and values measured from
various devices at a rate as high as once a second.
This means my table will grow to tens or hundreds of gigabytes within
a year. I want to know if SQL server will be able to handle this much
data. In addition, is it better to separate this data into different
tables? Does a table become hard to search (search by the timestamp
field I am logging) once it gets to a certain size?

Does anyone have experience or advice aboutt his problem? Is there
possibly another product out there which is better suited for logging
time-based data like this than SQL Server?

Thanks


I don't have any personal experience of applications like the one you're
describing, but there are certainly SQL Server databases which are handling
those volumes of data and transactions:

http://www.microsoft.com/sql/techinf...calability.asp
http://www.tpc.org/tpcc/results/tpcc...rf_results.asp

It is possible to partition data using partitioned views - you can find more
details in Books Online.

Simon
Jul 20 '05 #2
NewbieNewsGroup er (ne************ ***@hotmail.com ) writes:
I need to log data collected from instruments in a laboratory at a
very high rate. I need to log timestamps and values measured from
various devices at a rate as high as once a second.
This means my table will grow to tens or hundreds of gigabytes within
a year. I want to know if SQL server will be able to handle this much
data.
It will. And once a second is not going to put SQL Server to the test.

With one caveat: you need to have a reasonable table and index structure.
Now, how that table and index structure should look like, I cannot tell,
because I don't know your application. It may also depend on you will
use that data. Maybe once the data has been collected, you should
move it over to Analysis Services where you can build cubes, dimensions
and whatever.

So the answer is, yes SQL Server can do it, but you have to design
carefully.
In addition, is it better to separate this data into different
tables? Does a table become hard to search (search by the timestamp
field I am logging) once it gets to a certain size?


Again, that depends on your indexing. But depending on your requirements,
partitioning may be a good idea. For instance, if many queries will be
against today's test, it can be helpful to have them in a separate
table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
I once supported a similar application which recorded lab measurement data
to SQL Server. The application architecture was such that data was
initially inserted to staging tables and then imported into a separate
schema for reporting and analysis. It is common to segregate operational
and reporting data so that you can optimize your schema (including indexes)
for the different requirements.

As Simon and Erland said, the key to performance with large tables is
appropriate indexing. SQL Server can certainly handle VLDBs but be aware
that performance is ultimately constrained by your hardware speed.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"NewbieNewsGrou per" <ne************ ***@hotmail.com > wrote in message
news:1e******** *************** ***@posting.goo gle.com...
I need to log data collected from instruments in a laboratory at a
very high rate. I need to log timestamps and values measured from
various devices at a rate as high as once a second.
This means my table will grow to tens or hundreds of gigabytes within
a year. I want to know if SQL server will be able to handle this much
data. In addition, is it better to separate this data into different
tables? Does a table become hard to search (search by the timestamp
field I am logging) once it gets to a certain size?

Does anyone have experience or advice aboutt his problem? Is there
possibly another product out there which is better suited for logging
time-based data like this than SQL Server?

Thanks

Jul 20 '05 #4
It is usualy the standard to have your historic data sent to a seprate
database. At my company, we have a huge database with millions of
transactions. Right now, the database is well over 350GB (note hoever
this is on a million dollar 64 processor machine processing millions
of records a day).

Each evening, a DTS job runs which copies specific data (based on
timestamps) out of the production database, and into a datawarehouse.
This server can then be accessed offline. The benifit to this
alterntive database is 1) its not getting the busy hits that the
prodution system is getting. 2) You can have the DTS transfer the data
in a way that is benificial to any reports you may want to run.
(submit the data in a certain order, or into tables other than as are
structured in your production enviorment).

As far as size goes, You can pretty much go as big as you want,
assuming you have the disk space. If you are going to be collecting
data for over a year, I assume this data will be difficult to
reproduce, so you will want to back it up. The datawearhouse is an
execlent tool for this.

Hope this helps.
Mark
Jul 20 '05 #5
I am currently engaged in pulling operatong system metrics from BMC
Patrol agents on over a 1000 sperate servers into one SQL database. I
have found a dramatic improvment in DB performance when inserting data
into a staging table and then aggregating data into reporting tables.
I currently process over 100 million records daily using a fully
loaded HP DL760 attached to an IBM FasTt SAN.
Jul 20 '05 #6
In article <42************ **************@ posting.google. com>, atyoung75
@yahoo.com says...
I am currently engaged in pulling operatong system metrics from BMC
Patrol agents on over a 1000 sperate servers into one SQL database. I
have found a dramatic improvment in DB performance when inserting data
into a staging table and then aggregating data into reporting tables.
I currently process over 100 million records daily using a fully
loaded HP DL760 attached to an IBM FasTt SAN.


It's only to much data if you can't get the results you want.

--
--
sp*********@rro hio.com
(Remove 999 to reply to me)
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
7989
by: Chris | last post by:
Could someone please provide me an effective means of exporting data from a data set (or data grid) to Excel?
9
3294
by: Tony Lee | last post by:
Some time a ago, on this newsgroup the following comments were made in recommending good references for Access (2003) >I used to recommend Dr. Rick Dobson's, "Programming Access <version>" for >people moving from power user to developer, but now I suggest you browse >it, >too. It strongly emphasizes ADO, which knowledgeable Microsoft...
1
17281
by: djozy | last post by:
Please, I want to insert data into SQL Server database. I know for this commmand: SqlCommand myCommand= new SqlCommand("INSERT INTO table (Column1, Column2) " + "Values ('string', 1)", myConnection); ,but how to insert,lets say,a string from textbox1? Or datetime from textbox2? Thank you djozy
1
6839
by: T8 | last post by:
I have a asp.net (framework 1.1) site interfacing against SQL 2000. It runs like a charm 99% of the time but once in a while I get the following "unspecified error". Sometimes it would resolve by itself (asp.net recycled?); it also can be solved by restarting IIS. Any ideas what cause(s) this to happen? System.Data.OleDb.OleDbException:...
0
5634
by: NicK chlam via DotNetMonster.com | last post by:
this is the error i get System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement. at System.Data.Common.DbDataAdapter.Update(DataRow dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at...
3
2047
by: bbernieb | last post by:
Hi, All, Is it possible to access a variable inside of a data binding, without the variable being out of scope? (Note: On the DataBinder line, I get an error message that says "Name 'i' is not declared". The data bind is for a DataList.)
5
2217
by: Gene | last post by:
What can I do if I want to get the result using the sql command? for example, the select command is "select Name from Employee where StaffID=10" How to get the "Name"??? dim Name as string and then..... what should I do?
5
1785
by: DC Gringo | last post by:
I am having a problem reading a simple update to the database. Basically I'm testing a small change to the pubs database -- changing the price of the Busy Executive's Database Guide from 19.99 to 1997 and back. Unfortunately, it seems does reflect the change on my web form. Perhaps it's caching the data retrieved by the SQLDataAdapter? ...
14
14620
by: Rolf Welskes | last post by:
Hello, I have an ObjectDataSource which has as business-object a simple array of strings. No problem. I have an own (custom) control to which I give the DataSourceId and in the custom-control so I get the ObjectDataSource. No problem ..... ObjectDataSource src = .... //is ok i have it
0
2059
by: Winder | last post by:
Computer Data Recovery Help 24/7 Data recovering tools and services is our focus. We will recover your data in a cost effective and efficient manner. We recover all operating systems and media. Call for a free consultation. http://a.uuload.com/Computer-Data-Recovery.htm LiveVault's Online Recovery Service Protect vital data with...
0
7841
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8271
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7858
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8137
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5335
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3774
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3793
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2273
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1369
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.