If you have a cloud based solution hosted in Windows Azure and you are using SQL Azure Database as a backend database. You will need to think about how much storage each client who is going to use your solution will consume from DB storage you have in the cloud. This concept is essential since the concept behind Software As A Service aka (SaaS) is to pay as you go. So, the client pays only for the storage he takes in the cloud and this is a competitive advantage you should give when you sell cloud based solutions.
Since you have a backend database that has all your clients data, You need to give the storage that has been taken by each client in your database. To do this, I was thinking that if i can get the Database size in MB and the size of each table. I’d be able to calculate the storage for each client.
-- Calculates the size of the database.
SELECT SUM(reserved_page_count)*8.0/1024 FROM sys.dm_db_partition_stats;
To get the size for each table, here is how to get the size of each table in SQL Azure:
-- Calculates the size of individual database objects.
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id GROUP BY sys.objects.name; GO
So if i get the number of records in Table1 for client 1, So the storage that has been used in table1 by client 1 is equal to =Total Table Size * ( Client Records / Total Records )
Hope this Helps, Please post here any questions if something came across your mind ?