millions of transactions a day 1

[sqlnerd]

Hi pals,

I have been working with Sequel Server for a long time. Now, I face a situation which demands Sequel Server to handle approxly 60 millions transactions/day (more inserts, and a few updates). Also, the database will be queried heavily (mostly simple selects). I request suggestions (design, performance tuning and hardware) that wud help me finetune my database as well as the server to withstand this.


Regards,
SQLnerd

 

[chiph]

60 million a day is almost 700 inserts a second.
That's a *lot* of data.

It might be do-able, but your row had better only have 3 or 4 columns, all of which are fixed-length character or numeric. And no foreign keys.

My advice would be that to properly design this database you should be working with Microsoft Premier support. They're not cheap, but they're good.

Chip H.

 

[SQLSister]

Anything this size is going to need a really strong hardware design first. Remeber that you will have to have very frequent transactioin log backups to keep up with this volume of data. Make sure to put your indexes and transaction logs on separate physical drives than your database to improve processing. YOu will probably also want to spread this database across multiple drives and will almost certainly want failover clustering of some sort. YOU might look at Storage Area Networks (SAN).

Certainly with data this size, I would try to design to avoid joins as much as possible. I'd want to handle as much data validation as possible at the client end to avoid sending bad data to be inserted and thus reduce network traffic.

For this level of data, I would investigate if relational databases can really serve your needs. There are other newer types of databases out there that were designed specifically for this type of thing, storing large amounts of biometric or weather data for instance. This would be expensive. We once considered using an Object-Oriented Database Management System (ODBMS) in a research proposal. I don't know anything more about these (my boss wrote the proposal) other than they are supposed to be for the storage of large amounts of real-time data. Maybe an Internet search on this topic would get you more info.

Have fun.

 

[tlbroadbent]

Check out the SQL Server 2000 Operations Guide. Chapter 6 discusses "Capacity and Storage Management."

http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/sqlops6.asp

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.