Is it possible to "measure" the usage (e.g. in MBs) per user, of an SQL Server database in web farm conditions?

Question

I have an ASP.NET web application hosted in a web-farm environment, and I need a way to be able to indicate how much a user is using my database.

There are several reasons for this, and I mention a couple. First, because I pay for the database space per month, I want to have a reasonable way to charge my users. Second, it would be nice to know (again in a per user basis) when to inform the user to upgrade his subscription.

I don't have enough experience in RDBMS, I come from a different background (windows applications, graphics), and so I can't figure out if this is possible, and if it is, how this can be handled: through SQL or ASP.NET (some tool, library, etc.).

If you, also, have some other idea, I'd like to hear what you suggest.

Any other advice on this subject, including good places to learn, would also be appreciated.

Answer 1

It depends on your schema. If you use a database-per-user multi-tenant schema then is very easy, the size of the database is the size consumed, and is really easy to measure and, morei mportantly, enforce. If you use a shared database schema then you'll need to keep track in each table of what rows belong to which user and keep accounting. Both measurement and enforcement are more difficult and there is no general answer, you will have to properly code for accounting the bytes used and to enforce any max size per user constraint.