Choosing the right database on cloud, Azure SQL

Question

We are building an application, an ad serving platform, which will include many processes such as reporting, storing user data, requests, advertiser info, pubisher info etc. Moreover, we will be generating optimization files to serve better ads.

We are using azure for cloud services. We are hoping to benefit from appfabric on azure and their architecture.

My question is related to Azure SQL. Does anyone have any experience with Azure DB, what are the advantages, disadvantages? Performance, reliability, challenges?

While choosing Azure DB, what are things should we benchmark or prototype?

Any ideas or thought, you would like to share, especially related to database on cloud? COnsistency, availability, Partial Tolerance, replication?

Thanks.

Answer 1

You can find out more about SQL DB offerings and their corresponding throughput on the SQL DB pricing page. Also, do check out details on the new SQL DB V12 that provides nearly complete compatibility with SQL Server engine.

Edit (4/29) :

Check out the new Elastic DB offering (Preview) announced at Build today. The pricing page has been updated with Elastic DB price information.

Answer 2

Although this has been answered already, I'd figure I add some more info to it anyway.

In the cloud, you have three storage options, each for its own set of data:

1. SQL Azure - this is your standard MS SQL database, with some features currently disabled (note: the team has said, several times, all of the features will be turned on eventually). It guarantees reliability (AFAIK) by keeping three copies of your data available at any given time. You also get some issues, like connection drops here and there, and you need to plan for that (in the repository logic, you need to have retry logic). Obviously, as with other relational databases, this is mostly useful for storing relational data. Pricing is OK up to 1 GB (~ 10 USD) and goes up dramatically with up to 10 GB (~ 100 USD) so make sure you need it.
2. Azure Table Storage - this is a simple table storage. You primary key is defined based on two IDs (Column & Row keys) and you can store in it, more or less, whatever you like. You cannot index the data (but the key columns are indexed, so your queries can be pretty optimized if you know what you are doing). This is cheap (you pay by transaction), I think the price is around $0,01 per 10.000 transactions. You can use this storage for a lot of things, and you can also implement relations to some extent if you do it right.
3. Blob Storage - you can use this to store your optimization files. It costs around $0.15 per GB/month if I remember correctly.

As for performance considerations, the blob storage is really fast. I didn't expect it to be this fast. SQL does good as well, it is after all your normal MS SQL database. Since it's located "close" to your instance, the network latency is not that much bigger than it would be were it located in a separate machine in your data center.

For up to date info on pricing, look at this Windows Azure Platform Offers. I'm not an expert in pricing, so my figures are rough estimates.

Answer 3

You really need to size your data.

SQL Azure is perfect for small (<1 GB or few GBs) with relational requirements. It provides easy on-boarding for existing sql server developers and makes it really easy to develop complex reports.

However once the data size increases beyond a few GBs, it starts becoming more expensive. Also the patterns for scaling SQL Azure is not as simple as scaling Windows Azure Database - although the new feature of Federations is supposed to make it simpler, it is still not the same as scaling SQL Server (which people would normally scale-up - here in SQL Azure there is a limit of 50 GB and the recommended pattern is to scale-out to more databases).

So, going by what you have (requests, maybe user click data, etc) which can really increase a lot, it might be better to save all this in Windows Azure database instead of SQL Azure. You can also take a dual approach, by saving some data in SQL Azure so that it is easier to report against, and then have high quantity data saved in Windows Azure.

Architect well! It could save a lot!

Answer 4

We used this for a web application.

Experience:

We faced hardly any challenges as it was easy to use because it provides the same TDS interface as the more familiar SQL server so developers can easily adapt if need be. This saved us a lot of time that is usually taken up with Developers having to learn new tools.

Overall I found it to be consistent with good performance and quite reliable.

Other thoughts:

-Manageability

• offers easy manageability, thus saving on a lot of IT related costs

-Scalability

• runs in worldwide data centers so can reach new markets easily
• Can choose a pay as you go pricing model

Hope this helps!

Answer 5

Advantage of SQL Azure is that you can develop against a pretty standard SQL database that people have lots of experience with. Make migration of an app to Azure pretty easy.

The disadvantages i have seen are: 1. Limited backup. This is getting better soon, but it may never be the kind of backup many people want. 2. Some SQL Server features not available, but 95% are. 3. No real control over database server hardware. 4. Price might be an issue, depending on your database size.

Other than that, SQL Azure works well for me.

Answer 6

The first thing to do is look at SQL Azure's feature list to ensure this meets your needs (it probably will), and then the get started page.

Then there are free Labs: SQL Azure Labs.

You can get a live, free 1GB database for 30 days.

The SQL Azure Team blog may be of interest.