This month’s T-SQL Tuesday is hosted by yours truly. Our topic this month is simply the Cloud. If you work in IT there’s approximately zero chance that you’ve managed to avoid this word in some respect. Has your manager asked you to look into what cloud solutions can do for you? Are you ahead the curve and have taken it upon yourself to already start using and/or testing cloud solutions? This month I asked everyone to share what their thoughts were on the cloud.
Choices, Choices
When people talk about cloud solutions there are a myriad of options you could be talking about. Since this is a SQL Server focused blog, I’m going to focus on offerings specific to that. More specifically I’ll be talking about offerings from Microsoft’s cloud solution, Windows Azure, since that’s the platform I have experience with.
In regards to choices around SQL Server in the cloud there are two routes you can take: use Windows Azure SQL Database (WASD). This offering is known as Platform as a Service (PaaS). What this offering does is it offers developers a relational platform to develop against quickly and easily without the hassle and worry of the administrative overhead that goes with standing up a full SQL Server server. The drawbacks here are there are certain limitations around this option but I’ll drill into that in further detail below.
The second solution you’ll come across, and my personal favorite, is Windows Azure Virtual Machines. This offering is referred to as Infrastructure as a Service (IaaS). What this gives you is an on-demand, scalable compute infrastructure. In non-marketing speak it basically means you can spin up a virtual machine with SQL Server already installed, storage allocated, and customized number of CPUs and memory in minutes instead of waiting around for your IT department to go through its normal provisioning cycle. If it sounds like I’m advocating completely circumventing your company’s policies and going rogue, I’m not. More detailed thoughts on this offering below as well.
WASD: Hey DBAs, It’s Not For Us!
Ever since Azure came out and rolled out the various SQL Server offerings I’ve been trying to wrap my head around this particular facet of the solutions offering. Ever since it came out (and was still called Azure SQL Databases), all I could do was focus on its limitations what it couldn’t do.
Some of those limitations have changed/increased over time such as database sizes. At first the largest database you could create was 50GB. Now you can create databases up to 150GB in size and you can shard you data out so you can get beyond that 150GB size barrier if you need to. However sharding data like that requires different coding techniques that your development team likely isn’t doing today.
Additionally there are other restrictions like requiring a clustered index on every table, which isn’t necessarily a bad thing. Since this database is in the cloud another issue developers need to code for is network connectivity. Network connectivity can (and will) drop on occasion so it’s necessary to code retry logic for connectivity in your application. Finally if you write a bad query that causes the transaction log to “blow out”, your connection gets throttled for a time. For me, as a DBA, all these restrictions why would anyone in their right mind want to use this?! And therein lies the crux of the issue: I’m a DBA…this isn’t a solution meant for me.
It wasn’t until having some conversations with folks at this year’s PASS Summit that the whole use case, and my understanding, of WASD really clicked into place. After attending Connor Cunninham’s (Blog) pre-con on Azure Data Platform, attending Grant Fritchey’s (@gfritchey | Blog), having conversations with Lara Rubbelke (@sqlgal | blog ) and Eli Weinstock-Herman (@Tarwn | blog ) amongst others I came to a realization about PaaS: It’s not meant for me, so I really shouldn’t be bothered by the fact that it can’t do X, Y, Z. Just because it has the SQL Server label on it, doesn’t automatically mean I, the DBA, need to own it! “But Jorge, in my shop if it says SQL on it I end up supporting it anyways!”. Well that’s okay, because with PaaS the administrative side of things are handled (for most part) by Microsoft. These tasks include backups, hosting/provisioning of servers and day to day administration of the infrastructure that supports the solution.
Long story short, this is a solution aimed at developers. They just want a relational data store to develop against without headache of waiting for someone to provision them an environment to do so, nothing more. Think this isn’t happening today with devs? Check out this Twitter conversation I had with Scott Hanselman (@shanselman | blog) recently:
@SQLChicken I use SQL as the back end all the time. But I’ve never had to use the management tools. I use EF Code first and migrations.
— Scott Hanselman (@shanselman) October 31, 2013
Scott not only uses WASD for development purposes, he wasn’t even sure what I was talking about when I asked him if he used WASD, that’s about how transparent they’ve made it for developers. The conversation was based around my discovery that not all administrative pieces of WASD had been ported over to HTML5 yet from Silverlight. He didn’t know because as a developer that’s something he never had to deal with or care about. In the words of Martha Stewart “that’s a good thing”.
OH NOES, CLOUD HAZ TAKEN MAH JOBZ!
Don’t worry, dear reader (totally ripped that from @sqlballs), not all is lost and no your job as a DBA isn’t going anywhere. If anything, your job stays intact and it’s going to evolve. Evolution is good, ask Charles Xavier. If anything the rise of cloud technology not only cements your role in the company but will actually upgrade you a bit as now you evolve into more of an architect role. Still like staying close to the technology? It’s still there and not going anywhere. We still have our on premise options. Not only that, we have pretty cool cloud options that are made to work hand-in-hand with our on premise environments. Which brings me to my favorite option…
Windows Azure Virtual Machines FTW
I love virtual machines. I especially love Windows Azure Virtual Machines (WAVM). Not only do they keep my job intact, in that you’re still doing everything you, as a DBA, do today in administering and using full SQL Server in an operating system but it also makes my job a hell of a lot easier in some respects.
One of the coolest things about WAVM is that Microsoft provides you with a nice selection of pre-built template virtual machines for you to choose from. SQL Server 2008 R2 + SP1 on Windows Server 2008 R2 + SP1, it’s there. SQL Server 2014 CTP 2 on Windows Server 2012 R2. Only a few clicks away. Not only that, you can fully customize these virtual machines’ resources such as number of CPUs, how much memory allocated to it and disk space. Disk space should probably be the best news anyone who has had to beg a SAN admin for disk space has heard. You also get the benefit of applying high availability options as well as backup protection options in a few clicks.
So if it’s just a virtual machine, just like you have today in your datacenter, what’s the big deal? Well there’s a few things. I just mentioned that self-service ability. Unless your enterprise has invested in a full blown Private Cloud solution then you probably don’t have anything like that available to you. Today you’re putting in a request, or opening a ticket, outlining what you want and writing up justifications for it. Then you get to wait for the network team, SAN team, sysadmins and DBAs to all do their part in setting up the machine then finally turning it over to you.
Fantastic…What’s The Catch?
I know, I’m painting a rosy, unicorn-laden picture. Well fact is there are certainly some things about WAVM you need to consider. First, it’s not connected to your network. Not a problem…maybe. There are ways to have your network extended out to the cloud through Windows Azure Virtual Network. If you were to extend your network out to Azure, you can also stand up a domain controller out there so any virtual machines you spin up out there look and feel just like any other server on your corporate network.
Okay then what about reliability? Each component of Azure offers its own SLA, which you can see here. As of time of this article the stated SLA for the virtual network is 99.9% and other cloud services (virtual machines with availability sets) at least 99.95%. Do you get that sort of SLA at work today? You might. Well compare that level of reliability and service compared to what you’d pay using Azure versus what your company paid to set up the infrastructure and staff to offer the current level of reliability.
What’s security like? Well I’ll be blogging and presenting more on Azure security this coming year but for purposes of this post I’ll condense it. It’s as good as you make it. Just like your current environment. Again, because we’re talking virtual machines it’s all the same as what you’re doing today inside your data center. In fact, I would bet that most of you currently work in places where your company’s datacenter is actually located outside your company and hosted by someone else (e.g. colo sites). In these massive datacenters you have racks and racks of servers and equipment that are bought and paid for by customers of the host but are physically located side by side. Azure is also a co-located situation but you have a little more dynamic control over where components of your solution are located.
Okay so we have our virtual machines “hanging out” in public for anyone to get to then? Not exactly. The virtual networks you configure, by default, essentially have their tin foil hats on and are not open to the world. Portions that you do open up you have to explicitly grant access through the firewalls in place. How about that data in storage? Again, how much do you secure it today? If you leave it unencrypted, at rest, in your data center today then you’re potentially exposing it to theft as well so technically this risk exists in both worlds. In the end, with security, there comes a point where it’s simply a matter of trust. Trust Microsoft to secure their data centers. Trust yourself to do your job correctly and secure what needs to be secured. This last point brings me to my final epiphany about the cloud, thanks to Grant Fritchey/Tom LaRock (@SQLRockstar | blog ) for this one…
The Cloud Forces You to Do It Right
This goes for both PaaS (especially) and IaaS. One of the best things I heard at Summit this year was Grant ranting on how WASD forces you to code correctly. Write code that forces a log to start blowing out and it kills your session? Well write it correctly to avoid that. Network glitches can and will occur. Have you written retry connection logic into your application? I guarantee you will now.
Like it or not we’re seeing a fundamental shift in how computing solutions are offered and used. We’re seeing a world of consumerization of IT (I hate myself for typing that marketing buzz phrase) where end users expect the freedom to pick and choose their solutions and don’t want to wait for the black hole that IT can be to respond to their needs. They will discover solutions like Azure, see how fast they can do stuff on their own, and potentially get themselves in a bind. Instead of coming off as the stodgy group that doesn’t want to help, embrace these solutions yourself and offer them up with guidance. In the end it’ll be a win-win for everyone.
How do you feel about this whole thing? If you didn’t write your own post this month I’d love to hear your thoughts in comments below.
7 replies on “T-SQL Tuesday: Head in the Clouds”
If anything, WASD needs a DBA more than ever. Or at least a good SQL Dev. The performance of a single database in WASD is not particularly good, so designing your schema and architecture correctly, as well as tuning queries becomes all-important. I would certainly try to avoid EF when using WASD as the back-end. Properly thought out queries and access paths are too important on the platform.
Also, I don’t agree with the “forces you to do it right” concept. I appreciate the sentiment, but in truth it’s not forcing you to do it right, it’s forcing you to think differently. It’s a different platform, with different architectural concerns, and you have to make allowances for that in the way you design your applications. Sure, there are more restrictions and fewer places to hide so poorly written code will be more exposed in Azure, but it’s not quite the same thing.
I do agree that the VMs are excellent. But beware of the IO performance! Don’t be fooled by the specs of the large and “A” sized VMs, as even with multiple disks the IO performance is not the best. “Variable” is a word I’d use, loosely.
All-in-all though, as time passes and Azure improves, I think the flexibility of the cloud offerings is truly excellent and adds a worthwhile dimension for consideration when designing systems.
Michael,
You raise some good points. First off I’ll have to disagree somewhat with WASD needs a DBA, at least in the traditional sense. You’re absolutely right that tuning queries and good design are crucial elements in order to make it work, that’s where the “forces you to do it right” comes in. It’s essentially putting onus on developer to learn more about WHAT they’re doing, which may employ the help of your friendly neighborhood DBA.
With VMs yes the initial iOPS are limited but there are methods of scaling that out (e.g. adding disks for more throughput, adjusting cache options). Again, this new world has new skillsets and we have to architect things a little differently.
Like you mentioned, the solutions will continue to mature and improve over time. I’ve personally been pretty impressed with speed that they continue to roll out new features and adjusting services/offerings based on feedback. It’s an exciting new world. Thank you so much for your feedback!
You raise some good points. First off I’ll have to disagree somewhat with WASD needs a DBA, at least in the traditional sense. You’re absolutely right that tuning queries and good design are crucial elements in order to make it work, that’s where the “forces you to do it right” comes in. It’s essentially putting onus on developer to learn more about WHAT they’re doing, which may employ the help of your friendly neighborhood DBA.
With VMs yes the initial iOPS are limited but there are methods of scaling that out (e.g. adding disks for more throughput, adjusting cache options). Again, this new world has new skillsets and we have to architect things a little differently.
Like you mentioned, the solutions will continue to mature and improve over time. I’ve personally been pretty impressed with speed that they continue to roll out new features and adjusting services/offerings based on feedback. It’s an exciting new world. Thank you so much for your feedback!
That’s essentially what I meant when referring to a DBA or good SQL Dev – design and tuning ability is important. Having said that, depending on the scale of your system, certain DBA skills are actually needed believe it or not. When you scale out and have a large number of shards, you have to keep careful control over configuration management – schema versions, source control, deployments etc. Each database is discrete, so it adds complexity (and powershell becomes your best friend). This also adds complexity to monitoring performance through DMVs. It’s certainly not the same as a traditional DBA role, that’s for sure.
It’s frustrating at times to see recognisable performance traits in the DMVs but have little or no control over the OS or infrastructure. I guess that’s why it’s thought of as a development-driven environment, as DBAs simply don’t like that level of abstraction, or lack of control.
Here’s a poser to leave you with – if no DBAs were involved with WASD, would it even exist? (Most devs I know would put everything in NoSQL, queues and cache…) 😉
My boss wants nothing to do with “clouds” and his data. Its not secure in the least.
No offense but I respectfully disagree. What exactly isn’t “secure” in your opinion? I could argue that Microsoft’s datacenters are probably more secure than your current datacenter.
No offense but I respectfully disagree. What exactly isn’t “secure” in your opinion? I could argue that Microsoft’s datacenters are probably more secure than your current datacenter.