I am a SQL Server database administrator and developer and I also have many years experience as an application developer. I currently have a customer with a very big project who is looking to me for direction.
The customer has started development of the client side of the system using Microsoft Access and says this portion is nearly complete. I asked why MS Access was used and was told those in charge at the time knew MS Access and felt it was easy to use and powerful enough to do the job since it is only on the client side and has already been tested with simulated data (I assume a very limited amount of dummy data and no real stress/load testing, but rather simple client side logic is functioning as desired).
But now the customer needs direction and has turned to me. They seem to think the rest of the puzzle is simple and all on the "database" side. They realize there will need to be servers and communication between the client app and the database, but have no real clue what actually needs to be done or how.
I know how to develop applications, especially client server applications. And I know how to design and build databases and all the server side processes and business logic functionality. But I'm not sure exactly what the customer needs in this particular instance.
The customer is requesting this system communicate between client and server via 3G cellular wireless connection.
The system will need to be scalable because there will be high transactions, although the transactions can be "batched".
There will be multiple locations and each location will have 2000 - 5000 users.
Each user will communicate with the server automatically about every ten minutes.
Each user communication with the server will pass about 60 transactions. (5000 users passing 60 transactions every 10 minutes = 300,000 transactions every ten minutes [or 30k transaction per minute; 500 transactions per second])
My questions to this group are:
What is the best design for such a high transactional system that is scalable as the number of transactions increase as well as the number of locations increase?
What would be the best development language for the client app? (will MS Access work??)
I know Microsoft SQL Server is powerful enough to handle the workload, especially with multiple servers and data partitioning, etc. And I'm pretty sure MySQL would also work. But are either of these two databases the best choice (please clarify your answer with clear objective reasons and not just personal preference or opinion)?
The customer mentioned GoDaddy offers multiple MySQL databases for a specific amount per month, but doesn't this require a web site/application? GoDaddy doesn't host just databases for client server applications do they?
Can such a system function properly over 3G cellular wireless connections between the server(s) and up to 5000 clients at the previously mentioned transaction amounts?
Your thoughts and suggestions will be appreciated.