Take the 2-minute tour ×
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.

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
1  
SQL Server 2010 is not a real version, do you mean SQL 2008 R2 or possibly SQL 2012? –  Shawn Melton Aug 14 '12 at 12:43
    
Sorry, have corrected the SQL Server Version details. Was in parallel was also installing TFS 2010 on a machine while creating this queestion...so versions got mixed.. –  Dhejo Aug 14 '12 at 14:49
    
@dezso My comment was aimed at the dearth of information in the question, and the fact that it was pretty off-topic for where it was originally posted. –  Andrew Barber Aug 14 '12 at 19:22
    
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
    
I down-voted this question since it does not show that any research was undertaken. In fact, the question is easily answerable by a simple check of the Microsoft SQL Server website. –  Max Vernon Sep 5 '12 at 14:14
add comment

migrated from stackoverflow.com Aug 14 '12 at 8:19

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

1 Answer

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
add comment

Your Answer

 
discard

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.