locked
Rewriting desktop database app to web app, multiple databases or one? RRS feed

  • Question

  • I am coming from a desktop application, where each customer has their own database and moving to the web. The advantage of the web is that I can make upgrades to the application and this would be immediately available to all customers.

    I expect to have 200 to 1000 customers using the application. My idea is to use one database. All customers will use the same data tables, but as each logs in they would interract only with their own data by using a key to identify each record as belonging to them.  If the database designed changed, it would change for all.

    My question is if this is the right approach - one database for all customers. Should I use a 'view' approach?

    My two main concerns are perfomance and data availability:

    1. With 100 customers running the application at the same time, will there be an unacceptable response delay.

    2. If the database 'goes bad' it will go bad for everyone, which of course would spell disaster.

    I would appreciate any advice on these issues.

    Thank you in advance!

    Terence


    Terence Wehle


    • Edited by TerenceJW Thursday, March 1, 2012 1:58 PM
    Thursday, March 1, 2012 1:57 PM

Answers

  • Yes, it is the correct approach. VIEWs are usually helpful, though it matters on the data required and object involved.

    Concern 1. There is no way to know without testing. Assume there will not be, and address problems as they arise.

    Concern 2. That's what backups are for. There's also the idea of having a failover system in place.


    Thursday, March 1, 2012 2:08 PM
    Answerer

All replies

  • Yes, it is the correct approach. VIEWs are usually helpful, though it matters on the data required and object involved.

    Concern 1. There is no way to know without testing. Assume there will not be, and address problems as they arise.

    Concern 2. That's what backups are for. There's also the idea of having a failover system in place.


    Thursday, March 1, 2012 2:08 PM
    Answerer
  • Concern 1:

    We run thousands of concurrent users on one multiple TB database,  so SQL server is not a problem. However, you need a good database design, the right indexes in-place and the hardware that is scaled correctly for your load. Areas I would monitor are:

    1) Index usage. watch for tables scans.

    2) Blocking and long lock wait times.

    3) Disk latency, CPU and memory. 

    From a security prospective, I would force the application developers to use stored procedures as much as possible, and verify then any direct table access is using parameter binding (prepared statements). This will help defend against SQL injection attacks which is one of the most common and simplest attacks.  I like to think it is common sence to have the web service connect using an account with the least privileges, i.e. not an sa account.

    Concern 2:

    In either case you need a D&R plan, look at SQL clustering, log shipping and mirroring to prevent/reduce downtime.


    William F. Kinsley

    Thursday, March 1, 2012 8:11 PM