Question about SQL Azure editions considering ability to create multiple databases

Question

Hello,
I have noticed that we can create multiple databases in SQL Azure using the CREATE DATABASE command.  I have seen the pricing for SQL Azure where we get a 1GB database for the web edition, and a 10GB database for the business edition.  I am wondering how this will work.  If we have a single SQL Azure 'account', and we want to create multiple databases, do we have to create each one of them as either a 1GB or 10GB database and get charged for each respectively, or can we create 4 250MB databases under the 'web edition' plan since they are in total less than 1GB?

Thanks,
Shan McArthur
www.shanmcarthur.net

Answer 1

When you create a database you have to pick 1GB or 10GB options.    You can create as many databases as you want and drop them when you don't need them.  We bill you for each database for as long as it exists.  There is a time lag between create/drop and start/stop of the billing clock.

It is important to note that:   4x250MB databases are not equal  to 1x1GB database because with the 4x databases you also get 4x the amount of compute resources for your databases. 

The resource utilization of each database is managed independently and your four databases are not bound to a single physical hardware resource.  With four databases you would be able to consume 4x the memory, CPU, IO and network bandwidth compared to a single 1GB database.   With SQL Azure you can run stored procedures, sorts, joins and aggregations in your database so we have to account for all resources and not just the bytes on disk. 


I hope this helps -- we are interested in your feedback around these models so please send us your opinions!

---

Tonyp

Answer 2

Thanks for the clarification - this helps a lot!  The explanation also helped me understand the service better too.

As far as feedback, I find that 2 sizes of databases (1 and 10GB) are completely unacceptable.  I would prefer to pay per GB per month; pay-for-what-you-use is the cloud model.  I also have MANY databases that are larger than 10GB, and the limit of 10GB is WAY TOO SMALL for today's databases, especially since I cannot add additional space to the database.  I also find that picking the database size up front and not having the ability for it to grow is also unacceptable.  $100/m per database that for a 2GB requirement is also too much from a cost standpoint.  The high pricing makes it infeasible for the small businesses (that exceed 1GB) yet the 10GB limit is way too small for any medium to large deployment - leaving no room in the middle.  Unless this is seriously looked at, SQL Azure is going to have a very tough time getting market acceptance. 

From a service standpoint, not having a backup/restore solution is also going to be an issue that will block many users from considering Azure.  Every time I upgrade my database, I back up prod, make a copy and host as test, apply my upgrade scripts, then validate with my new application.  If bugs arise, I fix the app or the data and repeat the process until the new version is accepted, then when signed off, we back up prod again, apply upgrade scripts and deploy new application.  Without having backup/restore in SQL azure and the ability to stage databases, this type of change control is not going to be feasible.  Without clear change control mechanisms in place, Azure will suffer from adoption issues.

Thanks,
Shan

Answer 3

Shan,

Thanks for the feedback -- since I have your attentions I would like to ask one more questions:-)

In most cloud models where you pay for GB per month I assume you would also have to pay for compute per hour to run your RDBMS software that manages the data.  If you have your database up and running all the time that would be a fixed 720 hours per month regardless of your data size or your usage pattern.  What is the preferred pricing model for the compute associated with the database?

We are very aggressively working on addressing your other concerns.

Thanks

---

Tonyp

Answer 4

I am happy to hear you are trying to address my other concerns.  I was actually interviewed a few months ago and I was VERY clear in the interview about my concerns about a non-scalable fixed-bucket sized databases.  Too bad that marketing person didn't consider my feedback then.  Oh well...

As for your follow-up question, I would expect to pay for what I use, and in the database compute model, that would be CPU cycles.  I would like to point out that it would not be calculated by the hours the service is available, but something that is actually a reflection of the workload.  I also have the same feelings about Azure compute - some base service level plus CPU consumed - not the current $86/m that it works out to even if the website has low activity.  But that is a different story - sorry for the digression.

Shan

Answer 5

A very interesting thread that highlights some costing issues that were not conveyed in the pricing press release last month.

My first thought was "ouch, so I cannot host 3 x 3 Gb databases for $100 per month". Now after more think time I am actually reasasured to hear that SQL Azure resource allocation is more sophisticated than a blunt disk space allocation model. I was previously concerned that disk space only resource rationing would allow badly designed apps to soak up SQL Azure compute power and so adversly affect the performance of my application.

As to the proposed DB sizes... for my multitennant application I would not hit the 10Gb ceiling because it would be simple to spill new customers over into a new 10Gb DB instance. As to whether 1Gb and 10Gb are the sweet spot sizes for the SQL Azure service launch? Definitly not, these numbers represent strange extremes. I am pretty sure there is a silent majority here who consider 2 to 5 GB is the norm.

But does the 10Gb size annoy me? No really, if my Azure hosted application is commercially successful I anticipate that I will end up purchasing multiple 10Gb allowances before my first DB reaches 3Gb because I know that in reality I will be purchasing extra DB query compute power in order to keep my query round trip times within acceptable margins. 

Answer 6

camelCase,

Not everyone gets to build an app from scratch.  There will be a number of apps that originate from an ISV that has a single-database design (I don't know ANY ISVs that support sharding their databases), or are already on premise and want to move to the cloud.  For both of these scenarios, rearchitecting the appication from scratch is not an option.

You may think it easy to build a multi-tenant system, but that requires careful design with a way of tracking which database any piece of data is on, monitoring the database sizes, moving data from one database to another (without any downtime) to rebalance the data or deal with an issue where a database is getting full with new transaction data, etc.  You also lose referential integrity constraints as well.  This is very much a non-trivial design.

I am not so concerned about worring that someone else's application is going to slow mine down.  Consider Windos Azure - someone else's website can be churning away and it won't affect mine because I am on another VM.  I have the same expectation of SQL - that they would use some form of governer to ensure that my database gets its fair share of CPU cycles and I/O operations.

I am more concerned about 3 3GB databases costing me $300/m (3 business editions), which is 3 times the price you were thinking about.  And in the end, even if you consider your application wildly successful, if Azure costs you $300/m and the next competitor is a fraction of that, you will still likely want to host it on the best-value for the least-cost option.  If Microsoft were to change their plans and only charge us per the GB we use, then the cost of 3 3GB databases would be $90/m (3 * $10/G/m), and that is within reason for me.

Shan

Answer 7

@Shan

(1) Size limit.

My concern is this... when Microsoft announced their plan to expose the RDB functions of SQL Server on the Azure platform we were told the DB size limit might be around 5Gb. MS explained this was because it would be difficult to handle replication/failover events for a larger database. Now 4 months later the official max size has doubled to 10Gb and folks are still campaigning for a higher limit.

Stability and consistent query performance are my top requirements for SQL Azure, but if MS gives in and raises the max DB size again could this cause my application to experience a performance wobble when a SQL Azure server crashes and the Azure fabric struggles to replicate a new copy of your 15 or 20Gb database?

(2) Protected resources.

I think you should be more aware of the potential for a bad neighbour application to affect your application. Things look promissing for VM partitioning because hypervisors have had years to mature and the CPU manufacturers have helped by adding microcode instructions to assist hypervisors. That said you should still be aware of the potential for a physical server's network connection to be swamped, this is an issue on Amazon's EC2 VM technology.

As to SQL Azure resource partitioning it is early days and I imagine that MS has had to retrofit the resource rationing into the SQL Server engine on a telescoped development schedule.

(3) Competitive cost advantage.

SQL Azure's cost per Gb of storage is not really the primary measure of value. One Gb of cloud disk storage costs only cents*, 98% of the monthly SQL Azure fee buys query compute power and data memory cache. $300 per month to host 3 x 3Gb RDBs on a highly performant platform could be excellent value, we just don't have the comparative performance data yet.

Have you costed out hosting a 20 Gb db on a clustered pair of Window SQL Servers at Amazon together with block storage and S3 storage for you backup cycle? The monthly bill goes way north of $1000.

* = Assumes that SQL Azure uses the same quality SAN storage as general Azure table/blob storage.

Answer 8

@camelCase

As far as quick recovery of a large database, I do this all the time with SQL 2008 database mirroring, and prior to that with SQL 2000/5 log shipping.  You don't have to move the entire database - simply keep the logs shipping to the other server.  I have done this with databases of about 100GB and failover to the secondary servers is in seconds.

I would assume that as with Windows Azure, our databases would be mounted on a fast SAN device and would be easily remounted on any failed SQL server.

I have many databases that simply cannot be moved into Windows Azure because of the 10GB limit - some of the things that are of obvious value to you are not as important to me, and I find it a shame that I cannot move some of those databases to SQL Azure simply because of this limit.

It should be noted that there are a number of different types of customers, and each of them will have different requirements.  For you it might be performance and availability, but for other customers, moving on-prem applications into the cloud might value compatibility higher. 

Thanks,
Shan

Answer 9

@camelCase

As far as quick recovery of a large database, I do this all the time with SQL 2008 database mirroring, and prior to that with SQL 2000/5 log shipping.  You don't have to move the entire database - simply keep the logs shipping to the other server.  I have done this with databases of about 100GB and failover to the secondary servers is in seconds.

I would assume that as with Windows Azure, our databases would be mounted on a fast SAN device and would be easily remounted on any failed SQL server.

From what we have been told about SQL Azure in public the conventional mechanisms of SQL Server clustering, backups and failover do not apply in the Azure cloud.  When you have 20 minutes to spare read up on the new style SQL Server corum/federation created for the Azure cloud.

I would be overjoyed to hear that my SQL Azure database volumes were mounted on enterprise class SAN storage but some early end-user testing of another well known cloud offering indicated SAN disk performance was closer to a single drive fitted on the average desktop PC. One hope I have for SQL Azure is that Microsoft could in theory ringfence a region of higher performance Azure storage for our RDBs unlike other mix and match cloud systems where a database is stored on generic cloud file infrastructure.

Time will tell, I cannot wait for Roger Jennings to fire up his database cloud demo applications on SQL Azure and report the numbers in his Oakleaf blog.

Answer 10

Hi Tony,

It's nice to see that you guys are still open for suggestions/discussions on pricing policies.

At first i was very exited about the azure platform, we made some integration tests very early on and after enabling full trust in march we were completely ok to make the jump. Unfortunately as soon as the pricing policies became public i had to cancel all our plans on going to azure.

We actually have a multitenant application relying (partially) on a 1 database per customer model. This leaves us with about 90% of the databases being smaller than 20mb.

What I’m wondering is that if this model if final and not discussable of if there are still openings?

Joeri

Answer 11

CamelCase,

The SQL Azure database files are not stored in generic cloud file infrastructure. All SQL Azure systems, including storage, are isolated and dedicated for use in SQL Azure.  SQL Azure uses specialized version of SQL replication technologies and  this is done at the database level with full transactional consistency and support for transparent failover.  There are no single points of failure in the way we manage a SQL Azure database.  

---

Tonyp

Answer 12

We are interested in hearing the specific issues around pricing -- are your concerns around Windows Azure Compute? Windows Azure Storage or SQL Azure? 

What pricing model are you interested in?

With SQL Azure, for a flat fee of $0.34 a day you get a database that includes the use of memory, IO, CPU plus 1GB of data.  The only additional charge is for the bandwidth in/out of our data centers. 

If you prefer to have an off-line conversation please send me an email:  [e-mail]

thanks

---

Tonyp

Answer 13

We are interested in hearing the specific issues around pricing -- are your concerns around Windows Azure Compute? Windows Azure Storage or SQL Azure?

I am writing a new SAAS application with multi tenancy designed to fit Azure so I do not expect to hit constraints others face when porting existing apps. The DB sizes are not an issue for me but I would like an intermediate upgrade option between $10 and $100 that would add more compute power to a small 3Gb DB.

Having studied the amazon offering in detail I will eventually need the added reassurance of a Silver support contract that offers an emergency suport phone where I can tell Azure ops to do a forced reset on an Azure resource I own.

Finally the SQL Azure security topology troubles me, I will eventually need a much stronger authentication mechansism for admin work. The new hardware client passwork token gadget released for Amazon Ec2 points the way here.