Server Fault is a question and answer site for system and network administrators. 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

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:

  1. 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?

  2. What would be the best development language for the client app? (will MS Access work??)

  3. 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)?

  4. 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?

  5. 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.


share|improve this question

closed as not a real question by Mark Henderson Jan 16 '12 at 3:40

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

mrdenny answered your question and I think he is right on track. But, something else you might consider is a solution like Microsoft's "Sync Framework Database Syncronization". It could help with the lossy 3g connection and allow the workers to work locally offline. But adds a whole level of complication for the users as they would have to keep their copy "synced". But then again, it would require (re)coding the client side and I am not sure how that would scale with Access. (The licensing $ alone for that many copies of Access would be enough for me to build something web based instead). – MikeAWood Aug 17 '11 at 2:20

SQL Server and MySQL can handle this sort of workload without issue. 500 transactions per second isn't a very high workload.

As for the 3G cell service, you'll want to use the cell phones to simply get access to the internet then have them contact the web server over the normal internet.

You wouldn't want to have the application connecting to the database directly unless you've got a VPN connection from the end users to your application's data center. Instead you'll want to setup a web app on a web server (or several web servers behind a load balancer) and have the end application call web methods on the web server which then talks to the database server.

While GoDaddy can handle this, for something of this scale you'll probably want dedicated servers. Starting with something like RackSpace, EC2 or Azure then later moving into dedicated servers in your own CoLo or data center.

share|improve this answer
Thanks for the quick response, MrDenny. This was what I was thinking as well. It's good to get validation from other knowledgeable professionals like yourself. – Todd Aug 17 '11 at 1:01

I used to earn a crust as a MSAccess developer - it's very easy to create simple applications, and once you've got to grips with VB still relatively quick to develop complex logic, however a constant frustration was trying to scale it up to multiple users - getting the right vetrion of runtime client software/middleware working and configured on the users machine, sorting out record locking, and getting acceptable performance from remote databases. It very soon becomes a nightmare. And this was just with up to about 30 users on the same site.

The thought of trying to support thousands of users distributed across multiple sites would make me run out of the building screaming!

Each user will communicate with the server automatically about every ten minutes. ... Each user communication with the server will pass about 60 transactions

So you're actually talking about a distributed database - OMG!!!!!!! YOU DO NOT WANT THIS PAIN!

As mrdenny says - the serverside workload is low - and just about any multi-user DBMS should be able to accomodate the level of transactions.

But if it were me there is NO WAY I would try to implement the UI using MS Access.

You've not given much information about the relationship between the sites / users / network. It seems odd that you would try to connect sites with 3G rather than using a conventional WAN/VPN, but it also seems odd that you would have clients within a site communicating via 3G. Its also unusual to find a 3G device capable of running MSAccess.

If I assume that there is a conventional infrastructure within each site and that the sites are connected via 3G, then I'd go with a local database on each site - with a web based front end (zero deployment). Then worry about consolidating/publishing data between the sites seperately (both MySQL and MSSQL have tools to facilitate this - with Oracle it's a bit harder).

Of course if you've got thousands of sites as well as thousands of users - that's a different story. And if you can only connect them via 3G, then it might be more effective to batch the data up at the client - which is rather tricky using older browsers but perfectly possible with GoogleGears / HTML5 local storage.

Even though you can redistribute the MSAccess runtime client free of charge, the cost of reimplementing the system in PHP or ASP will probably be less than the cost of deploying and managing all those instances of MSAccess (assuming the client devices can run MSAccess).

The customer mentioned GoDaddy

Oh dear. Sounds like one of the companies you read about every other day on the dailyWTF.

share|improve this answer
symcbean: Thanks for your response. – Todd Aug 18 '11 at 3:12
symcbean: Thanks for your response. The clients will be mobile with laptop computers and 3G or 4G connections to the internet. It looks like the back end is going to be SQL Server 2008 R2 64bit servers with the data partitioned appropriately to handle the workload. Each client will have a fat client application, currently developed in Access, request data via web app from the backend, work with the data for 10 min then send the batch of data back to the server thru web app, server stores & processes the incoming data then sends another batch back to the client... – Todd Aug 18 '11 at 3:20
symcbean: so client side processing will be limited to the single client, no multiple users at all, and a batch of data will be transfered to the server via web app over 3g/4g every ten minutes. Do you think Access can't handle this? Each client's workload will be pretty simple. The web app will need to handle the workload of incoming and outgoing data from the database. And the database servers will need to store and process the data through the business logic. Thoughts? Thanks – Todd Aug 18 '11 at 3:25
OK - so there's no real 'sites' of multiple users. Yse, MSAccess could do handle this if it is only one way publishing of data - but you still have a horrible overhead of managing the deployment of the MSAccess runtime and your application across a very large number of clients – symcbean Aug 18 '11 at 10:09

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