How to calculate SQL Azure Storage per client in your DB ?

Hi All,
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.

For example: If i have a table in my DB called Client, and set of tables Table1,Table2...etc.To get the total DB size in MB in SQL Azure, please check this command:

-- Calculates the size of the database.   

SELECT SUM(reserved_page_count)*8.0/1024  FROM sys.dm_db_partition_stats;   

GO

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 )

Same will be calculated for all other tables.

The total amount of storage for client 1 is the summation Storage by client 1 for each table.

So you can show this amount in your interface under your system admin module and will be able to charge each customer on monthly basis for example.

Another question : What about the synchronization feature for cloud data in DBs ? You can either back up the database on periodic basis or do data sync in SQL Azure. Read more about SQL Azure Sync. framework:

http://archive.msdn.microsoft.com/sync/Release/ProjectReleases.aspx?ReleaseId=5037

Hope this Helps, Please post here any questions if something came across your mind ?

Thanks.

References:

- Monitoring SQL Azure Dynamic Management Views:

http://msdn.microsoft.com/en-us/library/ff394114.aspx

Regards,
Mostafa Arafa
twitter.com/mostafaelzoghbi