Welcome back to another week of SQL University. This week we are going to spend some time getting familiar with SQL Azure, which is really just a fancy term for ‘distributed computing’. And while you may not have a need for such a thing today you should spend time getting familiar with SQL azure because one day it will be the most dominant version of SQL Server.
tempdb
Do you even know how to get started with SQL Azure? No? Go here and join the free trial. And by “free” I mean you have to give them a credit card number and you will be charged for any use over your specified trial amount. Last year I signed up for this same “free” trial and got billed a whopping $1.06 that I was not expecting.
But I never canceled my account. Partly because I couldn’t, and partly because I knew I would need SQL Azure again sometime. And eventually so will you. So, might as well succumb to the Cloud and go ahead and give in to our dark overlords now.
msdb
OK, you have your account, so now what? Well, you need to connect up to the Cloud. First thing I want you to understand is that Windows Azure is the platform that SQL Azure will sit on top of. Since this blog is part of SQL University, I will focus my comments and thoughts on SQL Azure. But it warrants mentioning that you are not going anywhere with SQL Azure without having Windows Azure.
Make sure you have the SQL 2008 R2 version of SSMS installed somewhere, as you can use that version in order to connect to SQL Azure directly. You can also use Visual Studio to connect to and develop against Azure as well. There is also a ‘Database Manager’, a Silverlight interface that allows for you to connect to and manage your databases in the Cloud.
So go ahead and start creating some databases in SQL Azure. Learn more about what is allowed and not allowed. For example, you can create a table without a clustered index, but you will not be able to load data into the table until you have a clustered index defined.
model
Here you want to be able to move data in and out with ease, and to be able to perform some basic diagnostic info.But here’s the catch: SQL Azure is a completely different version of SQL. As of this blog post the version number is 10.25.9501.0, so think of it as something in-between SQL 2008 and SQL 2008 R2. That means your standard set of DMV queries will not necessarily work against Azure. For a nice list of useful queries you can run check out Glenn Berry’s blog post on the subject.
If there was one thing I would tell you that Azure needs to make easier it is this: billing and backups. OK, so that is two things, so I will focus on just the billing for now. See, you really don’t have an easy way to know and keep track of how much your Azure database will cost you. Well, I sure didn’t when I got charged that $1.06. I mean, I was given my bill, and it had lots of lines, but to this day I have no idea what I did that went “over” my allotted amount of usage.
If you want to be at the model level for SQL azure then you need to be aware of how billing works but more importantly you need to be able to judge for yourself how much you can expect to be charged. This will make it easier for you to decide if migrating an application to the cloud is financially viable.
(As for backups, you can’t do them, so stop asking. Oh sure, I could copy my database into another database, or use SQLCMD or SSIS to pull the data down, but keep in mind you pay for the bandwidth. So, no backups, and no restores. Why would you ever need those anyway?)
master
At this level, you have mastered the ability to spin up servers on Azure, create databases, and migrate your data. You advocate using Azure to someone almost every day.
And your name may or may not be Buck Woody.
resourcedb
There’s only one place you need to go for more info about Azure:
- Buck Woody (blog | @buckwoody)
You can actually sign up for free, 30-day, no credit card-needed, trial of Azure/SQL Azure here: http://bit.ly/eTIKq9
Oooh one more thing, check out Cloud Tally by Redgate. They let you know, in their own awesome Redgate way, what your Azure usage is so you can monitor it easily: https://www.cloudtally.com/User/SignUp
Jorge,
Thanks for the links!
Are there any resources yet that might show why I would ever use Azure over say Amazon AWS? Is Azure only crippled SQL Server? Or can I get real SQL Server like on an AWS instance?
Rob,
Great question! I would ask you to list out your requirements and then compare the two services. I would not describe SQL Azure as a ‘crippled’ version of SQL, just ‘different’.
I had no idea you could deploy a version of SQL Server to Amazon AWS. Let me ping Buck to see his thoughts on the matter.
Hello Rob – yes, you could certainly do that, or even just set up a SQL Server yourself at a colo. But the point of PaaS is that you don’t have to:
– License/Install/Patch the OS
– License/Install/Patch SQL Server
– Configure SQL Server
– Monitor SQL Server
– Set up Ha and DR for SQL Server
– Pay for what you do not use
So the key is this – if those things are important to you and you want to do them, use IaaS (AWS or others) or on-premise systems.
If you would rather not manage them, use PaaS (SQL Azure). It’s just another choice you have available to solve a problem.
Thanks Buck!
“Set up HA/DR for SQL / license/patch issues”
Cool, so I never have to worry about corruption in a DB or ever worry about downtime with Azure? In addition, it never updates/patches features that would break my application ? Quite impressive!
Rob – I tend not to use words like “ever” or always”. I don’t think you’ll find those in my posts.
Corruption should not be an issue, as we use three-way database mirrors, and torn pages and the like can be rebuilt from the mirror. Updates are handled incrementally, announced, and you’re given an option to test your systems (as we do). If you’re interested in these details, you can refer to the documentation on how it is handled.
Thanks again. Perhaps it is time for me to create an AWS vs SQL Azure Death Match presentation…of course, I would have to get over my preconceptions and what fun would that be?
Ha! True enough, Rob. I would agree to a death-match like that, as long as the requirements are the advantages that a PaaS would provide. We don’t have an IaaS, and AWS isn’t a PaaS for SQL Server.
Let’s do it!
Yeah, it would need some clear cut scenarios from the start.
For example, if I use AppHabor(Paas) for .NET/SQL Server which uses AWS instead of Azure… does that count as a body slam or an eye poke? 🙂
Tom – As always, great post. I have been playing with SQL Azure for about ~ 5 months. Apparently, MSFT charges you transferring data to your DB in the cloud as well as storage (~$9.99/GB month). So when transferring data you want to make to move only the data you need opposed to dropping the database and moving all the data back up to cloud.
Mark,
Yes, I found that to be a little frustrating at first, but only because it was unexpected. So, if I wanted to backup my data, I needed to pipe it down form the cloud, which was not a free transaction by any means.
Tom/Mark,
Yup the data transfer thing is another thing one has to get used to in “Azure-world”. You can transfer all the data you want within a data center (i.e. DB–>app server) but if you transfer any data outside of the data center (i.e. backups from cloud to local, transferring db from TX data center to Chicago) you’ll get charged.