locked
Estimation of SQL Azure size RRS feed

  • Question

  • For our project, we need to estimate the cost of keeping certain amount of data with SQL Azure.

    We've a fair estimate about the sizing using our local SQL Server 2008 database hosted locally, which means know how the size of the DB grows with certain number of users added in the DB.

    What we now need to know is estimate of the size of SQL Azure (from point of view of billing for storage)? Can we extrapolate from our calculations from size of locally hosted SQL Server 2008? I guess data recovery, fault tolerance and other features might move the size estimate to the higher side than our current calculations.

    I'm looking for some ideas from members of this forum.

    Regards,

    Mayank

    Wednesday, February 2, 2011 12:08 PM

Answers

  • If the data you're linking too is from the billing portal, keep in mind that SQL Azure instances, while billed per month, are actually measured in (I think) hourly increments.

    Lets say you have a database that is set to a 5gb web edition. For the first hour its < 1gb, then for the next hour its at 1.5, then it backs off to < 1gb. The billing for this would reflect 2hrs at the 1gb price point ($10/mnth) and 1hr at the 5gb limit ($50/mnth).

    The published pricing, while not correct. Simplifies what's actually happening and reflects the maximum you'd pay at a given price point for storage. However, by leveraging the scalability targets, you can see how you can actually come in at significantly less per month by only keeping an instance up when its needed.

    Some clients are leveraging this for predictable burst situations and only spinning up additional instances when needed, then destroying them when the load dies back down.

    • Marked as answer by Raymond-Lee Tuesday, February 22, 2011 2:26 AM
    Thursday, February 3, 2011 1:49 PM
  • Please take a look at this topic on MSDN: http://msdn.microsoft.com/en-us/library/ff394114.aspx
    Selcin Turkarslan
    • Marked as answer by Raymond-Lee Tuesday, February 22, 2011 2:26 AM
    Thursday, February 3, 2011 9:39 PM

All replies

  • The numbers should be comparable. In SQL Azure, you are not charged for the redundant copies or the transaction logs. Its strictly $10/gb/mnth for the data you store in SQL Azure. Additionally, say you register a 50gb business SQL Azure database but only use 8gb of that the first month. Then you will only be charged for 10gb (the smallest business size).

    You can use a query similar to the following to get an idea of the amount of storage you are currently using:

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

    Just keep in mind that if you have special requirements related to the hosted application, such as making copies of the database for your own backup needs, you'll need to figure those into your calculations.

    Wednesday, February 2, 2011 2:04 PM
  • Thanks for your response on this. However, I've more questions when I try to interpret the available data.

    1. Our daily usage being shown for yesterday is as shown below:
    02-02-2011     SQL Azure Database     Web Edition         Database (db/month)     0.035714

    Per my understanding this translates to 0.035714*30 = 1.07142 GB current size

    2. Query suggested by you in your post returned answer as: 991.38

    3. We see following information about DB size on developer portal:


    * 2 databases

    * 1 - Size: 521.1 MB, Max Size: 1GB, Edition: Web

    * 2 - Master DB: 1 MB size

     

    Can I say our DB size is approximately 1 GB as of now?

    Even if 1 and 2 calculations are almost similar, why do I get size as 521 MB in developer portal?

    Regards,
    Mayank

    Thursday, February 3, 2011 11:07 AM
  • Hi Mayank,

    I don't recall where, but I read at some place that the database size shown in developer portal is incorrect because of a bug in developer portal code. I think the query mentioned by Brent above is the correct one.

    Hope this helps.

    Thanks

    Gaurav Mantri

    Cerebrata Software

    http://www.cerebrata.com

     

    Thursday, February 3, 2011 1:43 PM
  • If the data you're linking too is from the billing portal, keep in mind that SQL Azure instances, while billed per month, are actually measured in (I think) hourly increments.

    Lets say you have a database that is set to a 5gb web edition. For the first hour its < 1gb, then for the next hour its at 1.5, then it backs off to < 1gb. The billing for this would reflect 2hrs at the 1gb price point ($10/mnth) and 1hr at the 5gb limit ($50/mnth).

    The published pricing, while not correct. Simplifies what's actually happening and reflects the maximum you'd pay at a given price point for storage. However, by leveraging the scalability targets, you can see how you can actually come in at significantly less per month by only keeping an instance up when its needed.

    Some clients are leveraging this for predictable burst situations and only spinning up additional instances when needed, then destroying them when the load dies back down.

    • Marked as answer by Raymond-Lee Tuesday, February 22, 2011 2:26 AM
    Thursday, February 3, 2011 1:49 PM
  • Please take a look at this topic on MSDN: http://msdn.microsoft.com/en-us/library/ff394114.aspx
    Selcin Turkarslan
    • Marked as answer by Raymond-Lee Tuesday, February 22, 2011 2:26 AM
    Thursday, February 3, 2011 9:39 PM
  • Thanks.

    Will check with the given suggestions and report back on this.

    Regards,

    Mayank

    Tuesday, February 8, 2011 5:39 AM