SQL Azure: Building your first Microsoft database in the cloud

We’ve all been hearing about the cloud for a while and if you’re exposed to Microsoft at all that means Azure. If you’re a database person that means SQL Azure. So what’s it all about? 

Without getting too wordy, it’s more or less a high-availability, hosted version of Microsoft’s SQL Server database. Want most of the benefits of SQL Server but don’t have the hardware or human resources to run it yourself? SQL Azure might be right for you. Maybe you just want to do a quick proof-of-concept or prototype but don’t want to take the time of standing up a SQL Server. SQL Azure might be right for you.

I think I’m probably like a lot of developers in that new technologies can be a little hard to grasp and sometimes even a little scary until I get my hands on it and see it in action. The purpose of this post is to show you how easy it is to create and use database in the cloud on Microsoft’s Windows Azure platform.

Many developers probably don’t know that they get a free subscription to the Azure platform with any Visual Studio Professional, Premium, or Ultimate with MSDN license. Additionally, BizSpark program members also get the same free subscription that is available with Visual Studio Ultimate with MSDN. Details can be found here. If you don’t fall into the MSDN or BizSpark camps and want to give Azure a test drive but don’t want to cut into your budget there is also a free 90-day trial available at the time of this post (http://www.microsoft.com/windowsazure/free-trial/).

Time to start playing. At this point I’ll assume you’ve signed up, have a subscription and are logged in to the Windows Azure portal (http://windows.azure.com). You should see the Getting Started with Windows Azure screen below.

To begin the process of creating a database server and database, click on the Database link on the bottom left hand corner of the screen.

Now click on the Create a new SQL Database Server link.

Choose your subscription. My is named POC for proof-of-concept. 

Select the Region in which you’d like your server hosted.

Now set a password.

You’ll now have to add a firewall  rule to allow incoming traffic from the machine(s) in which you’ll be managing this server.

Once you’ve completed the process of creating a server you’ll come to a screen that provides all the details of your server. You’ll also see that you already have one database, the master  database. The master database is required and cannot be deleted. This database contains things such as permissions and usage metrics.

Now that we have a server we can create a database. Click the Create link in the ribbon-like menu at the top of the screen. Provide a name for the new database and choose an appropriate edition and maximum size. To be safe (in terms of billing) choose a 1 GB Web Edition database.

Congrats! You’ve now successfully created your first Microsoft database in the cloud. 

SQL Azure - Very Cool - Very Big Deal Breakers

THIS POST REQUIRES AN UPDATE WITH CHANGES MADE TO SQL AZURE

For anyone that doesn't know what SQL Azure is, it's essentially SQL Server hosted in Microsoft Data Centers and accessible via the internet. Throw the word cloud in there if you want the executives and sales guys to listen. A competing product would be Amazon's SimpleDB.

SQL Azure is cool. No doubt about it. In a matter of minutes I can provision a SQL Server database somewhere in the ether and connect to it from my machine using the same familiar tools like SQL Server Management Studio and Visual Studio. Reporting Services has a SQL Azure connection type and SSIS allows for SQL Azure sources and destinations using ADO.NET type connection managers.

The benefits of having your database be a SQL Azure database are almost in lockstep with anything "in the cloud." You don't need your own hardware, you get high availability, no software patching/updating, etc. All that stuff becomes someone elses responsibility. Additionally you get scalability and you pay for only what you need. In the traditional, on-premise model of hosting your own database(s), if you have a business where you need to support peak load that only occurs a few days or weeks out of the year you have to pay for that horsepower in both hardware and software licensing costs. Move those same databases to SQL Azure and now you have a platform that scales to your needs on-demand and you only pay for the resources consumed. There's no doubt in my mind that this model will become ubiquitous just as virtualization has become over the years.

So let's move all of our SQL Server databases to SQL Azure! Well, not quite yet. There are some major limitations that will keep 99% of "real" applications off of this platform in the near-term. My biggest "deal-breakers" are listed below.

Database Size Limitations
SQL Azure databases are limited to 5GB for Web Edition databases and 50GB for Business Edition databases. Of course there are plenty of databases that might fall within these limitations but I wouldn't want to risk my job on deploying to this platform only to hit this limitation and have a catastrophe on my hands.

No Windows Authentication
The platform currently supports only SQL Server logins. Intuitively that makes sense since the server doesn't live in your domain but this can become painful if you're an organization that has standardized on using Windows authentication. It might also mean major rework of existing applications that rely on user-based permissions.

No local restore of backups
This one kills me and I think many others as well. While you can now restore a database to another SQL Azure database you cannot bring a backup to your local environment and restore it on a local server. I imagine this could make things rather difficult. Microsoft recommends trasferring your database locally by using SSIS. No thanks.

No Replication
For a lot of organizations this might not matter but if you are an organization that relies heavily on replication you simply can't move a subscriber or publisher out to the cloud.

No SSIS, SSRS, or SSAS
These services are not available on the SQL Azure platform. I would expect that they will be at some point but if I was a betting man I wouldn't put any money on them coming anytime soon. That doesn't mean you can't use these services on local servers while consuming Azure databases but this could hurt your bottom line. I know plenty of organizations that run any combination of these services on one physical machine without issue. So now if you're paying for the hardware to support SSRS why pay a subscription fee to move your database out to the cloud? Maybe you can make some arguments around high availability or scalability for you relational engine but if you're running them on the same server to begin with then these are probably not a concern.

Lack of Profiler
You can't attach a profiler instance to a SQL Azure database. That could get rather dicey when trying to track down all sorts of issues. Seems scary to me. There might be alternative but I'm not aware of them.

Bottom line: SQL Azure is cool, it will mature to the point that it will begin to become adpoted, but it ain't quite ready from prime time.