High Transaction System Recommendations

Question

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.

Thanks

Answer 1

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.

Answer 2

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.