On Monday 2/1, SQL Azure goes commercially live. Microsoft’s charging for service now, and that means it’s officially official.
I talk to a lot of enterprise DBAs, project managers, and developers about SQL Server in the cloud. Here’s some of the questions I get asked most often.
What is SQL Azure?
SQL Azure is Microsoft’s solution for SQL Server in the cloud. It has a few key differences from traditional SQL Server:
- Database maximum size is 1GB or 10GB based on your price plan, but no larger
- You can have as many databases as you want, but they may not be on the same server
- There’s no cross-database querying
- There’s no SQL Azure encryption
- It supports a subset of SQL Server’s T-SQL commands, but not all of them
- It supports a subset of SQL Server’s datatypes, but not all of them
I like to think of it as SQL Server Lite – it’s an excellent stepping stone to the real thing. Build your app from the ground up for SQL Azure, and it’s pretty easy to support full-blown SQL Server down the road if your needs change.
How does SQL Azure pricing compare to SQL Server costs?
Azure is a lot cheaper if you only need one database – $10/month for 1gb of data, and $100/month for 10gb of data. Pricing is per database, not per application – you can write multiple applications to query the same database. Price is only one part of the equation, though.
The more databases you need, or the larger databases you need, the more it makes sense to have your own SQL Server. Generally speaking, I tell companies that if you have any of these requirements, SQL Azure probably isn’t for you:
- More than one department that needs to store data in SQL Server permanently
- You don’t have at least 2 very senior-level programmers who understand the concept of sharding
- You face security concerns involving HIPAA, SOX, or PCI compliance
- You’ve already built a schema and/or an application
I’m not saying you can’t meet those needs with SQL Azure, but generally speaking, you’re not the target market for Azure v1.0. On the other hand, if you have the following requirements, Azure might be a very good fit for you:
- No full-time IT staff, no DBAs
- No datacenter (or maybe not even an office)
- No (or very, very little) legacy code, and you’re building a new app from scratch
- You don’t mind putting in extra man-hours in the beginning to avoid capital investments
In that situation, Azure might make good sense, but you’re going to have to plan your way around Azure’s limitations.
Why does SQL Azure have a 10GB limit?
I can’t speak for Microsoft, but it would make sense to put a small database size limit to make sure queries run fast. If you’ve got a 10GB database, it’s easy to make sure every query runs blazing fast. Throw hardware at the problem, throw a lot of indexes at it, and you’ll get good performance. Keeping things small also makes backup & recovery easier, and makes high availability design easier.
There are no SQL Azure RAM limits or CPU limits. You have no control over how much CPU power or memory your database gets. SQL Server 2008 has a Resource Governor that allows database administrators to throttle queries based on login or database, but Azure doesn’t support those features.
How can I load balance SQL Azure or do cross-database joins?
Since SQL Azure databases max out at 10GB, it would be great if we could create several databases on the same server and use a view to select from all of them. No dice – that’s not supported. As of February 2010, your application has to know which databases to query. If you need to combine results from multiple databases, you’ll need to do that processing inside the application by querying all of the databases and then joining the results together in the app.
Scaling SQL Azure this way is called sharding – partitioning your data into different shards, each stored in a different database (or even different servers.) Design your app like this, and it will scale like crazy. If you’re the kind of coder who loves reading the stories on HighScalability.com, you’ll love sharding. If you’ve never heard of HighScalability.com, you don’t want to architect your own sharded database – bring in somebody who lives this stuff.
How do I handle the SQL Azure backup process?
Unfortunately, right now, you have to build one yourself. SQL Azure does not support the BACKUP command. You’ll need to figure out how to sync your data out to an external database, and while you’re doing that design, keep in mind that you pay Microsoft for data transfers in & out of SQL Azure.
Microsoft states that Azure is highly available and contains its own backups across multiple servers. However, just as RAID is not a backup, high availability is not a backup either. You need backups if you want protection from any of these scenarios:
- Your app might accidentally delete or modify data
- Your users might accidentally delete or modify data
- A hacker might purposely delete or modify data
- Or believe it or not, Microsoft services just might go down.
Remember, folks, as much as I love Microsoft, we’re talking about the company that brought you the Blue Screen of Death. You would be irresponsible not to back up your data for your own protection. (T-shirt available for around $25, and if you want different colors or shirts, click the Customize button after the link.)
How good is SQL Azure performance?
The biggest SQL Azure bottleneck is your bandwidth, because all Azure queries go from your application to Microsoft’s servers and back. Azure developers report that they’re very satisfied with the query speed, and usually report that it’s faster than servers they’ve built themselves.
The problem will arise when you’re not satisfied with SQL Azure’s performance. You’ll want to ask these questions:
- What else is querying my database right now?
- What does my query execution plan look like?
- What indexes could I add to make this query run faster?
- Is another Azure database hammering the server right now?
Unfortunately, SQL Azure doesn’t support any commands, dynamic management views (DMVs), or functions that will help you answer those questions.
Can I use SQL Azure as a backup with log shipping or database mirroring?
No.
Don’t think of SQL Azure as your disaster recovery solution – it doesn’t work that way. If you design your database for sharding, then you can work out a method to sync between Azure and full-blown SQL Server, but right now you’re very much inventing the wheel. Your next question would be how to run Azure inside your own datacenter so that you could keep an identical environment between production (Azure) and disaster recovery (your place.)
How do I run Microsoft SQL Azure on commodity hardware?
You can’t run SQL Azure in-house on your own hardware. It only runs in Microsoft’s datacenters. If you want to develop against Azure but you don’t want to pay for Azure, you have two options.
Option #1 is to buy SQL Server Developer Edition for under $50. It’s functionally equivalent to SQL Server Enterprise Edition, but the license prohibits using it in production. You’ll have to restrict yourself to only using the Azure-level features, though – if you accidentally design your schema to use, say, full text search, it’ll work fine on Developer Edition, but it won’t work in Azure.
Option #2 is to get an MSDN subscription with Azure benefits.
What SQL Azure plans do MSDN subscribers get for free?
It depends on your level of MSDN/Visual Studio:
- Visual Studio with MSDN Professional (around $1,000) – gets one 1GB database, $120/yr value
- Visual Studio with MSDN Premium (around $2,000) – gets two 1GB databases, $240/yr value
- Visual Studio Ultimate with MSDN – gets three 1GB databases
That pricing is retail pricing, and volume license users get Visual Studio & MSDN cheaper. Obviously you wouldn’t buy an MSDN subscription just to get $360 worth of SQL Azure databases ($30 * 12 = $360) but it’s part of the picture. MSDN subscriptions also come with other Azure benefits too, and those Azure benefits stay live as long as you maintain your MSDN subscription.
Got a SQL Azure question I didn’t answer?
Leave it in the comments and I’ll track down the information for you.
42 Comments. Leave new
Brent,
Great article however I think the following point needs extra clarification:
“You can have as many databases as you want, but they may not be on the same server”
The word “server” in the context of SQL Azure does not mean the same as what we are used to. The server is a logical thing rather than being a single process running on a single O/S instance as we are used to. Hence its certainly true that your databases won’t all be on a single O/S (in actual fact every SQL Azure database exists in multiple places on multiple O/S instances) but there is still the concept of a SQL Azure server and if you want all of your databases to appear on that logical server then that is eminently possible as far as I understand it.
Regards
Jamie
Right, we need to stop thinking about “server” period since there’s no cross-database querying. Really, all you have is databases.
Security functions is not supported by SQL AZURE
a) Session user
b)Sys user
c)user
d)user_id
Dude, why would you post a multiple-choice question on a blog post? We’re not here to be tested. C’mon. If this is your homework, put a little more effort into it.
How many options are there for azure and nsbb client layer
a)4
b)2
c)1
d)3
Dude, why would you post a multiple-choice question on a blog post? We’re not here to be tested. C’mon. If this is your homework, put a little more effort into it.
Thanks for sharing invaluable insight. Might be too early, but have two queries:
– do you have / know of some comparative analysis for “server” based and then SQL Azure based database performance of an application (more better if its from Prod), and
– any initial trends on how SQL Azure is looking like for live systems, any key clients as yet.
Am getting more case study driven I think… 😉
Please let me know if my queries are bit vague.. And thanks again for posting the details.
You can’t do comparisons between Azure performance and traditional hosted SQL Server because there’s too many differences. They don’t support the same commands, they don’t have the same data capacities, they have different sharding models, and Azure’s performance for certain queries is limited by your network connection. If performance is truly your deciding factor, then benchmarks between Azure and SQL Server won’t matter – you’ll use one or the other based on your other performance needs, like how you shard data (or whether you want to do that at all.)
I don’t have any case studies I can share, but you might check with analysts like Gartner or Forrester to see if they’ve got clients on it, or contact Microsoft directly.
That will be great Brent… Thanks.
Hey Brent, two questions:
(1) Reading Microsoft’s site, they are targeting SQL Azure to Small/Medium businesses. Your summary seems to corroborate that. Do you predict that will change in the near future?
(2) What have you heard about future enhancements to SQL Azure?
Lee – well, here’s where being an MVP kinda handcuffs me. I get into trouble if I say anything about future directions of a Microsoft product, especially if I’ve got any knowledge of them. I have absolutely NO knowledge of any future enhancements to Azure, so what I’m about to say is total conjecture. If I knew anything, I wouldn’t give an opinion.
So my thoughts are:
1. I don’t think Azure will target enterprises in the near future because (in my opinion) the current version doesn’t have anything to offer businesses with an existing SQL Server infrastructure. You can’t log ship to Azure, you can’t mirror to it, you can’t redeploy existing apps to it unless you’ve cleaned out any features that rely on non-Azure-supported commands, etc. It’s just not appealing to enterprises. If they wanted it to appeal to enterprises, they would need to add support for backups (hello!) for disaster recovery & archival purposes, plus automatic sharding.
2. NOTHING, I TELL YOU! I KNOW NOTHING! Heh. But even if I had, I couldn’t say anything. I haven’t, but only because I haven’t been listening. I tend not to put any faith in roadmaps. Show me the beta code. The time span between a vision for software and a deployable architecture are just too long. You can’t make decisions today based on what might or might not get supported two years from now. If Azure doesn’t meet your needs today, you can’t write code against it hoping that the architecture will add features before you need them. That’s dangerous design.
Brent,
I think the SQL Azure guys have probably got a serrupticious desire to target real end users rather than big enterprise guys. Remember when Access became big in the 80s/90s because every Tom Dick or Harry realised they could whip up their own customer database in no time at all? I see the same happening with SQL Azure – especially if/when Project Houston (http://sqlblog.com/blogs/jamie_thomson/archive/2009/12/06/project-houston.aspx) kicks into gear.
-Jamie
When you say “real end users” do you mean:
– Consumers (folks using spreadsheets now)
– Power users (folks using Access now)
– Developers (folks using Visual Studio)
Consumers can’t possibly write their own apps to store data in Azure. Power users might conceivably graduate from Access to Azure, but they won’t until they get a really good UI to do it.
I think the strongest argument is for developers – guys who know Visual Studio and really don’t care where their data lives as long as it’s fast, cheap, and doesn’t require talking to a DBA.
Yeah I guess I’m talking about power users.
We agree on the point though – that being that folks who can’t be bothered going through the rigmarole with corporate IT have a got a quick-and-dirty solution to their data storage needs.
Regarding the good UI – read up about Project Houston as I believe “a good UI” is exactly what they want that to be.
I can’t help but be a little flip, and don’t kill me, but I gotta say this:
Doesn’t every team want their UI to be a good UI?
And how many teams actually deliver? As the product gets built, the UI generally gets worse, not better, and I’m not all that impressed with those screenshots.
Oh, I never claimed it was good – just that they want it to be 🙂
I’ll rephrase. We’re talking about intent here. They want to provide a way for Mr Tim Dick or Harry Poweruser to go and knock up their own SQL Azure databases that then live for years and years for an ongoing cost that is no more than a rounding error; whether they manage it or not is a different question entirely.
I definitely sense ulterior motives for SQL Azure here!
Do you know if there will be a “Dev Version” available for everyone (10-15MB maybe)?
No, the only developer version is the one included with MSDN subscriptions.
That’s not entirely true. There is no free tier of Azure Database – there is however a free tier of Azure Webapps which includes a database. Choose that, drop the webapp and you have yourself a free azure database. Just don’t tell anyone I told you incase they take it away.
Sorry, I must have followed an old link here. Didn’t intend to necro the old thread.
I’ve got a small “hobby” web application with the website and sql server 2008 externally hosted with an online hosting company. So I’m basically renting space on a hosting company’s servers to make my stuff available on the web. Which sounds like the Azure concept to me – except that I currently have more functionality than I would with Azure. And I can do backups and store them locally. Is this right? What am I missing about Azure?
I feel like I’m way behind the times – been buried professionally in maintaining an old legacy application, so I’ve only just woken up and said “Cloud? What the heck is a cloud?” Since I’m now job-hunting again, I’ve been trying to catch up, but nothing I’ve read about Azure has quite de-mystified it for me…
Emily – exactly. Azure has a subset of functionality compared to what you’ve got, but it has the potential for greater scalability. If your hobby app suddenly became famous and popular, you would have to start writing some pretty big checks for SQL Server 2008 hosting. Azure has the potential to scale higher when the applications are written correctly.
I’d stick with your existing setup for now though.
Hope that helps!
Thanks, Brent, that makes sense – it’s not just about availability and scalability, but the economy of scalability.
I admit I’m intrigued by it. It’s interesting to wonder how this technology, as it matures, could influence software development and the kinds of applications that become possible to build… Sort of like how the decreasing price of storage sparked the shift to digital cameras, and how it combined with cheap/fast bandwidth to make possible applications like Flickr, etc.
As a cofirmed data geek, I’ll definitely be following along. 🙂
Is there any new information available about the physical resource available to an Azure Business Instance?
We have a huge (24 x 2.4 cores) SQL server that uses 72% resources at peak hours. Is there any way we could migrate to SQL Azure? Perhaps by sharding across 6 instances?
thanks,
Ivan
There’s no information about the hardware. I’d suggest asking your developers first – Azure may not support all of the engine features your app is using, for example.
We are developing a web portal.70% of the users are in Asia and 30 % are in north America…
Initially we were planning on hosting it with some providers but were worried by this geographic spread of the users..
With Azure we might not face that problem..
Lets say we host it with Azure ..
1. How will the users get routed ,Eg: North America users vs Asia users
2. How is the SQL Azure set up ..? Will there be latency in data syncing for North America users vs Asia users
If sharding will keep your data in different databases
and if cross database queries are not allowed.
How can you do an aggregate query or just a count(*) query if the data in the table are in two different databases as a result of sharding??
You’ll need to write this type of code yourself. If you have aggregations that you need to perform, you’ll want to pre-compute those at regular intervals, or even as data is written. There are no features, at the moment, that will let you perform some kind of fan out query that can touch every shard that you’re using.
How many simple queries per second can i do with SQL*Azure?
As we know Microsoft Azure introduced three new service tier that is Basic,Standard
and Premium for Azure SQL databases.Lets for Statndard tier service level “S0”, SQL database pricing says
they will charge $15/month for each DB(Standard-S0).
My question is I have to pay only $15/month for Standard “S0” or any additional cost like
the total cost for each SQL DB will vary depending on transactions being carried out in a month.
Shakti – you can buy different service levels for different databases. (I’m not sure if that’s what you’re asking.)
Can this post get updated to how sql azure looks today?
Sam – think of blog posts as newspaper articles. They’re not usually updated later.
Hii Brent ,
How To Over Come Open XML In Azure SQL
Alfaiz – I’m not sure I understand the question?
Sir ,
I Was Trying to Import The Data In Azure SOL Even I had Taken the BACPAC File Backup Of a Database but not able to Import it Azure SOL.
How to Overcome this Situation.
Alfaiz – OK, for questions unrelated to the blog post, head over to http://dba.stackexchange.com.
Tried to run sp_Blitz (v20160903) on an SQL Azure box (v12), got the following error
Msg 40515, Level 15, State 1, Procedure sp_Blitz, Line 19
Reference to database and/or server name in ‘master.sys.databases’ is not supported in this version of SQL Server.
JSJ – Yep! It’s for SQL Server, not Azure SQL DB. They’re different products.
Brent, I create a bacpac from azure and restore to my local on-prem server. However, it throws an error like “Please create a master key in the database o open the master key in the session before performing this operation”. The reason is one of the column in the table is encrypted. I don’t know how to restore the database from azure with master key and certificate etc. Do you know what I could do?
Thanks in advance,
Chung – your best bet is to head over to http://dba.stackexchange.com and post questions there.
Have you blogged about this same topic with updated specs? I’m sure 1 GB is not the current max size.