I’m a huge fan of Windows Azure SQL Database (WASD), I really am.
I know there are other fans out there as well. With each passing day we help transport more of the old-school DBAs into the 21st century by showing them what Azure has to offer.
Unfortunately there are always a few folks that tend to focus on what is “missing” from WASD. They don’t see WASD as a different version of SQL Server meant to solve a different set of problems than the traditional on-premises version of SQL Server. They only see WASD as something incomplete.
In short, someone moved their cheese.
So I decided to list out my top seven features that users will find are currently unsupported in WASD. I think this approach works best as it lists out the items, rather than WASD being a mystery. Once you spend some time reviewing these seven items you can decide for yourself where WASD is the right solution for your projects.
I put together a quick slide deck and posted it to Slideshare, but you can also view it here:
I’d suggest you look at the items in the slides which include:
- Database file placement
- Database mirroring
- Trace flags
- BACKUP and RESTORE commands
- CLR objects
- Distributed queries
- Service Broker
If you have a system that does not require any of those seven features then you should consider WASD as a platform for future deployments. Note that I am not telling you that WASD is the right solution for you simply because you don’t have a requirement for one of the seven items listed above. What I want to make clear is that you should use WASD when it makes sense for you to do so.
Those seven items are a good start for you to use as a guide. You can see the full list of SQL Server limitations in WASD here.
Come join us in the 21st century, we have the best frozen yogurt.
Alright, so no backups then what happens when you screw up and need to restore? Oh wait, no restores… hmmm
Also, what about maintaining T-Logs? How is that done?
I attended Windows Azure training at MS last week, I was impressed but we only went over infrastructure and the VM side, no WASD so this is all a bit new to me.
Good to know this stuff for future use!
No backups, no restores, and no need to worry about your t-logs, that is all done for you.
The reduction in options also means a reduction in administrative overhead. You can’t compare it to what you already know, you have to look at it as something new.
Once most people see it as a new architecture they start to think about places where using WASD is the right choice for them.
So it’s almost like your DB is permanently set to SIMPLE RECOVERY. Hmmm.. pretty interesting. Just wondering what if someone dropped a table or ran a delete or update without a where clause in there. What then? Do they have some sort of snapshotting (not a real word I know) system on the backend that you can rollback to? Just seems a little unsafe. I can already envision some DBs in my environment that would benefit from WASD just want to make sure they are id10t proof 🙂
You would need to have a system in place to create a copy of your database at regular intervals. That’s the only way for you to recover from such a scenario. WASD keeps three copies of your data but those copies are there to ensure HA goals, not for DR purposes.
And those copies that you do at regular intervals (sounds like a backup) can you store those in Windows Azure somewhere? Looks like DB Snapshots are still part of the WASD system that also might be a viable option if you can schedule those on regular intervals.
There needs to be an “Oppss” button somewhere! I understand that this is a solution for certain scenarios and I can think of a few where this would be useful, but the point of being able to recover from human failure is important nonetheless.
Yes, you can create a copy and store it in Azure. And for some systems a nightly copy would be sufficient for DR purposes.
Woo Hoo! No Service Broker!
Ha! Yeah, that sounds great…unless you have a system that needs it!
Well yes, but if I have a system that needs it then I shouldn’t be using Azure right? 🙂
Exactly.
Hmm..why would you want database mirroring though? Sql azure automatically maintains a default replica set of 3. And provides automatic faliover and geo redundancy. Or do you mean that you’d automatically like 3 readable primaries to be maintained?
Cole,
Correct, and the replicas are not accessible in Azure. Folks that use mirroring right now are (possibly) doing so in an effort to offload a reporting solution. Since mirroring is a fairly popular solution these days it tends to be one of the things I hear folks wish was in Azure, but is not.
Thanks!
I’m surprised by encryption and full-text being missing thus far too. If you use ’em, you have to have them. It’s a full-out show-stopper for some.
Stephen,
I’m somewhat surprised as well. My guess is that those technologies aren’t as widely used as we might think. At least, not used enough to make it worth the time and effort to get them working in WASD.
And now, with Azure VMs, it is easier for you to have those features…so my guess is WASD will never get them.