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?
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.