Sql Azure Pricing - Temporary Database

Question

Hey

I am looking to implement a backup scenario in Sql Azure using Database Copy and the new Import/Export CTP (use database copy so that the exported copy of the database is transactionally consistent).

How will I be charged for the database if I create a temporary database copy in Sql Azure, export the database to blob storage, and then delete the database copy?

For example, let's say I want to make a backup of a sql azure database every hour (and store that backup in blob storage).  I would do a database copy to create a temporary database 24 times a day, and delete each one when the export to blob storage is complete (using the new import/export ctp).  Will I be charged for one hour for each temporary database, even if that database exists for only a couple minutes?

Answer 1

Short answer: you will be charged even if exists for couple of minutes. Having said that, the database charges are amortized daily. That means that if you a 1 gig database - you will be charged $9.99 per month [ i.e. 33 cents daily ]. So if you create a 1 gig database and delete in just couple of minutes, you will be charged 33 cents.

[you can extend this logic to database of any size you may have]

 

 

---

Paras Doshi ( ParasDoshi.com | @Paras_Doshi )

Answer 2

Doshi,

Thanks for the response.  There would be two ways to interpret your answer:

1.  If I create a new database every hour (and then delete it), will I be charged $0.33 * 24 = $7.92/day?

2.  Since I am creating 24 databases a day (and deleting them), there is only one copied database in existence at a time, so would I just be charged $0.33?

I hope I am making the two scenarios clear - thanks for the help.

Answer 3

The first scenario.

You are charged for the database that you create. so if you create 24 databases, you will be charged for those databases.

Now if you delete it on the same day, you will not be charged for the second day.

it's basically - pay for what you use model.

i hope it is more clearer now, i am sorry my previous reply led you to two scenarios.

 

 

 

---

Paras Doshi ( ParasDoshi.com | @Paras_Doshi )

Answer 4

Thanks again Doshi.

Just to make sure I understand you correctly:

• Having a standard 1 GB Web database - $9.99/month
• Backing up that standard 1 GB Web database (hourly) - $238/month

Summarizing the backup cost: To create hourly, transactionally consistent backups of that 1 GB database will require the use of database copy and the new import/export feature, and each temporary database created by database copy will cost $0.33 even though it is deleted shortly after creation.  So, the cost of those hourly backups on the 1 GB database is $0.33 * 24 = $7.92/day, or roughly $238/month not even counting the blob storage costs.

If that is correct, doesn't the cost seem a bit prohibitive?

Answer 5

With this approach - yes. if you think of it, the system does not know that database is suppose to stay for few minutes only. if some day, a database is charged on hourly basis - then this approach should not be prohibitive from cost perspective.

And if you are ok with not creating Transactionally consistent backups, then i can see that your cost will reduce drastically. if not, then it does seem prohibitive.

have you considered other backup strategies for now?

I look forward to backup/restore support in SQL Azure: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI313

 

 

---

Paras Doshi ( ParasDoshi.com | @Paras_Doshi )

Answer 6

Doshi

Thanks for the info.  I am always amused by folks who don't do transactionally consistent backups in relational databases (i.e. pointless...).  Even so, every table in my database has foreign keys and the data changes frequently, so transactional consistency is required in my case.  The new import/export feature is perfect for what I want - backups stored in blob storage that I can then move offsite or where ever I need them to be.  The only problem is of course transactional consistency and the inherent cost.

For considering other backup strategies, they all start at database copy (because of transactional consistency).  I am aware of the announced Restore CTP from your link (announced in May - haven't seen anything about it since).  That CTP, as announced, doesn't allow backups to be independently scheduled and does not allow offsite storage of backups.  Moreover, backups for only two weeks were announced.  Anything beyond 2 weeks is outside of what was announced, and that is where the import/export feature plays a role.  Once the Restore CTP is available, just a daily import/export is feasible while relying on the Restore CTP for more granular recent backups.

Answer 7

I agree with you from technical perspective on not having transactionally consistent backups.

I think you should check out: http://www.bluesyntax.net/backup.aspx - see if it helps you achieve what you are looking for. 

---

Paras Doshi ( ParasDoshi.com | @Paras_Doshi )

Answer 8

Interesting - had not seen Enzo backup before from your link.  Too bad it's not a dll, so that it could be incorporated into a WebRole.  I wonder what tech they are using.  The red-gate solution uses exactly the same procedure mentioned above, but they don't mention the cost aspects: SQL Azure Backup

Answer 9

Interestingly with Enzo backup, you can deploy the agent as worker role and schedule jobs. And as far as i know, the backup is in proprietary format with Enzo backup.

And i am sorry i have heard about red gate's SQL Azure backup but have not used it so can't comment on comparing enzo backup vs redgate SQL Azure backup tool.  

---

Paras Doshi ( ParasDoshi.com | @Paras_Doshi )

Answer 10

Ok.  I had interpretted the "remote agent" to mean that a computer outside the cloud had to be running.  I looked for more details on the site, but couldn't find any.  I am not running any worker roles (min $30 for one role).  If it runs in a worker role, then I could make it run in a webrole by overriding the RoleEntryPoint.  However, the webroles I am running have multiple instances, and I am assuming that this will be problematic with the Enzo scheduler (wouldn't want 2 or 3 or whatever backups running simultaneously).  If you know the devs at Enzo, ask them to put out more details...

Answer 11

Hi Brian,

"the webroles I am running have multiple instances, and I am assuming that this will be problematic with the Enzo scheduler"
There is a mechanism in place to prevent this scenario, so that shouldn't be a problem.

If you need more specific technical details, you can contact the dev support of the Enzo product here: [e-mail]. 

I imagine more details will be available once the product gets released later this month.

Thank you

---

Herve Roggero, MVP SQL Azure Co-Author: Pro SQL Azure http://www.herveroggero.com

Answer 12

OK, I'll admit that this has taken me completely by surprise. I thought Microsoft charged in peak usage per day not total databases created and dropped. I will of course update the copy on the Red Gate Website to make this clear. I've also e-mail Microsoft to ask directly if this is their intent as it does seem excessive.

If you have any questions on SQL Azure Backup please feel free to contact me directly [e-mail]

 

Answer 13

OK, I'll admit that this has taken me completely by surprise. I thought Microsoft charged in peak usage per day not total databases created and dropped. I will of course update the copy on the Red Gate Website to make this clear. I've also e-mail Microsoft to ask directly if this is their intent as it does seem excessive.

If you have any questions on SQL Azure Backup please feel free to contact me directly [e-mail]

 

I wish you are right! i really do - And it would be great if you could get it confirmed.

From what i know - you are charged as soon as you provision a database. And the amount of charge will depend on the peak usage per day. 

That means if i create a web edition and do not go above 1 GB, then i will be charged 33 cents per day. And say my peak usage was 3 GB, then i will be charged 99 cents for that day. Basically you pay the "minimum" price - right?

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

"The daily fee depends on the peak size that each database reached that day, the edition (Web Edition or Business Edition) of each database, and the maximum number of databases you use"

 

---

Paras Doshi ( ParasDoshi.com | @Paras_Doshi )

Answer 14

Doshi - I am truly afraid you are right (would be a bit consistent with how webroles are charged - even if I have one out for testing for a couple minutes, I get docked for an hour).

We are potentially talking about a lot of money in charges for backups, and we definitely need an official response from Microsoft clarifying their charges (at least to make sure they have considered this scenario).  To be honest, I don't think the "temporary database" scenario has been considered from a pricing perspective by Microsoft, even though the video that Doshi linked made it clear that the Sql Azure team intended database copy - import/export - backup/restore to be used together.

I am holding off marking a response as an answer with the hope of attracting an official response from Microsoft......

Answer 15

I've made the changes to our product page to try to make it clear what Microsoft can charge you for using the tool. I'm also working on putting a pop-up into the tool itself so that it's made as up-front as it can be.

I think it would make sense for Microsoft to charge in this way just a bit of a shame, although 24 hourly backups is certainly going to be an edge case. I'd have thought 1-2 backups a day is more likely.

As you say with no other way of making the backup transactionally consistent you are stuck with this mechanism. For archived backups that aren't so frequent this could be acceptable.

---

Richard Mitchell Project Manager Red Gate Software Ltd

Answer 16

Did you get any further clarification on this?

To me "maximum number of databases" could mean the maximum number at a time, so 2 in the above scenario.

We have large databases so this costs thousands for us.

Do you know when a day is counted (what time zone)? Our backups take a few hours to first copy the database then export it so we could potentially be charged 2 days extra for that one backup if it happens on the boundary.

Thanks for any help,

Jane

Answer 17

I never got an "official" response, but as best as I can tell Doshi's answer is correct.  Yes - the massive expenses that potentially arise from these backups are the elephant in the Microsoft's Sql Azure Import/Export feature closet......

Answer 18

SQL Azure runs on UTC, so I guess charges start at 0000UTC

---

Seonaidh

Answer 19

Adrian (Microsoft) just did an update to a thread at http://social.msdn.microsoft.com/Forums/en-US/sqlazurelabssupport/thread/dd79696f-88fd-4c52-912d-a50f35ab01ab.  It appears that there are plans to include consistency in the import/export feature.  No release date yet..........

Answer 20

Adrian (Microsoft) just did an update to a thread at http://social.msdn.microsoft.com/Forums/en-US/sqlazurelabssupport/thread/dd79696f-88fd-4c52-912d-a50f35ab01ab.  It appears that there are plans to include consistency in the import/export feature.  No release date yet..........

Thanks Brain for posting the link.

---

Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )