Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Aug 14 '12 at 8:19

This question came from our site for professional and enthusiast programmers.

Could you please fix the table? Either it's missing a column of data, or there are one too many columns. – Jon Seigel Aug 16 '12 at 16:50

Based on the information given, which is pretty light SQL shouldn't have a problem handling that workload. You'll want to bulk insert the data into a staging table, then load it into the production table using a traditional INSERT INTO ... SELECT ... FROM ... approach as you don't want to be bulk loading into production tables.

As long as the IO subsystem of the disks is fast enough there shouldn't be a problem.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.