This artist formerly known as SQL Azure is a cloud service something akin to Microsoft SQL Server.
When it first came out, it had a lot of challenges – a small subset of T-SQL commands and datatypes, inability to take backups, and absurdly small database sizes.
But much like Emma Watson, when your back was turned, WASD matured into a surprisingly capable platform. However, most of us still aren’t using it, preferring to run SQL Server on-premise.
This leads to an interesting question: what would Microsoft have to do to get you into Windows Azure SQL Database?
33 Comments. Leave new
Wish #1 – Increase the maximum database size and/or add a ‘listener’ for Federations so apps don’t have to be Federation-aware.
Wish #2 – Better backup / restore options.
Jon – yeah, both of those are pretty tough restrictions right now.
Part of the appeal of the “cloud” term is using things as a service so that they have unlimited scale, but you can’t say that when there’s built-in size restrictions that are much smaller than on-premise options. I totally understand why they do it – Microsoft could get to market faster by making the customer design the scaling solution rather than doing it themselves – but it just isn’t really “cloud” when we have that kind of restriction.
The backup/restore thing is really, really tough. I was pretty surprised when that came out, and we do have backups today, but it’s still a rough story.
In principle it’s about changing mindset – it’s a different platform so shouldn’t be directly compared to the box product. In reality though, that’s what people do. Despite its increasing maturity it still has a few key flaws that need addressing before it’ll be considered a serious player:
1. Backups – they have point-in-time restores “coming soon”, but as yet you’re still limited to BACPAC exports. And if you want transactional consistency you have to COPY your database first (at a cost!)
2. Tracing – traces and thus the profiler are not available, nor XEvents. Again, there has been talk of introducing the latter, but as yet there’s nothing to monitor events and activity.
3. Performance, both speed and consistency – this one is a real mindset issue. By design it runs on commodity hardware, in a multi-tenant environment. This brings with it low performance per database, and inconsistent performance due to shared resources. You have to scale out your databases to achieve performance, meaning more work in the app layer. Of course, they now have “Premium” for Azure SQL Database (in preview) to address this very issue, but it’s too bloomin’ expensive!
I think once some of the fundamentals have been addressed, it’ll be about getting the platform feature-rich. Suddenly people will start seeing the benefits.
Mike – yeah, I agree, I think those fundamentals are, well, fundamental. It’s also interesting that all three of those are areas where traditional on-premise database administrators have struggled as well. If you choose WASD today, you might be trading apples for apples – getting a platform where you’re facing the same challenges you face on-premise. Does that make it an easier sell, or do you think most shops have already mastered these three problems?
I think on-premises, those challenges have solutions mature enough to consider them solved. Everything evolves, in general we’re in a good place. WASD is playing catch-up.
The cynic in me actually thinks a lot of it is planned. Even given the sheer enormity or their datacentres, Microsoft would not cope with demand if the Azure uptake increased. It’s anything but infinitely scalable. Perhaps they’ve been withholding releases until they can deal with capacity issues… 🙂
The biggest factor for us would be security. We are very touchy about placing our data on cloud systems. I’m not sure if we can ever consider moving our data off of site.
James – When folks say that, cloud proponents usually answer with a question, so I gotta ask it here too: how are you doing offsite backups today?
We have 3 geographically separate sites and we exchange encrypted backups between them. These are all very secure sites with dedicated links in between. I would like to consider Azure for SQL but until I could prove it was at least as secure as what we have I don’t think I ever will
James – gotcha. That makes sense, and typically it’s a hallmark of a large enterprise company that spends tens (or hundreds) of millions per year on hardware, software, infrastructure, and staff. That’s indeed the right answer if you want that kind of security – but of course there’s a cost for that. 😀
I think the problem is that most folk do not really trust a database in the cloud just yet because of mostly performance/security reasons. Also, when you compare it’s offerings to heroku.postgresql it looks pretty lame and expensive. Mind you, come to that, I think I am one of the ever increasing crowd that wonder why on earth we use SqlServer full stop when PostgreSQL exists. Surely Postgres must be just about the best kept secret of the internet.
Mogga – that’s interesting. Would you say that most folks trust on-premise database performance and security?
Re: Mogga’s comment and Postgresql – I am wondering whether the limitations of SQL when moving to Azure do mean it’s often not going to be the most natural choice, even for teams with a lot of years invested in MS SQL.
The position Microsoft ideally want to be in is that it’s a no-brainer for existing clients looking at the cloud to jump to Azure, and I just don’t think they’re there yet. If I was involved in design for a cloud-based data project, I think I’d have a strong case for at least looking at other options, because staying with the same vendor is still going to be disruptive and place new/different limitations on architecture.
If adult Hermione is the new WASD spokeswoman, I’m in. But seriously, in addition to the comments already made, I would add that there is a more comfortable feeling with on-premise solutions in that they seem more final/concrete. I know in reality there are on-going costs for an on-premise solution too, but there is that feeling that I’m going to be set and in control for x years if I spend x dollars. I don’t know how the pricing works for Azure; but, from the little I have heard, the pros and cons seem similar to renting or leasing versus buying a car. I am much more comfortable owning the car, but both solutions have their place. I think if Microsoft continues to take steps to appease us database people with control issues, adoption will continue to rise.
Very simple thing, but support for object-level metadata in the form of extended properties on database objects in Windows Azure SQL DB would be great. When I last checked, this wasn’t supported. This is a situationally-useful feature in the on-premises SQL Server where the trend is that things change at a moderate pace and stuff is usually documented somewhere, but in a cloud system where the expectation is that things can be very dynamic and most changes are done via scripting, not having this simple feature available creates unnecessary pain.
This is probably one of those things where Microsoft said “we have to cut something, so let’s cut the thing people don’t use so much”, but ironically the need for this feature in the new environment is much more than in the on-premise environment where they would have gathered that data.
One practical example of how this missing feature is affecting software is RedGate’s work on their new SQL migrations v2 feature. When they only supported on-premise SQL Server, they could store metadata in extended properties, but to support WASD they are forced to do things like creating hard-coded TVFs and vendor schemas in customer databases to enable the new functionality. I’m sure you can understand how that might be met with resistance.
At the moment there are still limitations on Azure right and left. I feel that makes it *less* productive than on-premises. It isn’t that labor-intensive to run a SQL Server on-premises. Once it is set-up correctly it is just there and works. Cloud does not seem to help that much with ongoing labor expenditures. It just converts all up-front costs to recurring costs.
What limitations are stopping your adoption of Azure, tobi?
Cost is an interesting question, too – do your cost calculations of SQL Server being up front include power, cooling, rack space rental, and personnel to manage and monitor the hardware?
As an ISV we started looking at Azure when it came out and actively using it more than 2 years ago. The backups are a significant issue (they have improved). From our point of view we offer both on premise or Azure hosted options. The difference was that if you liked the product we could have it in the cloud for you tomorrow and you paid on a month by month basis. The business could see the benefits, but the traditional model then involved their IT manager and a 6-12 month wait while they decided if the would or could support this and got around to implementing it. As our market was predominately SME’s the performance limitations of the Azure database were manageable, for large companies we would either look at an on premise solution. We now have greater options around dedicated SQL Server VM’s in Windows Azure which provide a mid-sized option. Yes it required some re-coding to support the other limitations of SQL Azure but having done that it is the same database we deploy either in Azure or on-premise.
I notice that several replies are about trust — everything from “is my data secure?” and “Will it perform well enough?” to “Is this whole thing just a ploy to turn up-front costs into a monthly revenue stream?”
In one of Bren’ts replies, he asked, “…Would you say that most folks trust on-premise database performance and security?”. For me, that’s the nub of the issue. I don’t have to just “trust” them. I can _see_ them and I’m confident that they are individuals of integrity who highly value their reputations (not to mention their jobs 8).
The big cloud providers, on the other hand, probably value my business. Of course, if you’ve had any dealings with, say, your cable provider lately then you can assess what that’s worth.
“What would Microsoft have to do to get you into Windows Azure SQL Database?” Nothing in the way of “ubbles” and “ilities” matter to me until Microsoft (or Amazon or Google or …) manifests corporate character (i.e., integrity and competence) long enough and in enough situations that I trust them with possession my company’s data.
Gary – that’s an interesting take on it. You’re saying that you don’t have to trust your coworkers as long as you can see them and you know that they value their reputations and jobs? That’s rather interesting – how do you gauge their integrity?
I’m not being flip here – I know a lot of folks who have trusted their data to people with the best intentions, but those people – through a lack of training or experience – have made crucial errors that left security holes or corruption risks in place. What I’m hearing you say is, “As long as they’ve got good intentions, love their jobs, and are within eyesight, we’re good” – but I don’t think that’s really what you meant.
Do you want to take another shot at defining your answer?
Good grief, Brent, you’ve caught me in philosopher prince mode this morning. Remember, you asked for it.
To me it’s about manifest character … which isn’t just good intentions or a need for income. Again, to me, character is a combination of integrity and competence along with the demonstrated capacity to make and keep promises.
Neither integrity nor competence alone is sufficient. If you needed a serious operation, would you go to a surgeon who, though the most technically competent in his field, tends to discharge patients with one less kidney than they came in with? How ’bout one whose picture is beside the word “integrity” in the dictionary but couldn’t dump water out of a boot if the instructions were printed on the heel (not to mention that suspicious tremor in his right hand 8)?
“How do I gauge their integrity?” Well, how would you gauge the integrity of a surgeon if you needed a serious operation?
I didn’t mean to suggest that simply being within eyesight was sufficient. Over more years than I care to think of at this point, I’ve had the astonishing good fortune of working for, with, and around people who are not only smarter than me but excellent teachers. Not always (or often) close friends but still quality folks. I’ve seen them make and keep promises. I’ve seen then handle crises, been with them on “death march” projects, and asked them dumb questions that I should have known the answers to already. No, I haven’t met any perfect people but I have seen and known some very fine ones. So I think of them when I try to gauge integrity. (Plus good people tend to know other good people … so sometimes I’ll just ask ’em what they think 8)
The sort of trust I refer to isn’t a blank check. People make mistakes; that’s why we have teams. And they sometimes fall off the path of the righteous; that’s why we have audits. (and why we don’t just hand out sa willy nilly)
On the whole, Microsoft SQL Server folks have demonstrated a certain quality of character for a lot of years. I’ve seen them long enough and in enough situations that I trust them. When Azure gets to the same place then we can talk.
Gary – I’m going to focus on that last paragraph, because it’s quite interesting.
Do you know who’s on the Microsoft SQL Server team, and who’s on the Azure team?
(I’ll give you a hint – they had to get the SQL Azure developers, and the code base, from somewhere. Where do you think that was?)
I’m using Windows Azure SQL Database in production, teaching about it at conferences, and helping my company develop tooling for the silly thing. The two things I really, really need:
A bigger database. You bump it up to 500gb and you’ve probably covered 99% of the world’s implementations of SQL Server. And for people beyond that, unless they can shard their data, they shouldn’t be trying to run it in WASD anyway.
Extended Events. Or some mechanism other than querying the cache, which is hyper-volatile since it’s a shared resource with 100+ other databases, as a means to understand query performance. Make it easier for me to identify slow running queries and I’m off.
While there are other limitations from the tool set, I don’t see them as show stoppers or things that prevent me from getting on board for some of my databases. Just note, I wouldn’t put all my databases here, no way. But for some, it just makes sense.
Wish #1
I would like to be able to access different databases in one query within the same instance. I’m not saying that i want to query databases in different instances like 123456.database.windows.net and abcdefg.database.windows.net – that would be too much for the moment. But within the same instance?
Wish #2
I would like to have dedicated environments for DEV, TEST and PRODUCTION. At the moment everybody fires up right into the cloud. So one customer might stress the same physical hardware for performance tests that host _my_ production database and i get into performance trouble. We have faced problems with the secondaries several times, so this is one great wish.
Brent, WASD has the following limitations that don’t work for us:
1) No CLR Support
2) No Service Broker Support
3) No Full text indexing
Support for some type of sql agent like capabilities would be nice too
These are all related; but it basically this is what is stopping us from moving several apps to WASD.
I am using it on prod.
In Hybrid solution.
Wish 1: agent – why do not we have way to long batches not from application?
Like rebuild huge index.
Wish 2: i need scale and autoscale not only for size but for CPU, IO or so.
Pini
Greetings from Australia.
For us, the primary issue is security. Being from a non-US jurisdiction, the recent revelations of the activities of the NSA and the forced collusion of US software vendors under the Patriot Act (amongst other instruments) in general make it impossible (and possibly unethical) for Australian companies to use US vendors to host sensitive corporate data (particularly customer data).
We’ve been using SQL Azure for a while now and I think it still has a lot more maturing to go. We have a database that’s not even that large(around 700k records in our problem table) and we’re running into one of the most common database issues, index fragmentation. Not only does REORGANIZE not exist, but it’s not even recommended that you rebuild indexes because the transaction log will get throttled and you aren’t even guaranteed contiguous space back because it’s shared storage. This is coming from someone in Microsoft.
If something as common as index maintenance on a <1M record table is an issue, then I feel like the platform isn't enterprise ready. I personally don't think that federating on such a small data set should be necessary.
Steve – sounds like it’s time to take a step back and revisit your own skills, actually:
https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/
https://www.brentozar.com/archive/2013/09/why-index-fragmentation-doesnt-matter-video/
Brace yourself – Microsoft just might be right. 😉
Different platform, different approaches. Microsoft have their reasons for recommending against defragging indexes, but it can be beneficial for different reasons: http://sqltuna.blogspot.co.uk/2013/10/index-fragmentation-in-wasd.html
I really need to update those Azure names…
I’ll certainly admit that I’m probably more of a novice, but when I see queries consistently performing better when I have lower fragmentation levels and updated stats, I can’t help but think that it’s at least remotely helpful.
If there’s a good solution that Microsoft offers in the Azure stack(excluding VMs) that allows me to store more in memory, then I’m down for it. If you know of anything that I’ve missed, I would be sincerely happy to hear about it.