SQL Server Blog

Dr. Cloud-Move or: How I Learned to Stop Worrying and Love the Cloud

With apologies to Mr. Kubrick for my title, I’ll give you a little honest backstory. I’ve been working with SQL Server for 17 years now. Mostly in the box, on-premises. I’ve done all sorts of things with SQL Server. Always heading back to DBA, performance tuning, high availability, etc. My career owes a lot to SQL Server on-premises. When the cloud started really having conversations 3-4, maybe 5, years ago I listened. But I sort of listened while mostly listening to all the other stuff I was keeping busy on. Fast forward a few years, I’ve done a lot “in the cloud” for sure. I’ve helped some smaller one offs migrate to Platform as a Service (PaaS) options from Amazon and Microsoft, I’ve helped “lift and ship” still more firms to the cloud in Infrastructure as a Service (IaaS) options from both. And I’ve done other things like tuning already existing cloud clients, or build hybrid solutions. Something’s changed in my head, heart and eyes the past few months. From the MVP Summit in November where I heard about a lot of interesting things about roadmaps and products, to watching the Microosft Connect(); conference in November or some fun client projects – including one client I was out at Microsoft with working on proofs of concept with some really neat Azure SQLDB projects. I’ve moved from liking, using and thinking the cloud makes sense to “loving” the cloud. Hence the play on the title…

Enough with the backstory. There is a slight point to this post. I’m using it to make a new category on this blog, Azure. I’m using it to introduce some posts I’m going to be working on based on what I’m learning in the cloud and some of the challenges I’m seeing solved there. I’m also going to stay focused on Microsoft’s cloud offerings with these posts to start. I’ve done a lot of playing with AWS and their offerings, but I want to stay focused a bit on the Azure stack.

I think they both have some great stories, but I’m really impressed with what the teams at Microsoft under the newer leadership there are doing and I see some stories developing there that I want to be a part of and want you to be a part of.

Things Change

This week I worked with three clients in cloud based projects. There is the client I’ve been out at Microsoft with working on taking their 1TB multi-tenant SaaS application from a single SQL Server database on-premises to Azure SQLDB. There is a client I am in the process of building an Availability Group in Windows Azure for so we can start migrating data there next week. And there is an ISV I’ve been talking to about some SQL Server changes and tuning in an IaaS deployment using SQL Server Availability Groups – looking at moving them to a PaaS solution eventually. This isn’t that big of a deal, like I said I’ve done services for clients already in or helping them migrate to AWS and Azure, but what is interesting is this week more of my hours were spent in the cloud than not.

And when you look to what is happening in the PaaS space especially. It’s pretty amazing. The Machine Learning algorithms, the AI, the automation – so much of what I do as a DBA is sort of changed out by PaaS – and it needs to be. Think of my client this week – let’s say they have about 1,000 tenants today in their application and are growing (the number is close) – we could have gone with many patterns – and in a forthcoming post I’ll talk about that – but we are going with the single tenant per DB approach in Azure SQLDB. That allows us to use Elastic Pools which allow them to scale based on some assumptions and learning around the knowledge that not all 1,000 tenants are going to be slammed at the same time. Instead of building a server with peak capacity always there, paying for it always just in case – we are pooling resources and spreading them. That’s a benefit for many reasons.

But imagine if we had to spread out to 1,000 databases today. That’s not that big of a a deal, I have many ISV and SaaS clients on-premises with this model and they scale. But that is a lot of DBs to maintain uptime for. A lot of DBs to make sure corruption (as I blogged about earlier this week) isn’t a problem for. It’s 1,000 databases each with potentially different table usage and access patterns to maintain indexes for. Well Azure SQL databases fix some of those challenges. Want HA? It’s already there with 3 redundant copies. Backups? Done for us. Want DR? You pay for it, but it’s a simple PowerShell script or Azure Portal option. DB-N needs some indexes that the rest don’t? Sure we could analyze all the data and review it and create indexes. Or we could turn automatic tuning on and allow index creates when the Database Query Store and the algorithms feel it is necessary. Sure we can review that data, but as the algorithms learn and grow and we build successes with it? We don’t need to. This one client? They use me a lot for just these things today. Done away with in large part.

And what about the pain of building queries across the sharded DBs? Done with Elastic Queries. The shards are mapped with a library they’ve created for us and made open source so we can tweak if necessary. What if I want to build a job to make a change on all of these database? Elastic Jobs. It’s already in there. And the innovation cycle is quick. And they listen to feedback. Heck – the migration process to bring a DB to Azure SQL database? Really easy, bumped into a potential roadblock and found some feedback I’d like to see in there to give option to skip errors – well the teams listen. Not because I am an MVP, but because they want feedback to make their products better. They are continuously integrating. We quickly just threw a PowerBI query out on some common queries and it worked. We had some basic reports in a few minutes – with more time we’ll have more complex reports, but it isn’t months or years to that point. Once this phase is done? We’ll be looking at Azure SQL Data Warehouse to take advantage of features there.

My client gets to focus on what they are great at. They get to maximize their time on innovating and moving forward. And I’ve not even mentioned what we can do with microservices on Service Fabric or Azure Web Apps for improvements or Azure Data Factory for loading data. These problems that have been bigger problems to solve are still hare problems to solve – but they are architectural choices with a lot of pre built options to drag into our solution like an artist choosing a color to put on a canvas. We’re still the artists – but we aren’t getting bogged down reinventing the wheel each time.

This is a brave new world. And in just a few years? The DBA role will be dramatically different because of these innovations. In 10-20 years? Unrecognizable perhaps. (Though I still find SQL Server 2005 quite often.. So not everyone will be there right away). And I’ve not even talked about  so much of the surface area available in Azure in this post. I’m scratching the surface.

So anyway. This is my quick post to just say “I’m an idiot.. I was sleeping about this stuff. I’m waking up now. And I’m excited. It reminds me of how I felt maybe 5 years into my career when I really started getting the basics of what I could do with performance tuning or architecture decisions on-premises and realized I had so much to learn and was going to have a fun career” I’m excited. I think you should be too.

Coming soon are some posts talking about all of these things. I’m going to start talking about elastic database pools, sharding, automatic tuning, the ease of deployment, the ease of migration, and other topics with the PaaS offering for SQL Server databases  – Azure SQL Database. I’ll start those posts next week along side those “Straight Up SQL Server Tips” posts I’m doing on different days.

Mike Walsh
Article by Mike Walsh
Mike loves mentoring clients on the right Systems or High Availability architectures because he enjoys those lightbulb moments and loves watching the right design and setup come together for a client. He started Straight Path in 2010 when he decided that after over a decade working with SQL Server in various roles, it was time to try and take his experience, passion, and knowledge to help clients of all shapes and sizes. Mike is a husband, father to four great children, and a Christian. He’s a volunteer Firefighter and EMT in his small town in New Hampshire, and when he isn’t playing with his family, solving SQL Server issues, or talking shop, it seems like he has plenty to do with his family running a small farm in NH raising Beef Cattle, Chickens, Pigs, Sheep, Goats, Honeybees and who knows what other animals have been added!

Subscribe for Updates

Name

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This