We are working on an application architecture which need to record card activities that happens on different building entrances. There are multiple regions which have multiple buildings in each of them. There is a custom C++ application which has a built-in PostgreSQL DB which record the data from different card readers in a building. The custom application calls a service on the server to pass the card read data in batch of 5000 reads. The service then will write the batch into a central repository. We are also planning to build a queuing layer at the service from which 3-4 items (each of 5000 record) will be written into the repository using bulk upload. Below is the volume of writes from 7 region over a month (70 region expected by final phase )expected on the central repository:
#Bytes/Card || Read ||Card Reads ||GB/Region#
100 ||300000000 ||3.991382463
50 ||300000000 ||1.995691231
20 ||300000000 ||0.798276493
Expect most reads to happen during 9:00 am to 5:00pm during each day and 7 days a week
We are planning to use SQL server 2008R2 as the central repository. Would like to know if SQL Server can support these volume of data writes. Also would like to hear any recommendation/caution regarding the overall proposed architecture.
Also would like to hear about design caveats.
Would it be a good idea not to have any index on the staging DB? Also are there any such tips that can come handy?
