Sustainability of excel (VBA) - SQL server 2008 App for large number of users

cynx
cynx used Ask the Experts™
on
Currently we have a vba application with Excel front end and sql server 2008 database which performs complex calculations and loads/saves large amout of data to-from sql server backend.

Currently this is being used by approx 150-200 users within same country.

We are now planning to roll out this application to larger group (approx 1000 - 2000 users) across large geography (accessible within our VPN) .

1) I am no database expert, hence need few suggestions on what to be considered (technical aspects) on database and front end side for above roll out?

2) Also on other note, if we have luxury to rewrite this app in other technology what would be the best replacement (for eg. aspx - sql ?) considering this app has large number of records to be imported and processed from excel files.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
First of all i don't see any problem in scaling out your application from 200 users to 2000 users. But considering that it is excel and vpn, please check:
1. Network bandwidth. As you are using a lot of excel rows to import/export, it may be a hinderance when working across the globe on VPN. Though, you can not do much in this aspect.
2. Current Architecture. If you can get the current locking/blocking status with 200 users then you can anticipate the locking/blocking that may cause in 2000 users. This might gives you some idea about changing the architecture of current system.

If you don't put something like blue-moon benefitis, no-body would allow to re-write the application. But still if you get some chance, my idea is to go with ASPX~SQL or VB~SQL. Being the microsoft technology, much of your current VBA code can easily be re-written in the new technology.

Author

Commented:
Thanks for the information. In addition to above any tuning required on database side (which is hosted on a poly server) ?

My justification for aspx-sql over vba-sql would be that a web app would use the server to process the calculations over vba front end which depends more on user's local system (which usually results in slow performance if sys is installed with low memory). Would you agree on this ?
Hi,
I am not PRO aspx, but just sharing a thought. It is not only the point of calculation @ server or @local. Few things that need to be considered:
1. Minimal data should flow from Server -> Client -> Server
2. If calculations need to be done then identify who can do it in better way (example: SQL Server or a VB Code)?
3. If SQL Server can do the calculation in better way, code that in SQL Server (and it will remain server side).
4. If VB Code for instance can do the calculation in better way, then need to identify that should we distribute the load of that VB Code on individual client or We can utilize our server itself to for such type of load from all the users. Accordingly you can put your VB Code to run @ server (by using a VB Client or ASPX Client) or you can install that bit of Code on the client (along with VB Client or via ASPX Client).

I have seen ASPX applications, using [downloaded] local components are running much faster then those which are simply using everything from the server.
I have also seen VB.Net applications, using [web services/aspx pages] on server resources and are running in much faster then those which are simply using everything locally.

So, if you really get a chance of re-write and have the opportunity to decide in aspx/vb, then i would suggest to include a technical architect/consultant who can understand your application scenario and can guide you in specifying "where to write what" and in "which language".
Commented:
The presumed advantage of using aspx over a remote app in Excel/VBA would be that the query results would be local (either on the same server or via a LAN connection to the SQL Server).  That will also be the bottleneck for your remote app--the speed that data flows over the network.  Most likely VBA itself will not be slowing things down in this kind of app.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial