0

I need to create a design to meeting the following requirements for a coupon system

  • coupons to be issued every 6 weeks
  • each issuance will give approx 90m coupon instances
  • coupon instance record can be deleted once it's over 6 months old
  • total coupon instances approx 380m
  • approx peak of 3,500 redemption requests (read / write) per minute + 1,500 validation requests (read only) per minute
  • must support connections from an estate of approx 3,000 POS tills
  • db must provide sub 0.5 second response

I need to go with SQL Server 2008r2 64bit. Will 16gb and 8CPU be sufficient? Do you think I need to also employ table partitioning?

Thanks

Rob.

tr0users
  • 403
  • 3
  • 9
  • 16

3 Answers3

1

I'll keep my answer to the hardware part:

Basically SQL Server tries to keep the database(s) comletely in RAM and polls the system frequently to see if RAM is available and only begins swapping out data (not completely true, seldom used tables might also be swapped to disk AFAIK) when it runs out of free memory. If you can somewhat estimate the size of your database the try to get as much RAM as possible to fit your db completely into the RAM and then some. If that's not possible (or better: do this also) try to get a fast I/O subsystem and spread the db files across multiple disks as was also suggested.

Gir
  • 21
  • 1
  • 3
0
  • Your design and indexes will be far more important
    Without this you'll have problems no matter what you do
  • I'd add more RAM
  • Your disk setup is important: separate data/logs/tempdb
  • Partitioning may be useful but not essential: a separate disk array per partition would be better
gbn
  • 6,009
  • 1
  • 17
  • 21
0

Will 16gb and 8CPU be sufficient?

Yes, But this is like sizing a car by the model of the radio.

The far more important factor for transactional databases has always been the disc layout and speed. For example, you hit 3500 changing transctions per second (writes via redemptions). A 15k SAS drive is onyl good for 450 IOPS. Bank - here is a problem. Your CPU wont even sweat at this, and the RAM is not even in the game.

So, far more important is a very fast IO subsystem.

TomTom
  • 50,857
  • 7
  • 52
  • 134