We are designing a data extensive application. There would approximately one thousand customers for which we'll be entering data on daily basis.
All the data entry would be done in-house i.e. customer won't be entering the data themselves. Customers would be allowed to generate reports on the data entered. Reports being generated for one customer may be using data from all one thousand customers. The guesstimate for the daily data is around 10MB for all thousand customers.
Here is how we are planning to setup the environment:
We are planning to setup two different databases on two different machines, one for transactional data and other for reporting purpose. All the customers will be using the database daily for reporting. We'll be using a service that will fetch the data from transactional database, compile it, and dump into the reporting database.
I need feedback on the following:
1. Any comments/suggestion on above design.
2. Any other better idea for load balancing for the above situation.
Deployment environment would be: Window 2003 Server, MS SQL Server 2005.