I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued periodically, usually every six weeks although there will also be ad-hoc issuance - eg for a special event. There are 15 million customers and for each issuance event, every customer will receive 6 different coupon types, giving a total of 90 million coupon instances. We need to track the coupon instance redemption data and maintain this for 6 months, although typically a coupon is only valid for six weeks. Any redemption request for an invalid coupon will not reach the database because it will be validated by the POS till.
Over a six month period we'll need to store up 360 million rows in the Coupon Instance table and up to 72 million (assuming max 20% redemption rate) in the Redemption table. I get the feeling that these numbers are too big for a single partition?
My question is - what to use as the partition key? One obvious candidate would be by issuance event, giving approximately 6 partitions. But then I think that maybe even that would give a partition size that is too large to allow for optimal performance? Would it be possible to partition by two keys eg by issuance event + last digit of the customer id? So the logic would be:
If issuance event = 1 and last digit of customer id < 5 then Store in partition 1 Else if issuance event = 1 and last digit of customer id >4 then Store in partition 2 Else if issuance event =2 and last digit of customer id <5 then Store in partition 3 Else if issuance event =2 and last digit of customer id >4 then Store in partition 4 Etc...
Also, I'm not sure of the spec of the database server that we'll need. Will 16gb and 8CPUs be enough? The db needs to be able to return a result from the coupon instance table, keyed on a numeric barcode value in less than half a second. The expected transaction request for validate (select) and redeem (insert) is expected to peak at approximately 3,500 per minute.
The SQL Server 2008r2 64bit db server will be provisioned as VM from a very powerful host with access to a high performance and large capacity SAN.
I'd be very grateful for any advice from those that have deployed a SQL Server solution to manage similar volumes.