How to Install Azure Data Studio for Microsoft SQL Server

[Music] good morning people how's it going it's a happy Monday morning unusual for me right it's like rare that I would stream on a weekday hello Netherlands good to see you gee surgeon nurse G see I still call you G surgeon but you know it still looks like surgeon to me and I see boss goes in here good morning W Robinson or rub your son I guess it is Oh mayor de Spitfire welcome Brittain here pit bull proton again with bringing the fantastic name there has to be a story behind that pit bull proton proton at some point you have to tell that story because it just sounds so awesome I will ill will I see good afternoon good morning alien good to see you I don't think I've seen you in here before coming like and now I'm not remembering and then zoom it's in the Netherlands as well I know gov DBA right so I was supposed to have jury duty last week and this week and I of course obviously ended up jury duty getting canceled with the whole kovat 19 thing so I ended up deciding that last week I was gonna take off the week and go to drive up to Malibu my wife and I drove up to Malibu stayed in a beachfront hotel and just sat listen to the ocean man I'm in trouble like we really loved hearing the ocean and Jim good Majin Man sequel dftba dang it's a whole weekend crew we're all in here in the week week base - but man oh we had such a great time sitting by the beach and just usually when I go away and sit by the beach I try to bring a notebook and I go through and build out my list of things that I want to do good afternoon from France Olivier I'm and then it but this week I didn't do last week I didn't do any of that so this week I probably have some you know like long term life planning to do Malik good to see you again as well PowerShell freak good to see you for the first time they're from Germany Aylan I don't blame yous it usually when I've been doing my weekend sessions my sessions have been on the weekends just because it's early in the morning before my wait wife wakes up that kind of thing but so this week now last week I was in Malibu this week now I'm just gonna have stream while I work basically I've got a whole bunch of tasks lined up that I want to work on this week my big goal this week is I really want to be able to ship a new fundamentals of database administration class now I'm not gonna be able to ship aasif aza it's good to see you again as well grumble grumble I was gonna say you don't you don't usually put in a lot of comments there s muscle all kinds of things this this shirt is from the Spy Museum in Washington DC the there's a spy museum and it's amazing if this virus thing ever ends and you get the chance to go to it I can't say enough good things about the spy museum and Washington DC just utterly phenomenal good morning cigar they're on Facebook so I really want to ship a brand new fundamentals of database administration class and a couple of weeks ago you may have seen me streaming oh as of Oz's has do you use some force sort of tool for GTD I use Remember The Milk to Remember The Milk calm it's not really designed for GTD it's an old school what just to do application for the web that's a buzz I love it yeah you seem so productive I did so part of the key of that is not having any kind of social life and during the quarantines it certainly seems to be a whole lot easier and pail says I love data science would like to be able to pull data by myself yeah totally usually comes down to permissions thing Matt good to see you as well Jim that I've seen this the Air and Space Museum I haven't seen the Vietnam Wall yet no I heat bogies I am usually up at 2 or 3 a.m. it's just that I don't start streaming until 5 a.m. so like this morning I was at a 301 a.m. no alarm it just that's how it works now and I go to bed at like between 8:00 and 9:00 and I usually spend half an hour to an hour answering hmm PowerShell freak says I have a question about differential backups I can't do diff backups anymore cuz it can't find the LS n for the latest full oh that's interesting I have I would want to see what the exact error message is I think that there's something else going on I think it might have been that you don't have full backups it's terrifying as that sounds - just the whole reason that you do differentials is you're gonna need the most recent full plus the most recent diff in order to restore been unfortunately that doesn't work because it doesn't give you the sequel server extensions so just like anyone else who's overly into PowerShell and all these things they no one command and they think that they can solve everything with it and they're always wrong thank you for putting that in though it's very nice of you to put in an incorrect answer into the chat so so in this I want to ship by the end of the week a fundamentals of database administration course I'm not gonna be able to record the videos most likely by the end of the week but I want to have all my material lined up and done by the end of this week so what I'm gonna be working on is the layout of everything that I need to do so oh Santi good to see you again so let me switch over and I'm going to show you the abstract that we built together in a stream like a week or two ago so a week or two ago when we built this we said alright here's the abstract I'm going to build the whole thing in markdown and I always like to define a target attendee so we defined our target attendee I want to say it was surly deaf who came up with the name Bo problem guy was used like to use Simpson style names inside there then here are the list of things that went bit binos and then what she doesn't know is well this kind of defines how the course lays out let you read that there for a second oh I need to tweak my camera just a little because he's showing a little white line up at the top I'll do that while you read that some of that just a little bit and there we go so that's what she knows in what she doesn't know and here's what she's going to learn in the class now this you don't have to read through this because it's gonna be epic long it's gonna have all kinds of stuff inside here it already does have all kinds of stuff in here frankly more than I can teach in a day which leads me to other problems that I'll uncover as I go through and build out the material trying to figure out where I'm gonna edit things and whenever I have a class that I want to teach and it's more than the target length what I try to do is figure out no sati I will do a webcast with that this week but I won't I'll do it after I get done building the blogpost so it'll be like an hour and a half who the last fullback was deleted yeah yeah so you got to do a full backup yeah unfortunately you got to go do a full backup there so so in here there are gonna be things that I'm gonna declare us out of scope and that I'm not going to have the time to teach them and half as I went through this whole entire list of everything that I wanted to teach in this class I started realizing that I wanted to teach it in an azure data studio notebook W Ribeira son it's type aura you can see it right up at the top of the screen there type aura I don't usually use type aura the only reason I'm using it is because I'm running in a Windows VM just for streaming I tend to like to use windows vm's when I'm streaming because I work in a Mac natively and I'm not quite ready to show that quite yet only because I think most people would be alienated by the work involved in working on a Mac they're like oh it doesn't look like what I'm used to which is actually a good thing about Azure data studio so Azure data studio is cross-platform it works on Windows Linux Mac and as I'm looking at the stuff that I want to teach be a problem is I'm looking at the stuff that I want to teach her I go you know what I I don't know that I really want to teach this inside sequel server management studio and that's kind of a big departure for a lot of folks a lot of you in here are probably used to dealing you're doing your work in sequel server management studio but really as your data studio is has a lot of interesting tools for teaching or in learning it's a little easier to deal with things like notebooks inside of Azure data studio there's a little lesson to it over over in management studio so as I'm looking at everything that I want to teach BO problem I'm like you know what I actually want to teach her inside of Azure data studio cuz plus two if she's learning about PowerShell she's probably gonna learn about PowerShell notebooks which are also inside Azure data studio so I figured out and decided that I want to write a so yeah PowerShell freak that's kind of above beyond what I can tackle here quickly so did I immediately want to go off and start troubleshooting that but it's way beyond the scope of what I can do quickly here so I'm gonna need to teach her Azure Davis to do but I if I'm gonna teach some of the azure data studio I don't have the time to do it in a one day fundamentals of database mentals of database administration class I need to ship that as a blog post and make it part of the prerequisites toad is slow to show you what I mean I've got the product page already up for fundamentals of database administration along with a new piece of art from our cartoonist Eric Larsen does I was just a tickle pink with all the stuff that he does in terms of graphics so for this one I I'm gonna kind of reword the class description a little bit to make it be like hey look I'm your senior advisor here I'm gonna give you the real rundown it's the kind of exactly very much bad guy which I loved about that that's kind of cool but I gotta I gotta work that into the class description a little bit so here's the product page let me zoom in a little bit so y'all can see the product page move down so this is your a Systems Administrator developer who knows how to create a VM install windows how to troubleshoot it you've got a couple of Production sequel servers with around 100 gigs of data or less and you're responsible for backups uptime patching and so forth and you have no idea what you're doing so in this one-day class here's the stuff that you're gonna learn so I'll let you read through that and then prerequisites you need sysadmin level access on one production sequel server we're not going to be doing as your sequel TB or Amazon RDS it's not about development servers this is strictly about production servers and then there's this line right here and this is the part that I gotta fix this morning install as your data studio on your desktop not the sequel server itself by following these instructions and these instructions is going to be a link to a blog post and that's the blog post I got to write this morning now installing has your sequel datastore as your data studio looks really easy when you have people like been going did I calculate E and it doesn't actually work so now the chocolaty part does work if you have chocolaty installed and all that other kind of stuff but I need to have them do the bare minimum of tasks available in order to get across the finish line to get them into the class and when they install as your data studio it has to be able to query sequel server which bends command would not have worked so let's go do that I've got a blog post start us our smoogle shell started up here where I'm going how to install as your data sequel as your data studio for sequel server with first sequel server work and I already I don't like that title already to work with sequel server let's change that around and then I'm gonna go write this I'm not gonna publish it immediately the date I'm gonna publish it is actually going to be let's go look around and see if my calendar will say the 9th whoa Dave welcome to the club oh man you've been around here for so long I just kind of assumed you're already subscribing so July the what I say it was the ninth I think it was 8:50 okay Dave's a regular around here so he gets a round of applause so now I gotta say I'm gonna write up the instructions on how you go and install as your data studio now throughout the course of the next you know half-hour 45 minutes I'm gonna take screenshots as we go through I'm actually gonna install it here on this VM and do the things that are necessary in order to get it to query Microsoft sequel server so let's start by going in googling will say Azure data studio download and download and install Azure data studio do I want to point them to this do I want to point them to this or github I'm always kind of torn about where let's look at the github release page Dave I know that feeling I had the same kind of feeling when I first started I could probably write a blog post just about that as well botsko yes there is absolutely it's it's a century one plan Explorer I'll show you extensions as we go through here so github Azure data studio let's look over at the github page and let's see if the Installer experience now you know what I don't want to send him here because boy you look at that it's a big wall of text okay so I don't want to do that I'm gonna go over to this page then so we're gonna say download and install as your data studio I'm gonna say there's gonna be an intro we'll go here go here and I'm not gonna bore you by typing out the intro just yet we'll get straight to the meat of this go to Microsoft's site and so download will switch this around download Azure data stereo is your data studio on your desktop so copy that boom download and go to Microsoft site and you don't you don't need to go you don't need to install certainly to welcome good morning sir or good afternoon as it may be for you do this on your desktop do this on your desktop not the sequel server itself you always want to run management tools from your own machine to reduce the overhead on the sequel server itself and after it downloads install it install it just accept the defaults accept them so now let's go do it on our so let's go download I'm on a Windows so I'm gonna go take the user installer and then I'm gonna go run it and we're gonna make sure that there is not there are not any gotchas around the wizard ii progress process yep i'm going to accept it yep sure i'll take the defaults I'm fine with Azure data studio require shell restart I'm part of me is tempted to say register as your data studio as an editor for different supported file types but I don't know if Microsoft down the road is gonna change what file types it takes as the defaults gorilla right lool yeah I go that's the exact sound I make when I look at some of these dialog boxes I'm like yeah I'm just not sure about that add to path require shell restart I get why they word it that way I'm gonna say during the in the blog post so let's say during the installation process it may ask and let's go back over here let's grab a screenshot let's get our little snipping tool oh we got cur move and Karl K joining thanks and welcome to the club that's not what I wanted that's not what I wanted that that's a messed up dialog box you'll need a new app and ok and ok is even grayed out I hate windows so much can I just take a second to rant I hate windows with the violent passion that very few people outside of serial killers will even understand and what the hell is wrong that's exactly right Jetta my gorilla you nailed it this is BS that should never have accepted gotten past anyone's testing ever that's ridiculous no one should have a window especially a company the size of Microsoft it's not like it's a dot-com startup operating out of somebody's garage they just got a window that says you'll need a different thing than order and all this okay and the okay is grayed out this is why I switched to a Mac like over ten years ago because I get so frustrated during daily experience with Windows to even understand how that what that just infuriates me this is like a toaster where where you push the thing down it's like you will need a different toaster perform this operation and then the lever just stays down and never goes back up that is utterly unacceptable that is garbage code but anyway that's where we are so that's why I've also said over just long term I am so I like like only bi do is clippable I I'm just mind blown by that and so when i every time i have bought multiple windows machines i bought multiple desktops over the years going this is gonna be the time when i switch back to windows because i want to go back I love Microsoft Office office on the windows is amazing it's fantastic it's so good but then every time that I have to deal with Windows people like oh no it's gonna be different this time they finally turned it around and then I go install it I'm like what the Sam hell is this anywho back over into this so you'll need a new app god bless so let's go grab a snip hi June bug so let's grab a snip of this just that I can explain their god-forsaken error messages now I'm saved desktop will put it on downloads sure as your data studio install just to be clear I'm not upset about this window and as your data studio it's a PS window in the snipping tool surely ridiculous during the install process it may ask and then let's go put this in AD media upload files and it's that guy right there and then we'll explain it during the installation process it may ask to do do - you don't need to register as your data studio you can leave the check box it's like I agree I agree you can leave the check box for Guillermo welcome to the club thank you I appreciate it you can read the check box enabled for add to path and no I have no idea why path is capitalized but you don't need to restart yeah immediately for the purposes of this blog post nor do you need to do it quickly ok so that's the first thing that they're gonna go do is download that lets I'm gonna save my draft here and I'm gonna continue going with the azure data studio install and install and Dru I agree yeah that I look at that user vs. system installer and I'm like that's the world's worst way to explain that problem there needs to be a different uh uh which Oh C certainly dev that that's another thing that just I don't even want to go into so click finish to exit setup launch as your data studio okay so we'll launch azure as your data studio okay so now we're past the first hurdle we have Asia as your data studio installed just like a friend with the okay button right help us install this by allowing us to do something is there a not okay nope there's just to read more and that's all it is so let's go ahead and close that preview functions are another two in order to be fully supported and for some actions to be available would you like to enable preview features yes so I'm going to go back into the installation instructions and I'm going to say that launch as your data studio after the launch it may ask if you want to enable preview features click and if so click yes okay so we got that so we got Azure data studio installed Baba new welcome to the club on you thank you thank you so we'll launch will say launch as your data studio and well that's interesting that I don't even want to know why control B isn't working there it goes okay so that now we've got that installed so now I've got as your data studio so now what do I need to do next next I need to get it so that it can connect to Microsoft sequel server god bless been but abuse this admin that unfortunately there's got to be you know hills that I'll choose to die on and I'm not gonna die on that hill I love that you suggested it though so now the next thing that I got to go do is I got to get it so that it'll connect to sequel server so and this isn't quite as intuitive as you might think what we need to do is we need to go go to extensions and of course now I have to tell them what button to click on because it doesn't have does it have anything about file can I get to install I don't I don't want to do that I want to do it from the store I don't think I can give them Oh yep I can click view and extensions or ctrl shift X open the extensions pane click view extensions or use the keyboard shortcut I'm not gonna tell them to use the keyboard shortcut because God knows that or how that's going to start up so to do-do-do-do-do yeah well that register for the trial f1 install see that f1 becomes a little tricky so what you got to remember is that for cross-platform people f1 may not do what you think it's going to do PowerShell freaked PowerShell freaked let me tell you a little something because I think you're a nice person you're brand new around here I have a lot of problems with PowerShell people the reason why I have problems with PowerShell people I'm going to switch cameras for a second this is just between you and me because I like you and I think you're a good person but a lot of PowerShell people they like to think that things are a hammer and they're like hey I know about this hammer and you should try this hammer and then what they don't realize is that the hammer is made out of solid glass and then they hand it to someone who needs to PI Souness and Bill HUS in Val thanks for joining so then they hand people this silver this does glass hammer and they go to use it and then it shatters in a million pieces I like many people out in the world I use a Mac and if I hit the f1 key that changes my brightness let me tell you why that's important because you when you say things like use the f1 key that affects your own brightness people don't think you're all that bright so just as a small thing and this goes to all of you out there not just PowerShell freak stop saying just hit this keyboard shortcut stop saying just run this command because most of the time you're wrong you look like a so for those of you who've been around here for a while you'll know that I hit my keyboard all the time yes that's true that's actually what's worth so those of you who've been around he's I understand that you meant open the command palette I understand that you meant that in some alternate universe I would be using your keyboard I am NOT in that universe so here's just as a pro tip for those of you who've been around me for a while you will recognize this tip whenever you think you have the right answer and you want to give someone the right answer never ever ever ever say that this is the right answer instead what you always do instead is you say my friend told me or I read somewhere once so that that way you don't look so damn stupid that instead you say oh I I read this over here about how then that way it's totally okay and it's not you who looks like the complete and utter it's just your friend who looks like a complete and utter I've only had one espresso this morning it's true believe it or not does the Mac have an any key I know right geez surgeon this is I'm on a roll this morning and I have this is only my first espresso so today is bound to be a very long day huh is it what happens when I work with Windows and I install Microsoft products going in from the beginning my blood pressure starts at one from from remember next thing you know I got to go in for a physical she'll freak so thank you for the tip now and also I should be fair to partial freak the whole reason that I love working in front of y'all is that y'all teach me things all the time if this is seriously the big gain for me and streaming I love streaming news it's like having dozens of smart barýþ that's it if one I said I can make dozens of smart people with way more experience than me peering over my shoulder while I work and you all solve all kinds of problems I can't even tell you the number of times that I've been in the middle of a demo and I'm like I don't know why this doesn't work and somebody from the crowd points out I'm like Oh y'all are just amazing it's so much more fun than working by myself right plus it's like the only other person inside my house is my wife and she as soon as I started describing what I'm doing technically she's like Nina do I think I hear the dryer you know and it's not that she's not technical she's technical she just doesn't want to get involved with this so she's not that technical but this guy reeks of MS shell when you say this guy I wonder which guy it is it's not power shell freak though you're saying f1 that's totally okay and if you think that I'm at Microsoft shell Oh buckle up cuz I am NOT alright so you have so many bad stories there people like what Brent why doesn't Microsoft do what you say well hang around and you'll see why Microsoft doesn't do what I say okay so we got Azure Davis - where were we we've got Azure data studio and I said open the extension Spain and now y'all get why I said QlikView extensions Erika's give a damn is busted I've never heard that before that's actually open this is where where you can enable all kinds of cool add-ons that give as your data studio more power here today we're looking for a powerup to quit that gives that will give us the ability to query Microsoft sequel server and so now let's go find it in the list Smoove this over here a little bit so y'all can see a little better so we're gonna click view and then extensions I would click a few problems but oh yeah it only knows so let's click view and extensions and then here we let's go search for sequel server and see I want to give them the bare minimum of things in order to click in and make it work that might be I wonder if they did had I wonder if they added the ability to query sequel server built in or I wonder if that was one of the preview ones let's go see if we can actually query so let's go go into connections and add connection and let's go see if we can connect in database Oh oh my god oh my god they have it now okay so that's good that's actually phenomenal all right so I don't need to add that but I do need to add the rest of the admin stuff I'm so sequel server agent I'm gonna do sequel server agent so let me ah I wonder if there's a oh the admin pack I think I also need does the admin pack have everything that I want the admin pack has everything that I want oh but it may also have other stuff I don't really need profiler and I don't need import agent is a big one that I need agents probably the only one that I need so let's go a sequel server agent PC eglee yeah welcome to the club now well you've been here before I know who you are so we need to go so this powerup gives us we're looking for power that gives us the ability to manage manage sequel server agent jobs so type in sequel server agent in the search box and come back here so we'll type in sequel server agent and then let's see if it Oh beautiful okay great let's go get a screenshot that supports the story we're trying to tell and then new and then doo-doo-doo doo-doo-doo-doo-doo-doo I always kind of want to get it to me yeah that's that didn't that there we go that's fine and then we'll save it save sequel server agent hi Bhushan over on Facebook sequel server agent and then let's come back in over here to do and that media then normally you know normally I'm really good about when I have my own workflow for Windows or for a Mac when I'm doing blog post authoring and move it around just a little bit here and then I usually just drag and drop stuff into here so that works a little better hi Sanjay over on YouTube insert in and clue that's kind of ugly and click the install button which button it doesn't matter Microsoft just likes to make it easier for you by spring identically named buttons all over the screen relax it's not like these extra buttons cost you any more extra this is all I didn't want to go you're absolutely right free to you that's the beauty of open source so now let's go click on it click the install button alright the installation will finish and let's see here let's go back over alright so we got that is there anything else that I would want to tell them to go do well yeah we should probably get them to query the first to get them to query the first sequel server um so let's see I want to get them to connections but I try I'm gonna try not to you haven't me hit this icon over here because stuff like this isn't necessarily discoverable by people as easily I may click by may not because there may not be a thing for connections so I guess the only thing that they can do in order to get to connections is to push this button over here ctrl shift D it's gonna be my blood pressure today's gonna be the day so normally some stuff would be available via the menu up here but it doesn't appear to be available up there so that's fine so let's get have them go to connections and I'm gonna have to go and get a screenshot of that too as well um I'm so hot and bothered so here we go connections button connections button the installation so let's just stop there now you're ready - now you're ready to connect to a sequel server click the connections button up at the top left it looks like this and of course I'm gonna have to say that because deep calming breaths do to do to do to do to do to do so did I not save it I didn't save that and there's a snipping tool save connect oh I did save it it's connections button it's just that Windows Windows didn't refresh this no it put it at the bottom okay ladies and gentlemen a quick reminder as to how Windows File Explorer works over here as you can see up at the top of this column there's a little arrow next to date modified that shows that these files are ordered by the date they are modified so the things that are freshly modified are all up at the top right wrong the one that's brand new the mostly recently modified is down here at the bottom because w.t.f seriously your windows how big is Microsoft is a company and they can't manage to figure out that when a new file comes up maybe that no botsko I don't need to refresh you've been programmed by all these years of really crappy Microsoft products that they need to refresh WTF why is it the end users responsibility to go in and refresh a view when windows refresh that view already yeah Elka did it that's ridiculous this is so crappy this is such a BS operating system anyway anywho let's click view and then no so up here in the view there's a refresh right right right where where's the refresh button I swear to God what a piece of junk this is come on Andrea Ritchie's in here it's your fault for using Windows Ritchie you're like you're missing I'm like this is my third rant in this morning about how bad window sucks wouldn't you think that the refresh button would be under view where the hell he exactly see the owner of your hall are all on wonderful I love ill-will you did a very good job of that right there and BOTS go I like how you said that too so I bet because it's ridiculously unexplored excusable I bet if I right-click in here it's yep be sure of course there is you have to know to right-click in the window and hit refresh what a piece of junk what a just an utter piece of junk insert into post so now I'm gonna align that to the left the connections you can also you can also access the connections and I'm gonna move that around I'm gonna put it over at the left of the first paragraph and then put that up here paste whoops don't do too and then visual let's make sure that area clear looks like the server button all botsko is in by the search bar what the hell search bet that makes no sense it's not your fault kooky that's it's Microsoft's fault obviously on my on my Bosco says on the question on the topic how did your transition from Mac to PC pass oh and Mickey says the refresh button is to the right of the file path the hell how you supposed to know that that's this is like reboot right that doesn't even make sense um Moscow okay so about the transition between Max and the transition between Macs and Windows I actually wouldn't make the transition today if you're happy if you're used no no don't apologize it's totally okay because I do get that question a lot people will see me lose my mind about how bad window sucks and they're like oh so is Mac easier the problem is is it transitioning blows transitioning from one OS to the other you have so many habits that you're used to and you're like why would anyone do it this other way so all the stuff that's inside Windows you're so ingrained into you're so used to seeing it a certain way that you be frustrated if somebody does it another way even if that way seems more intuitive and reliable to other people so I don't know that I would switch from Windows to Mac today and with Mac's transition to ARM processors on the horizon I don't I really don't know that I would switch right now and this is even one of those moments where I was like oh my god should I try and switch back over again but I'm not gonna try and do it instead the switch is heartbreaking all you're like the keyboard combos that you're used to sequel dev DBA says parallels is great for that transition VMware fusion same exact kind of thing so I use VMware fusion it's not that it's any better to me they're six and one half dozen of the other VMware fusion does offer container support I'm not using it yet though I don't have an urgent need for containers right now okay so we've got the connections thing so back over a hat so click on the connections button at the top left it looks like a it looks like a game of kicks I would say that it looks like a physical server but you haven't but it's been so long since you've seen seeing a physical server that the comparison is about as relevant as because I want to say you can play kicks online online to do to do yup through there we go perfect that's so good and for those of you who have not playing get played kicks before alright let's let's see here up down left right and then start and select our enters X and a oh this is gonna be a little tricky so let's see here enter do to do enter one player and here we are so with kicks you control a little oh I guess I can't control them inside here maybe I need to click in it oh it's too late um so I should be able to click inside there oh well anyway kicks is amazing I really love that game so I will be over here playing that in my Mac in between breaks so it looks like a game of kicks as a Nintendo game you can also access connections window by clicking I did I know right there are only gonna be like five of us but Jared Joris you're old enough that you're gonna get that as well so you can also access connections by clicking what did the little pop-up say ctrl shift D control shift D so ctrl shift D just make sure that that actually works in here and I'm going to remove this list of servers and now I'm gonna go say you need to add a server so you're gonna need to point to the server that you're working with so click on the little click on the new connection button click on the new connection button to add a connection to your sequel server and then let's go back and get we'll come back here little buddy and so I'm gonna get a screenshot of the the this tool immediately makes my blood boil doo-doo-doo oh you know I should fill mine in before I take a screenshot I'll fill mine in just so that people can see it I want to say that my VM name is sequel 2019 a Oh huh I thought this was already launched so it's gonna take a second order to launch because the thing with viesta to geocode yeah yeah and I'd like the command palette the command panel it's kind of cool I kind of like that quite a bit undocumented features if exactly I like the idea of the command palette that's pretty pretty nifty okay it is sequel 2019 okay so sequel 2019 sequel 2019 David you're welcome glad thanks for stopping in anyway so the database I'm gonna pick my stack overflow database and I'm gonna pick I'm gonna call it production and let's go get our screen shot again where the heck did our screenshot window go new and then highlight all that in and save it new connection yeah it it's and I think it's kind of it reminds me of the search in max so the search in max spotlight like I can just hit command and space anywhere that I'm at and spotlight search grabs all kinds of stuff across all kinds of applications window search these days is pretty good too whenever you hit Windows search it tells you about all kinds of games you haven't installed and websites you'll never visit shows you ads that's really useful and helpful so um so I use Windows Server yeah I use Windows Server inside only because I want to I want to use the same OS that clients use on their servers just to keep me vaguely fresh I don't do a lot of Windows stuff these days I tend to just delegate that to the clients Windows teams but it's just nice to be able to Lee still use the OS so click on the new connection button and then fill in the details for example here's my server it's connection details and then let's go drag in the eye now as you can tell I have to go down to the bottom now for the new files piece of junk so then go insert into the post so here my server name so server is let's go through and say the DNS name or IP address that you use to connect or that you're weary where your sequel server answers connection requests this is usually the same as the server name itself but if you have if you have if you have things like or you have fancier set ups like named instances or special or non default port numbers non default port numbers you'll need to specify those here so that's the server name authentication type authentication type probably windows try that first if that fails you either don't have get some yoga it's true Ted Ted seems like a good name for a yoga teacher you know that's a very mellow California kind of name Ritchie what kind of yoga do you do I'm now I'm curious because that's one of those things that I've always wanted to pick up at some point please don't say hot yoga just cuz you live in Miami all yoga is hot yoga in Miami if that fails you either don't have access to the server or its configured with sequel authentication education tip you're not no you're not learning yoga from Ted Bundy are you that would be but if they listen if he says that he has a broken arm and he needs you to help him find his puppies don't do just be careful I need you around here this is so terrible I'm going to hell for that one database if you want to run queries against a specific specific user database you can pick it here but otherwise you can stick with the defaults if you're doing whoops if you're doing database administration like we got rid of that guy when I was in middle school well if database administration like attending my fundamentals of database administration course then the database you connect to doesn't matter that gives this database server group server group leave at the default sir group and named leave at default ah I'm gonna say server group leave at default name did you say name or friendly name yeah name this is a user friendly name just for your own purposes for me I'm calling mine production because sometimes my servers have long and complicated names and it's easier to tell at a glance which one I'm connected to if you have lots of servers you wouldn't want to use production you might want to use say production accounting to clarify which server you're on alright so there we go so now at that point they are connected to a sequel server and I'm gonna say if all went well wow that's really odd it doesn't show that what because I picked a database it doesn't show the list of databases Wow that's messed up what the what oh god okay so I know exactly how this happened what happened was is the fine people at Microsoft and I use that term seriously because they're really smart people the fine people at Microsoft needed to make one tool that worked for both as your sequel DB and for sequel server if you connect to Azure sequel DB you can't change databases so you just are stuck in the one database that you're in maybe me fit refresh Oh Jedi Mind gorilla that was really good that was really I have to hand that to you yes that is very good so I get what they did there so all right so let's delete that oh my gosh so I'm gonna connect again and this time so I'll say sequel 2019 windows and then I'll call a production this time I'm not gonna pick the database and I get the list of databases okay great so that's good because it affects how I what instructions I get here so I'm gonna say do leave leave leave at default if you pick a specific database then you won't be able to see the list of databases after you connect okay good all right so I think we're good there so now I can say if all went well you're gonna see a list of databases so I'm gonna give them the Nell take one final screenshot and say if all was done correctly new so if everything was done correctly to do do-do-do do-do that's perfect so save as your data studio connected if everything went well everything went well you'll see a list of databases databases like this and then drag that in from Windows Explorer which is now at the bottom of the list and insert into the post if things did not go well then and you want to get support need to get help here are a few places to look because I and I'll stop for a second SATs it's for whenever I write a blog post choose because I have a lot of subscribers I've got like over a hundred thousand subscribers on the email list and I don't know how many I have until it's been a while since I've even looked at the blog to see how many hits I get on a regular day whenever people find this stuff via Google they immediately want to ask their support questions down in the comments and I don't want to answer support questions on how is babby formed so instead what I want to do is direct people to other places where they can go and get support so those places will be the sequel help channel in slack the sequel help Twitter and the azure data studio channel in slack I could also put them to DBA Stack Exchange comm I guess as well so let's hear a few places to get help and so we'll say the azure data studio channel in slack go here to get an invite if you're not in it yet you pronounce weirdly discord I don't think there's an active discord channel for Azure data studio where whereas the azure data studio one for the community Microsoft actually pays attention to that so here's slack I'm going to get the URL for Azure data studio so we'll copy that out so the azure data studio channel in the community slack and so we'll see the invite is at DBA tools DBA tools dot io / slack okay cool so it's already redirecting me into the room DBA tools io slack is the URL though so I'll copy that out and then go paste that over into the blog post go here to get an invite and then the sequel help channel in the community slack the sequel help chant - tag on Twitter so let's go get the sequel help URL I think I've got it on my slack page and the only reason I say that is because I'm not in that room right now well I don't have it on there oh I need to go at it let me go at it on that page too as well then so I'm now going to go hop into the slack or the sequel help room and join sequel help and people are going to think that I'm going in there in order to answer a question and I am NOT copy link now so sequel help is where you can ask general questions general text more questions and paste that into here paste oops I always do that and then there we go and then there that goes I'm not doing that let's move this out of here there and then update so now we've got the how to do a sequel help community channel let's make sure we got that link in perfect and then the sequel help hashtag on Twitter I have a post on how to use that go sequel help and copy paste that out copy and paste paste there we go just keep in mind that the folks on the other side of the screen are volunteers so say please and thank you a lot when they help you out with your challenges okay so there we go I think that has everything that I want that tells them how to go you install as your data studio to work with sequel server so now let's go dump that out and hit schedule who me okay so we're almost an hour in and of course an hour in because we've spent a lot of time dealing with support questions with Azure data studio and with Windows where I'm about to lose my mind so it's about 6 a.m. here in San Diego so I'll take a break and then when I come back we'll be looking at what I need to do next in the fundamentals of database administration class so we'll take a five-minute bio break I will go refuel my espresso not that having more caffeine is probably a good idea at this point in the day but it is what it is I'm so we'll take a five-minute bio break we'll come back to make it easy we'll come back right at the top of the hour so we'll come back in seven minutes and then next we'll go and dig in - yep Spitfire says coffee time sandy had a tech support question you don't will do is after after in seven minutes when I come back ruled sandy will do your tech support question and I'll do any general sequel server questions before we switch in and start doing the next thing for fundamentals of database administration so see y'all back in here in at the top of the hour in like six minutes [Music] yeah [Music] yeah [Music] [Music] damn [Music] [Applause] it's delicious [Music] yeah [Music] yeah yeah [Applause] [Music] yeah [Music] [Music] here we go [Music] [Music] all right welcome back Oh a perfect timing Naga thank you for subscribing very cool right Bosco says looks like useful and sanitized oh wow oh I see how you're talking I think y'all are talking about like as your data studio yeah so as your data studio is one of those cases where Microsoft's just slathering the azure name on everything and it's kind of like how power was 2 3 years ago was like power pivot power bi power desktop power you know all these things power query yeah now evidently the new the new hot sexy keyword Iran sometimes I think the people at Microsoft must get bonus by how often they use words dotnet they used to like several a long time ago was like dotnet by IO and then it was nothing with uh that sandy you were absolutely right Santi that's true I and I a lot of it is decaf like this happens to be decaf at the moment believe it or not I know I know yes decaf espresso but for me it's a lot about the taste more than it is about the caffeine level I jump out of bed and I'm like ready to totally go cold world ups do all kinds of rants sadly so sandy you had a question earlier what was your question remind me of what your question was and copy paste the question back in again and then I'll go I'll that one before we go on and tackle the next piece inside of our fundamentals of database administration class figuring out how we want to go do that of an azure pencil and I keep forgetting to measure oh my god see what I did there I said as your pencil instead of Apple pencil oh my god see Microsoft's even getting to me yeah so died loved it thoroughly that's true to remember to post your friends question that's true sanity says I have a query where I have six tables joined I am getting logical reads of 4,500 reads for a table which only has three rows what am I missing oh sure so what's happening is is when you read from right to left top to bottom so sequel server decided to do something else first it didn't decide to hit that table with 3 rows first and what it probably did was it said ok for this other table that I'm gonna go sus first for each row that I find in there I'm gonna go do a lookup over into this table that only has three rows it didn't expect to find as many rows in the first table as it actually did so as a result it made bad Esther and made bad decisions on everything else that it did throughout the plan Oh Matt you have a point I'm only supposed to use the sequel server voice so let's do a demo real fast and I'll use the sequel server voice so I'm gonna switch back over to sequel server management studio although I guess I could do this in azure data studio come to think of it so let's go do it in sequel server management studio I'm gonna say I'm gonna switch over to the stack overflow database I'm gonna say select star I'll zoom in to so that you can see it select star from dbo vbo users you where you location equals Miami Florida so let's start with this one a sequel server go tell me how many users are in Miami Florida and so sequel server goes from right to left top to bottom the first thing the sequel server did to say well I'm just gonna go do an index seek in on the location index and I think I'm only gonna find 13 rows how many rows did you actually find sequel server but more than 13 so that that ends up backfiring and it'll cause a problem in other parts of the execution plan so let's say just to say that we're also gonna join to another table let's say we're gonna say inner join dbo comments hi good morning Bert comments see on UID equals C user ID so now all I've done is I've added joined to another table so what sequel server is now gonna go do is he's gonna go find all of the users in Miami first and then go look up their comments so we read from right to left top to bottom first thing again sequel server mom only gonna find 13 rows here inside the users table for Miami so I should go do this first and it'll narrow it down now he's wrong about that which also cascades into all of the rest of his estimates he thought he'd only find 13 people so instead he when he went to go find those 13 people's comments he ended up doing a whole lot more work Santi in your query what you do is in the plan read from right to left top to bottom find the first place where estimates versus actuals became way off and that's the part that you want to troubleshoot and it happened way before the three rows table now I will say though that 4580 logical reads is nothing like I would not even worry about that at all sequel server can rip through 4,000 logical reads in less than a second easily so that's probably not the big bottleneck but hopefully that that answers that one okay next up w robinson so or dope w rabbir some see I keep seeing your name and thinking it's that this is what would be a good alternative in your experience to stop saving files into a database for me I'd rather save the URL in the database like save the file name of where the file is being stored in the database but then store the file in something called up and you might have heard of this before a file system so a file system is is where you save files I know right is it awful of them so Sandeen that queries don't have an estimated time though do the actual times maybe but not an estimated time it's exactly Android says so a pointer database exactly yep so you just save the pointers in the database but you save the actual files in something called a file system file systems are great for files and databases are great for relational data that's kind of how that works so D Toby says the file systems can get out of sync with DB so sort of what you do is you always only append new versions you always only add new files you never change existing files always add new versions so that way as you're adding new files and you're saving the new pointers into the database if for some reason you need to restore the database to an early your point in time there will be extra files in the file system that's okay though the point is if you had to rewind the data to a specific point in time the database won't know about those files and that's fine it's okay if there are extra files it's just not okay if there are files missing and you might be saying so what happens if we need to restore the file system from a backup that's where you start to do things like file system replication Sande snapshots I'm a really big fan of all of those and Ricky said earlier saving RAW files into the database is one of my biggest regrets just to be fair to if you're doing like less than a terabyte worth of stuff it's not really that bad yeah elapsed time 289 milliseconds here all illustrate 289 milliseconds for you so if your users are coming to you to tune 289 milliseconds to get it to be faster that's cool that's how I got started working with Stack Overflow too as well but when you get down to tuning 289 milliseconds to a lower number that's where you don't want to rely on free questions that you post on the internet that's where you bring in someone for hands-on consulting because it's way beyond something that you're gonna get really quickly in a Google type answer d Toby says it's use Oracle used to say it was faster than a file system to put files in the DB that that's true a lot of relational database platforms will say that the speed of putting it in isn't the problem the money of putting it in is C Oracle costs forty seven thousand dollars a core for Enterprise Edition and make sure and make sure you understand that correctly Oracle costs forty seven thousand dollars a core for Enterprise Edition forty seven thousand dollars a quart which means to license my iPad 50 grand a core times eight cores four hundred thousand dollars to license my iPad Hellyeah should be faster than a file system should be faster than the Millennium Falcon at that kind of price tag so the speed isn't the question it's a matter of how much you're willing to pay for it and most folks aren't willing to pay 400 thousand dollars for a file server a sequel dev DBA says that course description is that published no it's unpublished it's not something that's publicly available yet because I haven't started selling it yet and I don't have it like a date on when I'm gonna go publish it because it all just depends on my workload this is the first time that I've ever started developing a course publicly like showing it to y'all as I'm building it and of course there there are pros and cons with that if I show you the course while I'm building it that also means you know the contents that are in it so that's kind of a wild thing there too I'm kind of taking a risk to some extent to doing the development out in public so those of you who it's kind of weird if you think about a Venn diagram the people who follow me on Twitch versus the people who would buy the training class it's not the same circles and you could even say that you could even say that someone could go through and watch the entire twitch series of me building the class up rather than watching the class itself and that's true it's just that they would have to deal with a huge amount of time because it takes so much time to build the course and Jim you're absolutely right yeah Andy totally that I'm working on that it's just taking quite a bit of time because I have to focus on you know 95% of my customers who aren't governed by the gdpr versus the 5% of them that are and it's always really weird people for the longest time people from the EU were like if you just let me give you my money I would give you all of my money and I'm like okay so here the consultant toolkit it's out there on online and they're like no that's not what I want and I'd be like okay so we're doing a training class in the UK at sequel bits for example they're like people when it comes to actually laying their money out they're kind of a little different so okay so now let's go on to the next part of writing the class I'll put the the XP put the outline back up on screen so here is as a reminder of the class that I'm working on fundamentals of database administration my target attendee what she knows what she doesn't know so I've decided that one of the things she doesn't know is writing and running queries and she doesn't know sequel server management studio at least not to the point where she's comfortable with it so if she doesn't know how to write T sequel and she's not necessarily comfortable with management studio what do I want to teach her in and I've decided that I want to teach her in Azure data studio which is why we just installed that so now the first module that she's gonna learn is how to configure a brand new sequel server I am NOT gonna step through the process of installing sequel server today in order to gather screenshots but what I am gonna start doing is working through an azure data studio a notebook on how to go figure out how to learn how your sequel server is configured so for example I'm gonna start jumping down and cherry-picking some of these things which drives to install it on so like if she wants to know where the stuff inside her sequel server lives today how do I want to find that stuff out inside Azure data studio so I'm gonna start by just kind of starting out building a notebook here I'm gonna say file new notebook and for some of you this might be the first time that you've ever seen an azure data studio notebook so I'm gonna say go add a click on text to add a text cell learning how your you're discovering discovering how your current sequel server is configured so you've got an existing sequel server and you want to learn more about how its configured let's start by seeing where the data and log files live so now with Azure data studio what you can do is you can have a mix of markdown which is that text that I just started putting in there and you can also have code now they just added a new freaking design to this so it's a little bit different so now I can write type in a query inside here right inside I can say select star from sis databases for example and then I can go click the Run button and then I'm gonna go connect to production and so now I can get one document that has both code and queries in line so then I can have B go hit the Run button and it'll go update and run based on whatever sequel server she's connected to so what I'm gonna do is I'm gonna ship the entire class in a series of azure data notebooks as your data studio notebooks so that that way I'm gonna have the students open up these files and go okay everyone here let's first talk about discovering how your current sequel server is configured and then she's we're gonna have people run it and I'm gonna talk about the results that are in there and talk about what that means for her in terms of administration to some extent to some extent this is kind of how SP blitz got started yeah yeah PowerShell freak you're absolutely right that that's a something that I love in for it as well plus two students can send me their notebooks if they want to get a second opinion on something to some extent this is how s people it's got started with SP blitz I used to give people a script and say go through and run portions of this and interpret it and understand what is going on inside here for this for this one it's gonna be much more about teaching the fundamentals of database administration inside of here so in I'm gonna go through the outline and I'm gonna pick out a module that I'm gonna learn or that I'm gonna write together with y'all I am NOT going to write the modules in the same order that they're going to be delivered you would think if someone's gonna write a book for example if you're Stephen King or Danielle Steel or whatever if you're gonna write a book you start with the first chapter and move your way through that's not what you do you write an outline so the outline there of what I've started out is this markdown file so this markdown file here with what I'm going to teach her just because this is the list of what I teach her doesn't mean this is the order that I'm going to teach her in for example the first thing that I'm gonna have to get her comfortable with is running queries inside of Azure data studio notebooks so I'm gonna pick one I'm going to cherry-pick one that I think is gonna be easy to write with y'all together hello how to configure and test backups oh I like that a lot welcome to the club Carlos so how to configure and test backups I like that a lot so let's say back up here how to whoopsie-daisy I gotta click in here how to configure and test backups everybody thinks they don't want to lose any data Thanks they don't want to lose data and they never want to go down thing is backups are hard let's start by looking at how your current backups are configured you sequel server stores Aiyappa welcome to the club thank you thank you sequel story shorter stores history about your past backups in a system database called MSD B we can see your sequel servers most recent backups here so now let's go for this I want what I'm going to go do is I'm gonna go pull their most recent full backup for each of the databases that they have now one thing that I wish I had was in intellisense and intellisense in Azure data studio is kind of the this is like the dumbest sense that I've ever seen so if I say select star from sis databases and whenever you query tables you like to use aliases so I'm gonna say DB space and then if I ain't go inner join em SDB when I should do master master sis data bases inner join em s DB DB Oh backup set it all of a sudden like I lose all kinds of intellisense inside here I don't understand what's going on with this whatever that is a valid object dumb until out yeah out elephants stupid a sense so MS DB DB oh and I back up and I just get nothing back up as a keyword that's not even what I'm looking for so what I'm gonna do is I'm gonna go switch over to until a nonsense that's actually that's actually pretty good Intel until a nonsense so I'm gonna go into MS DB start a new query and I'm gonna say select can i does this zoom in and out no I don't want the windows magnifier I want the different sizes in alright let's go see let's go see how bad this is let's go into file preferences and settings let's see if there's a zoom mouse wheel zoom well I don't have a wheel I have a trackpad okay just the little let's try this let's just try Windows zoom level well let's try font size let's try font control key middle mouse button or wheel yeah but there's no bus button or wheel font size controls the font size and pixels let's try that or font weight to finger or three finger swipe on that trackpad okay so let's see no that moves my inner out nope nope god bless you ctrl + + hey alright that's cool that's better now pinch or stretch it's so funny so let's see here so now we've got that well let me make sure that I don't screw anything around with the settings there all that what the hell that didn't even oh my god I gotta go look how comically large the fonts are up here and then one finger swipe so let's say select star from sis databases this is so terrible god this is awful it's as if they made everything you don't care about bigger and they made the one thing that you care about really really tiny there was a there was a website that was kind of funny let me go pull it up so drunk usability testing there was this guy who would get drunk and he would review your website while he was drinking so you would pay him and he would go in and try your website as he was drunk and record the whole experience so that you would see what it was like when a typical user was navigating your site I love this I'll get very drunk and then I'll review your website I'll send you your my screencast you'll get a video of me as a new user and as a designer trying to keep it together and I really love that because I feel like I'm doing that sober sometimes I'm like this is so dumb why would you make the text the thing that I'm trying to edit this tiny and then give me an icon this large for connection that's the size of my head over there that's ridiculous whatever of all the things to make bigger I don't give a rip about that and they're gonna be little roots you should change the font size you should give me an easy zoom yeehaw dB [Music] so let me just show you what just happened there that was so ridiculous so new query when it holds new query now all right now it's things so damn big how do I get to a new query without blowing this whole thing out new query and then over here lose this select star from sis databases I'm gonna type DB and hit enter because I would like to start a new line now I don't want any of the stuff below you'll notice that dbcc is highlighted but when I hit enter the hell alright so sis databases DB no I just want to enter inner join em s DB DB Oh backup set BS because I like naming it that so now this is gonna give me the backups that are on here and then I got to do an on on it's gonna be terrible to watch me do that DB database ID database ID equals BS database oh it doesn't have the database ID in there it has database name so I'm gonna have to go back and say DB name you can his shift and enter to overwrite that why should I have to hit shift and enter though so let's see here and Bert says add a space after DB I think that might have been well I'm not gonna go back and trigger my my ranting again there for a second just use vim it's better it can't the autocomplete can't be any worse than what we're dealing with here alright so we've got this at least gives me my oh I don't want an inner join oh I can hide this results pane and I never remember what the shortcut is so let's see oh my god this is gonna be terrible ctrl - let's zoom back out because I want to figure out how I hide the results pane is it QlikView an output no that's not it how do I hide the results pain inside here appearance where's the how do you hide the results bar to church nope it's none of those okay so let's try how do we get to the palate again and the I've got customized on mine command palace shift control shift P let's try results just toggle query results Oh shift windows are ok thank god there's gonna be a lot of swearing going on - so this is - just as a side note this is the challenge with developing content in a new medium I know that if I built this class in sequel server management studio it would be faster for me so part of what today's experience is about is me deciding whether or not I want to deal with slower class development time in exchange to do what I think is probably gonna be the right thing for the students I think that putting the class stuff inside a notebook is the right thing long-term for the students because it'll give them a better experience of running code and reading the explanations of it but it gives me heartburn as a teacher because this tool is a piece of junk right now it's getting better it's getting better all the time I have great faith that it's going to be amazing someday it just becomes a question of will I have a heart attack before it becomes good enough to use or will this tool kill me so back over here on our query I'm not gonna try to zoom it in any more because it was just freaking unusable inner join I don't want an inner join I actually want left outer join tree tree ass says I see an editor zoom action so editor zoom editor font zoom in and it has no oh oh hmmm yeah that's better oh that's much better hmm thank you so let's go left outer join so now little by little exactly and you know this is how you build knowledge this is how we all build knowledge about stuff so we've got this I said that I wanted to switch to left outer join because not all the databases may have backups so I'm gonna say DB name as database name now I want to get the most recent backup I may I actually screw it I'll just get all all the backups that are inside here before a certain date will go like the last 14 days or something like that Cindy says SMS is far better it is but I think it's one of those once you're used to it it's not so bad as your data studio plus I really want this management studio doesn't have this if management studio had this I wouldn't leave management studio so yeah left outer join backup set and I'm also only gonna get the full backups BS type equals if I think is full I can never remember if F is full or D I think D is full full backups only I think B is full so let's go through and execute that oh when you hit that it doesn't actually execute that's fine yes the thing is Doug I don't want to if I go back to the what she knows and what she doesn't know she doesn't know how to do T sequel server or T sequel or management studio and I think most people don't understand how projects work either or solutions work in management studio so I'd rather teach her with Azure data studio because then she can use it for PowerShell to where she can't use management studio for PowerShell yeah D equals full and I equals ad if so it was a command shift what was the thing to hide the results toggle query results shift Windows R okay so that's full backups only hi hey soos welcome to the club order I'm gonna go order by the database name then by their BS backup finished date descending so this will get them the most recent backups I use my mute button there it was the first time did that work oh it did it oh nice very nice I've got a mute button on my stream deck now and so I was trying to do that for things like you know coughing and sneezing where Thank You surly to if I appreciate them and I need all the blessings that I can get as evidenced by my rants where be s backup finished date finished date is greater than date add D minus 14 get dates so that just so that we're getting backups in the last 14 days and then run all right so we don't have any that's cool let's what man I'm never gonna remember that it was wind shift our shift windows are [Music] I'm not on Windows there are the Linux users my friends from Microsoft who are watching this and taking notes you should know better than to call it the Windows key all right so shift I'm gonna I don't know what Windows is on this thing this is that's why I never remember this shift windows are let's try if it's option shift this is also another living hell of keyboard discovery so if you don't know what the Windows car he is in the pub says it's window shift arm right but there's no Windows key I'm not on Windows I am on something that doesn't have a Windows key and of course it's not going to focus correctly until I get all the way out we don't have a Windows key someone else set a system key there's no system key either what in the holy hell is wrong with these people so there's the thing that really frustrates me about stuff like keyboard discovery now if I'm gonna go do that I'm talking about randomly hitting keys and who knows what kind of backfire that is cuz we're gonna trigger a shortcut that I don't even know shift Windows R so let's try shift option was it are yeah are so let's try up ssin shift are nope that's not it command shift are that's it command shift are on max alright so back over here one of you asked earlier strands kiss says why is the BS the type thing on the join here that's a great question you could put it either on the join or on the where Clause I'm fine either way it makes no difference here in this case cyborg says aren't you running on a Windows VM I wonder what it displays natively under a Mac yeah that's a great question but the thing is the type of Windows VM that you're using doesn't dictate the kind of keyboard that you're using it's really cool these days you can run machines on different kinds of hardware in that cool amazing it's me being sarcastic teddy says are you using dual-boot no I don't use dual-boot I run a Mac Pro which is like I've got 32 cores and 96 gigs of RAM so and I've got 256 gigs more on the way but it's just it's really fast to run VM so I'm totally okay with them okay so this gives me my most recent full backups my most recent 14 days worth of full backups and I should say BS backup finish date so that's fine and now but for me to really test that I need to know whether or not I need to put some data into here I need to have some backups so I need to say I need to go take some backups let's go back over here wasn't there supposed to be a list of oh no the databases is under here okay so let's go take a backup let's take a backup of this guy I don't honestly know I've never done a backup before inside of azure data studio and this isn't the way that I would usually do backups I'm usually used to doing them inside sequel server management studio so okay that's not bad it's not bad at all oh and there's a script button yay good okay good you know I'm gonna stop for a second here I'm gonna stop for a second here JT lays uh thank you for the subscription I'm gonna I'm sitting here going I'm sitting here going like my blood pressure is rising with each new dialog box that I open so like dealing with doing a backup in here if I'm going to teach someone how to manage sequel server if I'm gonna teach them how to manage sequel server in the year 2020 I don't think that I'm going to be prepared to teach them how to do all of that in Azure data studio because here's my other worry here's my other concern my other concern is that the dialog boxes are going to rapidly change over time that the dialogues for things like taking a new backup or wrist bring a backup may not be in the same place in the GUI three months from now or they may not have the same look and feel three months from now that also means that I would constantly have to be updating things like videos and screenshots and my goal for this class is actually to not do it live my goal for this is to do it live once but then just only sell the recordings Oh Thank You Jodi I appreciate that welcome to the club so with that I don't know that I really want to use as your data studio for this I was really thinking that that's the best user interface because it's gonna let them jump back and forth between powershell and t sequel but you know what's really funny is just opening the back up dialog made me remember that all the times that I've tried to build stuff I'm gonna give you the examples power bi so a long a year to three years ago I shipped a power bi first-responders kit dashboard for DBAs the you could go use power bi to slice and dice the numbers out of the first responder kit as people it's cash SB blitz first all those kinds of results so I shipped that and then I just continuously got training questions how does this work in power bi how does that work in power bi the thing that you I will i yeah I'm the ill well I think I might be with you the so people were continuously asking training questions about power bi and I had to keep going back and updating my notes because things changed all the time in power bi cuz it was a relatively new tool and Microsoft was investing in it like crazy well that's the same as as your data studio and if I'm gonna build a piece of training that's gonna be relatively timeless that I'm gonna be able to record once and sell for an extended period of time and rely that the contents and the training are gonna look the same I have a terrible feeling but I don't think it's gonna be a sure date a studio and that they know they really hit let's look at you know they seriously are like Vicky harp and our team are investing a ton in Azure data studio and it has a ton of cool new features but if I'm gonna invest in something for the management side for teaching people how to do database administration I don't think I actually want to ship it in an azure data studio notebook in July so on July 6th of 2020 I think it's too early and that kind of breaks my heart because I really wanted to do this I really wanted to do it as a notebook I really wanted to do it but this is also one of the risks of me learning this stuff and teaching it to you live as we go through it is that I'm gonna be your so you'll see me make some of my changes and like shift gears and change ideas I don't think I want to use as your data studio I don't know I should ask y'all what you think because as I look at this like I love as your data studio for writing new T sequel queries but ill-will says if you know as the semester you can navigate 80s when you need to the terms are more or less similar that's true if I'm gonna teach someone something though I kind of thought I was like oh I'm gonna teach them as your data studio because then they're gonna be able to use it for PowerShell but at the same point I'm like I'm not teaching a PowerShell course I'm not going to go down there Albert welcome to the club Thank You Albert appreciate it so Bosco says you have a point with the constant change dito ve actually it's driving you too mad trying to create the content that's totally true and I don't want y'all to think that I'm doing that for show either if I'm doing that at home like if I'm building the content at home I see I have the same reaction I'm like son of a except I curse a lot more when the cameras aren't on I'm Doug says how about management studio having notebook capabilities I would love it if it would if it was true it's just not true you know they just don't have notebooks inside of sequel server management studio and they don't appear to be investing that much and as in management studio right now and I'm ok with that because you know what it works for things like management it does work Jedi says in my opinion it's nice to know that you can use as your data studio but it's better be strong in a baseline tool and know that there are options not to be a expert in all of them and that that's my fault period my fault that's my thought with Asher data studio - I don't usually teach stuff about Azure data Studio B I use it like seriously 50% of my day is probably spent in Azure data studio between working on my DMV scripts between working in sequel constant care whose back-end is in Postgres so I use it for that um no Jedi mind gorilla that's good that's that's true alright so you know what I think I'm gonna do is I think I'm gonna shift gears and I think I'm actually gonna write this I don't think I'm gonna write it and as your data studio I think I'm gonna keep that blog post just cuz it's an easy simple blog post and it doesn't hurt and I'll just you know put it in my queue it doesn't have to go out on July the 9th I'll just put it out of my queue for when I have this'll be that's funny for those of you who watch the stream you'll be like oh I know that Brent's running low on blog posts cuz he finally ran that post on how to install Azure data studio now but I'll put it in the queue for December but that's it good PowerShell freak says in my opinion management studio is good for beginners because I think every DBA uses management studio you want you okay so PowerShell freak you have another good point there the other good point that is kind of behind the scenes in what you're saying is that if they're gonna go Google for help if they're gonna go Google for how to help to get common tasks done the thing that they're probably gonna find answers in is probably going to be management studio because there is so much documentation out there on how to use management studio for common DBA tasks yeah I think that is probably another thing I need to consider okay so let's stop here and we'll do a bio break before we do I want to give a quick shout out to this week's sponsor so this week sponsor is red gate red gate has a new survey out about the state of database monitoring for things about what are the kinds of problems that DBAs usually run into what kinds of sequel servers or what kinds of other database servers are they hosting out there you can get the report totally free over a Brent Ozark aam /go slash read it Brent Ozark aam / go slash read it gov DBA in talking about the tool choice to says what helps them grow is a database administrator that's an entering question cuz I could argue both I could argue that as your data studio will help you grow in terms of being able to run a PowerShell but also you know what's kind of goofy is if you're gonna learn PowerShell I don't think you're gonna learn that with Azure data studio either I think you're probably gonna learn that with some other front-end tool so I don't know that I'm doing them a service by getting them into Azure data studio I think to some extent if I'm teaching people how to write queries for the first time I think that might be a course that's a better fit for Azure data studio but in the meantime I think I'm gonna have to bail on the azure data studio for the purposes of building this course I don't think it's going to work okay so we'll stop here we're gonna take our next five minute bio break we'll take a five-minute bio break and when we come back I'm gonna try to regroup I'm gonna walk around and think as I go and make my espresso I may stop the stream he I don't think I'm gonna stop the stream here for today I might well we'll see let me take a five-minute that's the whole point of taking a breather as figuring out what I'm gonna go do next so we'll take a five-minute breather and when we come back we'll go figure out what we're gonna do next so I'll see you back in here in five minutes [Music] you [Music] [Music] [Music] [Music] [Music] [Music] [Music] okay I know exactly what I need to do how it came to me as I was waiting for my hot water so I microwave hot water whenever I don't want to drink espresso just plain hot water oh thanks glad you like the webcast I'm uh Asif Asda says reading the red gay report it looks like sequel servers are most far in a way use database bingo exactly it's it's kind of a readership bias just like when you read my salary surveys I do an annual salary survey in the database industry and I ask people which database they're working on as part of that sequel server is the biggest one far and away but it's just cuz my readership is based on sequel server people but in knowing is you if you go in knowing that then it helps to make it helps understand put the rest of the stuff in perspective that you're reading about the kinds of problems that sequel server DBS are having specifically and I find that really helpful because there's a lot of stuff out there targeted at other platforms and I'm usually more interested in sequel server so I walk away and I was like oh I know whoa L Getti welcome to the club thank you I appreciate that thank you very much I was like how it makes it look like he's running after me I always have to get it quite right or maybe he's giving me a backrub maybe that's how that works um so let me put the abstract up and I'll tell you about the Eureka moment that I've had I'm gonna close as your data studio for now so the class abstract I said it's fundamentals of database administration and I talked about B and what she knows and I said what she doesn't know is this so I walked away and I'm making my hot water to have over here and I'm like okay so the whole reason that we came to Azure data studio was that I decided she doesn't know a tool and if I'm gonna teach her a tool I I want to teach her Azure data studio because I can use notebooks well I've kind of come to the conclusion that that's probably not the case that I don't think I want to teach her as your data studio for a number of reasons so if I do want to teach her T sequel so here's what I need to do is I need to start with what she'll learn how to install seek server management studio and connect to her server how to run a diagnostic query not necessarily not how to interpret the results now if I'm going to teach somebody how to go open up sequel server management studio and connect to their server you know what else I want to do if they're a systems administrator they probably have sis admin rights on the server if they're just getting started fumbling around managing their sequel server I do not want them accidentally breaking something I don't know about you but I've totally had a bunch of those moments in my career where I've forgotten to highlight the where clause or I copy pasted a query from the Internet and I didn't exactly understand what it was doing and it had a side result and it backfired so the next thing that I want to have her do is how to configure a read-only account for her to use when learning how to switch between the read-only account and her dangerous account I don't know why I put that in single quotes dangerous a powerful account so as part of that when I say create a read-only account for her to use while learning it's got to be powerful enough to be a systems administer to see the kinds of stuff that systems administrators need the the-- so we're gonna have to figure out what permissions we need be to grant herself I think this is what I'm going to want to do instead now whenever I'm gonna go write training material whenever I go to write a new piece of training material something that I think about a lot is is there already something good out there is there something already good out there that I can point people to like how do you connect how do you install sequel server management studio how do you connect a management studio to production how do you run your first query and you would think as common as that is I mean that's a pretty mainstream kind of demand that a lot of people are gonna need there's probably good info out there on that already let's go see because if there is I can just link directly to that and make it part of the class prerequisites and I want it to be good I don't want it to be garbage I don't want it to look like it was recorded in somebody's basement so let's go see what we can find so let's say and let's start by googling the same thing how to install sequel server management studio the installation is pretty straightforward wow that's weird do jô I've never even heard of this down under geo solutions what what what what that's the number-one-ranked thing on Google Wow okay so just also as a side note if you're a blogger if you search for something and you're disappointed with the top-ranked result in Google you could own that that's something that you could own then they says has a very good SEO yeah well so could you you could write a good post covering exactly that same thing and as a father says it looks like the structured SEO thing yes so and they're not keeping it up to date because that single server 2012 Express let's go see that again ah ha ha ha romera Rui Miele Google is ok get better results in Bing let's go see so let's see Bing calm and let's get the exact same result let's copy and let's see how it does let's say [Music] okay how to install sequel server developer edition install management studio let's go click on this dang I forgot to use my mute button so I don't think that that's very good either I don't think that's very good either I would argue you oh God thank you thank you for catching I love how y'all jump right in there good catch so this is an even management studio if you go click on this everything that's up here is sequel server development edition and if I come down further you have to come way down in order to catch management studio you have to go past all kinds of crap so here it is way down here and is that even still the correct link at least that's the correct link so I for me Bing is not any better here for me it's especially when come on are you serious really the best of the web that's what you call the best edge you think you're the best of the web I'm not even going there okay so both it looks like how to install doesn't have a great post on it I might want to do that but let's say how to use sequel server management studio oh there are tips and tricks connect to in query oh yes good oh lesson one what do you mean lesson one [Music] hmm it's okay it's not very good I don't know how many are the rusty y'all are on tick-tock what is it I like it but not a lot I don't like it that's so we learn to spell us masks that word I'm not sure I this isn't bad it's not terrible but I think that I could write a one-page getting started I say one page a one post on how to get started with sequel server management studio let's try how to get started how to get started with sequel server management studio and installed sequel server now what and they're making a table what you know that's not enough idiotic getting started with sequel server but it's not management studio all right I think we may need to do this install connecting use SSMS Oh liquid web what is s SMS and the screenshots don't even work some of them work oh that's probably ads they're probably ads sitting holy cow that's a lot of ads wow that's amazing yeah that are there screenshots don't work okay so I think we have what we need to do here if I go back to what bees I wanted to go see if she if there was a thing out here that would that I could point to that would be good that I would be happy with that would have a straight to the chase here's how to install it here's how to connect to your server go so I think that I'm gonna want to write I'm talking out loud here so I'm not don't hold me to this yell but I think I might want to write a one week series of blog posts on how to install sequels or how to install management studio and connect to a server how to create a low privileged account and then how to switch back and forth between that low privileged account and your regular account maybe it's not even a whole week maybe it's just one long post start to finish on how to get started with sequels our management studio in ten minutes something to that effect so I'm gonna call this as prerequisite or I'm gonna say what she already knows accuse you how to fit install adventureworks yeah but I'm not I'm not gonna so installing invention works as things like for people who don't have a database yet my target audience here is for people who already have a database server Conti arias no because that's not fundamentals that's not a fundamental type thing that's something that's much more advanced than fundamentals it's a good question it's just beyond the fundamentals so I'm gonna say we're gonna now change the design to this course we're now gonna say that she does know how to install sequel server management studio how to create a low privilege to count and how to use it cuz I'm gonna make that as part of the prerequisites it's still gonna be up here in what she doesn't know but it's gonna be down here I'm gonna cut this stuff out and I'm gonna move it down under the prerequisites section oh I don't have one inside here it's up in the the course sales page so prerequisites install SSMS read my post on how to connect to a sequel server create a low privileged account and switch between the that up here we took the low privileged account and her regular account so now we got to write that post so what she's well let's stop for a second here hold on a second so what are we going to do next I need to write a blog post that covers how to install sequel server management studio which is gonna be like a one-line kind of thing you go download this step through the wizard and you're done why you want to install it on your desktop not the sequel server management studio itself and then how to create an account when I write the post that says how to create an account we're good right now I'm gonna start with just view server state permissions but as I go through and write the fundamentals of database administration post the privileges that she gets may need to change over time so I'm not gonna publish the blog post on it live yet I'm gonna hold that for later so let's whip that one out so let's say add new and the title may change I'm gonna do a little SEO to figure out how I want the title to look long term there we go so how to install how to get started with how to get started with sequel server management studio so you're in this post I'll cover how or we'll go big in the big picture you need to install download download the free Microsoft sequel server management management studio connect to your sequel server create a low privileged account so that you don't accidentally drop objects and then learn to switch back and forth between the low privileged account and your regular one let's get started the first step is really easy go here to get the latest version of SSMS and install it alright so let's go get the official Microsoft link for download sequel server management studio and then you and copy you out copy yeah it's over here I'm over here dad download the free management we'll say accepting the defaults and then connect to your sequel server okay so good so go here to get the latest version of SMS oops and install it if you already have a version of ass mess the Installer installer will automatically update it AJ can you you're welcome can you be more specific in terms of what do I think about just because it's such a general question you know Doug it's really funny that you say sans the version link and I was thinking about that exact same thing and I was like do I want to tell them about that and Doug since you mentioned it I'm gonna do it I'm gonna take out the view version up there so that the post is a little bit more timeless because I want to make sure that it always points to the latest version so it was funny I wasn't sure whether or not I wanted to go there and Doug good for you for bringing it up if you already will update it to the latest version it doesn't matter what version of sequel server you're running in production you always want to run the latest version of SMS which will include a ton of bug fixes there's a there's a little bit of a caveat to that I should say as long as you're running as long as you're running a currently supported version of sequel Server 2012 and newer as of this writing yeah okay alright so let's see that's good so the first step is really let's make that let's get started first step is really easy alright so then now we're gonna have to go and get to their connecting so let's change this whenever I'm building a blog post for the future I just stick it in December Oh 8 15 in the morning okay and then for come down here into production practices because that's where I'm gonna stick it and say save agent says it seems with as your sequel server on-premises installations will still be available as how we know them uh nowadays yes yep yep so so we got the first step done now connect to your sequel server after now step to connect to your sequel server after launching SSMS you get a connection dialog so let's go to do do do do do ed angry so let's get a picture of the SMS connection dialog will close that no close this disconnect and connect so here's what the connection dialog looks like so let's go get a screenshot of that because I think that one's pretty stable I don't think that one's gonna change too much over time so new and then grab that guy do to do to do and save is SMS connect and then pop back you know what I'm gonna get it with a border - I'm gonna get it with just a little bit of the border so that it'll pop just a little bit more when I'm looking at the blog post so SMS connect and save yep to do - - new - new - to - MS connect there you go and insert into post the server name hey you know I can steal this from the other post we just wrote I already have some wording in there about the server name so let's see how to get started how to install Azure data studio hey and that's good because while we're in there I'm going to change his production or his publication date to December December and okay and update so in here let's come down to the connection piece and let's grab that copy oh hey I get the authentication type in to copy and paste server name and authentication server name and authentication you might have a user name on a post or a username and password password on a post-it note somewhere like from the person who installed it yeah ill-will you're right except for there was a little problem what we discovered whenever we want to launch snitch so the snip and sketch I'll give you a second I'll move it around so that we can see it a cipher so when you click this snip and sketch it's the world's crappiest pop-up there's not a genetic close there's not you can't click OK that's just so crappy it's ridiculous yeah exactly so you missed a whole rant if you ever want to go back and watch the videos in here I lost my mind at that one and it might have a user name of si in that case go ahead and use that for now in the next next Spitfire with the paper bag if in the next step we'll set you up a low privileged account click connect and you'll be handsomely rewarded with a window that looks like this and we'll go back over here and go connect oh you know I forgot something up over here in the installation piece oh I do need to make it a little bit longer one down Lopes download and install SSMS but not on the server itself so let's do this this do this installation on your desktop or laptop not on the sequel server itself over time you'll learn that running SSMS as with any other app on the sequel server itself will slow it down you wouldn't remote desktop into the sequel server and start playing fortnight now would you don't answer that you probably would you're the kind of person who reads this blog after all and I let's just stop there install s SMS on your desktop o own and open are you just following oh no it's always oh there's another person always open who's just absolutely brilliant in here okay so we got that so install connect to your sequel server so now you'll be rewarded with a screen that looks like this let's make this a little bit smaller so that the screenshot doesn't suck so bad and to do to know whoever gives me a brilliant I like you anyway you're my people so here we go wutface you enjoy my sense of humor the people at Microsoft do not share your enthusiasm oh so now let's grab this and let's take a screenshot of that little fella doo doo doo doo doo doo doo doo doo and say SSMS and and then let's put that in here into the blog post and drag that over here insert it into the post okay so you'll be handsomely rewarded with a partner now you're able at this point you're able to do all kinds of dangerous things we need to fix that so you don't do something stupid so what with step 3 what I call it step 3 was to create a low privileged account so 3 is create a low privileged account create yourself alright so we'll go here and call this let's save our work cuz I'm just a little bit paranoid so now I need to go create a low privileged account now one way that I could do it is with the GUI um I could walk them through creating something in the GUI but honestly there are gonna be so many options inside the GUI and I don't really want to confuse them with that I want to keep it relatively straightforward and simple so what I'm gonna do is I'm gonna tell them to right-click on the server name and click new query and I'm gonna arrange the dialog just a little bit differently okay there we go uh-oh so normally on the Mac I would just hit a separate key and it would grab a screenshot of the entire window but I got to do this delete this is so cool delay for 3 seconds ok now let me get over here and oh oh I got a hit new now is that what I have to do jason statham whoa what the transporter is inside here I guess I also have to create and click new ok so let's click new right-click and then new query huh well what drag the cursor area I well how do i okay good there it works ok whoo all right there we go hello yeah more taenia alt printscreen oh let me see where the Print Screen key is at oh that's right there isn't one welcome to the year 2020 I didn't mean to take it out on you it's it's your eye on the bright side you're old like me I remember when there was actually a print screen button and I remember when it actually printed the scream oh my goodness I'm old so let's save that so let's say new query button so let's start a new query start a new query start a new query by clicking file and do you click file new query file new query file new database engine query new database engine query exactly Doug exactly or right click on the sequel server name and click new query and frankly right click doesn't even make sense because also you know I have a no buttons on that trackpad whew click new query let's add the new query thing inside there due to do to do and he goes inside insert into post then I'm gonna teach them how to create the account with a query however I am going to use the GUI in order to generate the script so if I go into the GUI and I go into security logins right click new login oh I should call it shoot I should call it a login rather than an account so let's go back up there and call it a create yourself a low privilege login doo doo doo doo doo in then scroll back down so there we go now create our new login and we will call this brent read-only this is going to be a sequel authentication and I'm gonna say it is what do I feel like naming my password just to be funny hourglass hourglass our glass no you know I said I should call it change me change me change me no one can because I'm terrible let's see user must pass Excel I'm not gonna check this user must change password at nest next login I'm not gonna change excite that's gonna present a whole kinds of problems inside here server roles public is fine user mapping I don't have to play around with any of this stuff in terms of the statuses however I am gonna grant them low privileged authentication so I click to the script button and I went over here so now let's zoom this out just a little bit so create and I'm not I wonder if I can do it without specifying their default database I might even specify it as temp DB I kind of like that so let's try it go execute yes sweet that's exactly what I wanted cuz I want this for the blog post I'm so I'm gonna say copy if you get an error that says then you need to use a more complex password let's go put this up here and then do the code snippet paste the put this in just break some lines meet the operating system policy requirements because they're just not complex enough perfect that's fine copy are just add right there we go beautiful to do to do that we do have that in the course outline actually we do have that in the course outline ok so there then you'll get a an empty new query window copy/paste yeah the thing is I'm gonna hold off on the screenshot stuff I actually usually work on a Mac and one of these days I'm going to transition over to using my Mac natively on the twitch stream cast like if I show you I'll zoom out a little just to show you my desktop looks like so let's say do not crop so this is all of a sudden gonna start streaming my entire full desktop fit to screen so I'm running a 4k I have a 4k monitor here and I'm using VMware fusion to show just a Windows VM in a window as I work so that this way you're kind of just seeing a tiny portion of my desktop then I have other things like my restream chat where you can see everything that's being chatted over here now the reason that I do this is because I run a nice big like 40 inch that's 34 inch 4k monitor I don't need a an alternative the screenshots are built into the Mac it's totally built in you don't have to it's really funny it's some operating systems include the things that you need on a day to day basis but so anyway so that's why you see me doing this kind of thing here let's go pop back out and I'll crop this to to a window and just show VMware Fusion and I'll fit that to screen so that y'all can see it a little bit better move it out like this gallon Bunga says this mana so well alright so I think that gives you good kernels that's okay that's it now all of a sudden I have to subscribe thank you coronal I appreciate that alright so now you've got a new copy paste the below into your move and note that you have some changes to make change the password from change me to whatever you want I also have to get him to change the username so let's go back over here to this Oh PowerShell freak Oh PowerShell freak now I know where you were going oh you were going that if they don't have mixed authentication on this low privilege to count isn't going to work I got it I'm like probably five minutes behind you I'm like who's he talking about that for we're talking about that in the set of class I also need it for the blog post that's a great point so I'm gonna have to have a separate section about windows low privileged users or sequel low privileged users so after I do that let's go say Brent read-only and then change me so I'm gonna say change me let's see if that's long enough there we go nope change me bang execute there we go all right cool grant now we're gonna look at the grant privileged permissions stuff let's copy-paste this well here let's get look up the grant privileged yeah digital axiom that's true except this isn't Windows 10 I'm on a server so thanks for thanks for that the tip though I appreciate that I'm just it's just irrelevant that's okay you know it's a golf clap so Rimel yeah staked environments yeah except that's just not the target for this what you'll find is that if you do like a Venn diagram of all the people out there who have sequel servers it's like this big huge giant pie and then if you get a set of binoculars you can find the people who are using mistakes so so in here so now I got to give the I got to give that permission a view server state so I got a grant view server state account and then let's see here here we go and let's see Wow all they have is the GUI oh man they didn't even have the teeth equal oh man that's weird so we'll just try and wing our way through it so I'm gonna say grant whoops grant view server state to grant read-only let's see how that works is that the right syntax it is oh my god I got it right the first time that's amazing I never do that that's incredible so wow that is just amazing so let's go cancel uh you know what I'm also should also do though too is I should bar them from should I bar them from writing anything I think I should I should bar them from writing anything whatsoever let's do that too as well let's refresh the list of logins let's go which one did I create Brent read-only properties and thence thing a server role DB isn't it DB deny writers at the database level is there a server level is there a server level tonight a rant about to view any database is there a server there may not be a server level deny I would really like a deny writing data tomorrow hey Camaro good to see you and also I love it that's actually very good Camaro you may like this so Camaro 322 there is at cars and bids there's an auction ending today for Tyler Hoover of who V's garage Fame his Camaro z/28 I'm not saying you should buy it I'm just saying that you you might be interested in it like I am as well and Hanny I think what you mean is there's no server level denied there is database level deny but there's not a server level denies that could that kind of sucks I was hoping that there would be a server level tonight so just I know it is this is a beautiful so for those of you who aren't familiar with Tyler Hoover's so he's a former car dealer who has a YouTube channel now and he calls it the dumbest channel in YouTube what he does is he buys the worst example of a car like the cheapest Camaro z/28 that there is and then he does all the work necessary or he has his mechanic the car wizard do all of the work necessary to bring it up to spec to make it a good solid car he didn't buy the cheapest z28 there was here he actually bought a good z28 and then had more stuff to do it yes I didn't I am on twitch yes dumpin and during a weekday - so that's cars and bids which I just absolutely love so I wish the word there was in database or a server level read-only but yeah it is which I love wheeler dealers two wheeler dealers came over to the u.s. there it's on our us cable channels now and they were broadcasting out of out of California Oh Chevy bought back my 96 z28 under lemon law let's be fair my 80s and 90s weren't GM's brightest time in terms of quality so my ride also there was there's a pimp my ride van that tovarisch recently got which was interesting - if you haven't seen that just a branch off topic here for a second tovarisch pimp my ride so tovarisch found a pimp mountain one of the pimp my ride minivans bought it for 850 bucks bought did all the work necessary to bring it up to spec like - current legal you know nice quality and then if I remember right he gave it to somebody either gave it to somebody or gave it to charity I can't quite remember how he did that a lot of these repairs are questionable at best whoa jeez automata I wondered if pimp my ride would actually be okay on the on the Auto modding I wondered how that would work okay so we have a script there and I'll copy/paste out the script and we'll talk about the changes that these people need to make copy and then paste this over paste and then I'm gonna I'm gonna switch this change me back to just plain old change me because especially then it won't work so things you need oh they only need to change do things change Brent change Brent read only to be whatever username you want typically your regular username but read only oh now I can't call it read only I want it to be clear because remember this is a beginner level course or a beginner level post I want to be clear to folks what this is and what it isn't it isn't a read-only account at this point because after all they still have the rights to read store to write stuff because I didn't deny that balance on this cameras all weird this morning so I don't want to call it read-only unless I can figure out how to safely written make them all hmm I mean I could loop through all the databases and do that I'm just not a big fan of that but you know what I should I really should write a cursor to loop through all the databases and just deny um writer yeah let's do it listen we just got to write that it's the cost it's the cost of doing business so first I'm gonna say that as check change read only but append let's move the supple so y'all can see it but append read-only to it so you know that it's you're less privileged account change note that you also have to change Brent read-only twice note that you have to change Brent read-only in two places Oh custom server role so the problem is I love where you're going with that con them in a custom server role I don't think that I can have a server role though that denies writer for any new databases that get created let's go ask okay so let's do you know what's really funny I don't ask a lot of Stack Overflow questions I don't ask a lot of database administration type questions on DBA not Stack Exchange but this seems like a really good question to go and ask now first let's add it to model that's all I like that a lot that's really good and STP are a few were close no not STP or if it's not MSD its model y'all are really close on there but it doesn't work for restored databases if they restore database from another server or whatever so I love where you're going there y'all are both in great interesting spots so before we ask the question let's be good citizens and let's go Google for it sequel server deny rights to new databases or just deny write a server level server level to do to to deny objects and didn't deny database permissions deny understanding grin deny custom roles yeah that's true oh wait Oh oooh so let's see here this looked good you should have Chris that bug for it that's true create a new role dole oh but that's inside the database I want server level roles server level roles where's a server level role that it looked like vina Terrence attendant yeah I did yes this is the downside is if you ring yeah that's not it either though I want deny at the server level okay so I'm gonna I'm gonna ask a stack overflow question let's go do it so let's go DBA Stack Exchange calm and let's ask a question how to deny rights in all databases including new ones and restored ones I want to create login that does not that is des that has that is denied that's good that's entirely too good that's way too good that has denied the ability to write to any databases not just databases that exist today but any newly created databases or databases that are restored from other servers I can't use database only roles that's not bad STP are off that's not a bad idea I don't mind that that's actually kind of cool and depending on what we I'm you can post that as an answer to this too if you want once I get the question and that'll totally work and I can't use database only roles because those won't take effect on newly restored databases I can't just write an including not even not even in MSD be because of restored databases business purpose I'm trying to write a blur I'm writing a blog post a blog post on how folks can set up a new Jetta my grill set up a new login for themselves that has low privilege and they can make sure that they don't accidentally have an oops moment when they copy paste code or run it without a where clause alright so this is about sequel server sequel server and I guess it's technically about security yeah security and then acid so that in all sequel server databases there we go and then let's say review your question why did you pop up to back to this post your question all right there we go so now we have our new question whoo now it's probably just gonna get down voted into affinity but at least I've got it in there so I can use that to write the post out later so I'm a demon did I say MSD B I did good catch GMA's so even in model fixing typos typos in database name Thanks GMA SSB be a save edits okay there we go so all right good so we have that set up now it's probably time for raw amel says the same thing that STP did STP over in YouTube said do a DDL trigger on the master databases on master sustained basis which is the interesting I'm intrigued by that so I'm gonna stop here for the next bio break oh it's 7:30 to my coffee shop downstairs opens in half an hour grant select all grant connect all see that's the opposite of what I want I want to deny the ability to write scone amend your clothes that gives them the ability to read but it doesn't deny them the ability to write which is what I'm trying to do here you might cleverly guess by what's up at the very top there where it says how to deny rights so I am at a complete a good stopping point though and I don't know that I really I do want to go downstairs for coffee but it's gonna be another half hour okay so let's take a bio break here and when we guys screw it let's keep going I am giving you the warning though that at 8 a.m. so in about 30 minutes I'm probably gonna stop the stream and go downstairs and then go get nice coffee would grant deny work in this case so it works it's not grant deny but what you want to do is deny someone's login something but it doesn't work or databases that don't exist yet script FN built-in permissions I'm not sure what you mean Jedi mind gorilla script SS FN built-in permissions what you want so what I'll let you all figure out if you go in and ask comments in and the stack overflow question X camera says how was your vacation it was wonderful went and goofed off by the ocean in Malibu okay so let's come back over here so we've got at least I've got the blog post started so I've got create a low privileged account and I call it read-only so far I'm gonna come back in here and I'm gonna go add in whatever I need to do - um do grant rights to all databases once we figure out what approach we're gonna use for it hi John C welcome to the club should let you give them a background there so here are the things that they need to change now but up above though one of you said hey it what if they don't have when if mixed authentication isn't turned on so first let's find out if you have sequel authentication turned on and to do that we're gonna have to run a query and I think I can get that from sis configurations sis configurations order by name let's see what we get inside there and is it mixed is it called mixed no it's not is it under - all right screw it we're gonna Google for that let's refresh our stack overflow question nope yeah nothing's been posted in there yet so let's say sequel server how to query if mixed authentication is on and I misspelled authentication doesn't matter start Enterprise Manager come on are you kidding me come on now that's just a bit and the sequel tips chagall Shah so goo XP instance reg read oh my god check using server property yay okay I like that so much better that's much better yes so let's do that paste so let's see if that actually works so that's good that works I want to do it differently I want to say if select case I'm just gonna get this on and nothing else copy paste what the what extra printable characters for the win that's a case you you execute okay so run this query tu-tu-tu-tu-tu-tu where the hell is there it is didn't do do do do copy paste paste the below where shoot I need to run this down lower start a new query to do do do-do-do do-do-do do-do-do copy paste the below into your new query window thoroughly def thank you let's get so attitude attendant and then paste that in here whoops no tonight yes so we can find out if you have windows only authentication turned on let's go copy the script copy and paste that in here paste and if if the result is one that means your sequel server only allows Windows logins my mom went completely gray by the time she was like 28 and she you know used her I shouldn't be saying this on a stream but of course my used my mom used hair dye and all that to cover up her gray but of course I'm lucky and that American males for some reason there's not as much judgement about gray hair so I loved it when I turned started turning gray early cuz it made me look a lot smarter than I actually am though we must hire this guy he's experienced no he just has strange genes still make the same dumb mistakes and so I don't know what I'm gonna do in here with Windows logins I don't really want to teach that here so in that case I deeply apologize I'm not gonna I have to do it this is where it's tough being a blogger is where stuff being a blogger is you go I want to do justice to this topic but I feel guilty if I don't go deep enough in it and I feel like I need to put in Windows on authentication but you know what I don't feel like doing that I want to go down to my coffee shop in about 20 minutes so I'm gonna get the vlog post to the point where I can ship it and then if I end up later or figuring out the windows off part I do if not who cares screw in that case I deeply apologize but I'm not covering that yet in the scope of this blog post if the result is zero good news you can create sequel logins select let cop to do that to do that copy paste upload dudududu to because the problem is so why don't I want to do that so why don't I want to cover windows logins because then it becomes a matter of you've got to go into Active Directory you've got go into a machine that's got Active Directory users and computers on it the person's machine may not have that in which case I got to tell them to go remote desktop into a server that does I got to explain what that tool is and I'm just like I don't really there's limits as to what I'll do as a volunteer and that's kind of where I draw the line um alright so that is this that creates the sequel logins and I could say too so I could have them change it I could have them run SP configure and change to allow sequel logins but then to do that that might screw up their company's security guidelines it requires a restart of the sequel server so it just opens this whole Pandora's box if I'm like mmm way too early to be running that that song and throw on okay well DK DK that's true but then they could create a Windows user and then inside the database they would go in and do the read-only part so if you get in there it says that so now we're using more complex password alright great so in here fix fix come back and what and add the true read-only part after if anyone fixes or answers this so I'm just gonna make a note for myself on the stack overflow question I'm also going to tweet that just because peat folks may actually have an answer so I'm gonna go pull tweet bot here and I'll drag this in over here I don't ask or I will say how can I deny rights in all sequel server databases including new and restored ones I'll copy/paste this out share copy and that's way over here paste sequel help keep in mind that a deny statement and model won't work newly restored databases specially you're probably you're probably right thanks in advance so there we go and that will there we go now I expect a bunch of answers to not a bunch of answers but I bet replies will come in and ever be a problem oh that would be pretty funny but I expect answers to come in and I expect you know what's gonna happen same thing what's gonna happen is I'm gonna get a whole lot of answers to save the same things that y'all did they're gonna say things like hey you should try a grant hey you should try using the deny role inside user databases one of the things that's frustrating about asking questions publicly is that it doesn't matter how much work you put into it like we put in a bunch of work into this here we still get this problem of people giving us the wrong answers so I can I totally sympathize with how hard it is to get your questions answered for the public because even here where we're putting in a ton of work we're still getting we're still gonna get really crappy answers back smart my words that's the way that the internet works okay so we have that set up now what else did we need to do inside the post so inside the post we also needed to that's the wrong post that's how to install a sure so we're past that we're close that close that so oh I forgot to put the link in inside where I come back to here that's really good and really true okay so now what was step number four what was the fourth thing that we said we were gonna do inside this blog post the fourth thing was learned is how to switch back and forth between the low privileged account and your regular one so for learn to switch back and forth between accounts now that you have a low privileged you're going to want to use this by default when you connect in to sequel servers with management studio this helps prevent you accidentally causing a resume resume generating event close SSMS and reopen it again this time at the connection dialog choose khalil thank you welcome to the club thank you oh yeah I will copy paste this back in this time at the collection connection dialog now this time I'm going to go connect in again and I got a copy paste this password out cuz I sure won't remember that copy and let's go throw it in a notepad link here somewhere just so that I've got it and then move that down out of the broadcast window now like I'm worried about the secret security log in there so let's close this in order to get a good screen shot let's close management studio as if we were going back in for the first time and we'll reopen it you could totally argue that I could just have closed windows individually but with a new user the kind of target user whose aunts reading this blog post it's concerning because they may not I don't want to teach them anything that they don't need to know I'd rather just say close the whole thing and open the whole thing back up so this time they're gonna choose sequel authentication they're gonna put Brent read-only and they're gonna put the password in there so now I'm gonna go take a new screenshot of that now I don't need the delay new and go over whoops shoot perfect there we go and grab you and save SSMS connect read-only and minimize you and come back over here and drag you in and dududududududu insert it into the post oh man it uses the period so the period is what I usually use when I'm connecting into a local sequel server management studio but that's gonna raise connect or it's gonna raise questions with people they're not going to understand what the period means and that's out of scope for what I want to teach them inside the context of this blog post so I'm going to call it sequel 2019 remember password let's take the screenshot again new say and we'll say connect read-only cycle 2019 du duta-de du-deta duta-de du-deta duta-de and insert it in a post we got any answers yet or any comments yeah nothing tumbleweed and nothing's popped up on Twitter either as far as I know yeah nothing inside there nope okay so not that close this time at the connection dialog choose your put use sequel server authentication because we because we created a new sequel login and then type in your low privileged privileged username and password hello privilege is that what you want oh I mistyped privileged privileged privileged username and password click connect and you're now working a little more safely without the superpowers of your regular domain login okay whew man dang alright we've done pretty well here if I look at that I you may have if you've attended one of my prior note streams you'll note that I kinda usually look at a vague word count down there the word count in WordPress we're running like seven hundred twelve words I usually aim for between five hundred and thousand words on a blog post so I'm comfortable with that I may want to put more things inside here but for the kind of person who would read this blog post the person who doesn't know how to get started with sequel server management studio this is probably a good start I what I may want to leave them with now that you have this here's where you may want to go next so because to remember I don't have how to loop through the databases yet and set every all of them to in the read-only role or to a grant deny write or deny right role so if I go little further down let's go back down into next steps for learning now that you now that you're in let's go back over here now that you have an account oh man well okay now that you have an account that's safe to use for learning here are a few next steps of tools to explore on how to get to know your sequel server better SP blitz as people it's a totally free health check script that tells you daint what might be that warned you about dangerous things warns you about dangerous things in your sequel server that may present problems later DK Kimbo did i miss something if this account is only public at the server level it can't write to any database what you don't know is that people didn't grant permissions to public so I've been environments where they've granted write permissions to public I know I know unbelievable [Applause] that's good let's go says I liked it so let's go let's do SP words here let's pull that up here s people it's what else would I want to send somebody to to learn more about how to do a free health check how to do a health check my free process for writing up your server writing up a sequel servers health and performance issues so I did a multi-part series on health checks how to add multi-site bronto's are.com there go copy paste that out copy and paste and paste that and that's probably a good place to start okay so we've got that I'm happy with that I'm not wild about it DK Kimbo says fire them so true story I'll go can't you use a login trigger and deny on I u D so MO 64150-9636 yeah that came up in the twitch stream we're doing now and I like it a lot waiting to see if someone posts that as an answer to the DBA se question and then hopefully as a community we can flesh that trigger out and make it work all right so there's that and move that down here so okay good so a healthy pet sausage if it's a great name you always look good with your names I just love that one that's pretty cool um so we have our post we have a draft on that I'm kind of happy with that the way it is um I think I'm gonna stop today's stream there so let's zoom out for a second and talk about the things that we kind of covered the wrong Drive courses so we got started with today's stream I walked in or you're welcome Jedi my grill I walked into this really thinking that okay this week this entire week all I'm going to be doing is working in as your data studio and I'm gonna flush out my new class fundamentals of database administration then as we got started we worked on a blog post on how to install as your data studio to connect to sequel server and had a great time doing that but then realized as I started fleshing out the very first module in the course when I was doing with backups I started to realize that I didn't really want to teach people inside of Azure data studio because I think that the user interface is going to change so dramatically over the course of the next several months the jawed joined in your ending now you can always watch the replay stew the replays are always back up on live on YouTube on Twitch Facebook all that kind of thing um so then we ended up shifting gears and I said you know what I'd rather teach them with management studio so what I'm gonna do now is I'm gonna take a bio break for a while I'm gonna just stop the stream I'm gonna go down walk Oh get myself some fresh coffee and a bagel later on today I'll start another stream and when I start the next stream we'll start figuring out the feet okay that's true i I need a swear jar around here it's really funny people who have known me in person they're always shocked by how much I swear I swear a lot like I swear a stunning spectacular amount I really clean it up for you all in public but then I'll do another stream here later on so thanks a lot for hanging out with me I swear 55-gallon drum I should I should probably just be sealed inside a drum like that and especially when I work with some of the Microsoft products so thanks for hanging out with me today y'all and I'll see y'all later if you're around uh later today so teach see y'all later adios [Music] [Music]

Channel: Brent Ozar Unlimited

Views: 3,493

Rating: 4.7538462 out of 5

Keywords:

Id: 5Y6ZQdBZCE8

Length: 178min 36sec (10716 seconds)

Published: Mon Jul 06 2020