Cloud curious DBAs and Developers who want to see an example of building out a database environment.
You know about the cloud but you’re not there yet. Is it hard? Is it easy? How do you get started? Come to this session and see for yourself. We’ll start with nothing and end up with a deployed Azure SQL Database. We’ll even run a quick PowerBI report and enable Geo-Redundant Disaster Recovery with a couple clicks.
The goal is to take the mystery out, to show the capabilities and get you thinking about what going to the cloud could look like and what it can do for you and your company. I believe the future belongs to those who have this knowledge and know where to apply it.
This will be nearly PowerPoint free and we’ll log into my Azure Portal and build out an environment from scratch and learn as we go. We’ll migrate data from an “on-premises” database into our SQL DB and we’ll query it. You’ll leave with an understanding of the capabilities, some resource links outlining what we did and hopefully some curiosity to see what else is up there in the cloud as you start exploring with your own trial. Platform as a Service isn’t the answer to every problem, but after you see how simple it is to get started, maybe you’ll get some ideas of where it is.
Why I Want to Present This Session:
For 17 years I’ve been working with SQL Server – on premises. I’ve done some dabbling in the cloud lately but it just started accelerating. For a couple years I didn’t bother digging in and I was blown away how easy it was to get started. I just want to let folks see for themselves on a guided tour and let their minds start going.
Brent Ozar: In the next section at GroupBy, Mike is going to give us a guided tour of Azure SQL DB. Something I think probably more development money from Microsoft has been pumped into this lately than all other things, it’s all kinds of exciting things happening there, so take it away Mike.
Mike Walsh: Thanks, actually before I start I just want to – this is…
Brent Ozar: You want to take your clothes off?
Mike Walsh: This is just proof that Brent really does love the SQL community, so this shirt, does this look familiar to you?
Brent Ozar: It does, that’s our old…
Mike Walsh: This is from what? 2009? And this is actually one of your codes on it, so you know you’re a SQL DBA if…
Brent Ozar: If you snicker every time someone says the word access database. Kevin Klein and I did shirts for them way back when.
Mike Walsh: Yes, I remember that. I still have it, it’s a little dirty but I want to put it on just for you Brent. Just to kind of…
Brent Ozar: You probably only wear it when you’re working with the pigs and the sheep.
Mike Walsh: Nope, not this one. Not this one. So welcome everybody, this is – well actually, instead of playing the introduction slide we’ll go to each slide and talk through this. And Brent, by the way, if my WiFi gets weird, let me know. I have my LTE ready sometimes, my cable modem, I live in the middle of nowhere so it’s – my LTE signal can sometimes be better so I’ll switch it if I need to.
So again, this session title is – it’s a guided tour through Azure SQL DB, and it’s level 100. If you want to go someplace else because you’re already in Azure SQL DB, you’ve already deployed your SQL DB and you don’t really need that help, go ahead and leave. If you’ve not used it yet and you want to sort of see hey, what’s available, what is all this Azure stuff, what’s this Cloud stuff, what’s platform as a service, this is the session for you.
So it’s a gentle, and kind of interactive introduction to Azure SQL DB, really talking about V12, although I’ll talk about some of the things that I actually just talked about at Data Amp a couple weeks ago, or a week ago, I lose track of time. And it’s an hour presentation, although Brent gave me more time, so if we need to talk more we can and I tend to talk kind of fast, it’s a New England thing, but we’re going to start with minor death by Powerpoint.
I promised in my intro there wouldn’t be a lot, so there will be I think maybe six or seven slides, most are pictures, like Brent’s slideshows. We’re going to build a Azure SQL DB when we’re done with that though, we’ll kind of talk about the surface area, we’ll play in the console, we’re going to connect to that database we built and I already have some built just in case. We’ll build a few things, we’ll just kind of talk features.
You’ll basically just watch me go to the portal and we’ll just sort of – by going to the portal by building the database, by building the server and by using the options, we’ll sort of see what the options are, and hopefully there’ll be questions and answers, and Brent you can ask questions throughout and if I have answers, great, if not, I’ll stay in the Slack afterwards too.
Brent Ozar: Deal.
Mike Walsh: Or you can answer them. So who am I? I’m a dad to four kids, a husband to one wife, I’m a gentleman farmer. I’m not really a gentleman but that’s just what they call it. Basically means I have a real job and I farm on the side. It’s a 95, maybe 100 animal, I’ve sort of lost track. I’m a fire fighter in the small town I live in, EMT, I’m a database consultant entrepreneur, like I was telling Brent and Erik before, I have to be the consultant to be able to afford farming part time. So I am a full-time IT person, not a full-time farmer. I work with data, and by the way with the four kids, my wife’s actually gone – we home-school and my wife’s gone with one of my daughters to a play thing, so the younger kids – we may have one of those BBC interview moments, I hope not, but there won’t be anybody in here to save me so I’ll have to sort of do it on my own, but hopefully that doesn’t happen.
Brent Ozar: We had Tara’s kids in earlier, and it was all good.
Mike Walsh: Good, good. I think it’ll be fine, the youngest is six and he’s good so I’ve been working with data in some fashion for a little bit over 18 years now. I’ve been MVP for the past six, doesn’t really mean much, it means I speak and blog and do these kinds of things. Microsoft’s decided to you know, give me the knighthood of MVP and we’ll find out in July if I still am. I’ve been speaking and blogging since 2009, probably about when I got this shirt. Over at my blog, I have my company website, which is straightpathSQL.com, and again I founded Straight Path in 2011 as a SQL Consultancy. We do database engine work, we do a lot of Cloud enablement lately, a lot of tuning optimization, remote DBA services, and I’m on Twitter.
So this is what I used to be like with the Cloud: at first, I laughed at it, I thought it was kind of like, what’s the point, you know – did that come across Brent? It looks funny on my screen here.
Brent Ozar: No, it looks perfect.
Mike Walsh: Okay, so you know, if you remember, I forget what the size limit was, but what was the size limit when whatever the precursor Azure SQL SB was? Was it like 50 MB or…
Brent Ozar: No, I think it was 5GB or 10GB right, initially.
Mike Walsh: It was something small and it was kind of like, yes this is kind of silly, the performance wasn’t there and it was sort of jokey and even the servers and the story there, when the story first came out, it was sort of like the thing that the CIOs talked about when they went to golf. Are you on the Cloud? No, I’m not on the Cloud, are you? I don’t know, should I be? Well, CIO magazine said we should be. And people would ask you questions about it but it was more like this sort of, what is this thing, so it was just kind of like a joke.
Then I kind of got a little scared, and maybe scared is the wrong word, but Microsoft especially, and I’m a Microsoft data platform person primarily – Microsoft really – two, three years ago Brent, they really were gung ho on the Cloud, it seemed like they were sort of saying the Cloud is the only story. Remember that?
Brent Ozar: All in.
Mike Walsh: You have to be all in and if you don’t do the Cloud you’re not going to be anything to us, and finally though, couple years ago, maybe a year and a half ago, really around the sterling and the V12 release of Azure SQL DB, and really around [inaudible] time, Microsoft started realizing, you know what, we’ll take your money wherever you are. You can be in our Cloud, you can be in Amazon’s Cloud, you can be in Google’s Cloud, and we don’t care because we’re going to get license fees and we want you to sort of be where you need to be. So it sort of became this hybrid story and this what’s right for your organization story, and I’m kind of big on the Cloud.
I know you are Brent, we have a mutual client that you help and I help and they’re going along with the Cloud and it’s a good story right, because it makes sense. So I like it, again, not too many slides but here’s a bunch of over word uses, big data, huge, this is ping me, that’s in Slack, just kind of ironic here, synergy and the Cloud, so the Cloud is an overused word.
What is it? Well, this is not a topic about what the Cloud is but you know, yes, it is someone’s data center; in fact, there’s a bunch of funny shirts out there that say the Cloud is just someone else’s data center. It kind of it, but it’s not just putting a server into a colo, it’s not just going and calling up rack space and saying hey, I need a server and waiting a couple days to get it and dealing with all the ifs ends and thises and that’s – it’s the server that – it gives you the ability to host a virtual machine as infrastructure of servers or host a database or host an application in the Cloud that maybe you don’t necessarily want to invest in the infrastructure.
I think Brent, you know, you’re not part of the presentation but I’m going to put you on the spot, GroupBy and a lot of sort of the tools that you guys have built like Paste the Plan, you know, those are built in the Cloud, right? You don’t have a data center some place with a bunch of servers hosting things.
Brent Ozar: No, think about anybody who’s watching this webcast. If you were going to go start a business, do you want to go buy servers somewhere or build a data center? We can’t afford to do it as small business people.
Mike Walsh: Yes, and it’s true for even big companies. As you grow, software’s a service and providers who do that are just – it makes sense. Most services we buy, and in fact I’m sure it’s the same for your company as it is for mine, your accounting software, your time keeping software, you pay a monthly fee for these things. Maybe you pay it by year to save a little bit but you’re basically paying a monthly fee, and you scale when you need to. When you add three users you can scale, you don’t have to buy the final big product and say, oh no, we just got rid of five people, now we don’t need those licenses.
And there’s a lot of flavors to the Cloud – there’s Microsoft Azure, that’s what we’re really talking about here and really just SQL Azure. There’s Amazon Web Services, AWS, Google’s making a play there, Brent, you’ve been kind of a part of that and talking about that on your blog, that’s been fun to read. Then there’s infrastructure as a service and platform as a service. I say IaaS and PaaS but basically infrastructure as a service or IaaS, as where you say I want a VM, and this is the VM I want. We’ll talk about PaaS in the next slide and that’s really what this whole presentation is about.
IaaS, you know, if you think about it, I think back to seven or eight years ago, the last time I had a full-time job, and whenever we needed a new server, even in the virtual environment, we had to fill out this info path form. This Microsoft info path form in the sure point site and it asked all these questions like how many HPAs or how many connections do you need? What’s your backup retention? All these questions, you’d ask the question, the CIO would send you an email back yelling at you because something was wrong, you do back and forth, about a week, week and a half after you put the form in, the form is finally in the process, it’s approved, then they requisition the hardware if they don’t have it, if it’s a VM they’ll say yes, this is in our list.
Maybe for a VM, you know, a week and a half to three weeks you’ll have it. For physical, I don’t know, I’ve seen five weeks you’ll finally have it. In the Cloud, for IaaS, you go to the Amazon portal or the Azure portal like we’ll do today, you pick the option you want, I want a D6 server or whatever, and you tell it what you want for storage, I want local redundant or geographically redundant because I care about backups. You click a button and you know, what’s your experience Brent? Five, maybe 15 minutes later for VM you’ll have it?
Brent Ozar: Yes, exactly. Faster than I could fill out an info path form, that’s for sure.
Mike Walsh: Yes, that or find it, or browse through it, and those are just buzz words so the bingo there is if you’re playing buzzword bingo. So, why, right? Brent and I just talked about this right, but you know these are conversations that I’ve actually been a part of or had part alright.
Think back to 2011, you know, when kind of Google apps for business, you can be for them, but when Google apps for business in O365 really started sort of getting popular, this was a common conversation. Why do we have to host for our email server? We’re not an email company, we’re a company that does whatever, we just want email that works and is as or more reliable – we don’t want to mess with CASs and exchange DAGs and this and that, we just want email. Like why – and I firmly believe this. Why is anybody managing their own email?
There’s probably reasons and I’m sure people say so in Slack, I’m sure there are reasons but in general, for most sized companies, why do it? Even Microsoft you know, their internal email is O365, and I mean, they’re hosted themselves but they’re still – they don’t have their IT department doing it, they have their O365 department doing it. And you know, then few years later – really, few years ago, this was a common conversation. We’re a company who makes widgets, we’re not rack space, we don’t have a fancy colo, we can’t do DR, why don’t we – we do we have to buy servers every three to five years? Why do we sort of have to buy our servers now, hope we spec them right and live with the consequences for three to five years and then maybe buy more?
Why not just do VMs in Amazon or in Azure? And now, this conversation – this bottom one happens a lot. I just had this with the software and service vendors and really in that space it’s big. We’re a company who sells services to other businesses, maybe it’s invoice payment, maybe it’s tax accounting software, whatever. We’re a company who provides services, we charge per month to our customers, we have great developers, we have great engineers, we don’t really have a DBA team, we don’t have a solid data team, we’ve gotten by but every once in a while we have weird outage we don’t understand, why do we have to have databases that we run CHECKDBs on? Why do we have to manage our backups?
Now, I think you still do have to manage your backups, we’ll talk about that but why do we have to build an availability group or what happens if our software – one of my customers I’m moving to the Cloud writes software for CPAs and tax firms, so every April, every June and for people like me who always file extensions every October, they get really, really busy. Do you do that too, Brent?
Brent Ozar: Yes, sadly yes.
Mike Walsh: My taxes are always done right at October 15th, basically whatever that date is, 18th maybe this year. But you know, what happens in tax season, right? So do you have to build a server and build a database infrastructure that can handle the people and if you’re in your own colo, if you’re in your own data center, you kind of do – you can do things and sort of programmatically – you can make your own Cloud, a virtual private Cloud and programmatically scale your VMs up. But in general, most customers and most of you watching are probably building for the peak, and you use a peak three times a year and you’re okay with it because that’s the only choice you have, but you have more choices, whether it’s IaaS or PaaS, we can build and we can scale up as needed. We can even build some Cloud formation or build some automation to serve watch performance and when performance gets towards the level, we can increase the resources with minimal downtime. So it’s just a new way of dealing with this and you know, you’re not off the hook for owning things being responsible, you still have to be a DBA or an architect, you just have to do a little bit less practical things that you do today or day-to-day things, and we’ll talk about that.
So PaaS, platform as a service, we’re not talking about IaaS, so we already did an overview of that on the last slides. Basically, you worry about the data, which is what we should be worrying about mostly, the data is what matters most to us. I’m a database guy, Brent you’re a database person, we care about the data the most. The applications are important, but they’re just ways to get the data. Those developers – tell Richie, he’s not that important, it’s the data that’s important.
You worry about the applications, you worry about the architecture, the access, you worry about the budget, what is your budget, Microsoft or – the last time I delivered this I actually delivered it at a Microsoft event so I made a joke on my slides, I said those other guys – but I have no problem, I have plenty of clients in Amazon as well. I’m seeing Microsoft or Amazon or Google, they worry about the redundancy, the availability, even some of those pesky DBA duties you know, ray groups, spindles, VMs, patching cycles, dual redundancy, hardware upgrades, firmware, nit configurations, updates, upgrades, gluten allergies and world peace. Although actually, Microsoft doesn’t directly worry about that but the Bill and Melinda Gates foundation have divisions for that so they’re taking care of it.
But what I mean though, you’re not off the hook. If I asked anybody in the Slack to just type out about an outage that you have in mind from somebody’s Cloud servers, you’ll probably say the S3 one that just happened, you might say that distributor now service type that happens, so downtime still happens, things still go away.
So you’re not off the hook for worrying about these things, but you’re off the hook for some of the ways you can manage them, and – let me say this, the people who – the S3 outage, people who had storage in Amazon’s east region and west region for S3, had no problems. If they had some process to kind of keep that in sync and have the ability to failover, because it was a single region failure. Some people said Amazon’s got it and they didn’t look into all the details and figured it was good, figured you know what, we’re going to go ahead and deal with the SLA they promised us. Some people said I want to add some redundancies and they built some redundancies. So you need to have that mindset, you know, what I call the paranoid DBA control freak mindset. So in the ambulance – I’m on the volunteer ambulance and we have a lot of trainings and when there’s EMS trainings, a lot of times we’ll role play things. We’ll talk about hey, you get toned or dispatched for this call, 62 year old woman with abdominal pains, and you get there and you see these medicines and somebody will throw curveballs and people just while they’re talking. We need throwing curveballs in until we finally – we picture here’s all the things that could happen, here’s what we’re going to do. You have to have that mindset as a DBA or as an architect to sort of ask what can go wrong and get ready to deal with it and then take care of it.
But some things, like you know, the backups really do work. There’s long-term backup retention, although, like many things, it will talk about today, those are in public preview, so we’ll talk about that too when we get into the portal. But a lot of those things, like the CHECKDBs, even some of the index tuning will talk about there’s automation there. Is it perfect? Can that system tune an index as well as I can or as well as Brent can or Erik? Probably not, but how many databases are there, how much is on your plate of things to do and are you going to tune them?
I can’t tell you how many clients I go to, Brent, I’m sure it’s the same story with you, and all you have to do is run an update stats and add a few indexes and they want to worship you like you’re C3PO and Ewoks. I hate mentioning the Ewoks obviously because it’s a dark error of Star Wars, but like the Ewoks, you’re like who is this mythical person who saved the day and all you do is add a couple of indexes, because you just don’t have the time, and it really does work. And by the way, that feature is part of SQL Server 2017, so even if you don’t have PaaS, some of the PaaS features and some of the PaaS automation is coming.
The recovery advisor you see in SQL Server, where you can pick a point in time and recover from that pretty little GUI, and pick your time, that’s a Cloud kind of thing as well and it’s in the product. So a lot of those things we do are moving about.
So let’s just explore, let’s see if I can get into the console here, and see if it works and then we’ll come back to the slides. If we don’t come back to the slide, I’ll make sure we get this. Just a couple of resources, actually we’ll talk about it now. Docs.microsoft.com, it’s basically – it’s Microsoft’s documentation site, you’ll see they actually have some pretty decent documentation about a lot of this.
As of last week, Tim Radney actually published a new plural site course, which I watched. If you haven’t noticed, I talk kind of fast, Tim’s from the south, it’s like about two and a half hours material, but if you watch it like 1.6, 1.7x, I can still follow along. I got done in about an hour and half, and it’s a pretty good class actually, really good class, I recommend that. And a lot of you, if you have MSDN or various offers, you probably have a plural site subscription already. Andy Mallon has a blog post out where he talks about what a DTU is and he kind of maps it out, it’s not perfect but it’s good.
We’ll talk about that and reference that, and next week on my blog, next Tuesday, I’m going to have a post and sort of start a series, starting with these building blocks and kind of building and inviting you to join along. I actually want to create a Facebook group as well, and have folks join that and if you’ve not played with Azure SQL DB, jump in, start playing with it and you know, we can kind of ask each other questions as you’re learning, as you’re growing in the product.
It’s not for everybody, we’re not going to talk about all the unicorns and the bitter hard reality things here, I want to try and keep the slideshow piece down, I really cut my slide deck up but in some of my posts I’ll talk about that. You can’t just take the AdventureWorks database and migrate it to Azure SQL DB. There’s a lot of features that are not supported there that won’t come out, so there’s gotcha’s. This isn’t the answer to everything, we shouldn’t – I shouldn’t just go work on a farm full time and find some other way to pay my mortgage. Azure SQL DB is not taking over all the jobs, but for a lot of people, this has a good application, so we’ll kind of go into the portal and talk about that. I’m not sure how that looks Brent because I can’t see…
Brent Ozar: Perfect, no, you’re totally good. One of the things I like about presenting online is you don’t have to worry about zoom it.
Mike Walsh: No zoom, I saw Steve zooming in so I wasn’t sure if that was because…
Brent Ozar: He’s a professional.
Mike Walsh: Yes, I’m not. I mean, I can if you want.
Brent Ozar: No, you’re good.
Mike Walsh: I can even do control 4, I can do it. I’m going to leave now, mic drop. It’s hard to do a mic drop when you have a headset on. Headset drop, I’ll do that at the end. So if you’re taking this and you’ve been in the Azure portal, well, this next little bit maybe a little bit a review for you. But basically, this is the Azure portal, so I’m logged in, I’m using my Mike@straightpathSQL account, so I have a bunch of other accounts but this is one I can use for demos and dumb stuff like this. This isn’t dumb, but you know, demo stuff, non-production things like this.
What you see, this is the new portal, this is the arm portal and you know, I don’t necessarily love the way it’s built and you’ll see in a second what I mean as the windows keep showing in. In fact, I actually love – I love presenting this way because when I do this presentation on a screen behind me, the resolution always stinks and you have to – it just looks ugly, but here I have my full monitor so it shouldn’t be horrible, but we’ll close things and move things around. You have this little Azure service health that shows you all the data centers and they’re healthy. I don’t know if I’ve ever seen non – I don’t know if this was actually like – I think it’s just an image. I’m not too sure, but I’ve never seen anything other than these green check marks across the board, so either it’s – they’re following up with the 99.99% guarantee or I’ve just not on it enough to find the red regions, but everybody’s good, it’s all green. And there’s a bunch of options on the left, I’m not going to talk about all – we can see our billing here, if we click on billing you’ll see what I mean about the portal right – so it opens up and it’ll show us what my billing is… I have free credits too, but I was playing with elastic pools for a client and using my portal, so last month wasn’t bad. Actually, I had one – I don’t know if this ever happened to you Brent, but I had a really expensive Amazon bill once when was I doing some AWS test a year ago.
Brent Ozar: Oh yes, when you leave it [crosstalk] by accident.
Mike Walsh: Yes, do you still have that little button to use to turn your Amazon off and on?
Brent Ozar: I do, it’s right by my light switch as I come and leave the office.
Mike Walsh: Nice, very nice. Yes, so that’s important, to make sure when you’re doing demos and test to kind of turn it down. If you have MSDN, there’s a good change that you might have some credits here, if you have a Microsoft Support contract you might be able to get some credits. If you’re just a human being and you’re unaffiliated and you just want to go to portal.azure.com, sometimes you can sign up for a trial. Microsoft are really trying that whole Philip Morris thing to get the kids trying it out first, and get people hooked and then hopefully there’s no billion lawsuits later, but that’s what they’re trying to do.
So you can get a trial, and I encourage you to get a trial, and you can even build some really small basic databases for $4.99 a month I think is the least expensive. So it’s good for you to learn it, and if nothing else, if you’re watching this – I just happened to look down at the Slack, Jeff Russ said they store the green check mark images on AWS S3. Funny story about that actually, I’m pretty sure that Brent, you can back me up on this, but I think Amazon never was able to sort of give the status of S3, because Amazon stored all of their S3 status images in their S3 bucket. So that bucket was down and nobody – we have no problems because we can’t tell you about it. It’s null, the status is null. That’s not a good sign, but anyway, I digress, I happened to look down at the Slack down here. That’s your job, not my job, but…
Brent Ozar: It’s good, it’s good. Multitasking is good.
Mike Walsh: Except for when you’re presenting. But basically, where was I going – so here on the left, we see a bunch of options right, I showed you billing, there’s a help and support where you can actually put tickets in and you get subscriptions and really anything you can do in Azure, you can do here.
There’s applications, there’s various servers you can use. I’m not trying to show you all that, we’re just talking about SQL databases. So here I can click on new and I can get a dropdown on what to create or I can just click on SQL databases. This will bring me to the SQL databases that I already have, or I can create a new one. And I have a few SQL databases already and we’ll kind of use these in our whole cooking show thing, so these are already built and we’ll go to one but we’re just going to create a SQL database. And again, this is sort of a guided tour so this may be a strange type of presentation. I’m just going to use the portal, so you’re going to watch me stare at my monitor while looking at me, and build some stuff here.
So couple things to kind of talk about first – I actually won’t talk about it here, we’ll talk about the next one. I’m just creating a database, we’ll call this GroupBy, that’s very creative of me. I’m using one of my subscriptions, I have two page to go subscriptions, this is the one that has the trial and I want to use that. I like you all but I don’t like you that much to spend all that money on you. There’s this concept in Azure and really Amazon has it too and I haven’t played in Google, I’m sure Google has it too, of resource groups. If you’re at a large organization and you have lots of different purposes of servers, you might want to create resource groups and there’s reasons for that, for availability and other things, but really just as an organizational thing, so I’m just going to use my existing resource group. And I can either create a blank database, I can create a database from a backup if it’s a compatible database, or I can sort of create this sample AdventureWorks Light, and it’s really light. If you’ve worked with AdventureWorks Light, it’s a pretty light database.
I’m just going to create a blank database because we’ll use my other server for that, and this confuses some people when I’ve talked to people. You have to have a server. Every Azure database has to live on a server. You don’t have to create an Azure VM, so you don’t need any IaaS, infrastructure as a service to support this PaaS database we’re creating. Microsoft behind the scenes is effectively creating a server. I really wish this were a term, container maybe, this is really just a database container and it happens to be a server but I can’t specify anything about the server. I can’t tell the server to be a DS12 with premium storage, I can’t do that.
When I select my database tier, it’s effectively sort of behind the scenes, I don’t know, I don’t have a magnifying glass and haven’t been back there to see, but I’m assuming whenever I change my price tier the database, which we’ll talk about in a second, the server does change behind the scenes but I’m not being charged for that server. I’m being charged for the database or databases on it. So we’re just going to use an existing server, there’s no reason to create a new – why not, we’ll create a new one. You said I have a whole hour and a half if I need it so we’ll just create a server.
Now, one thing that doesn’t work here – nobody’s created a server called Brent? I don’t fully understand how this works, but sometimes there are servers that you can’t use server names that are used, so maybe it’s just reserve names, like I can’t create MSDN server. I can’t create a server called test.
Brent Ozar: I always figured it was because they didn’t want people to think they were going to their own test or to an official Microsoft site, like I could understand why they would filter out Microsoft or…
Mike Walsh: Yes, but I can’t create a server called hockey, I can’t create one called pig fart, or maybe I can – I can.
Brent Ozar: Well, but not hockey, that’s kind of surprising.
Mike Walsh: And I’ve tried different names and in fact I’ve been in a class, I’ve given this presentation a lot lately, and I’ve just tried creating servers with somebody in the class’ first name and it’s you know, it’s been taken. Brent, you can no longer create a server. If you want we can talk later about how much that server is worth to you.
Well, we’ll call this GroupBy, I’ll be gentle, I don’t want to take it from you.
Brent Ozar: You know that’s what the audience is doing. Somebody’s jumping into their Azure portal account and going, I’m going to claim Brent Ozar.
Mike Walsh: I used to work with Brian Moran, he has this problem where all he does is buy domains so he could just sit in Azure all day building $4.99 a month databases.
Brent Ozar: So he has the names.
Mike Walsh: That’s right. You can do that if you want, so go ahead and make a database if you want it, and if you guys are following along, please do, and if you’re following along, you’ll let me know. That’s right, Jeff Russ says hockey is all pucked up, so that’s nice. Let’s give it a name and a password. It’s so picky, like I just clicked in the field so they can’t be empty, I know, that’s why I tabbed into it, leave me alone.
Brent Ozar: User interface is a little…
Mike Walsh: Yes, I still like this better than the old portal, but it’s not great. Like I really hate how these windows keep – yes, it’s weird. But we’ll create it, we don’t create it in Korea, although we could, but we want to do it here in the US. We’ll do it in East US too, why not. And then, allow is your x service server so all the servers have firewall rules built into them, but by default, if this box stays checked, we can allow any other services that are already in Azure to talk to the server, so that just allows PowerBI or Azure SQL DW or Azure – any of the tools, DataFactory, that are already hosts in Azure to talk to it, so we don’t actually have to enable the firewall rules.
It sort of trusts people in Azure, so if you want to be a hacker, just do it from Azure. So we’re going to create this, so basically we’re creating a server, collation we don’t need to change. Two things to talk about here, we’ll talk about pricing and we’ll talk about scale. So pricing, there’s this cost called the DTU, it’s the database transaction unit and I gave you a link to go to and it’s just post here.
It’s Andy Mallon’s post, he does a pretty good job, so I love Andy, Andy’s a great guy who does a lot of great things to the community, and this was kind of cool because I always wondered what does it look like, and is this science? Not really, because he’s kind of doing guess work because he did the best he can, because we don’t have access to the source code. But he basically took this thing, the DTU calculator, which you can basically create a perfmon counter, grab these counters, so it’s a combination of processor time, disk reads a second, disk writes a second, so this is CPU utilization. This is your IOPS, and then your log bytes flush per second, so how busy your log is today. So you can run these counters on your existing database or databases and sort of give that number up to this tool, answer some questions and it will kind of spit out what DTU you should have. Remember, this is – the tool, I speak in terms of a database, but a lot of these counters, unless you have your logs, your disks spread out per database and you sort of have CPU – you really for most of you, this is talking about all your databases on your server so you know, keep that in mind.
So your DTUs may be higher than you need, and depending on that we can talk about it lasting but basically you go to this DTU calculator at the Azure website dot net and this will be on my blog post as well, and it will give you the DTUs. And Andy went through and took some fake performance data from just CPU, just reads, and he combined them all and kind of figured out what the DTU needs are, so you can kind of see roughly what he found out based on a calculator. If you have one core fully utilized, so 100% utilized, that’s 100 DTUs. If you have 14 or 16 cores all 100% utilized, that’s 4000 DTUs. That’s premium P15 server, and what does that mean? Well, what’re all the numbers?
Two things to keep in mind, you should use a calculator, I really think you should. I also think the log flushes, I don’t 100% know this and Brent, maybe you know more about that but my guesses is because behind the scenes we have – we’re automatically getting availability, we have three replicas and I’m assuming it’s an AG like technology and we care about log writes, we care about log flushes, how much we’re sending across the network, so I’m sure that’s what that part of the calculation has to do with, just a guess.
Brent Ozar: Yes, that’s my guess too.
Mike Walsh: So it’s a combination of your CPU, how much computer you’re using on the actual server you’re on, so this will tell – probably tells Microsoft how many people can live on this server, what kind of IOPS you need, so should you be on premium or standard service tier, which will probably determine what sort of storage you’re on, and then when you have availability how busy is it going to be. So use the calculator to kind of figure it out, but it’s also – it’s kind of like this, you know, I haven’t been to the farm, no pigs anywhere. It’s a bit like that, it’s guesswork right?
You don’t know for sure how close that’s going to be. It’s actually the calculator’s not too bad, but I tend to go a little bit lower than what the calculator would say and I’ll watch it because as we’ll show in a little bit, you can actually change the class of your server fairly easily. There’s a quick downtime but it takes like what Brent, five minutes to change your class, not even? 30 seconds?
Brent Ozar: Sounds about right.
Mike Walsh: So we’ll kind of walk through that once I can find my portal. So as we kind of look at pricing, there’s four tiers – really there’s three tiers plus there’s the strange one. We’ll talk about that one in a second. This one is relatively new, or newer. There’s a basic tier, which gives us a maximum of five DTUs and if you go to that docs website, which I’ll have in my link, you’ll actually see there’s more limits as far as max database size, so the max database size in basic is 2GB. There’s also – I think the max in-memory storage, there’s number of days, days retained, I think I want to say it’s seven in the basic and it’s 35 – they’ve changed a couple times. 35 in standard and premium, but the document I’ll give is like a six rate page document that will show you that. Also number of concurrent database, concurrent connections and the number of users, those things matter by this as well.
But the pricing ranges anywhere from $4.99 a month, which is for a DTU of five DTUs, any size database in basic, so you’re paying the same for 100MB versus 2GB, all the way up to premium and premium starts with 125 DTUs and goes up to the max, which is 4000 DTUs today. And as we see here, we can have anywhere from 100MB up to 4TB of database, so this a difference from when it first came out. People still believe, I can only have a 5GB database, well no, you can have a 4TB database. If you don’t mind spending $16000 a month and that sounds like a lot but I talk a lot to clients about Clouded ops right, so a DTU of 4000, a P15 with 4TB is probably an edge case.
The Clouded option store is probably some place in this 500 to 1750 DTU range, maybe they’re on standard and they’re fine, it really depends on what you’re doing. People sometimes will say well my hosting only costs this much per month, yes, but how much are you paying to your SQL Enterprise license just every three to five years, how much is your software insurance, how much is your full DBA team, how much is your new SAN every five years – all these things you have to take and sort of do the amortization and divide it and say alright, well that’s my true monthly cost. Your monthly cost is not just your rack space power and lighting bill. If it is, then you better not ever move from that arrangement you have because amazing. You’re going to be paying $2000 a month for whatever you want is what people think in that case.
That was premium RS – premium RS, and I’m not going to click apply here because that would be very expensive hour, but premium RS is it gives you up to the 1000 DTUs and it gives us good storage but the is only 930 because we have a limited availability guarantee. So instead of being able to say we have 99.99% uptime guarantee and instead of sort of being that synchronous style failover, we might lose some data here, we might be at asynchronous failover, so I wouldn’t do this for your production CRM system that you’re designing and selling or for that app we talked about earlier for the animal sharing app. Sounds strange now, out of context, but…
Brent Ozar: We’re going to be rich, man.
Mike Walsh: It’s the Uber – we were talking about making an app before the start of – the Uber of the pet industry, if you don’t want to have your own pets but you want to be able to have a pet every once in a while. But anyway, I wouldn’t use it for your import or mission critical service, but for testing, for maybe doing the data load, this is a fine place to be, and you can swap between these. So we’re going to be – we’ll do this. Again, I like you guys so I’ll spend $15 a month, divided by the hour I’m talking to you is not going to hurt you that bad.
So I pick my service, I’ve named my database, I’ve built the server called Brent Ozar GroupBy, I’ve put in east – hopefully, nobody’s created a server yet while I’ve been talking. That would be interesting if somebody did because then we’d see what happens with that conflict. While that’s running, are there any pending questions or anything Brent, or comments?
Brent Ozar: Yes, there’s a lot. So let’s see, Alex says, “What’s like 4000 DTUs compared to my on-premises SQL Server? Is there some kind of way that I can tell how much workload my on-premises SQL Server has, how many DTUs I’m going to need?”
Mike Walsh: Yes, so that would be go to the Azure, or DTU calculator, Azure websites.net and once you’re here, it’s going to give you some instructions. You’re going to run the stored utility or script or you can get it from PowerShell really, or a perfmon yourself, but you can run this script to create it. It’s going to watch your process time, your disk reads, your disk writes, so that’s your IOPS, going to watch your log bytes flush, and it will watch over a period of time, then you come to this website and you basically tell the server how many cores you have, you upload your file and then you click calculate and it will tell you roughly how many DTUs you should have.
And if you go to that Andy Mallon website, where he blogged for SQL Sentry, he actually kind of breaks down what that means and sort of where you are, so if you’re keeping 14 to 16 cores, massively busy and you’re doing 24,000 to 30,000 IOPS, or reads, that’s just reads, and you’re doing 24,000 to 30,000 – so if you’re doing 60,000 IOPS, and you’re keeping 16 cores busy, number one, you should probably call somebody to tune your workload, but if you’re doing that and you’re doing 45 to 75MB of log flushes per second, if you do all that you might get this. Based on your database utilization, your SQL Server is out of range. At this time there’s not a service tier level that will cover your utilization. So you know, if you’re doing 60,000 IOPS and you’re doing 80% of 16 cores, Azure SQL DB may not be the place for you. But if you come and look here, and come and watch the breakdown you’ll see what it is, so I’m not sure if that answers the question but that’s kind of where I would look.
Brent Ozar: Couple more good ones, Alex says, “On that 4TB database max, or really any of them, is that compressed or can I compress my data? Like with table compression or…”
Mike Walsh: Yes, I don’t believe you can. So I think that’s the raw size and I don’t think compression is a feature in Azure SQL. I need to look at that. Brent, do you know if it is, off top of your head?
Brent Ozar: I didn’t either, no. I was like, I’ve never thought of that, and generally – so they got you with that, that you would need to just think of is there’s a CPU offset with that, so just make sure that if you have queries that are ugly enough that you’re worried about compressing data to fit it into 4TB, you probably have CPU issues that you’re going to need to worry about as well.
Mike Walsh: That’s a great point and actually, if we look real quick to the pricing calculator here – I have to configure a couple things, let’s just say I want to see calculator, I’m not really going to create it, now I can do this – if you look at the calculator and watch this, so if we go to premium, and we go to 4000 DTUs and we wait, we’re paying $16000 for 1TB. We’re paying $16000 a month for 4TB. We’re paying $16000 a month for 250 GB. We’re paying $16000 a month for 500MB.
So what I’m showing you here is all these tiers today, and Microsoft did talk about maybe sort of separating these a bit and I think in the future I hope they will, a lot of people complain about that to say what if I want to have high performance but only 500MB, it’s silly to pay $16000 a month, like most people would not be economical to go here for that. But you need the performance for a small database. Really, you’re paying for compute power, you’re paying for networking and really that’s the most expensive resource in the Cloud. The storage is – they found the magic way to make storage relatively cheap, and expensive.
Brent Ozar: Dana says, “Compression works, we use it on our Azure SQL DBs” so that’s cool.
Mike Walsh: Perfect, thanks Dana. Appreciate it.
Brent Ozar: You want to keep going with a few more or keep going?
Mike Walsh: Yes, few more, we have time.
Brent Ozar: There’s – Astaway says, “The challenge in Azure for me is that it changes too frequently and it’s hard for me to predict what I should do next and when.”
Mike Walsh: Yes, so I hear that but it does change frequently but typically when it changes there’s some new feature being added. If you built an Azure SQL DB a year ago, two years ago, you would still be okay. Like they’re not taking features away. Now, a lot of features as well we’ll go to see them and they’ll just walk through a server we just built are on public preview. Public preview, I tell my clients, this is – past performance doesn’t mean future result so don’t sue me if – don’t take me as gospel truth here. But if it’s in public preview, I tell my clients it’s probably a safe bet that it’s going to become a production server, so they’re kind of giving themselves a little bit of a leeway on availability and service, so that’s what I would do.
Brent Ozar: Oh man, so many good questions here. A couple of them are really super specific and so I’m going to hold those for later. GLBY does say, “The Azure DTU calculator if I’ve got something other than English, if I’ve got like Spanish language on my OS, it doesn’t seem to work.” I would submit something at connect. Connect.microsoft.com or call support, because it should totally work in other languages. I bet it’s something we’re just missing there.
Mike Walsh: It’s a buggy thing. Or just kind of come here and you know, go to the help and support and put a ticket in. My [inaudible] that’s fine, I don’t want to build a 4TB server. It will be discarded, okay, yes. And another thing, once you build a server with a terabyte, you can’t go back. So you can change your class and change your size on any kind of Azure SQL DB you want; once you go to a terabyte or bigger, you’re committed. You have to destroy your server and start over if you want to change it. They’re probably doing something with dedication is my guess, I don’t know what they’re doing but – there’s also some support forms but in here you can also put a support request as well, I want to show you so I would ask that question there and I think – did I see the answer too, I saw them say that Connor mentioned something.
Brent Ozar: Yes, just saying that Microsoft has said hey, scout’s honor, we’re not going to take anything away from you anymore, which god bless them.
Mike Walsh: It makes sense, and like we said, they’re adding features that – it’s different than it was three or four years ago, so it’s a solid and safe place to be, and by the way, while we’re talking that deployment succeeded, so I lost track of how long it took but let’s say it took five minutes. Let’s be crazy and say it took ten minutes. So ten minutes, we had a database and database server deployed for us. If we click here to our SQL DBs, we’ll see – we won’t see it. Why didn’t I see it?
Brent Ozar: Hit refresh.
Mike Walsh: I just loaded it so it should – that’s interesting. I’m sorry, I called it – the server name, I was having a fun time with the server name the – yes, so GroupBy is a database, there it is. So that’s created as a standard S0, so these databases are fully queryable and connectable, so I have this database here, which is Walsh Demo. On this database I have – it’s just an AdventureWorks copy, so in that create database dialog, I chose use AdventureWorks Light or LT.
Sheep database is a database I migrated and I’ll show you the migration steps here in a second. We probably won’t have – well actually if we’re talking through questions we might be able to watch the migration finish but we’ll save that for the end. What I’m going to do now is I’m going to open up – actually I’ll go to the server level first, so I’m going to go to – I’ll go to the database level, I can’t decide. We’ll go to the database and we’re just going to kind of go through the features here and just talk about some of these features. We’re not going to talk about all of them, we’re just going to talk about the highlights, just to kind of give you a sense for what’s available to you in Azure SQL DB.
Here again, first one that asked the question about the language, you couldn’t click diagnosis off problems, this will also bring you that same sort of dialog box for help, it will be context specific to your database, so if I click that, we’ll see the screen change. So it will give you some knowledge based articles about common problems and you can put a support request in, and it will tell you if you had a failed deployment, tell you if you have alerts, it’s kind of neat.
You can tag a server at this, if you’re doing asset tagging or if you want to try and tag different resources, be able to query them, you can do that. Activity log, that just shows us what we’ve done and built here. The overview page, really this is what we first came to. It sort of shows us where the resource group is, what the server is and how we’re doing on utilization of resource, and again, we’re not watching the CPU because this isn’t our server. We don’t care about the CPU; we care about the DTUs. We have a DTU limit, and we care about what we’re using. When you hit that limit, they don’t kick you out and send the police out, you just – you’ve plateaued. Your performance is what it’s going to be.
You got a kind of quick senses your application and in this case, I actually built your application already, but we’ll walk through it and show you how I did that, but that’s a cool feature because I do a lot of work for companies – here’s the deal. I do a lot of work for big companies where maybe their expenses and the size of database and their performance probably still maybe makes sense to be on premise. I do a lot of work for small software service companies with five, six, 10 people in them and you know, the developer is also the partner who’s also the funder and also the janitor.
That’s a very common startup kind of mode, and they want things like availability because they know they need it. They know if that have an availability problem, their users will complain and they’ll go to social media and they’ll be the new United, and they can’t handle that. So they need availability, and we can build availability groups for them, we can build various DR scenarios for them but if I want to build DR here, I can just click geo replication and wait a couple seconds, and I can say – I’ve already built it so I’m taking away the fun but let’s say I want to – I want to say – I’m literally not clicking anything.
Brent Ozar: Fun with web browsers.
Mike Walsh: No shift or enter keys on my 16 keyboards are enabled so I don’t know what the problem there is. But so what I’m looking at in this map is a read and I can pick as a target region to share availability. I can make this anywhere I want it, so my server in the east US – this server, and I already built a second in the west but let’s just say I want to build a second in West US too. Now, these secondaries are readable and see non-readable is available until Monday. It’s not available after April allegedly, we’ll see if that’s still available or not. But I’m going to make my secondary readable, I’m going to build a server so again, remember this is not really a server, this is not a VM, we’re building this as a container, so I have to build a container because I have no container in west US two for Azure SQL DB, so I’m just going to call this west US two GroupBy, and we’ll just do the same – perfect. So I’m going to select this and that’s going to build my server. And I’m not talking about elastic pools yet, we’ll talk about that in second, but the pricing tier, I can choose whatever pricing tier I want for my secondary, so – but you notice, it can’t – if I’m in standard, I can’t go to a premium, as you see. If I’m in premium, I can’t go to standard, or if I’m in a read and support I can’t, so I can pick – in this case, I think I have a standard, S0 in production, because I’m cheap, as you already saw. Save all my money for grains for the animals, and hay. So we’ll be spendy, I’ll make it S1, so I’m going to make my DR server bigger, so I made an S1, click apply, I click okay, and up here there’s that little alarm bell you’ll see sort of this is a notification of what’s happening, so here’s our database we built earlier and if we just wait, we’ll talk and actually we can even answer a few questions here if you want Brent, if there’s anything worth answering now.
I’ll go into Slack too and answer in chat, but in a little bit when this deployment window says we’re done, we’re going to now have a synchronous pair in west US two, so we just built an AG basically, with that click.
Brent Ozar: It’s suddenly now just a matter of getting approval from the boss with the credit card.
Mike Walsh: Yes.
Brent Ozar: Alex asks, “Is there some sort of backup to glacier or some kind of slow offline or cheaper storage or how much are my backups going to cost me?”
Mike Walsh: Yes, so by default your sort of default, normal, daily backup – whatever you want to call that, they don’t cost you anything. Whatever tier you’re in, you’re getting that seven days or 35 days retention. If you want to do long-term data retention – actually, we’ll close this and show you. That’s a good question. If we wanted to do a long-term data retention – that is not here, that’s at the server level.
Brent Ozar: Well, it doesn’t help that they move things continuously.
Mike Walsh: Yes, I know, and then in portal so I’m trying to click back here I’m looking at all of my resources but I’m not. Sometimes I have to just do this. There we go, and we’ll just go to – I think it was Walsh Demo where we were talking.
So we’re looking at the container or the server and we’re looking at the options for it. So if we look at long-term retention here at the server level, again, some of the cool things I’m going to show you – or I think are cool, are – so by the way, our AG is built, so that was two minutes, three minutes and our AG is built. So like Brent said, all I have to do is get more money to spend on a credit card. Now, we now have a replica in west US two, so if something happened to their data center here, we’re up and running. But basically, this backup service here is a preview and I’ve already accepted the preview says hey dummy, this is a preview, are you sure you want to put your important ten-year backups here.
You can store here and it will tell you – you pick the databases, and you can basically configure your retention time and the policies. I thought it gave you the pricing here but maybe it doesn’t – maybe let me look in the vault here. Yes, I have to create the vault, so I don’t have a vault right now. Can I create a vault?
Brent Ozar: It’s an option that’s built in.
Mike Walsh: It’s an option that’s built in but there is pricing and I think you’ll see the pricing there, but can you take that SQL backup and sort of put it into something else – you can effectively script our your database or create a backpack of your database and put it some place, but that’s kind of – will it save you money going to glacier? Yes, sure, maybe but what’s the cost – I guess I’m curious, people on the Slack channel are in Azure as well, curious what they do for off Azure backups and what they do, if anybody has any tips for what they do.
Brent Ozar: And then there’s one – there’s a couple more but I’ll save them. Go ahead, keep going.
Mike Walsh: Okay. So we’ve basically built – we’ve already built our AG, and by the way, we didn’t have to do anything for AJ effectively. We already had AJ, every SQL DB has three replicas, Microsoft gives you a 99.99% SLA so that’s 57 minutes of downtime a year, sort of out of the box if you will. So if we now go back to the database, so close that… forget it – it’s hard to close windows and change things in the portal unfortunately.
So we go to our primary database here, just kind of continue down this and again I’m just going to show you some of the things we can do and then we’ll open up the questions and I’ll – actually, we’ll build – you guys can all laugh at my Power BI skills. So all of this is available, everything we’re doing we can do in PowerShell, we can do a command line interface, I’m showing you this way and that’s just how it’s going to be Steve. So you can turn your camera on and argue but I’m going to show it here.
Someday when you grow up – but it is – that’s a good point though, all these things are done in PowerShell. And in fact, everything we’ve built – I can’t show you now but there’s an automation so if we built a new database – I won’t show you now, I’ll show you if there’s time. But there’s an automation script, so you can click on scripting. So this is kind of funny because Steve was just talking about how bad the GUI is and how we should script everything and do proper DevOps, right, so this is all scriptable and what that means is you can build jobs and build processes to deploy databases. You can have workflows that hey, somebody comes on, a new customer comes on, they have to get this system, which means they have to go to database and they build a database, so we can do that. A lot of people do that, it’s a lot of Cloud automation happening out there. So even though it’s pretty quick, we’re trying to do it in a DevOps and automation fashion, trying to remove even the people like me having to click in here. Any more jokes from Steve?
Brent Ozar: No.
Mike Walsh: Good.
Brent Ozar: He’s trying to write code on the fly inside Slack and it just doesn’t work that way.
Mike Walsh: I see animated gifs going crazy behind me here so I’m trying to look for it. So if we have requirement for [trans by date] encryption, so let’s say the auditors come and we have to be encrypted to check that box off. We can click a button here and turn it on and click save and about – I’ve already done it to this database but about two minutes later, our database is protected by TDE. That’s important for the backups and the logs that rest.
Honestly, I think TDE is silly in most environments for a lot of reasons. It’s beatable number one and number two, it’s – you’re protecting against a certain type of failure. I care more about encrypting the data and trans and I care about the always encrypted type of features but a lot of auditors want us to do this, so we’re protected. If somebody happens to go in through Azure data center and they open up all the containers that have servers in them and they find out exactly which container one of your databases is in and they find out which of the thousands of servers it’s in and open it out and take their drives out, they’re not going to be able to take your data because it’s encrypted. So there you go. So you’re protected there but also your backups and everything else is protected there too so it is important. You can build – so these features sound familiar right, if you’re used to SQL Server features, we can do dynamic data mask, we can build it on tables that are sort of allow it or meet the rules for it.
This is a relatively new feature, unfortunately, I always tell myself I’m going to keep a demo around longer to test this, but you can turn on auditing and one of the things that comes with auditing is threat detection. And you have to have a storage account for this because it’s storing data, it’s storing a logs into a storage account or lob storage, so I have mine stored in this blob here. Of course, I clicked it so now I have to go back. But it will actually look for three types of threat really, two, but three ways. SQL injection attacks, it can find patterns that match SQL injection, is it 100% perfect? No, it doesn’t replace your need to do things, do your own pen test and do your own processes, but it can look for vulnerabilities and you actually find SQL injections. Also it can look for anomalous client log ins.
The problem is all these things, including the tuning I’m going to show you in a second, they’re built off machine learning, so you have to have some data and some history to make this tool work. So I actually tried building history, I should actually make a PowerShell script in all honesty, or some sort of automation or have Richie write me an application. I tried logging in a whole bunch of times for many days to one Azure DB from my own local connection here, and then I tried using my private internet VPN and I tried logging in from Turkey, from Romania, a bunch of places basically. Thank you, that’s nice.
Thank you Kratant, tell me what I told people. So that’s machine learning there there too, she hears me tell people what I want to do and she reminds me. Hey, you said you were going to do this, actually you know what, that’s a pretty handy feature by the way. Not that busy, but anyway, I tried to create anomalous log ins, that’s a login that – it succeeds because the password’s right but it’s coming from something else. It’s that fraud detection.
So it does work and I’ve seen demos where it works but I just have to keep one of my VMs around for maybe a week and build history. It’s kind of like bank tellers, when they’re looking for counterfeits they say they don’t need to give them counterfeit training because they handle good money all the time they can tell what the bad money is. I don’t know if that’s true but I hear that illustrations all the time. If it’s not true it sounds – so by learning what’s good you know what’s bad, and the machine learning will do that for you. So we can change our pricing scale, so if I said you know what, I wish I didn’t build a server this way, I wish I went to – and this isn’t available because now we’re in a global – you know, we have replicas that are in a different data center.
So we’re in a standard there so we have to stay in the same class but I can change, I can increase my DTUs, or I can just destroy my AG by clicking a few more buttons and then come and change my process here. So again, it’s a few more steps, but it’s not really that many more steps than nothing. This is really easy. If you wanted to do that with actual AG, change your class of machine, destroy it and build it, that’s a [crosstalk]. So if we go back to the database here, you know, here’s the automation script, it just disappeared – so this is the script that we could basically take and we can use this and add it to a library and basically build our server and then we could deploy it and we can use tools on that to deploy it. We won’t do that now obviously. I put the wrong x, see. I’ve been using this portal now for quite some time but I still do that. I’m the only one that does that, probably not.
We can build some alerts, set alerts up, I won’t go into that but there are some alerts built up, there are some diagnostic things we can do. There’s a performance overview, this does not replace other performance tools but it’s kind of neat. You can sort of see – we don’t have any activity here, I haven’t really put workload but we can see our DTU utilization, we look at resource health, we can sort of see the generic status of our databases and stop touching the mouse for a second – there it is. Looking at our history tells us what’s going on, query performance, I may have an old query here – no I don’t, actually, I do. As you run queries, you’ll see sort of – none of my queries are bad but this query has run 288 times, so what was it? I can click on this, I can see – it says query right here, so this is some internal query obviously but it’s not hiding itself from us. So I can see what this query did and this query didn’t do anything, it just happens to be a query I can look at. So it’s kind of cool, it’s built off of…
Brent Ozar: Query store.
Mike Walsh: Query store, thank you. It’s getting late. It’s built off of query store, so it’s looking at the same data as query store and some of the query store features are here. This feature scares people, if you’re used to database and tuning advisor…
Brent Ozar: Let’s see how you can keep that poker face up and going here.
Mike Walsh: I can’t. SQL Server is notorious for over the history of it, giving us some really horrible index suggestions. So while I’ve made most of my consulting money and performance tuning engagements adding indexes to clients, I’ve made some of it removing name of index, missing index, blah blah blah, sys.name with the brackets and everything and the whatever you call those.
I’ve also made money removing the DTA indexes. Not much, I’ve made more at adding indexes. Here’s the deal, this is not the DTA. It has some of the same genesis, but it makes kinder, gentler, better indexes and I’ve seen it make good indexes. Can it make bad indexes? Sure, but so can you, so can I. Brent, you probably can too, and so can your developers and so can your managers, who have access to the SQL Server and shouldn’t.
Here’s the other thing: how many databases do you have? How many databases do you look at and watch the queries? If we think about where the database space is going and where the DBA role is going, you know, creating indexes is kind of a silly thing for us to be doing.
Now, at development time, at design time sure, we should create sensible indexes, we should have a rule like Steve should never employ – apply a deployment or accept a deployment if those primary keys he said the tables must have don’t have indexes, and if their foreign key partners don’t have indexes. He should say no, I reject it, and he should build rules to reject it. So we should still do that, but as people use the system, I can think of one great customer, and this is one we both work with, they have ISV and they sell their software all over the place and it’s so customizable that you can create your tables however you want to create them, whatever data type you want, you can have your own different templates, you can create them from different templates, and you may have 200 databases on a server all active with different users using them in different ways, asking different questions of the data.
If you have the time to tune those all and look at them intelligently and say this needs an index and add it, then go ahead and do it and that’s all you’re going to be doing. Like your job is basically not a DBA, it’s index tuner full time for that kind of environment, for some of the biggest companies using that software.
This tool is a pretty good tool to add indexes that make sense, and it doesn’t just add the indexes, it has to learn about your usage and watch for a while and then add it based on the machine learning. It’s new, it’s still learning, if we’re allowing SQL Server to receive our data, if we’re allowing Microsoft to receive our metrics, it will learn when it makes mistakes, it can also – you can also automate the drop index, and you can basically keep the tuning on but keep the index automated creation off and get the insights and get the advice and see what it would have done if you want, but this tool really does work. I’ve seen it work, I can’t show you my customer data so I don’t have any data built, but I’ve seen it work and it does work and it makes decent indexes. Sometimes it misses, but I like this feature. If you want to restore – I’ll show you a restore and then maybe we’ll kind of stop for questions and I’ll show some Management Studio, but go ahead for questions.
Brent Ozar: To do the sales pitch, how much does checking that box cost of auto-tuning?
Mike Walsh: You mean in terms of what you pay per month for having it?
Brent Ozar: Yes.
Mike Walsh: Nothing. It doesn’t cost you anything to click it exactly, so it’s cheaper than calling you or I to come in and add indexes, right. Not a lot, so you still call us, but you know…
Brent Ozar: Please call us.
Mike Walsh: But it’s a tool in your toolbox, it doesn’t cost you anything to try it; that’s a great point, Brent. I like that, that was a great question, that was a question with a statement, that was well done. Had something here and we were giving things away. I’ll give you some maple syrup next time I see you, how’s that?
Brent Ozar: Alright, as long as it’s not any of that kombucha I’m good.
Mike Walsh: No, that’s her own experiment, she can do what she wants with it. I stand ready to get the ambulance if I need to. Anyway, if we go back to our database, I’ll pick the sheep database because we’re talking about the farm. If I want to restore my database, I’m in the database and again, yes, I can do this with PowerShell, I can do this with the command line interface, but I’m not going to. If I want to restore, I ship database – I made a mistake, I was up late last night with lambing and I typed something in wrong, I want to go back to – this is all UTC times by the way, so just keep that in mind. Most of the times you see in Azure are UTC, so do pay attention to that. Let’s say I want to go back to Thursday or whatever, so I’m actually at the current time so – it didn’t give me the time change dialog, so I’ll just say…
Brent Ozar: Is it too early?
Mike Walsh: Yes, it might be too early because I created this database – probably it’s too early. So we’ll go back to the 28th, and we’re going to target a server, so we can restore it, we tell it where we want to restore it to, we can restore it to this server, which is a server that’s already built. If I had other servers – in my east region I only have the one server. If I had others, I could create others. We didn’t talk about elastic pools, we’ll kind of get to that. And I can then – any time you see the word pricing tier, that’s a little hint. So Brent’s earlier question, how much does this feature cost? Nothing. Because there’s a pricing tier this is going to cost me something. So I can restore it to whatever class I want to restore it to, so I’m going to again be cheap and do a $4.99 basic, sorry guys, and click okay and I have two options.
I can restore all of my database or I can create a new database. I’m going to create a new database that has the time stamp of when I’m restoring to. I click okay and if we look up here, in a little while we’ll see the restore done. Maybe five minutes. So that was pretty easy to do that restore.
Brent Ozar: You got a couple of questions on that automatic tuning thing. Matan says, “Does it have any intelligence as to when it’s creating indexes? Like away from maintenance, away from peak windows?”
Mike Walsh: Yes, so you know, maintenance is a different kind of beast right, if you notice, I didn’t show you the CHECKDB box because we don’t run CHECKDB. I’m assuming and hoping Microsoft does, but maybe because it’s AGs they’re not running. I don’t know, I don’t know what they’re doing. They’re taking care of it with their 99.99% guarantee, so maintenance is one thing, but…
Brent Ozar: The maintenance windows like time wise.
Mike Walsh: Yes, we don’t tell – they’ll add it next year. These are online adds and I don’t know if the machine learning – I don’t know to what extent the machine learning is looking at the system. I know it doesn’t create them when DTUs are high, I’ve seen it create them when DTUs are low, but I don’t know enough and I haven’t tried enough to see what really happens, and Microsoft documentation doesn’t necessarily say where it is, so we don’t have full control. That’s a good question though, and I’ll play some more with that and I’ll write a blog post about that in that series.
Brent Ozar: And then Astaway says, “Does this mean that the database administrator is basically going away?” At the flip side of this – well, I’m going to ask one other question at the same time, Dana says, “We use auto tune on every database and all our subscriptions and we’ve never had any negative issues.” So note that Dana still has a job, so…
Mike Walsh: So DBAs – no, I don’t think the DBA role is going away, I think it’s changing. So my restore, I think it’s done – let’s see. It’ll be done in a minute, that’s a bug I think, like restoring. But I don’t think the DBAs – like, this is a discussion point right.
Talk amongst yourselves in the Slack channel, but I don’t think it’s going away, I think it’s changing right, so I’ve been doing SQL for 18 years, started as a DBA and I do a lot of DBA things for clients. I do a lot of remote DBA stuff, I’m actually – we have retainer service for my employees and people and we help customers as their DBAs. I have some customers going to the Cloud and they’re still using us. Some of the boring things and the things people forget, yes, that is going away. Some of the server configurations and Brent I think, you know, you do health checks a lot like I do. I’m floored something and I don’t know why I’m still floored but I’m floored by the sophisticated, well-budgeted, smart companies that miss a lot of basic configurations and don’t do CHECKDBs and they’re in full recovery and once a night they truncate, they backup their log and then shrink it because they don’t understand why it grew all day. I see that all the time still. And some of those aspects of the DBA role, yes, probably will go away in time but I still have SQL Server 2005 clients calling me. There are still insurance companies using green screens, lots of them.
So it’s not going away, but I think it’s changing and I think one advice I would give people is it’s time to learn this technology and learn where it applies and learn where it doesn’t and be that person in your company who can actually not be afraid of this whole adoption story, but sort of know where it makes sense, and use this and try it and get there because it’s coming and a lot of the manual things we do are changing.
Even Steve’s DevOps presentation, if you watch what he was showing, some of the Redgate tools and other vendors tools, a lot of the things that we used to do five years ago, we don’t have to do the same way. So do you have any more to say to that Brent? I mean, what do you think?
Brent Ozar: There’s a huge question I’ll hold until the end, which is around performance tuning and how consultants – like will consultants still have work, but we’ll talk extensively about that at the end, but several of you have asked that, but no, keep going there.
Mike Walsh: Alright, so I’m going to show you two simple things. This is ugly; let me bring this into the right monitor. So I’m just going to show Management Studio, and I’ve connected to Azure, and I’m just showing you – you know, I’ve opened my firewall up to allow my IP, and I’m just going to look at the Sheep DB. Actually, the Sheep database, that’s a little bit newer, and you know, there’s no weird joke here, I really have a farm, really have sheep, so it’s a pretty boring use of the term you know – actually, I need to open this.
So just one thing, just so you know, I can’t run a query, I can’t run this query, I can’t run SELECT star from WalshDemo.dbo.saleslt or whatever. The cross-database queries aren’t supported. Now, at the Data Amp fest event, and actually in my blog I’ll make a note now to include that as well for Tuesday, there’s a couple good stories out there about what they released, but one of the things they talked about was Azure manage instances, so on Azure manage instances, it’s kind of like they have the – it’s sort of a halfway step between IaaS and PaaS.
Instead of having your database as a service, you’re getting a SQL Server instance as a service. So if you want a little more control and you need to kind of be able to do those cross-database queries, that’s an option. Now there’s a bunch of other things like elastic queries, where you can query multiple databases at the same time, elastic jobs. So if you have say a software as a service company, and you have one database per client, and you want to update the schema and you have a thousand clients, that sounds daunting but you can do an elastic database job and update all the schemas at the same time or do them on a rolling schedule for the job. So, out of topic for this obviously towards the end here, but cross database queries are one of the things you can’t do, but if we just look at the Sheep database and you know, I have – so strange database but I have a dewormer table and a color pattern table.
Again, I don’t really use this for my sheep, these really are three sheep I have on the farm. My farm is my tech-free zone. I go out there and I have a pitchfork and a shovel, I talk to the animals and I use my tractor – my tractor is my only technology I have out there. Everything else is all century old, millennium old technology and I love it, I need that, it’s good. You need that too. Whatever your unwind is, if it’s sitting down drinking wine, walking your dog, that’s one thing, if it’s shoveling cow poop, that’s whatever is your thing.
But anyway, this is my table so I’m querying this database in the Cloud, I just want to show you that and then I can do a bunch of other things. One more thing I’ll show you here, and maybe I’ll connect to my local instance – it’s an L, not a semicolon… It’s an ugly old keyboard, you can’t see because the way my cameras, but I like the keyboard feedback. So I’m not typing hard, it just sounds like I’m typing hard. So if I right click on this database, this is the Sheep database, and I want to send it to the Cloud, I can do it in a bunch of different ways. I can script it, I can back it up, I can export it. It’s a small database, I’m just going to – just to show you how I can bring it up there I’m going to say tasks, then I’m going to – does this look okay in terms of the screen?
Brent Ozar: Totally, yes.
Mike Walsh: Alright, so I won’t go crazy with my control one skills. Deploy database to Azure SQL database, I can also deploy it to a VM that way too. And what’s going to happen is this is basically going to create a backpack and ship it in this sense, so I’m going to connect to my Cloud instance here, we’ll give it a new name. There’s already a SheepDB up there so we’ll call it SheepDB2 and I’m resisting the – I don’t know how to type all of it, but – boogaloo, I don’t know how you type boogaloo, it’s probably OO, right. I don’t know, every time I hear two I just have to say Electric Boogaloo. I was born in 70 and it’s just something I have to say. I had a boring childhood watching a lot of cable.
So it creates a backpack on a temporary location, this does get rid and it’s basically the scripts to create your database and to sort of deploy the data. Click next, you wait and wait and wait, and we won’t actually watch – we won’t watch this get created. This may be created while we’re answering the questions before the four o clock, but I don’t want to, so we’ll wait and wait and wait.
Brent Ozar: Which is a good point that it’s not necessarily transactionally consistent. It’s not like – you don’t want to do this to a running database. This is…
Mike Walsh: Yes, this is not your migration to take your database live and have – you know, sync it in sync like a replication kind of thing. This is not that. There are ways to do that but this is not that, so this is going to basically extract the schema, extract some data, script it and create it, and while that’s happening, I promise one more thing and I look at this Slack channel, I make sure I don’t see anybody like Patrick Oblenk or – I am not a report writer. So this is just me playing in Azure. I don’t know any more Power BI, I have the AdventureWorks database on there, and I went up here to get data and I went to more for my sources, because I did this the other day and I kept it up, my sources in Azure SQL DB, Microsoft Azure SQL database, connecting to it, can do a query, type my server name in – I’ll do it.
Actually, I won’t do it because we’re running low on time here, but I type my server name in and I say direct query and I click connect to the database and all these tables are now here. So you know, they’re available to me to use because I grabbed these tables. And I just pick some visualizations and start dragging stuff around until it makes sense.
I know more about reporting than that obviously, but I’m looking at – I just want to see a map to say hey that’s cool, I didn’t have to do any work for this, I’m just dragging the map. I want to see a pie chart and see you know, bike sales or AdventureWork sales or anything per city, see which cities were the busiest across the globe, I put a slicer in to be able to slice and see what happens when I do. Unslice, and then I took categories and I think I can – can I do that? Yes, I can look at categories in just the cities, so I can uncheck that, I can see what categories are popular in Monrovia, I don’t know because it didn’t – so there must be no category, it’s so small you can’t see it. So the point though is that I’m using the Power BI desktop, by the way, you can’t use Power BI online to Azure SQL DB right now, you have to use Power BI desktop to create the reports but I clicked on here and I created some ugly, really ugly sorry, reports.
You can do that too and if you’re a reporting person, you can create even nicer reports and more sensible reports for your data using – you can write other queries, you can do whatever you want, so basically, in an hour, an hour and 20 minutes, I joked around with Brent a little bit, I had some problems with the portal and I clicked around talking to you people, I don’t mean you people in a bad way by the way, but talking to you all – I talked to you all and kind of walked you through what I did and I built the database container, I deployed my database to it, I started querying it, I built an availability group effectively – actually, I built two different availability groups, or had two different replicas in two different locations, I set some long term retention up, I set up my index tuning wizard to tune indexes for me and I’m done.
Now I can build my access and connect and all that and I’m right now, I’m migrating my database and that might even be done as well, and it is, so I’ve also migrated my really small Sheep database up there. So that’s that, and the main point here is just to show you, you know, if you’ve not played here, it’s not that scary and you should really explore here because – I mean Brent, if you had to break it down, would you say – are you at the point now where maybe 20 – can you say 20% or 30% new clients or more are doing this Cloud stuff?
Brent Ozar: So it’s probably 20% to 30%, for me, none of it is Azure SQL DB, but I think that’s mostly only because we don’t blog about it, so it’s not like people come to us and find it, but like 20% to 30% is a safe enough number that Tara and I were even talking – Tara’s going to an online AWS class just because it’s like why would you not at this point, we get so many of them.
Mike Walsh: Yes, makes sense, yes. And I would say right now, maybe a little bit more, my Cloud work is also Amazon but I’ve been doing quite a lot of Azure and bringing actually a good sized software as a service company to Azure SQL DB, in fact, where can you use an elastic pools – I didn’t talk about elastic pools but basically an elastic pool allows you to pool DTUs across databases.
So if you have 100 databases and maybe any one of them might need 1000 DTUs, not at the same time and you know, just you think about think time and how people use the system, you have multi-tended application, we’re building one DB, one Azure SQL DB per tenant and we’re putting them in elastic pools so they pay for their max level and everybody can scale around it and it’s just a little bit more flexible for them, so it’s a pretty cool story, and I’ll shut up and if there are questions or conversation points, what’s happening?
Brent Ozar: Throw your contact info backup on the slide too, just so people can have that as we talk through a few questions.
Mike Walsh: Yes, and like I said, if you go to my blog – I’ll put it there, it’s straightpathsql.com next Tuesday will be that and I’ll set up that Google group will be unveiled then. It’s nothing special, I just want people to be able to play in the Cloud and it doesn’t have to be dedicated to Azure SQL. We can talk about RDS as well. But anyway, I’ll shut up now and ask your questions.
Brent Ozar: Yes, bad news, you’re not going to be able to shut up now, because now we have the questions. Alex says, “Have you done any databases with AD authentication and are there any gotchas with that?”
Mike Walsh: Yes, so yes, and yes. ADFS is – I don’t get to play with it much thankfully. ADFS has some quirks and the system administrators I work with sometimes have some frustrations, but yes you can do Windows authentication with ADFS and what is it – AADS I think. It’s not so much the Azure SQL DB that you’ll bump into some struggles with, it’s the ADFS. It’s a federation between the two. Once that’s done you’re good. Once you get your share point federation and everything else working, the Azure SQL DB stuff sort of comes on easier.
Brent Ozar: All you have to do is get security to work and you’re done.
Mike Walsh: Yes, exactly.
Brent Ozar: It’s not a SQL problem, that’s someone else’s problem.
Mike Walsh: That’s right.
Brent Ozar: Astaway says, “Have you got any favorite resources for Azure DB performance monitoring and tuning apart from Microsoft site?”
Mike Walsh: Yes, you know, there’s not – I haven’t bumped – so hopefully, as I start my series, we’ll have some here on my blog. I’m sure there are some people writing on SQL Server Central. You know, various tools vendors, SQL Sentry and others are having Azure SQL stories, so it’s still coming. I think if you just stay tuned to the SQL community and you know, like Andy Mallon just blogged on SQL Sentry on SQL Server performance twitches on SQL Sentry’s site, they’ll be putting more up there about the Cloud as well, so I think you’ll see more and more coming, and maybe someone in the Slack has other ideas too.
Brent Ozar: Let’s see here. So Tim asked earlier on, we had a vibrant discussion in Slack about it, this was it – he said, “Do you find that there’s more enthusiasm from companies to tune for performance when you get billed monthly from resources?”
Mike Walsh: That’s a great question. Yes and no, so when we put the restraints on and when we tune – quick answer is yes. It will happen more I think as more people are going to the Cloud and their performance bumps down a little bit. Yes, some but sometimes old habits die hard and bad code is still slung a lot, so there’s always going to be a need to tune stuff. By the way, this is just a fun thing, since you asked – totally unrelated question, I just always find this funny. 20 trace flags – so in your Azure SQL DB there are 20 trace flags enabled and a vast majority of them are undocumented. I just find that funny. Like, they’re probably documented in some place, but not anywhere I have access to.
Brent Ozar: Right, well you got to imagine half of it is feature flags that they’re turning things on for you know, specific customers in advance.
Mike Walsh: That’s what I did, totally unrelated but somehow that question made me think of that.
Brent Ozar: Em Swaroski asks, “Why would – can you elaborate on how you would choose Azure versus AWS?” My answer is that usually it’s just not up to the database administrators, usually, it’s up to the companies because it’s however many other services they use, because every Cloud provider out there, even you go back to older school stuff like rack space handle SQL Server just fine. It’s about what are the other features that developers are called to used and that’s what ends up – or, that companies have a Microsoft enterprise agreement and Microsoft throws in free Azure bucks, so basically you just are incentivized to go start with their club.
Yes, that’s a good answer. The other thing, it depends on what you’re doing. So when it comes to IaaS, I think dollar for dollar and performance for performance, I think you’re going to get the same thing. It’s pretty comparable I think.
Brent Ozar: No.
Mike Walsh: I think it’s getting more – we can talk about that. Maybe price and advertises is comparable. So when it comes to PaaS though, if you want to do SQL PaaS, I’m not as much a fan of RDS for SQL Server as I am Azure SQL DB, but if you were hey, I don’t care what database I’m on and I want to go to Postgres or I would look at Amazon. But if you’re looking for a SQL Server platform as a service, I would probably better see you in Azure SQL DB, because they’re getting newer features faster, there’s more ecosystem to support their – it’s more native right, that’s one thing I would look at. And when it comes to IaaS, my experience lately has been fairly similar, although we know somebody is having some performance struggles too, so…
Brent Ozar: [crosstalk] go fast, everything is just moving so quickly.
Mike Walsh: And in the complain story, they’re both – there’s a race to the bottom in pricing right now between the two and there’s a race to having who as a bigger compliance, so one of the blockers has always been they’re not complying yet but everybody’s compliance slide now is like it fits off – it’s not – doesn’t fit the presentation anymore. You like grandma Flo’s certification for apple pie is probably – they probably have now, so that’s not really as much of a blocker.
Brent Ozar: Let’s see here, so Qurious says, “The MSDN credit is also a big help too because the developers and DBAs end up playing around with it before the company makes a choice.” Yes, if you’ve got Visual Studio, you can do some damage with a 150 bucks worth of credit. It’s surprising, and look at it…
Mike Walsh: Yes, it’s easy to spend it.
Brent Ozar: Yes, well and you look at it and you go oh my god, every VM that I wanted to run costs a thousand dollars a month and you don’t run it all month. You run it for the weekend that you’re going to play around with it and you can really do some damage.
Mike Walsh: Yes, it’s good and really Amazon too getting an Amazon portal account is good to look at. You should learn them both and understand them because I really think the future is probably – there’s going to be a lot of people who aren’t just in one. I know some people who take their own IaaS and on Azure, and they actually have off-site backups to S3 buckets, you know.
Brent Ozar: The other thing I would say is so, some of you may be walking away from this going oh my god, the Cloud is going to take my job, I got to learn everything immediately, all this stuff changes every six months to a year. I mean, just rapidly, as long as you’re vaguely aware of what’s going on, that’s a good start so that you can start deep diving in when you want to.
Mike Walsh: Absolutely.
Brent Ozar: Alright, well thanks a lot Mike for hanging out with us today and sharing everything at GroupBy. I really appreciate the work that you did, lots of people asking and answering stuff over down in Slack, so…
Mike Walsh: Sure, I’ll go there now. Thank you guys, have a good one.