PowerShell For SQL Full Course By MicroSoft

hi and welcome to microsoft virtual academy i'm chris randall i'm here with mike fall and we're here to talk about powershell for the sql data professional in this demo heavy presentation there are not a ton of slides mike is going to help us understand why we as sql people would want to use powershell it's been around for a long time but what can it do to us and for us as administrators of on-premise sql server hybrid scenarios or azure sql db well let's get started mike why don't you introduce yourself uh well thanks chris it's good to be here my name is mike fall i'm a sql server professional sql server dba been doing it for a fairly long while now working with some sql excuse me working with sql 7.0 first and then on through the years and sometimes i look back and go wow it's really been that long but yeah uh know i speak at sql saturdays i do a whole bunch of different stuff with the community and uh i've been doing a lot with powershell uh for the past two years you know working a lot with powershell really enjoyed it uh you know a bit of a dirty secret for a while i was an oracle dba working in linux and you know that was when i came over to windows again it was like oh my gosh i've got i've got the shell scripting language that i can use that i can leverage to do so much so it's been something of a long journey for me but uh you know here i am doing some great stuff with powershell and and hopefully can tell more people about it here today all right well let's talk about what this course is all about what's your agenda uh well so we have three modules to go through uh we have first off powershell basics so we're going to talk about just kind of the fundamental structure of the language how it's used how it's constructed how people can learn about one of the cool things about powershell is it was built with the idea that you could learn about powershell while using powershell so we're going to cover that in this first module next up is then you know where do powershell and sql server meet what's what's kind of that in the venn diagram of these two languages how do we find that middle space because there's a lot of strength there there's a lot of flexibility and robustness then once we get done with that obviously the cloud is the future right everything's moving to the cloud whether it's you have a hybrid model whether you have just a strictly you know pure cloud model whatever you're going to do we're moving to azure it's happening and powershell is built as a fundamental component of of the cloud of azure and so we want to look at how we can use powershell to spin up azure vms azure sql database and how we can make use of that whole model so if i'm if i'm a sql dba who's going to learn powershell to work with on-premise sql server is that going to give me a running start toward learning how to use it against virtual machines or against azure sql oh yes absolutely absolutely all right well let's dive in we've got module one is your powershell basics sure thing what we're going to do in this one mike so powershell basics like i said we're going to talk primarily about the language and from a general perspective how it's used right you know we're going to be looking mostly around what the where did where did powershell come from uh how do we write it you know what are the tools available to us to work with powershell to write with powershell how to create our scripts and how to debug our scripts you know there's there's all sorts of work in there as well uh commandlets so commandlets are kind of a cool thing and it's this piece of functionality sometimes it throws people off but really it's just functions that are the building blocks of powershell we'll talk about the object-aware nature of powershell so powershell is kind of cool versus some other scripting languages where it realizes that objects are out there it has some relation to object oriented programming i don't think of it as object oriented i think of moore's object aware but we'll get into that and then pipelining is a nice feature of scripting languages that allow us to extend out functionality and really leverage this robustness of okay well i want to do i like to think of it as doing one i can do anything in powershell in one line right it could be this where i can take the output of one command pass it as the input of another let that do its magic and pass that on to a third absolutely absolutely so that's really the overall this is what we're going to do in this first module so with that do you want to just uh dive right in and you go with the first module or first lesson yep okay uh but what is it what's powershell powershell is a scripting language right uh but it's more than that to me it's a it's a framework it's a you know yeah we can write scripts in it but because there are so many hooks into it so many ways to use it uh it's more of this overarching framework that we can build automation and tasks into but it is its own language right it is its own language it is absolutely its own language uh now it was first released back in november 2006. so it's actually been around for 10 years and i don't think people quite realize how old powershell is but it's been a little slow to adoption particularly i think in the sql server well that's why i like to talk about powershell so much in the sql community because you know most sql server folks are like oh yeah i've heard of powershell but i'm not quite sure what to do with it uh so here's here's an objective okay i've heard sure you get an experienced sql dba they know t sql inside and out oh yeah absolutely why are they going to bother learning another scripting language why can't they just do it all in tc so so the thing about powershell and t-sql i've heard this kind of people making this case a lot is well you know oh i can do it in t-sql why would i do in powershell uh powershell is not a replacement for t-sql powershell is not something that oh i'm going to use powershell instead of t-sql we're going to use the two in conjunction right we're going to use powershell and c-sql together to accomplish automation and tasks across multiple sql servers so that's really the key thing to i think for people to understand is it's not one or the other it's both together it's both together now one thing to call out i always like to call out this so jeffrey snover here at microsoft is actually the guy who he and his team came up with powershell back in the day and he wrote this really cool document if people really wanted to get in the geek and the technics of it uh the monad manifesto is where he really first came up with the idea and he wrote this back in 2002 but it's this this document that kind of lays down the fundamental principles of what powershell is and what it's about would you recommend that people read it today oh yeah yeah we should probably put a link to it in the mba yeah absolutely absolutely we should do that okay now the last uh piece here too is is the current version of powershell is now at 5.0 it just came out a couple months ago and it's got every version of powershell comes with a whole bunch of new features new functionality there's a lot of new stuff in it i always whenever i'm talking about any sort of tools whether it's management studio powershell what have you it's always please use you know try to use the current version i shouldn't say please use but try to use the current version because you're going to get all that new functionality and the nice thing about what microsoft does is they make a lot of their tools you know backwards compatible for example i can use i've used management studio 2014 but i've used it to manage sql 2000 servers and sql 2005 servers so you know if you're going through this course i highly recommend you get windows management framework 5.0 get the most current version of powershell and you'll be able to follow along pretty well so if if i'm in an organization that's using sql server 2012 or we haven't yet moved to 2016 but we're planning on it i can still get on the powershell train learn against 2014 and be ready for 2016. yeah yeah and in fact i'd absolutely even if you've got 2012 servers uh 2014 service 2008 and 8r2 servers you know install the most current version of powershell certainly on your client tools wherever you're running your client tools from and you'll be good to go so why why bother why bother get back to my questions so i'm a sequel guy i've been using t-sql for longer than i want to admit uh i understand powershell's got some cool things especially administratively yeah and you know when i start talking about azure for example i'm talking about managing virtual machines and virtual networks i'm talking about managing uh sql databases and azure sql uh logical servers so i get that powershell's got some hooks there but i can i can get around in the portal i can get around with management studio and t-sql what does this do for me so there are three reasons that i usually focus on for why people should use powershell the first is and this is to me the most important thing is i use the term cross stack powershell is built not with one specific aspect of windows in mind it's built with all the different parts and pieces of of windows you know when we're working with stuff we're not just working with sql server we're working with the operating system with the file system with active directory with iis maybe you know what have you so powershell again because it's a framework because it covers all these different aspects we can use it to run tasks that touch the file system and sql server and active directory maybe whatever a use case example i like to touch on is is backups you know when we do a sql server backup if we run this through t sql you know if the file system directory that we want to back up to doesn't exist it throws an error right so powershell is great because it can natively go in and say well i'm going to create your directory and we're going to see this later on in the lessons and then the demos but you know we can do all the file system work with powershell then have powershell call the t sql statement to execute the backup and then maybe do some other file system tasks to clean it up uh there's there's innumerable examples of how we can work across the stack and that's really the uh import one of the big things about powershell the second thing is it's enterprise ready and that that sounds a little marketing a little you know fuzzy uh but what that means to me is when we're administering servers most sql server dbas are not administering one or two or three sql servers we're administering 10 and 50 and 100 and sometimes thousands of servers power shell is designed from the ground up i mean snover and his team were like look we know administrators are not working with just one or two servers they're working with large enterprises the powershell's built from the ground up to say i'm going to be able to execute the same way across multiple servers that multi-server execution is huge to me that means i'm running one task once in the same way across all my different servers and that's a key component of automation right that's the key facet the third thing is is it's built on.net back in the day again snover and his team they said we're going to build this on.net and why that's important this actually goes back to the cross stack piece because all of the windows tools access windows access microsoft components and applications through net libraries powershell is accessing everything in the same way the example again for sql server folks is and we'll again cover this in the demos if you're using the management studio using sql server management studio you're using dotnet libraries to access sql server so powershell works in the same way it's a way for you to actually interact with all the tools and all the components in the same way as all your gui tools all your graphical user interface tools all right so it sounds like with what you've said there with it it being crossed across the stack and that's really useful in situations where again as an azure i've got a i've got to set up and manage firewalls i've got to set up and manage virtual networks my vms and then sql server within that right we've got that that that's that that span across the os and networking into sql itself and that kind of takes you into what you're going to talk about next right right the usages and and and sharing and collaborating right so the next piece that this all kind of blends into is it gives you a common framework uh and it's not just a common framework for dbas for database administrators it's not just a common framework for developers or assist admins you know everybody has some if they're if everybody has an understanding of powershell we can hand these scripts around and using the the sysadmin example i can write a powershell script say for example i write a powershell script to install and configure sql server in the way i want it i mean i can i know i can sit down and say okay somebody bang out the click click click but if i hand them a script with a couple calls and it interfaces with ways that sql server is already uh used to set up and automate that i can hand it to assistant admin or i can hand it to a junior dba who may not have the level of knowledge that i do and just say look run the script and and call me when you're done you know call me if there's a problem right and this also feeds in i mean i'm one of those people i'll fully admit that i'm a lazy dba i am a lazy dba and this takes away a lot of the tedium of the work right i can again write this composite script hand it to somebody who doesn't have the level of experience and knowledge that i have and say yeah go ahead and run this and call me if there's a problem and that's going to handle 90 of the work and you know so that's why to me the the common framework for automation of using powershell is is again another huge advantage all right so well a little confession for me sure even though powershell has been around 10 years and i've been around sql server longer than that yeah i first got my deepest exposure to powershell through sharepoint which sounds a little strange but i was doing a lot of bi work as back back when i was a bi consultant before i i took this role at microsoft and so i was spending a ton of time configuring power pivot which is incredibly lengthy and detailed and all the pieces you got to get and so i really roll up my sleeves and got into uh scripting that through powershell and then realized well you know i probably should have been doing this with sql server all along so now i'll probably learn the right way to do it okay well that wraps up our first lesson good intro to powershell let's dive into our next lesson after a short break we'll be right back all right well welcome back to our second lesson in this module on powershell basics so mike you've given us the background of powershell and why we should use it how how do we use it yeah how how is always the trick right how is always the trick so there's to get started with powershell really we need to talk about the tools there's a couple things that are available to us within windows that we can use and really we need to think about the use cases so first off is ad hoc command line use right just jumping into a command line somewhere and hacking out some powershell and calling something but the second of course is once we start getting into powershell and we really start automating things we're going to need to be using scripts and i always like to call out at this point that anything that's a powershell script is going to be having that dot ps1 extension there's a couple other extensions in use but for basic script files you're always going to see that dot ps1 so this is a successor to our old.cmd.bat yes yes yes and there are two different interfaces so we have the powershell command window which looks very much like the previous command window cmd.exe but has all the powershell functionality baked into it and then we also have the powershell integrated scripting environment or the isc which is more of a development tool right so we get scripting window and command execution and again we'll talk about that more in the demo itself so i always like to call out these two pieces and parts because this is something of a fundamental concept with powershell so we have the the command window here and we have the ise and you know don't worry too much of the tax that you're seeing on the screen this is just more or less to get familiar with what these look like but in powershell these are referred to as hosts uh it's a front end it's it's a development tool or it's a way that the the scripts and the commandlets and the functions are all executed and it accesses what's called system.management.automation it's a dll.net library that is what really runs all the powershell magic and what's kind of cool about this is when we talk about the command window here and the powershell ise as being hosts anything can be a host any net application can be a host so you'll see as windows develops as a lot of the teams build tools out they're building their own gui tools but they interface with powershell they interface with the system management automation there's also plenty of other development tools out there for powershell and those are also hosts also can sql server management studio ssms run powershell it can't right now cannot uh but it might uh the sql server tools team has done a lot of great work over the past year and there's a lot of stuff coming into play so who knows you know it might very soon here okay so with that i think we're ready for a demo sounds good let's take a look at a demo all right to use powershell in windows we're going to use one of two applications we're either going to use the powershell command window or the powershell integrated scripting environment ise for short to open these applications you can click your start button or press your windows key and select all apps in here we have a windows powershell folder with four different applications we have the powershell command window and the powershell command windows 32-bit version we also have the isc and the 32-bit version of the isc now for ease of use i've gone ahead and pinned both these applications to my taskbar so let's go ahead and open up the powershell command window when it opens up it should look pretty familiar we've had these kind of command windows in the past this is different because it has windows powershell in the title bar and it has ps at the command prompt indicating that we're in powershell other than that it's works pretty much the same as the command windows we've had before the cmd.exe i can type a command such as ipconfig and it will give me the same output network adapter information for my server however we are in powershell this means we can make use of powershell specific commands i'm going to call a powershell system variable called ps version table what ps version table will show us is what version of powershell is installed so as you can see we have 5.1 installed we also have some other information such as compatible powershell versions and the windows build version ps version table is a handy system variable that can help you identify what's going on in your environment how it's configured we can also run powershell commandlets now we will talk about commandlets in the next lesson but for the purposes of this example i'm going to call the getdate commandlet getdate will show us the current date and time of the system by hitting enter as you can see we get wednesday june 8 2016 9 51 in the morning the current date time this is the most basic way to use powershell in the windows environment we can run ad hoc commands we can call scripts however there's another way we had talked about the integrated scripting environment so let's go ahead and exit and then open up the integrated scripting environment and see what that looks like as you can see we have a full featured windows application with a toolbar at the top some icons for certain actions what we have next is we have what's called the scripting pane within the ise this is where we can write powershell scripts edit different commands and run them all as a single script below that we have the console pane now the console pane works very much like the windows powershell command window that we were just in we can run a command such as ipconfig and it will give us the same output so we can use our same ad hoc command line execution from the console pane if we need to however what we really want to be doing is working with scripts so i'm going to go ahead and open up a demo script which has a few simple commands the first line is a comment line comments are noted by the pound or hash sign in powershell they aren't actually run they're just there for comments notations what have you the next line is our ps version table call and then we all have on line three our get date call if i want to run this script it's in in its entirety i'll press the green arrow and this will run all of my commands at once you'll note that we have the ps version table output just like we had before in the command window we also have our get date information now you'll notice it's formatted differently and this is a function of having all the commands run at once powershell is trying to identify a format that it can use across all your commands if we wanted to see the individual execution of the commandlet we would select getdate individually and press this green arrow with a box behind it and this will run only the immediate selection when we run it as you can see here we get wednesday june 8 2016 9 53 in the morning we only get that one line of execution so this application gives us a good way to work with powershell scripts to edit our scripts and and get entire sections of code to run we can still use the ad hoc functionality within it there's also some customization that we can do of the integrated scripting environment these three buttons here help us layout the script pane and the console pane together with this first button selected as you can see we have the script pane on top and the console pane on the bottom if i want to i can click the second button and what this will do is move the script pane to the right and the console pane on the left the last button will maximize the script pane and take up the entire view if i ever want to see the console pane from here i'm going to click this little arrow in the upper right which will hide the script pane and show me only the console pane so you have several different options to arrange how you want to edit your scripts so there was the demo on using the different powershell tools the command window and the ise so it's that's that's really where you get started that's the beginning of everything and so now we need to figure out what to put into those those yes right yes it's all about absolutely it is all about commandlets so uh commandlets are it's a funny term that uh people are like they're not quite used to and what people are probably seeing right now is this big wall of text right right so take all that put it into two words if you had to describe a command line two words only well really what a commandlet is is it's a function right that's really all it is uh the reason that the commandlet term was chosen specifically was uh it was something that the windows powershell team when they're putting the language together they said we want people to be able to find our functions when they're looking for help you know without getting cluttered with everything because if you do a search on a function you know you might be getting java you might be getting c sharp you might whatever right there's all sorts of functions out there so cmd led commandlet was a term chosen specifically so that people who are looking for help could find it on the web could find it without it getting cluttered up with everything else because only powershell was calling things exactly it's a it's a unique term chosen by microsoft uh snover and his team to say okay this is what we're using for powershell now what it really is is two words i want my two words [Laughter] yeah so it's really like i said it's it's the basic building block of of powershell everything that you do somehow boils down to a commandlet of some sort and this is where the the syntax of it is kind of interesting so the syntax is this verb noun structure anytime you see a powershell commandlet you're going to see a verb such as get or remove or set or set and that's an action right that's something that we're doing and then the other is a noun and what we're doing it to now the verbs are restricted the verbs are standardized uh by microsoft so that you know you're only going to be using a limited set of syntax or pieces of language now what's interesting about that is how do you find out what your approved verbs are you run get verb right so you're already using commandlets into the language and this is one of the cool things is the language some starts to become intuitive in how to figure out where things are now the verbs are restricted but the nouns are you can anything goes and you want the nouns to be somewhat descriptive and they're going to be context sensitive aren't they yeah yeah exactly so in azure i'm using ver i'm using nouns like subscription or account right right well and and i have some examples here of what a commandlet looks like uh so the first one like get date right obviously get date right get whatever the current date is now get date will do some other things as well it will you know allow you to get specific dates depending on what you pass to it but at a foundational level elsie they're going to get the date so in sql server terms if i run the get date function right sql that's going to return a date but it's also going to be a typed uh return i'm going to get that as a date absolutely does something like that happen with powershell yeah so we're going to actually talk about that in i believe the next lesson uh but yes powershell objects are strongly typed so everything's an explicit type of whether it's a date type or a integer type or there's even sql server types and again we'll talk about that as we go now another example is new item a new item is creating a new item a file or directory it actually can do a lot of other things again depending on what your your arguments are but it's new something and then finally remove module is another example and what does it do yeah i see module a lot in the context of powershell you know if i open up a powershell command window and i try to uh uh issue sql server or azure command sure sometimes i get an error that says they're not found in in my current module what is that so modules and again we're gonna you'll see a lot more of this particularly when we get into the sql server section uh modules are ways to extend powershell functionality so you have a core set of functionality but you know maybe you need to work with active director you need to work with failover clusters or you need to work with sql server so you're going to add these modules to extend your functionality now there's some some cool stuff with powershell like with powershell 3 version 3 and forward they added this functionality that if the modules are installed in specific paths and you call a commandlet that's in that module but isn't necessarily loaded powershell will automatically load that module for you it works most of the time i'm not going to say it works all the time but that's that's a good start i always like to explicitly load my modules that makes sense that way if you can run on a machine that isn't configured the same way yeah okay yeah now i talked a bit about the discovery the self-discovery aspect of powershell so this brings me to what i call the big three this is and i was whenever i'm giving this presentation at a sql saturday or a similar presentation i always say if you don't remember anything else remember this slide okay uh git command get help and get member are the three most important commandments and the reason why is because they give you the ability to drill through the language to discover things to learn things uh git command is a dictionary lookup it's a way for me to say okay i want to find what my commandments are and in the demo we'll see how we can use that to explore for some stuff get help the linux guys always get a kick out of this because in linux you're probably used to using man pages right right get help's the same thing it's just in powershell and in fact it aliases to man to man and then get member we won't really talk about too much in detail here but when we start talking about the object-aware nature of powershell git member is a way that we can explore the methods and properties the object-ness of the powershell variables and objects and such so these are like i said if you don't remember anything else these three commandlets will help you find out anything you need to know about powershell and help you learn the language so get command get help and get member absolutely absolutely get demo we can absolutely get demo get demo this is a great time uh this demo will actually show commandlets how to explore them and we will go into use cases of get command and get help let's take a look all right as previously discussed commandlets are the core piece of functionality within powershell pretty much everything you do is going to have a commandlet associated with it now the construction of commandlets is pretty important this verb noun syntax what this is is this is something that allows users of powershell to find useful commandlets for what they want to do the basic example is is using getdate now we've seen the getdate commandlet before this construction of it should make sense now at this point we're going to get date you know the construction the wording of the commandlet in and of itself tells us exactly what it's going to do so if we run it it will give us the current date time wednesday june 8th 2016 10 47 in the morning now with considering these verbs and the standardization of them we might want to say well i want to see a list of all my verbs of course to do this there's a commandlet called get verb to get all the available verbs to us and if we run this we'll get a list of all the different verbs and the different areas of functionality they're associated with now there are quite a few and we're not going to really go through them but if you ever have a question about what verbs are available to you and what you should be looking for for a specific command you can use git verb to see what those are now get command is one of the more important commandlets in powershell and one that you should always remember it's a dictionary lookup it's a way for you to look at the commands available to you the commands available to you and pull them up so i'm going to just call git command to get an entire listing of all the commandlets that i have installed you'll note that there are quite a number of them and we have a lot of information as this flies by us commandlets the names of them the versions and the module that they're installed with if we hide the script window and just scroll up for a bit you'll see that there are many many commandlets so you have different commandlets for different areas of functionality within powershell pretty much anything you can do within the windows operating system you're going to have some related powershell commandlet for it now if i wanted to filter this lookup to look for only specific commandlets i can filter on the name and then for example i'm going to look for all commandlets that have new in the name if i run this now we get a list of only those commandlets so you can see that we have things like new time span and new service and new variable and there are quite a number of new commandlets so if you're trying to figure out something that's available to you using these wildcard searches is a great way to do it now once you've found the commandlet you want to get information on how to use it and that's where get help comes in get help is one of the second most important commandlets to use and we just call it simply by calling get help and the command that we want help information on so i'm going to call get help get command and this gives us we'll hide the scripting pane for this a full output on what the commandlet is so we get a name a synopsis syntax for it so all the different arguments and parameters for it as well as a full description of it and some additional information such as related links and remarks however there's a lot more information actually available to us let's go back to the scripting pane and now i'm going to run git help get command with the full switch when we run this and let's hide the scripting pane again we're gonna we get a lot more information so starting right from the bottom as we scroll up there are 14 different examples of how to use git command so if you ever have a question of what's the syntax and how should i use it you've got examples baked right into the full help file as we scroll up even more we had seen earlier where we had the syntax with parameters well now with the full switch we can see full documentation on each individual parameter so we have the type we have a description of it whether or not it's required it's position and a default value if it has one so lots of information as you can see though as we're going through this it's a little clumsy to read as it all outputs to the console window so let's go back to the script window and now we're going to use a switch called show window if we open this up or run this then we get a second dialog box with all of our help information in it right alongside our die our powershell work window so in this case we still get the same information such as the description the parameters we can search throughout this if we need to we have a search functionality and it you have it handy for you to give you any sort of information information that you need we also have a sec settings box here where we can show certain help sections so if you only want to see the syntax say you don't want to see the description or the notes we can hide those and now it changes our output so now we have a way to filter out the help information that we want to look at within the show window now there is one more thing to look at and that is as i said all the help information has an online component it's based from web information that you can update periodically if you ever want to look at that information you can use get help get command with the online switch if we run this now it will open up our browser and it will go to the technet page of that commandlet so as you can see now we have the full web documentation for that that we can look at alongside of our scripting window so multiple ways to get at your help information there are more than just commandlets in the help system as well there are topics which start with an about and if you want to see a list of these topics you can run get help about star to list them all and as you can see we get quite a number of them we'll hide the scripting pane for this but to show here we have things that you can look at such as parallel if you need to look at parallel execution there's a topic on that uh variables if you want more information on variables you can look at that other syntax other syntax keywords within powershell so there are lots of options within the help file in the help system to help you learn about the language but let's go through a practical example say for example i wanted to create a new firewall rule i wanted to open up the firewall rule for my sql server i can say okay well i know there's a powershell commandlet for this so how do i find it i know it's going to start with new and i know it's going to contain the word firewall so i'm going to run git command with a couple filtering items here so i have new and it has to start with new and then i put has to have firewall within the remaining syntax of the commandlet or the remaining part of the commandlet name we run this and it will give us our dictionary lookup and we get a command that comes back and says new net firewall rule that probably sounds like what we need so i want to find out more about it i want to learn more information see if it's right for what i i want to do so i'm going to run git help new net firewall rule and i'm going to show window we run this and it comes up with our our help window all our help information and we can see the synopsis we can see parameters for it any of the additional information we need to see to use the new net firewall rule commandlet now one other thing i want to talk about is aliases we've talked a lot about commandlets and that they're that everything you do in powershell pretty much matches to a lid let's talk about directory lookups if i wanted to do a directory lookup in powershell i would use the get child item commandlet so i'm going to get child item on the c drive we'll run this and we get a fairly standard lookup here's all our folders here's files that i have in the root of my c drive now for most of us working from the command line if we've been working for with windows we know we can use a dir or a dur c drive to get the same information and as you can see it does come back with the same info however what's interesting to note about der is der is an alias der is a an alias to the get child item commandlet and we can see what that looks like by using the get alias commandlet and give it a definition of get child item this will show us all the available aliases as you can see we have der which aliases to get child item gci and ls for you linux and aficionados so if you're used to using the linux operating system you can use ls here in powershell as well to accomplish the same task so this overall gives you an idea of how commandlets can be used and how you can look up useful commandlets for whatever task you need to accomplish so that was the demo on just using commandlets you know how you can explore through commandlets how you can call up information about them and hopefully helps people right that's that's the whole point of this course right right so okay so we've got commands we can use command let's function right um other classic programming languages have things like variables we're going to talk about that oh yeah yeah absolutely so that's that's obviously next in the queue because yeah any language that you're going to use any programming language has to have variable support you have to be able to declare variables and powershell is no different now the thing about powershell and the powershell variables is particularly when you talk about scripting languages it's a little different now first off let's talk about what defines a variable anytime you see something with a dollar sign in front of it in powershell that's a variable okay so you know dollar food dollar bar dollar lunch dollar whatever now the thing i this is another thing that i i say a lot and people are like okay mike we get it everything in powershell is an object and this is unique i shouldn't say unique but this is different than certainly corn shell or bash shell in a lot of previous scripting languages where everything was like a string or a number right powershell a lot of its richness and functionality comes because everything is an object everything has these met things things called methods and properties well so in sql terms right give me an example if i'm going to be manipulating sql server using powershell what are some of the objects i'm going to manipulate ah you see so that's the thing is is it doesn't quite translate to a sql server model it sort of does because when you have your your properties those are attributes so let's say for example i have a table with columns in it rows and columns you know each my primary key has a bunch of columns attached to it with data in there so we might have our our object and have all these properties associated with it that say okay here's the other things that define this object methods though are functions methods are specific actions that we can take and if we look here looking at the slide on the right you know we can see what this actually sort of looks like so we declare a string variable called t and i give it the value of hot now this is a system.string a dotnet string variable and attached to that is some methods and the methods and the properties now the property here is length this is an attribute that doesn't change as long as the value of t remains hot and it's always going to be three and we can just call it as an attribute but let's say you know in sql server it would be car index right we would do a car index lookup of where the letter o appears in powershell or in the dot net model i should say it's index of and so it's just a function we call to say okay where's the letter o because we could call the same function and say where's the letter t where's the letter h where's the letter j which would come back with a zero because there's no index value of that so this is really to me one of the key foundational pieces of powershell it's this object aware model because it gives us this richness of data really around our variables how we're declaring things so with that i think we're ready to jump into another demo here to see just how this works sounds good let's take a look at a demo all languages need some sort of variable support and powershell is no different variables in powershell are notated with the dollar sign in this case i have a string variable called string with dollar string as the name of the variable i'm going to assign a value to it using the equal sign and the value is this is a variable if i run that individual line you'll notice nothing comes back because what we've done is we've assigned the value into the variable and now if we call the variable itself we can see what that value is and there it is this is a variable now everything in powershell is a strongly typed.net object so every variable we create is not just a string or an integer it is a dotnet object relating to that we can see that by using the get member commandlet i'm going to pass dollar string to get member and we're going to get the output let's scroll up here and look at this so the output first off tells us it's a type of system.string and because of that we get all these methods and properties all dotnet objects have these additional methods and properties which give us a extra level of robustness when dealing with our variables and the data contained within so we can call methods which are essentially functions on those variables and what are contained within them or we can look at properties which are attributes of those variables now as i said everything's a strongly typed.net object and this is goes for dates so let's create a date variable and we're going to use getdate to create it we're going to call the currentdate we will assign that value and now when we call that value using the variable you'll see that we have the date of wednesday june 8 2016 12 24 pm to investigate what type of object this is we're going to take that variable and pipe it to gm which is the alias for get member and as we scroll up and look at the output you'll see that we're dealing now with a system.datetime object and our methods and properties are relating to a date time object not a string object as we were previously looking at if we look at that a little more closely in detail we can call those methods and properties by using dot notation so in this case i'm calling dollar date and i'm going to look at the day property i'm going to look at the day of the week property the day of the year and then because the date that i grabbed was the local system date time i want to run a function to universal time to see what my utc time would be so we're going to run these four lines and you'll see that our output now is eight is the day so it is june the eighth so there's our eight it's wednesday which is the middle of the week it's the 160th day of the year and utc time is wednesday june 8th 2016 6 24 p.m so already we can see that we have a whole additional level of functionality using these strongly type.net objects to see different parts of our data and expose different aspects of it now what we've been talking with so far is implicit data typing you'll notice that we didn't declare anywhere in there what type of data was when we created the variable we can do explicit data typing if we want to say i want my variable to hold a very specific type of data in this case i'm going to create a string variable called date string and by using the brackets in front of it bracket string bracket we're going to explicitly type that variable as a string variable and then i will assign the value of get date to it which would be the current date now if we call the output call that variable we will see that the output is 6 8 2016 1227 and the formatting is different than what we've seen before the reason for this is is because now we're dealing with a string and powershell is using our regional settings to appropriately assign the formatting to it if we look at get member for it using that gm alias we can see that we're dealing with a system.string object if we scroll up a little here we'll just see that again there's our type name system.string and all the methods and properties that would come with a string object this allows you to control what types of data you're working with if you feel that's necessary however most of the time implicit data typing is going to give you what you need it all depends on what your use case is going to be for another example of that everything is an object i'm going to create a new file i'm going to use new item create a item type of file and i'm going to create it in this path ctemp and it's going to be junkfile.txt i'm going to assign the output of that commandlet to the dollar file variable and now if we look at the methods and properties of that file object you will see that we're dealing with a system.io dot info object so we're dealing with an entirely different type of object for our file and this gives us other methods and properties let's go back to the script pane and look at some of those properties let's say i want to look at the name or the full name or the extension or even the last write type right time of my file we'll run this and our output is our name is junkfile.txt the name of the file itself we have the full name which includes the path and we know that it's a dot txt extension the last write time was wednesday june 8th 12 28 pm so very quickly we have access to different types of data around our objects and this goes through the entire scope of the powershell language as everything is an object we'll clean up this file from our example so that should show people how to use objects and variables within the powershell language right there's there's some trickiness to it and i think a little bit for particular sql people to get their brains wrapped around but it starts to make sense when they when you start working with it all right so you mentioned earlier that one of the other capabilities of uh of powershell is a thing called the pipeline where we learn these different commands we can string them together sure let's talk about that all right well yeah the pipeline so the pipeline is something that's been in scripting languages for a fair amount of time right it's it's been around uh in again the the linux the unix variable and yeah the linux and the unix environments it's also been i i'm pretty sure it's in the the command window i'll be honest i used it years ago yeah to pipe things to more so i could scroll the screen right right so the idea is is just to extend the functionality right to take output from one commandlet one piece of functionality pass it to another and continue to process down the pipeline uh i like to think of it as gates that you're taking information and passing it across the gate to the next section right across the gate and you can do this i don't want to say add infant item but you can do it pretty far out and it really does give you a lot of flexibility to work with the language to extend what you're trying to do particularly from an ad hoc perspective i tend to do pipelining a lot from ad hoc because i can then you know say okay well i'm gonna for example i was talking about removing files i can get a list of files i can do it to the pipeline you know do some additional filtering and then finally remove it and i'm all doing that ad hoc all ad hoc if i'm writing a script i don't tend to use the pipeline quite as much because i tend to want to be a little more verbose in my scripts to make things readable but the pipeline is is just it's huge you've got to kind of understand it so i think with that we'll just dive into a demo and talk about how we use the pipeline itself sounds good let's take a look the pipeline has existed in scripting languages for some time now as a way to extend functionality of a single command line execution we've been using the pipeline in some of the previous demos as a way to explore the objects using get member to go through this again i'm going to create a string variable and then we're going to take that string variable and pass it to get member what's really going on here is we're taking the variable and passing across the pipeline to get member which is the next command in the pipeline when we run this we get the expected output of our methods and properties for a string variable the next way to use this is with some collections we know there are about topics that we can look at in the powershell help system and the way to look at them is to get help about star however what we can then do is to see how many there are we can pass it to a commandlet called measure object so we're going to get the collection here and then it's going to be passed across the pipeline to measure object to give us an output when we run this the output is 131 for the count you note we don't see the actual collection we see the result of the end of the pipeline which is the measure object let's use this on some file system manipulation i'm going to go ahead and create a sample directory that we can work in and then what i want to do is take this content the quick brown fox jumps over the lazy dog and i'm going to send it across the pipeline to a commandlet called out file out file is a commandlet that will create files for us and populate that file with the content we pass it so we're going to take this content pass it to out file and write it to see test dummy text and then we're going to use notepad to open that file up to see what the content is when we run this as you can see we get a dummy file and there's the content that we just wrote to it using the pipeline the pipeline can also be used to manipulate multiple things just as we saw with the help topic we saw that we could process it with measure object what if we wanted to remove multiple files with one command i'm going to go ahead and create four additional files in the test directory we'll do a quick clear screen and then do a directory on that on c test to see what's in there and as you can see we have five files the dummy and junk one two three and four now what we can do is that directory gives us a collection of files so if we run the same directory lookup and pipe it to remove item we can delete all those files with one action see that it doesn't give you any sort of output or feedback but now if we do a directory lookup on c test there's nothing there this also stresses an additional point powershell is a very sharp knife you should be careful you don't cut yourself with it as you noticed that action we did with the remove item didn't give us any sort of warning or ask for any sort of confirmation it just simply removed the files so be careful when you're working with these commands and the pipeline to make sure you don't do anything that you don't want to do so let's get a little fancier now one thing that sql server administrators always like to know is how much free space is left on their servers we can do that using one line of powershell extended out via the pipeline there is the wmi or the windows management interface and we can use git wmi object to access that we're going to use that to get the win32 volume class which will show all attached volumes for our server now we want to filter on that a little bit because we may not want to see all those so i'm going to pipe that to where object and filter it on drive type equal 3 drive type being a property of the win32 volume output next i'm going to pipe it to sort object and i want to sort by the name i want to look at my drive letters ascending to descending and then finally i want to output certain fields with certain formatting so i'm going to use the format table commandlet with a bunch of other stuff and don't worry too much about this you can look at it at a later point but really what we're doing here is we're just doing some formatting to get the output looking the way we want it to and when i run this i have a quick and simple report of how much free space is left on my server now i only have one drive on this demo server but if i had multiples including mount points they'd all show up here the next useful pattern is is deleting old log backups or deleting files in general one of the things i know we as database administrators can struggle with is sometimes getting rid of older backup files and keeping enough space on our drives for new backups to be written to what we can do with powershell is manage that quickly and efficiently instead of having to go through and pick manually through your file folders to find the files to delete i can get rid of most of my old files with one line of powershell using the pipeline first off i'm going to do a directory lookup on my sql server where my backups are stored i'm going to use the recurse switch because i want to look through all the subdirectories as well this gives me a collection of a lot of files so i want to filter that down and i'm going to pass it across the pipeline to aware object and i only want to filter on transaction log files last with the last write time older than three hours ago finally i'm going to take it pipe it to remove item and i'm going to use the what if flag just to say show me what you're going to do but don't really do it when i run this i get an output of a lot of actions saying hey we're going to be removing all of these files and if i look at the timestamps in the file name i can see that it's everything that's older than three hours ago so this is a handy way for us to quickly clean out old files old backup files that we may not want to use and the important parts here are obviously getting a directory lookup of all the files we want to work with and then filtering it appropriately based on our extension and the last write time now a handy trick here is as sql server dbas if we have to delete a lot of records from a database table one of the things we'll tend to do is run a select statement with the criteria first and then if that all matches to what we expect put the criteria into a delete statement we can do the same thing with the pipeline and the powershell commands for example if i wanted to run this command and see am i getting the right files before i actually pass the remove item i can execute everything up to the last pipeline so everything previous to the remove item run it and get a listing of files and i can look at those files and say is that what i wanted to delete if it is then all i have to do is add pipe remove item because i know everything that's going to be passed from this call will be passed across the pipeline to the remove item and that's all it will act upon so the pipeline is a very useful piece of functionality that really allows you to flex out your sql server and powershell commands everything that you're trying to do across your environments all right so there's the pipeline and how you use it and like i said it it really does add a lot of flexibility robustness to using powershell particularly particularly in an ad hoc sense all right well so we've learned a ton in this module about powershell as a language as a framework and we need to to get closer to applying that to sql sure we'll do next terrifying that to azure but why don't we take this opportunity and kind of sum up what we've talked about so far yeah absolutely absolutely so what we've seen is that powershell is a framework to provide automation across the windows platform right we've talked about it kind of generally that way and we're going to get more specific to data professionals going forward yep right we saw that we could use powershell inside a command window or we could use the ise to write and execute our scripts sure we learned that commandlets are the building blocks for powershell scripts and we're grateful that the word commandlet was invented because it makes searching on msdn a whole lot easier yeah absolutely um we also saw that everything in powershell is really an object and that's going to be really useful for being able to uh take advantage of the types provided yeah like i said that is just that is a huge concept that i really want people to to kind of embrace is that.net object thing and then for extended functionality especially for ad hoc scripting in a command window um the pipeline is a really useful thing yeah yeah the pipeline is is huge so right yeah no that's that's pretty much it for that first lesson all right well uh when we come back we'll dive into using powershell with sql server in module two thanks hi welcome to microsoft virtual academy we're here for module 2 of our course on powershell for the sql data professional i'm chris randall here with mike fall and we're ready to talk about using powershells with sql server yep so mike what we're going to talk about so we've got a couple different things to talk about here so first off we want to talk about the sql ps powershell module so we talked about modules in the previous module right uh powershell modules are things that we can extend the functionality of powershell out within our session so there's one specifically for sql server and it's called sql ps then we're going to talk about the sql server provider so with the command line and the way that we're using powershell and any other command line operations we want to interact with things and usually the best way to do that is with as if it were a file system so the provider gives us a way to interact with sql server as if it were just folders and directories and files and whatnot for all the different objects then we're going to talk about sql server specific command list so commandlets again the basic building block of powershell functions that allow us to do things and there's a set within the sql ps module that are specifically meant for managing sql server and sql server operations so are we going to be able to initiate backups and restores using these commands backups and restores calling sql and sql scripts uh those are kind of the basic functions there's a lot of functions in fact there's a whole section on availability groups that we're not going to cover today because it's a little more advanced but there's a bunch of different commandlets to execute different sql server tasks yes uh next up we're going to talk about the sql server management objects so the smo is a net library that powershell can interact with just like anything else again we go back to powershellsbuilt.net so the smo allows us to interact directly with these to maybe do some more stuff with sql server that we couldn't do with the command list final well not finally next up we're going to talk about powershell and the sql server agent so with i believe it was sequel this out i believe it was with sql server 2008 when they introduced powershell sql server together they gave us the ability to run powershell scripts as sql agent job steps so we're going to talk about that so instead of instead of using t sql as right the source of the job we can use powershell absolutely absolutely cool and then finally uh one thing i would like to do when i when i instruct powershell to people and show them powershell with sql server is you know what do i actually use this for what's some practical use cases so we're going to go over some practical use examples of powershell and sql server some tasks that i've automated in the past and how i've gone about it we're gonna have lots of demos along the way lots of demos along the way let's let's dive right into sql ps all right well yeah so sql server ps or sql ps is powershell module now it was introduced with sql server 2008 r2 i believe and contains two areas of functionality the provider and commandlets we talked a little bit about the provider and the command list already and this is really the breakout there's not much more else in the ps module but these are i mean these are pretty big areas we load it using the import module sql ps command so import module again going back to that verb now nature of powershell import module says we're going to load a module into our powershell session sqlps is what we're going to use and so once i've done that i've got access to the power got access to all of this server now again kind of calling back if it's in installed to the right path and you have more greater than powershell 3.0 installed then you can call a commandlet and it will automatically load the module for you but it doesn't hurt to import it i always like to explicitly import modules when possible now the one other thing is it does require powershell 2.0 to execute uh now we're on powershell five so that shouldn't be a problem but depending on 2.0 or greater or you need two oh and five two oh and greater great yeah so one of the things that we we hadn't really talked about but with each version of powershell uh they've they've supported all the previous stuff right so if you have powershell five you can support everything back through history now one thing i do want to call out is you know some people might see this in the documentation from microsoft that uh sql ps is being deprecated so there's a subtle difference here there's this thing called sqlps.exe which is a command line executable that loads the sql ps module and sets a few other things uh perfectly honest i really don't use it because i use everything straight up from powershell but it does when you if you do a right click in management studio and you say start powershell task it's it's gonna run sql ps and get that all loaded that's going away eventually right it's it's the same with any sort of whenever something comes up on the deprecation list you don't know when it's going away it's just going away at some point so we'll just continue to use what we've already started to learn how to write which is the powershell command window right or the ise import the module and off we go yes yes and i just i'm only calling this out kind of as a technicality now some people also might ask well how do i install it right that's that's obviously a big question anytime you install any sql server component you're going to install the sql ps module which is kind of nice but at the same time kind of frustrating because i can't install it separately i i have to install some sql server component usually you know and honestly right now now that they've now that microsoft has broken out sql 2016 management studio into its own release you know you can install that separately as soon as you install the most current tools with the management studio you get sql ps so just to make sure i'm clear on that right the the the the path of least friction to get sql ps is just to install the monthly sms build right exactly exactly right so with that why don't we just dive right into a demo here and show what sql ps is all about how we load it how we start working with it sounds good let's take a look at the demo this demo provides an overview of the sql ps module the sql ps module is the powershell module specifically designed to manage sql server through powershell to load the module we're going to use the import module command and call sql ps as the module we're going to load executing this doesn't really give us anything back but the module gets loaded into memory we can see what commands are available to us in the module by using git command module sql ps executing this gets us a list of the commandlets their names and the version of them as well as that we're in the sql ps module in total there are 46 commandlets one sql server provider function and two aliases that are used to work with commandlets existing in the provider we can find out where the module lives on our system by running get module list available sql ps and it will give us the full information of the module so we have our module output as well as a directory that directory being c program files x86 microsoft sql server 130 tools powershell modules as long as you use the default location for your sql server tools install this path will remain the same and you will always be able to find your powershell module installed under there however if you use a non-standard location what you will be looking for is this path right here the tools powershell modules this will be relative to wherever you install the sql server tools now there's a couple gotchas to call out with the sql ps module prior to 2016. if you're using the version prior to 2016 you will get a warning message when you import the module about two commandlets that don't match the naming standard i call this out because the warning doesn't actually mean that the module didn't get loaded or was only partially loaded the module gets completely loaded when you see that message however it just is telling you if you're looking for specific commandlets there are two that don't match the naming standard and you might have trouble finding them another issue was when prior to 2016 if you loaded the module it would automatically set your location to be sql server the sql server drive this has since been corrected in 2016 so that when you import the module it doesn't change your location and leaves you wherever your current working location is this these two items are important because it stresses why you should always be using the most current version of the sql server tools you can easily manage previous versions of sql server with the updated version of management studio so i strongly recommend that if you are going to be working with it you get the most recent version of the sql 2016 management studio and the sql ps module so that was the basic overview of what the sql ps module is right we get the commandlet we get the provider we can load it and that's just how we get started but there's a lot more to it all right so you mentioned once we've got the sql ps uh module loaded that gives us access to the provider absolutely absolutely talk about the provider yeah so the provider let's think about it for a bit you know step back and we say okay we're working with a scripting language what does that mean well that means we're doing everything from a command line we're doing everything from basically all where we can do is just enter text right we don't have things that we can click on and that somewhat limits what we can do so the idea is is that when you work with a provider and there's providers for everything right there's not just a provider for sql server there's a provider for active directory there's providers for the registry keys and these providers allow us to get into these parts of the windows stack the windows platform and work with them as if they were a file system so we're used to changing directories and drilling down through folders and direct and all that well the provider says okay well now we're going to do that but with sql server we're going to drill down into servers and into databases into logins so your your path uh i shouldn't say a path you're prompt in the command window instead of being drive letter folder level is gonna be sql server name database name table name more or less right so to get the provider we're gonna go ahead and import the module and once we do that then we can we can do change directory sql server and now we're in the sql server provider we're not in a specific sql server but then we get this as you said command line you know what are we looking at for the folder path right and this is a pattern that this isn't like strictly what we'll see when we we would show up but this is the sort of pattern that all the sql server components are going to show up with in the provider we're going to have the sql server drive that's the sql server colon right and then sql is the folder this is the part of the engine if we actually and we'll see this in the demo there are other components that we can work with within the provider but then we'll have a server name or a host name and then because we have multiple instances right we can have a named instance we're gonna have the default instance then we can then we'll have that folder as well and you just browse down through that and then into databases or into logins or into tables so and so forth so coming at this from a t sql perspective i look at this and i think oh this would be an interesting way to get a list of all the tables in this database right whereas in t sql i might do that by querying a system view right right and in fact the system view the dmv uh comparison is very apt uh because and then some people are going to probably ask that question like well why would i use the dmv when i can use this and we talk about that in the demo uh really the the reason for me is is because we can go across multiple servers and we'll see some of that again well actually let's just take a look at it right now we'll just dive into a demo and see how this all how the provider works and how we can leverage it more than just saying well okay great i can go and look up a list of tables right all right well let's take a look at a demo on using the powershell provider for sql server this demo shows how to use the sql server provider a portion of the sql ps module used to manipulate sql server as if it were a file system providers are a common piece of powershell and exists for many different parts of the windows stack to look at all of your providers you can run git ps drive to display them as you can see we get a variety of providers such as file system providers which are the normal system drives that we're used to using such as the c drive and the d drive but we also get other providers such as the alias the environment and several others the one we're focused on is the sql server provider which has its own drive letter that we can change into changing into us into the sql server drive is as simple as doing a cd and then we'll do a dir to do a directory lookup of what's under the sql server provider we have several different components all relating to sql server the key point is is we're browsing this as if it were a file system but you'll note some familiar things such as data collectors or maybe sql registration the sql server engine is what we will focus on here and it is under the sql folder and we can change directory into that with just a cd sql now we're also going to go look at a specific sql server so i'm going to change directory into the sql folder and then the folder underneath it for the machine i want to look at in this case picard and then we will do a directory lookup you'll note that what it displays is the instances install that on that machine sql server can have multiple instances and have named instances so we have to be careful about what folders are going to be under the machine in this case we only have the one it's the default so to then browse into that we would do a cd default to look at the default instance and then we'll do a directory lookup there to see what's in there the listing we get is pretty familiar a lot like what we would see in management studios object explorer we have audits availability groups databases logins a variety of components that all represent things that we've probably worked with in the past and again these are folders that we can continue to browse into as if they as if we were looking at a file system to see what was in those subdirectories let's go ahead and look at our databases by simply doing a dur database running this brings up one record back for wide world importers and some information about that database things that we're probably used to seeing the recovery model the collation and the owner now what's obviously missing are the system databases the reason for this is is the way that the provider was implemented is that was to make all system objects hidden and only displayed if you specifically wanted to display them to do that we would use the force switch for our directory so just like we did directory of databases this time we'll do it but we'll use the force switch and the result is now we get all of our system databases appearing as well so this is very useful particularly for somebody who's used to working from the command line and browsing through file systems as with everything else everything in the provider is a dot is a net object we can determine what kind of object is by doing the directory of databases and using the get member call to see what kind of methods and properties are available to us there are quite a few and once this completes yeah so what we will do is we will now look at the full console pane and i want to scroll up past all the properties and methods and at the top we have a type of microsoft sql server management smo database so we're dealing with smo objects now there will be more on the smo and the objects that it are within it in a later lesson but the point here to understand is everything in powershell is a net object and it's no different with our databases because of this we can make use of methods and properties attached to those database objects so i'm going to do another directory of databases but this time using select object i'm going to show some specific properties we'll run this and the output we then get is a listing of our databases but now we can see when they were created their size and when they were last backed up so we now can make use of this to get even more information about our databases of course a lot of people are going to say hey you know this is just like a doing a select from sysdot databases how is this different for me we're going to talk about that in a bit but because we're in the provider and using the file system we get a little more flexibility about looking at multiple objects at once now if we wanted to look at other objects within our database it's just a matter of drilling down through the folders that represent the different parts of the database so for example we know that we have a wide world importers database so if we do a directory of databases on and then wild world importers and then tables we're going to be able to see all the tables within our database as you can see as this listing returns we get all all of our databases with the schema the database the table name and the the time that the table was created so everything here is a way for us to browse and explore our sql servers from the command line using some simple directory commands and browsing as if we would normally do on the c drive for the d drive where this really starts to become useful is across multiple instances and building loops to allow us to process multiple objects at once i'm going to take an array of my two instances picard and riker and then i'm going to wrap it up in some powershell down here now if this is somewhat complicated i'll let you pick it apart on your own at a later point but really what's going on here is we're taking a list of our instances and then for each one of these i'm going to do a lookup of the databases in that instance do some sorting on it and then pull back only specific properties for that list of databases so let's go ahead and run this and we get a listing of all the databases across our environment when they were last backed up how big they were this gives us a lot of flexibility for querying across multiple instances and this is really where the strength of the provider comes to play because we can do loops and we can use powershell logic across multiple instances with the provider we can we're not limited to just pulling one instance at a time we can very easily pull information across our entire environment because to expand this out to even more servers all i have to do is add their names to the array and the rest of this logic stays the same [Music] so that brings us kind of through the provider and how we can use the provider how they actually interacting with sql server as if it were a file system you know how that that can be advantageous to us all right so we can do that now let's tie this back to what you talked about last module which was commandlets which really seemed to be the the guts of uh working with uh powershell you learned what the command lists are that we need right right um and presumably you're gonna show us some commandlets that have to do specifically with sql server yeah yeah so again commandlet's fundamental building block there are functions within powershell that allow us to do to do specific actions and sql server has some of its own now there are 46 commandlets for sql server 2016 and we're going to go through those a little more in detail but to give people a high overview a high level overview of what these are now they break down into four categories now full disclosure here these are my categories microsoft hasn't actually defined any categories around the powershell command lines but this is how i tend to think of them and the focus areas are really four different sections we have general purpose command let's things that just do general actions within sql server like running sql scripts or maybe getting a sql server object and we'll talk more about how everything in powershell is an object we'll we'll talk about how that applies to sql server something just caught my eye did you see running sql scripts so i can take my library of t sql scripts and i can execute them from within powershell absolutely awesome absolutely awesome well and that's the thing right is is so again we come back and we talk about powershell and t sql people like well powershell or t sql no it's powershell and t sql and there are commandlets that we can use to wrap around t-sql things that we're used to that we're familiar with and just execute them in that powershell framework so we can run the same thing across multiple servers so general purpose is is one of the focus areas cloud management and we talk about azure we talk about everything going to the cloud so there's some specific sql server commandlets that are useful for cloud management again full disclosure i don't use these so much because there's probably some better ways to do them and i think either using azure specific commandlets uh you know there are two different teams developing the sql server tools and the azure tools so sometimes they're they're dealing with the same sort of problems uh backups and restores are huge right so there's a set of commandlets specifically developed for backups and restores and not just you know typical backups and resource but then we start talking about the azure aspect there's some commandlets in there to actually manage what's called the managed backup where you can set up and a backup of your database directly to azure i don't think we talk about those quite in the demo but that's there and that's kind of cool and that's the the hybrid nature of of what microsoft is moving towards and well and just to to advertise our own products uh we will be adding courses on some of those topics as well and extending some existing courses here on mva and other sources to touch on these right and then the final area is availability group management so there's a lot of commands in fact over half of the commandlets are dedicated to availability group management now we're not going to talk about that because that's a fairly advanced topic in fact i have a whole other presentation i've given before on that and there's just a lot of information to cover but i want people to be aware that they're there and that they might want to use them down the road so with that you know let's let's dive into a little more detail here let's dump into a demo and see some of the commandlets how i've used them how you know some practical patterns around them and what people might be looking for when they look at commandlets sounds good take a look at the demo this demo goes over how to use some of the commandlets in the sql ps module commandlets in the sql ps module are intended to manage aspects of sql server we can see the listing of all these commandlets by using git command module sql ps i'm also going to run git command module sql ps and pass that to measure object just to see how many commandments we have available to us we have a big listing of different commandlets the really the important thing here is we have 49 different commandlets the reality is we actually only have 46. three of these in the count are we have two aliases and one function for the sql ps provider so those are included in the overall account the reality is there are 46 commandlets for different aspects of powershell now we're not going to look at all of them i only want to touch on a couple that are important we're going to start with invoke sql cmd one of the main things to do with any sort of sql server interaction is to query the database inbuilt sql cmd is how we can run sql queries in powershell the way it works is we're going to have a sql statement in this case a statement that does a count of databases by database owner and then we pass it to invoke sql cmd we also give it a server instance name a database where we want to run the query and then the query itself if we execute this the output is what we would expect a name column and a db count column giving us the name of our owners and the count of those databases owned by that owner so pretty straightforward nothing surprising here now some folks might be saying hey i've had sql cmd the command line executable since sql 2005 how is this different why would i use invoke sql cmd over sql cmd well let's take a look at that i'm going to go ahead and take the output of that query and put it into a variable for sql cmd and then another one for invoke sql cmd we run this and then let's look at the outputs themselves we'll look at the sql cmd output first and it is what we're expecting however we get kind of some wonky formatting and we have this word wrap going on we have uh the things because of that word wrap our rows aren't aligned so this is somewhat problematic and we get what we're looking for but it's difficult to really make use of whereas if we run the invoke sql cmd out we get a lot cleaner formatting and the reason for this is is let's look at the types of objects we're dealing with again everything in powershell is an object so if we look at the objects in these two outputs we can see that with invoke sql cmd we have a data row with sql cmd we have a string so this is a key key difference between the two outputs and why you would use invoke sql cmd over the other by using a data row object we have a richer more robust data type that we can work with in fact if we look at the whole output using git member we will see that we have the system.data.datarow and we have methods and properties specific to manipulating the data what's most important is our columns appear as properties so now we can reference them directly instead of having to do string parsing to look at specific columns we can call the name column by simply using dot notation so dot name for invoke sql out and we get our outputs of the columns so this is how you can query the database in powershell and gives you a lot of flexibility for how you can work with your data once you get it out the possibilities are endless for how you can manage this and we'll see some other examples of this as we move through the other lessons let's now move on doing backups sql server backups are an important task and powershell gives us some commandlets to manage backups as well as restores we're only going to look at the backups for this example first off we have a backup sql database commandlet and it takes some familiar arguments such as server instance database that we're going to back up a backup file and then if we scroll over to the right we'll see that there are some other switches such as initialize and copy only that we've come to expect as sql server backup syntax now i'm going to make the first call using this script parameter because i want to show you something specifically it's this t sql command what's going on behind the scenes with any of the sql server commandlets is nothing funky or amazing as far as interacting with sql server but t sql pure and simple we're going to be running this backup database command when we call the powershell commandlet so it's nothing out of the ordinary or nothing complex everything we've come to expect when running a sql server backup one thing to also note is that the command also works the same way as other tools that interact with sql server you'll note that this sql output looks a lot like what you would encounter in management studio if you did a script action for what for doing a backup if you filled out the gui then clicked on script in the other upper left you would get this output so the other thing to understand here is powershell is working with sql server in the same way as other tools so let's go ahead and run our backup and then we're going to do a directory on the a directory lookup on that location just to verify that the backup occurred we get a progress bar indicating the backup action showing where we are and now if we do a directory lookup there's our backup file just like we expected now you're probably seeing yourself well great i can do that in t sql though why would i need to do this in powershell and the thing is is you need to understand that powershell is a framework and allows us to do things across multiple servers and with the stack so i want to show you a block of code here i'm going to first clean up make sure that we don't have any files in this c backups directory so it's now empty this is where we're going to be doing some work and what we're going to do is we're going to backup all the system databases across all of our instances into this one location so this next block of code i have as we've seen before a collection of my servers so i have an array to work through and i'm going to do a for each loop i'm going to go through each of these servers and the first line is going to get a list of all my system databases using the provider so i'm doing a directory lookup using the provider and showing all the system objects where system object is true this gives me a collection of databases to back up next i need to see hey do i have a folder for this server instance if not i'm going to create that that's what this line is here for and then the last line takes the databases and for each of them i execute the backup sql database commandlet and back them up to the folder i just created so let's go ahead and run this and now that that's complete we can do a directory lookup and see that we've created two different subdirectories so picard and riker and then we have our database backups for each of those underneath so by using powershell in combination with several of the things we've seen already we can very quickly back up all of our databases to the same location with the same command what's also nice is if we added another server or if we had a dynamic server lookup this block of code wouldn't change we'd only have to make sure we had the right collection of server names to work off of as you can see sql ps has some powerful commandlets in it that can give us a lot of functionality and flexibility with managing our sql server so that's the the basic use of commandlets again there's a lot of patterns there's a lot more advanced stuff that we could do with sql servers commandlets but that will get you started right that'll scratch the surface all right well going from there let's move into our next topic which is talking about sql server management objects or the smo or some people call it the smo either way mike's going to tell us about it right right so sql server management objects rolling back to everything we've been talking about up to this point is everything in powershell is an object it's not just any object it's a net object the sql server management objects or the smo is the.net library for interacting with sql server been around since 2002 it's it's been part of the library for a long while but it's it's what you use to interact with sql server now the thing i always like to stress with people again this comes back to the the kind of shared nature of all the tools is powershell's not the only thing that uses the sml management studio uses the smo so the way we're using powershell and the way we use management studio to interact with sql server it's the same everything's built on the same and there are other tools right that they all use management studio or i'm sorry they all use the smo to interact with sql server so that's that's a really important concept for people to get around now when we load the sql ps module it gives us all of these smo libraries there's a bunch of different libraries.net libraries that get loaded for the smo and so the nice thing is is we don't have to worry about loading all this stuff i think like if you're a c-sharp programmer right you got to include all these different assemblies with powershell when you we could load these individually if we wanted to but really when we type import module sql ps it loads all these for us so we don't have to worry about all of that and that's really that's it i think you know beyond that the best way to show people what we're going to do is to show another demo right to actually get our hands dirty with the smo all right let's take a look at a demo on the smo this demo will cover how to use the sql server management objects or smo within powershell as we've discussed previously powershell is built on the dotnet framework and because of this we can directly access dotnet objects within the language the smo is the dotnet library for working with sql server and because of that we can go ahead and create these smo objects directly in powershell to work with them for example i'm going to create now an smo server object we're going to use the new object commandlet and then we give it our object type so in this case microsoft sql server management smo server now when we use the new object we have to also use any constructor arguments for that object in this case the name of the server that i'm going to create the sms server object for in the in this picard so we'll highlight this line and then go ahead and execute it and now as with any new object we'll look at get member to see what our methods and properties are as you can see there are quite a few methods and properties for us to reference with this object and we'll scroll all the way up to the top and see there's our smo server object as the type just like we had declared to examine these properties we would use the dot notation method so if i want to look at the version i'm going to call the server object dot version and this will give us an version object with the major minor build and revision values now if i want to see this as if i normally see it in management studio i can just call the version string property and you can see we get 13.0.1601.5 which is the sql server 2016 rtm version some basic information that's really easy to access using this dot notation but we can get deeper what if i wanted to look at all the databases i can call the databases property and by running this you'll see that we get an output that looks like what we would normally get if we did a directory lookup of the sql server provider databases for this instance again stressing that when we use the sql server provider or the sql server commandlets we're using the smo everything is working through the smo the big difference here is we don't have to specify the force flag because it's not abstracting away the system databases from us that's an implementation of the provider with these databases we can drill a little deeper so let's say i wanted to look at a specific database the msdb database and a table within it the cisjobs i simply have to use dot databases specify within the brackets the item in the collection that we're going to use and then the same with tables we'll run this and then we'll run the get member on that output to see what sort of object we're working with again we have a lot of methods and properties so we'll scroll up to the top here and as you can see we're working with a microsoft sql server management smo table object so we have smo objects to represent almost anything within sql server all these objects have their own methods and properties as well so we can look at the indexes property which is a collection of all the indexes and you can see that we get the names of the indexes but this is just what's getting displayed back to us each of these indexes is its own smo object that has its own methods and properties what's also nice is most of the objects in the smo have a script method the script method if we run it will generate a t sql statement for that object after running that you'll see that we get a t sql statement to create the cis jobs table and it doesn't look all that different from if we had done this within management studio again stressing the point that management studio uses the smo and so we can get the same results in powershell by accessing the smo so this is a handy way for us to generate information for a practical use case we can say well what if i wanted to generate the logins on my sql i'm going to go ahead and clean out an old test file here using this statement and now that i've done that i'm going to run this line where i take the server object and i look at the logins collection i take that collection and using the pipe operator send it to a for each object commandlet call and what this does is it says for every object in the collection that comes to me i'm going to execute this script block and the script block says i'm going to script out the current object that i'm on send it through the pipelined out file and write it to this logins.sql file and we're using the append switch because otherwise each execution would overwrite the file that we're creating so if we run this and then use notepad to open it up you'll note we get a basic sql script with create login statements for all our sql server logins now one thing to call out about this is and this is just the same as if you did this within the ssms for every sql login the smo will actually generate a random password and then give you an alter login disable this is a security implementation so just the way the smo works and just something to be aware of that this password is not actually it does not relate to the real password in sql server but there are ways to get that and more advanced scripts that you can find out on the internet that will show you how to actually copy the logins with the passwords applied but easy way to script out our different objects and we could take this to other objects within our sql server so the smo is good for retrieving and querying information what about creating objects within our database is it useful for that that's a little trickier let's start out by trying to create a database so i'm going to create an smo object for a database smo.database and the constructor is the server object that i want to create it on and the name of the database so we're going to create that and then call git member just to see what we're looking at as you can see we have lots of methods and properties to make use of and they're all database related these are all specific to the database that we're working with as we get to the top there's our type that we just created now what we have is a database object but a database itself hasn't been created on the server yet and we can prove that by looking at a directory lookup of the databases on our server you'll note there's no smo test database yet so to create it we'll call the create method and now if we do our directory lookup we can see that we have two databases smo test and the wide world importers so it's really easy to create a database with an smo object the thing to understand is we haven't declared anything else about it and this is essentially like creating a database with all the defaults in sql server as a dba i always like to specify names of files locations of files and size and growth of files and we didn't do any of that here so let's actually drop the database we can use the drop method to do it and now let's walk through how to create an smo object database using all these declarations what we first do is we create a series of objects one for the database one for the file group one for the data file one for the log file and we have the various constructors for that for the data file i then go and specify some other values such as the physical name and location of the file i have to declare the size in kilobytes because that's really the only value that it will accept for the smo for the growth we can declare growth type kilobytes or growth type percent i'll then add the file group to the database object and then add the file the data file to the file group object once i've done that i will go through the same set of settings for the log file and then add the log file to the database so let's go ahead and run all that we now have a database object that we can script out and this gives us a create database statement that looks like any other create database statement that we use in this case it has the create database it has on primary and it has our file declarations that we just specified in the object and now we can create it using the create method just like we did before and we can do a directory lookup and there's our smo test object with the wide world importers database so then comes the question of which is more useful to do your database and object creation smo or t-sql personally i like the t-sql because it's a little cleaner and easier to work with as you saw creating a database within the smo can be fairly verbose with a lot of things to set and configure so really it comes down to what your use case is but i tend to lean more heavily on the t sql and if i need to script or parameterize it i'll wrap powershell around that but at the core i'll be executing t sql to create my objects we're going to go ahead and drop that database using the drop method and this concludes the demo on how to use the smo within sql server in powershell so that's the smo and you really can get down and dirty into the details of working with uh net objects you the provider gives us a lot of stuff the commandlets gives us a lot of stuff but if you're limited and you're like well i need to do a little bit more the smos your pathway to doing that all right well let's let's take this now and and get progressively more practical um in our next lesson we want to talk about using uh sql jobs in sql agent using powershell and then mike's going to show us some additional practical tips yeah so that's the thing right is with sql 2008 r2 they came along and they said okay well we got this powershell stuff so we're going to go ahead and give you the functionality to run a powershell script as a sql agent job you know you can do powershell as a windows scheduled task you can set up other job schedulers as a dba you know power like to have it all in the agent's my warm fuzzy blanket right it's this is what i like to use i'm used to this as a job scheduling tool so using the sql server agent to run powershell scripts is great now there's a couple of things about that when we do use the agent it does a couple things for us so it will automatically load the sql ps module for us so instead of having to type import module sql ps at those in those scripts and we'll see how we construct that script and that job step in the demo uh but we don't have to do that it's automatically done for us now if i if i did do it would it throw an error um it could okay and this is this comes into the gotchas so there's a couple gotchas with using uh powershell in sql agent jobs and the first is with sql 2008 2002 and 2012 you're locked to powershell 2.0 and that's i don't know really the the reasoning behind that uh it's that's just the nature of the beast and but they fixed that in 2014 and in 2016 and again this is when you're using kind of the client tools they they fix that right so now it will use whatever the current version of powershell that is installed in the boxes now the second is previous to sql 2016 the sql ps module when you loaded it would automatically change your directory to the sql server drive the sql server provider and because of that that could throw some errors if you try to do file system operations so if i've got a job that's going to create a a date stamped folder to hold a desk right uh not hold the demo to hold hold of the backup um i'm going to need to uh explicitly switch to the to the c drive to the file yeah and that's you know that's honestly just kind of a good habit if you're writing powershell scripts that execute in a sql agent shop just do a quick cd c drive at the top or whatever is an applicable working directory for you and that will get around that it's just one of those little gotchas and and again actually with sql 2016 if you have sql server 2016 installed it doesn't do that anymore they changed that the the sql tools team updated that with sql 2016 and so that is something you don't have to do once you start working with sql 2016. sounds good so the way you kind of go about this is if we uh if we look at just kind of the job step properties right what we're used to seeing when we go and right click through the gui and there is a way to do this through powershell but again that's a little more advanced but if we're just creating a job step you know first off we're going to create a step we're going to change the type to powershell simple enough now one thing i like to call out is people always ask about well what does this run ass right and just like any other agent job it's going to run as the sql server service account the agent service account unless you set up a credential or something right but by default it's going to be running is the sql server agent service account and that means whatever permissions you need on the windows operating system that the powershell script's going to execute under need to be applied to the agent service account and that's always a good argument for credentials anyway yes yes absolutely and then you just paste the powershell script into that that script window and off to the races so from a from a sort of day-by-day approach do you find it useful to write the script in the ise run it test it and then copy and paste bingo bingo the really the only gotcha there is the isc scripts are going to be running typically under my credentials whereas the uh the powershell agent job and that's something people you know just as you're doing this work kind of keep an eye out for that if you're like you put a powershell script into an agent job and it just doesn't run for whatever reason check your security maybe what you're running as and what the script is running as is different sure so with that let's just actually i mean again this is something to kind of get hands around and actually see how it's done let's actually jump into a demo here and show how to create a powershell sql agent job so it's demo time demo time demo time demo take a look this demo will show you how to create a sql agent job containing a powershell script one of the tasks that most dbas automate pretty early on in their setting up of an environment is backups backups are something that we always want to have done and can be a little tedious to do manually however one of the struggles that we have with doing backups is there's a lot of file system operations that have to take place in conjunction we have to have a valid directory path where the backup is going to be made to as well as we need to have some tasks cleaning up old backup files sql server can do these but it's not very good at it and this is where powershell has some real strength that we can leverage so what i have that we're looking at is a powershell script that does backups on all user databases does a couple things and we're going to walk through it first off i get a collection of databases using invoke sql cmd the query is pretty straightforward something that is commonly seen where we're going to select name from sys databases where database id is greater than four the user databases once we have our collection of databases i'm going to create a date string from the current date using the getdate commandlet the getdate commandlet supports an argument called format which will allow us to specify the kind of formatting that we want the date to come out in in this case four digit year two digit month two digit day hour and then minute this will be used within our file name so that we can have a unique backup file for each backup that has the date time stamp in it next we're going to loop through each database and execute a series of actions the first is we're going to check for a master path that we want the backup into so we want a separate folder in backup demo for each database that we're going to be backing up what we do is we create this path and then we're going to check to see if it exists if it doesn't exist which is the not what we'll then do is create a new directory with this name once we have that directory in place then i'm going to create a file name with the database database-thedatestring.bak i will join both the path and the file name together with this line with join path and then we have a full name for our backup i'll execute backup sql database against the database with the backup file and a few other switches and then once that backup is complete i'm going to get any backups that are older than a minute ago so essentially not the most recent backup and delete them so in this script i create any path that i need to back up to i do the backup execution and then i clean up any old backups so what do we do with this script well now we're going to go into management studio and i'm going to right click on jobs and create new job we're going to call this job demo backup job and then we're going to go to steps we're going to create a new step we're going to call this step powershell script and we're going to change the type to powershell now we're going to go back to our powershell script i'm going to select all of it copy it with ctrl a and ctrl c and then we'll paste it into the window just as it is once we have this i'm going to click ok click ok again and we've created our job now before we execute the job i just want to check our environment to show you what's going on i'm going to do directory lookup on the picard server c drive to show you that we currently have no backup demo folder or any subfolders under that so what our script should now do is when we execute it it's going to create that folder and back up our databases to it so let's go back to management studio and run our job we're going to start job at the step and it's going to execute so now that it's complete we'll close it out and we're going to now run our directory but this time we'll actually call out backup demo recurse and as you can see now we have the backup demo folder and under it we have a wide world importers folder the database on the server that we're backing up within that directory for wide world importers we now have a backup file with a unique date stamp of 2016 06 12 20 31 or 8 31 in the evening it's pretty straight forward pretty nice so if we run this again now because we have that file cleanup we should still only have one backup file and it will be the most recent one so we'll go ahead and execute this again and now that that's complete we'll go back and rerun our directory lookup and as you can see we now have a only a single backup but now it's from 8 32 pm so it's deleted the old backup and now we have a different one in place of it so running powershell within a sql agent job is pretty easy and fairly convenient for us to use as well as gives us the flexibility to work across the stack not just within sql server so that's how you create a sql agent job containing a powershell script it's it's really pretty simple there's not really a whole lot to it but when you do that and you start to use powershell you obviously get access to all other aspects of the windows platform all right well we said we wanted to get this module to be progressively more practical let's continue that mike's got some patterns and some use cases that you might find interesting yeah yeah so again when i talk to people about powershell and they're always asking me well okay what's the big deal i want to say okay this is this is a practical impact that using powershell in your environment can have and for this particular lesson i want to go over three different use cases there's there's really there's so many anything you can automate that works across the stack powershell is a great choice so getting sql server inventory information right we we as sql server professionals we need to create inventories we need to know what's in our environment and i've actually i did this a couple years ago at one of my previous jobs and we had the a new c o o come in and when he when i showed him the output of it he's like this is the most documentation i've seen on any of our stuff in our environment so and it didn't take me that much work that was the great thing about it right you build the script you run it once and it just goes another thing is a very simple thing is is testing your sql server connection how many times if we had somebody come by to uh the to our desk and say hey i can't x i can't execute this query i can't connect to the sql server the sql server must be down and i'm like well let's let's test that out and usually it's just i connect to the sql server and i run a select statement well instead of me having to click through a bunch of stuff in management studio to do that i just have a simple powershell script that i run and say okay here's my server name bang go and i actually you know for more advanced stuff i wrap it up into my own little function and have it as part of my profile but we're just going to look at the script part of that now and then the third thing is is whenever we have to restore a database that's in full recovery or in bulk log recovery right we have to apply first the full then maybe a differential and then a bunch of log backups and for those of us who are taking log backups every 10 15 minutes or more that can be a very tedious task powershell with a couple lines of powershell i'm able to just spit out the script and get me maybe like 85 90 of the way of doing that point in time restore so i can either create the script or actually execute the restore and save myself so much time of doing that so those are the three use cases and i'm just let's just dive into the demo and see how this all works all right sounds good let's take a look at the demo this demo will show you some practical use patterns for managing sql server with powershell the first example i'd like to talk about is collecting server inventories managing large environments we like to keep an idea of what our sql servers are what versions are installed various information about their our sql server environment this can be done using t-sql and the dmvs but it can be a little tedious without a good way to execute those queries across multiple instances we can get a lot of the same information using the powershell smo through the provider and because powershell is built to handle multi-server execution we can get this information pretty easily across multiple servers for this example i have a collection of my instance names picard and riker i'm going to take that collection and using the pipe operator i'm going to send that collection to a for each object call and for each one of these objects in the array i'm going to call git item on the sql server provider directory for the server this will get the smo server object that represents each instance i then take that collection and send it to a select object call because i want to see only specific properties in this case name version string edition and service account i will then take that output use the convert to csv to convert it to a text file though instead of using a comma for my delimiter i'm going to use the pipe character i also declare no type information because i don't want the type of object that i'm working with to be recorded in the output finally i take this and use the out file commandlet to write this to ctemp inventory csv now one of the things with convert to csv that it does is it writes its files with a quoted identifier this can be problematic for data imports so i like to strip it out this next line is going to read the content of temp inventory csv and for each line in there it's going to replace double quote with an empty string and then we'll just rewrite the file back out to its same location finally we'll go ahead and see what the output is so if i highlight this and execute it you'll see that i get a very simple csv file with my inventory information now this is a nice pattern because it's dynamic if our list of instances changes or grows all we need to do is add a server name to this we could also use other methods of use building our server name collection we could use a text file that has all the server names in it or we could use a dynamic table lookup we could query tables with invoke sql cmd and then return that data into a collection and use that for our loop there's a variety of ways to get this information as well as we could look at other properties and methods more applicable to what we need to know at the core the pattern is to understand using powershell and the provider to go across multiple instances to get your information and then write it to the appropriate output for the second example i'd like to take a similar pattern where we're going to check all of our servers in our inventory but we also then want to see can we connect to them now the way i usually test connections is by running a simple select statement against my instance if it returns to select i know it's queryable i've had instances where the service is running fine but i can't query it and that doesn't do me a whole lot of good so i always want to test my connection by simply querying the database so i'm going to take the same collection of instances though i'm going to add a new one that's not a valid server this doesn't exist i then declare an empty return set an empty array that i'll be putting objects into i have a sql statement that's my tech connectivity test a simple select at server name and the create date of tempdb from sysdot databases because this will tell me when the service was restarted then i loop through each instance the next part of the script is a little more advanced where we're using a try catch block but this is simple error handling and works much the same as it does in t sql we're going to attempt to query the target instance with our simple select if it returns data we're going to populate this row object which we initially set the values to null we're going to populate it with the values from the query so that we have a valid return for our connection test if the connection test fails and returns an error we're going to drop into the catch and not do anything because we know that this is an empty result set and we want it to remain null the finally will be always executed and we'll take the output whatever it is and add it to our return set we'll pull back the return set with a format table call to see what connections are successful and which ones aren't so let's go ahead and execute this and as you can see we come back with a simple result set that says here's our server name and when it started up and we know that this connection failed because it has no startup time so it tells us that this server is either down or maybe we have a bad server name just like with the previous example we can expand our instances collection to use the same block of code in the same logic to check as many instances as we need and we can use the same server list if it comes from a text file or a database table whatever works for us for the last example i'd like to go through creating a point in time restore script for a database one of the tricks and one of the more tedious things to do as a dba is to restore a database to a specific point in time because of a full backup we have to take a full backup with the full recovery model it can be troublesome because we have to restore the full backup and then all log backups after that and if we're doing log backups every 10 or 15 minutes we could have a lot of log backups that we have to add to our script this is why i like to use powershell to automate this because it can make the task very easy for the first line we're going to get our most recent full backup we do a directory lookup on wide world importers where our backup files are for star.back files i will then sort the output by last write date time descending finally i take the first of those so the most recent file next i will do the same directory lookup but this time looking for trn files i will check for only files that have a last write time more recent than the last full that i got and i'll sort them ascending by that last write time now we're going to use the restore sql database commandlet and it requires a special object collection to move the files so i create this collection of relocate files for each of my files in my database now there are ways to make this more dynamic but to keep the example somewhat simple i went ahead and explicitly declared these so once we have this information then we can create a loop for restore statements here i have the database name that i want to restore the database to and then i have a restore statement for my full and then two restore statements for my logs essentially what we're doing here is if it's the last log we're going to restore it normally but for all the other logs we're going to restore with no recovery for each of these i'm going to send the output to an out file temp restore sql and then i'm going to append those files what this does is this actually takes those t sql statements that make up the restore and writes it to a file that i can then edit and maybe eventually execute so let's go ahead and run this and now with the notepad open you can see we have a basic sql file that has all of our restore statements if we examine these restore statements particularly over to the right you're going to see that we have our move statements to relocate our files so this is very handy particularly if you want to generate your script and then tweak it a little bit before you execute it however let's say we just wanted to restore well then we can go down and execute the same block of code but this time if i go over to the right you'll notice i don't have the script switch and i'm not sending the output to a file so we're going to take that same block of code using all the same objects and run it and once the restore is complete we can go to management studio refresh our databases and there's the database that we just restored so those are some practical use cases for powershell like i said there's all sorts of things you honestly if you can think of something that you have to do a couple times you can automate it you can script it and powershell is probably a great choice for that all right well in this module we learned a ton about using powershell with sql server um let's recap what we saw so the first thing we learned was that there's this thing called sqlps yes that's that's the module for powershell with sql server and that provides us our sql server functions for powershell one of the things that sql ps also gave us is this concept of the sql server provider right expose the sql server as a virtual file system enabling us to essentially drill down into a given instance into a given database into a given table and use kind of a familiar way of looking at it we also saw that the module included commandlets that were specific to sql server a wide range of them you said there were how many uh there are 46 commands 46 commandlets all right that'll be on the quiz we also talked about the smo the servermanagementobjects.net library for accessing sql server and we saw that powershell is just one of several peer level uh clients that use uh uh that use the smo that includes sql server management studio that's going to include visual studio from there we went into looking at how we could run job steps in sql agent using powershell scripts and integrate that into our existing uh our existing jobs and then we got practical we talked about common uses for powershell and sql server uh mike mentioned his use of uh powershell to generate system inventories he talked about using powershell as a great way to automate point in time or stores and and do so much of the work that you might have to do to get that long list of full and differential and log log logs yeah yeah oh that that yeah that saves me so much time man all right sounds good so uh that's going to wrap up this module uh we'll be back after a break to take a look at using powershell with azure thanks hi and welcome back to microsoft virtual academy i'm chris randle i'm here with mike fall and we're going to continue talking about powershell for the sql data professional and in this module we're going to go to the cloud we're going to see how powershell can help us uh with sql in azure virtual machines and with azure sql database so mike are they still any thunder is there anything in there no no that's so i think the one thing to talk about is is it's it's important to recognize the cloud and that you know this is where things are going this is where uh much of our our computing work is is going to get moved to eventually and with that the really the i think the best way to manage azure is through powershell you can use the gui tools you can use the web portal and it's effective but it can get a lot you know you can get lost in the clicking through windows clicking through sections of it and it you can't automate it that's that's the big thing uh you know you cannot automate a gui or you can but it's really really hard powershell makes it easy for us to do this and and the cloud is about automation is about spinning stuff up rapidly and repeatedly so in this particular lesson or module we're going to go through three sections first off we're going to start about how to get connected to azure it's the fundamental entry point we can't do anything with azure until we can connect to it and authenticate to it i'm going to guess we have to import a module we have to import not actually we have to import two mods two modules okay so we're going to talk a little bit about that in in the first lesson that there is a difference between what's called classic deployments and resource manager deployments then after we get into that we're going to just go through how to spin up an azure vm with sql server installed on it the nice thing about azure and the azure model is there's a lot of templated machines out there and there's several templates for sql server so we're going to walk through how we can spin up one of those templates into a fully functioning azure sql server vm and then finally so there's the platform as a service uh you know this is the new thing with particularly the cloud is instead of having to worry about all the infrastructure behind it spinning up a machine and installing sql server we can simply create what's called an azure sql database which is a platform as a service when you spin that up you only you get the database you don't have to worry about anything else behind it other than this is the service level we're going to talk about service levels and what that all means but the end of the day it's it gives us an effective sql server database without all the other underpinnings that we have to manage and we're going to talk about how we can use powershell to spin that up and manage it all right so you've already kind of hinted at this we're going to need some new modules yes so let's let's go ahead into the the first lesson here and we're going to talk about two different powershell modules so this comes down to again there are two types of deployment models in azure there's the classic deployment which is how they originally how microsoft originally rolled out microsoft pardon me how microsoft originally rolled out azure and was pretty much the way that you would work with it up until i think a year and a half to two years ago and then they came out with what's called resource manager deployments now resource manager deployments work a little differently uh behind the scenes and it's mostly around this idea that they wanted you to be able to group resources into containers they wanted you to be able to have your everything that stood behind a particular application or setup to be within this one container so you can manage it you could spin it up you could clone it and there's there's a lot of stuff around that and we're not really going to get into that i just want people to be aware of you know the difference between these two and because of that we have two different modules we have an azure powershell module which has pretty much everything to manage anything under the classic deployment model as well as some additional things and then we have the azure rm module now azure rm focuses on the resource manager deployments it's still being added to and developed it has it i want to say it's bare bones it has everything you need but for managing some of the sql components we're going to go back to the classic deployment stuff and that's why you need both modules there are some overlapping functionality pieces to this but for the most part uh there are you're going to use one or the other depending on what your situation is now to get these up and going uh let's we'll go ahead and take a look at the slide here we're going to have import module commands right so to import the azure module is to simply import module azure but once we've done that then we actually need to authenticate or connect to azure you know if you go into the azure portal you have to log into it you have to use some sort of account you know for a lot of people it's their uh microsoft live account but if they're using a corporate account they'll use something else so we need to use the add add azure account commandlet and this will actually we'll see how that works in the demo but this will bring up something for us to authenticate into with azure rm it's a similar pattern we're going to use azure import module azure rm oh yeah excuse me we're going to use import module azure rm and then we're going to either log in azure rm account or add azure rm account now why the dis why the two different commandlets i can't really tell you uh it's just that was developed and i think what they did is they created login ad login azure rm account first and then came back and said oh wait that doesn't match the right the azure module so we're going to make the add azure rm account anyway and so that gets you to the point where you have access to your subscription yeah any existing resources you have and then you can start creating additional items as well yeah absolutely so that's the and then that's really the thing to think about this is the two pieces is first we load up all the functionality so once we import the modules we have the commandlets to use but then we have to authenticate to the account and you could authenticate to multiple accounts uh there are ways to manage multiple subscriptions really all the demo is going to show is how to manage the one subscription but it gets you started sure so with the yeah yeah let's look at a demo let's get into the demos let's see what's going on all right let's connect to azure this demo will show you how to connect to your azure subscription using powershell to connect to azure we need to make use of one of two modules either the azure module which is all the commandlets for managing the classic deployment azure functionality or the azure rm module which is used to manage anything under azure resource manager to install these modules to your computer if you have an internet connection you can make use of powershell 5's package management functionality from a session that has administrator privileges you just run install module azure and install module azure rm and powershell will connect to the powershell gallery which is online and download the appropriate code you'll get the most recent version and have everything you need i've already done this for this demonstration so we're going to skip that we're going to move on to actually seeing what's available to us using get module list available and filtering on azure the result is a number of modules if we scroll up here we can see that they're installed to see program files windows powershell modules which is the default directory and then we have the azure module which contains all the core classic deployment commandlets we also have the azure dot storage module which contains all the commandlets for managing azure storage following that we have many modules for azure rm and then we have an azure rm script what microsoft did for the azure rm functionality is they broke up the different pieces into different smaller modules for easier management and then we have this master script that if we do import module azure rm we'll load all the azure rm modules for us we could if we wanted load each module individually but there's really no need to to actually make use of these modules we just use import module so i'm going to go ahead and load the azure module using that commandlet and now that we've imported it i'm going to connect to and authenticate to my azure subscription i'll use add azure account and when i run it you'll see that we get a graphical dialog for logging into our azure account i'll put in my credentials and since i'm using my msdn account it will redirect me to a slightly different page it looks the same and works the same and once i connect i have my subscription information that i'm connected to now if i want to log into the azure rm command let's i will import module azure rm first and then i will use login azure rm account to connect to that i'm connecting to the same subscription and i'm using the same credentials the only difference is is we're connecting to a different implementation of the azure module so it has to authenticate a second time once i've connected we're actually connected to the same subscription just in a slightly different manner so that'll get you started with the azure modules right we we now know how to load up the functionality we now know how to authenticate to the account and now we're ready to actually do some stuff right all right so we've got a way to connect to our azure subscription uh we now need as sql professionals we need to start working with sql servers sql databases uh the first thing we're going to look at then is manipulating uh sql server vms in azure so right what do we do well so that's the promise of the cloud right is to be able to spin up infrastructure quickly to be able to deploy virtual machines in hours and minutes instead of days and weeks so really that's where we want to start because from a traditional sql server administrators you know viewpoint this isn't really any different from managing sql server on premise it's just now the virtual machine is hosted in azure instead of being hosted locally in some data center you know it's a traditional box version you know out of the box off the shelf whatever you want to call it and so we're used to using that's developer that's standard edition enterprise just now instead of in some data center that we work with it's in an azure data center which really to be honest as a dba isn't really any different from how i've been using it so far in my career i mean the last time i've actually been into a data center has has i think been over a decade right uh so really it doesn't change from my point of view how the how we use it now to do this though we have to do a couple things now when we would stand up a sql server on a new machine in some other environment and like a data center we would purchase the host whether you know it's a dell machine or an hp machine and we would pick the number of processors and all of that the way that translates to azure is we have a class of machine a size of machine and from microsoft that says okay you're going to get this many cpu you're going to get this many uh this much ram you're going to get these additional storage features so we need to decide how big our machine is going to be now here's the good news the good news is is like if we bought the machine for our data center we're stuck with that right you know whatever we buy we're stuck with azure and the cloud if we choose the wrong size we can change it at a later point so throughout this process as we show how to stand up an azure vm on with sql server if we choose the wrong size we can come back and say okay well you know oh we didn't get enough cpu or enough ram we're going to change that setting and we're going to change the the class of the machine and resize it to that value and it's not just about making a mistake it's also right flexibility as the application grows as the load grows the number of users we can scale up we can scale yeah yeah it's one of those that you can't like necessarily scale up and down on the fly as maybe like with a vmware or something where you could just say oh well just add a couple more vcpu but you do have that flexibility to say okay well oh we we're the applications going crazy everybody loves it this this particular you know widget website that we've created we need to upsize our sql server it's a very quick operation uh and by quick i mean like a couple of minutes and your machine was resized to whatever you need it to be and just just to clarify that you you can indirectly yes add cpus add storage add memory you just go about a slightly different way yes yes you're just you're just changing the the overall size of the machine as opposed to adjusting individual sliders absolutely absolutely and you know what helps with all of this too is is microsoft gives you templates to execute on this right so you you can you get a lot of the work stripped out from you where you don't have to worry about oh you know did i get this patch or you know this particular hotfix in fact that's one of the things that microsoft offers as part of the virtual machine setup is you can have automatic patching made available to you so now no longer do you have to worry about applying windows patches you have our sql server patches microsoft can take care of that for you if you just say oh well enable this particular switch and all those switches are exposed to powershell right absolutely right and so you talked earlier about the different deployment modes you've got the the classic classic model then you've got the resource model and in talking about the resource model you mentioned that uh a resource was a container there was a night or rather there was a container of resources that was a nice way of keeping things together so as we go through this and we talk a little bit about storage we talk about storage accounts we talk about virtual networks that that becomes the container for those things as well right right right absolutely and that that's that's the reason to use the resource manager deployment model is because you can then encapsulate that all and it's a logical container right there's really nothing there you know enforcing your your use of these it's just it gives you a good way to logically contain all the objects that you're standing up as part of your infrastructure and that's really what we're doing is we're creating an infrastructure now one of the things is part of our infrastructure is we need to figure out our storage you know just as if i'm going to create an on-premise machine i need to figure out okay well i've got this many luns and i'm going to slice out this from the sand and i got to get all this we need to create storage volumes for our azure vm we don't just get you know fully blown from nothing we get oh here's your your vm with all the attached storage and we do need to take some of the considerations in mind that we would if we were setting up an on-premise equal server so things like 64k alligator 64 kilobyte allocation blocks uh we need to make sure our volumes are set the the proper size but once we do all this once we figure out and there are some particular azure things to take into consideration as well they offer microsoft offers standard and premium storage accounts depending on how fast and how uh how well you want your storage to perform so we need to take these into consideration this is a one of the big design phases well and to back out a level sure if you're already in an organization that has a dedicated staff at the data center that is essentially handing you a virtual machine as a dba to manage sql server on that organization is probably going to do this stuff for you as well right and the nice thing that we're going to be finding out about powershell is that you can mix and match that you can have the people who know storage and know how to out how to manage the network create that part of the script and the people who know sql server and say this is what sql server needs to look like you can put those together into reusable apps absolutely and what what really works well for larger larger enterprises larger companies is now you can have a standardized build for what you need right and and you build that into powershell and then again you can either you could say to some engineer say please go execute this to create us a new sql server as part of our environment or if it's something that you happens on a periodic time frame or you know something that you can schedule you could easily automate the generation of virtual machines and say okay well we need to spin one up and spin one down uh so and so forth so really there are three steps though to kind of keep in mind when we start talking about creating an azure vm with sql server the first is is to figure out what your sql what your sql server is going to look like right so the version of it am i using 2014 am i using 2016 am i using developer am i using enterprise and then how big it is and then what's your storage requirements much the same as if we were going to sit down and say i'm going to spec out a machine to put into my data center now i'm just going to do it on on azure once we do that then we have to though we have to kind of allocate that storage space and there's other infrastructure concerns as well such as a virtual network ip addresses now a lot of this again would be handled by that maybe somebody else some other engineer but we do need to at least figure out okay what's our storage going to look like and then it does create the vm then it's just say execute the vm in fact i think the best way to show this is let's just dive into a demo and walk through the different steps here and and see how it's done let's take a look at creating an azure vm with sql server in it using powershell this demo will show you how to create an azure sql server virtual machine using powershell to get started as with anything with azure the first thing we want to do is log into our azure rm account to save time i've already done this and so i want you to know we're already working with the in the context of my azure subscription the next thing we want to do is set a few values to get started with and i do this so i don't have to type things over and over again primarily i'm going to initialize some variables for the name of my resource group the location i'm going to create these assets in and then a storage account name that i'm going to be using to create my virtual hard drives for my virtual machine the other thing we need to know for creating a virtual machine is a local administrator credential whenever we create an azure vm we can create our own administrator credential and have our own login and this gives us you know administrator access from the get go and allows us to secure it once i have these in place the next thing to do is to create an azure rm resource group now this resource group is a container it's going to house all the assets we're going to create because we can't just create a vm we need some other things to support the virtual machine now i've already created this resource group so i'm just going to step you through the script of the assets we're going to create and then i'll show you the virtual machine at the end of it the next thing we need to do once we've created our resource group is create some network assets we need a virtual network that our virtual machine is going to live in in azure so the first thing we'll do is create a subnet config that the virtual machine will live within so i'm creating a subnet and here's my address prefix for it so i'm going to have everything 10.0.0. something then i can create the virtual network with within the resource group and a location using the same address prefix and that subnet that i just created next i want to create a public ip address when i create my virtual machine i can create it but unless i create a ver a public address i can't access it from the outside world which is what we want to do so i'm going to need to create a public i p address with new azure rm public ip address again resource group name location that we're going to create it i want it to be dynamically allocated and then we need to give it a domain name label if we don't do this then all the only way we're going to be able to connect is by using an ip address i want to use a dns name and we're going to look at that in a bit but i want the dns prefix to be intro vm sql then once i have the public ip address i will create an rm network interface and what this will do is this gives me the whole network interface with the public ip address living in the virtual network that the vm will live on so once i have these virtual network assets i can go ahead and create a storage account the storage account defines the different parameters around our disks our virtual disks how fast they perform what quality of service we're going to get where they live and any sort of caching so to do that we create a storage account and we give it a name so in this case i'm creating within the same resource group and i'm going to call it intro to azure vm i declare a type of storage so in this case i'm doing standard locally redundant storage that's what the lrs means you have a variety of opera options with your storage between standard and premium as well as what kind of redundancy so locally redundant geo-redundant other different types and then the location i want the storage account to live in once i have the storage account i need to create some paths to where to my virtual hard disks these will be created when we create the virtual machine but i need to figure out where i want them to live in this case we're going to be putting them in the storage account and we're going to need the endpoint of that storage account as well as then a container called vhds and the name of the vhd itself so we're going to have the os drive be intro to azure os disk and the data drive be intro to azure data disk next we need to figure out what our azure virtual machine image is going to be called now we can use these commandlets to look up what the image name is but we need to know three different things the publisher of the image the offer of the image and the skew of the image if we look at the publisher and filter on sql there's a variety of options but the one we're going to want here is sql server microsoft sql server once we know the publisher we can look at the offerings by using image offer and filtering on the publisher name and this is where you'll see you have different sql types and different server types we're going to go with sql 2016 ws 2012 r2 so on windows server 2012 r2 then to identify the sku we'll get we'll use get azure rmvm image sku use the publisher name in the offering and this will tell us that we want we have these different skus and we're going to want the sql dev sku so now we can actually create the azure vm we do this by declaring a configuration using new azure rm vm config we give it a name and we declare a size now there's a lot of different sizes so pick one that's appropriate to you i've chosen standard ds1 v2 which gives me one core and 3.5 gigs of ram then i declare some parameters around the operating system i define my image and that network interface that we created above this is where i add it to the vm i set my os disk to the name and the path that we created above and then i add the data disk and here's where i actually declare not only the name and the path but i need to declare the lun the size and what kind of caching we're going to use now we won't talk too much about caching that's why i left it to none but you have a variety of options which could help your performance if you had sql server running on it once i have all these in place i can run new azure rmvm to create the vm now again i've done a lot of this so we don't have to wait for the creation of the virtual machine that can take several minutes i think when i created this virtual machine it took me about five minutes for it to be provisioned and become accessible i'm going to go ahead though and run get azure rmvm on the resource group name and the name of my azure vm to show you its existence so it's up it's in this location this resource group is the where it lives and it's status okay now that we have an azure sql server virtual machine we can go ahead and connect to it and we'll just use remote desktop to do that and i'm going to run that with this invoke expression now that we have this come up we can log in using our credentials and this is that credential object i created earlier and now we're just in a virtual machine it's a windows machine just like any other except now it's running sql server and it's already installed for us now before we get to sql server there's one other thing i want to talk about and it's that data disk that we attached just because we attached the data disk doesn't mean it's usable so i'm going to open up powershell you can obviously do this through the mmc if you wish but i want to show you how to do it with powershell because this is an introduction to powershell we're going to copy these lines from the primary script because these are to be run on the virtual machine and what we're going to do is configure and format our new volume so first off if we look and do a get disk we're going to see several different disks appear disk number 0 is our operating system disk and this is we can't configure it we did set it as part of the image but there's no configuration we can do around this it's just given to us by microsoft disk number one is a scratch disk provided to us by microsoft as well this is temporary it gets rebuilt every time you restart the virtual machine don't put anything permanent on this scratch disk then finally disk number two is the data disk we added and it hasn't even been partitioned so this is the thing we need to do is we need to first initialize it so that's what the initialized disk will do and now we have the partition style of mbr next i'm going to create a new partition and give it the drive letter of f i'm going to use the maximum size allocated to it and there we go but the last thing we need to do just like we would have to do with any other drive we're attaching is format it so we're going to use the format volume called you know call the drive letter give it a file system a label now this is sql server i want to format this for sql server and it's important that we do this with the 64k block size by default it would be 4k so that's why i'm setting my allocation unit and then just because i don't want to answer any questions i'm just saying confirm don't ask me to confirm my choices so we'll run this and now we have a formatted data drive so this is something that needs to be done even if we're creating new vm no matter how many data disks we're attaching no matter how many data disks we're creating the azure virtual machine doesn't automatically format them and attach them for us we need to do a couple additional steps so now with that we can go ahead and open up sql server we'll open up management studio which is by default already installed and you'll note that it comes up with a connection to the machine name we connect to it and we're logged into sql server we have our build number which is the rtm release of sql 2016 and other than that it's just a sql server installation the one thing to call out is you'll notice i used windows authentication that's because by default our local administrator account that we created is added to the system administrator role on our sql server installation at this point we have a fully functioning azure vm with sql 2016 running on it and we can go ahead and start creating databases and tables and store procedures and whatever other sql server objects we want so as you can see you can create an azure vm with sql server on it with powershell it's there's a there's a bit of verbosity to it there's a lot of different steps but once you have it scripted out it's repeatable and easy to do and that's kind of the point of all this because we want to be able to scale this across you know as many vms as we need to get the job done if we're dealing with availability groups you know we want to be able to get that reliable and and and repeatable all right so we want that that same approach that we would take on prem but now we can do it in the cloud right right exactly but we can simplify it we can definitely simplify things if we wanted to use the platform as a service offering the azure sql database because this takes away a lot of the components that we're used to having to manage a lot of those things that we had to declare for the azure sql vm we don't have to so let's talk about how to manage azure sql database using powershell right so doing this is again this is kind of the the way i think to do it there's a lot of there's there's not as many components to manage in fact like here this this particular slide here gives you a quick overview of what azure sql database all the different components now there are less here to worry about than with the azure sql vm but these are all manageable through powershell so we can create things like the sql the server that's going to host our azure sql databases we can create the individual databases and the firewall rules so this is all and again the idea is is that we have this automated process uh i'm finding actually in a use case that we have right now where we have a particular database that we spin out for a client we want to use the same database every time so we script it in azure sql database and just push it with powershell sure so i mean if for the purposes of this course right you know what's the difference between uh sql server and azure vm or uh azure sql database for the purposes of this course there's not a lot of difference because we can get at it manage it drive both now from the perspective of a customer or a learner yeah there's going to be a difference one of the things we talk about is if you are migrating an existing application you might want to use sql server in a vm if there are particular features that you're taking advantage of that are provided by sql servers full capabilities within that vm you stay with the vm if you're focused primarily on the core database engine you might look at sql sql database but again our key thing here is it's all the same with powershell well yeah it's all the same to powershell in a lot of ways it's all the same to sql server dbas right i mean at the end of the day you have a database and and the beauty of it is and this is actually i think some of the things that database administrators sometimes struggle with right is we there's a lot less we have to worry about managing there's a lot less that we have to worry about controlling and setting up and configuring and and for me that's liberating i'm going to go back to i'm a lazy dvd that's why i like to use powershell to spin this stuff up and the thing i like about azure is it takes away a lot of these concerns for me so with that um let's just show how easy it is we'll jump into a demo here and show how you can use literally just i think the script is maybe 10 lines long when you look at the whole thing to spin up an azure sql database that's good let's take a look at the demo this demo will show you how to create an azure sql database using powershell since sql database is a platform as a service offering it's actually a lot simpler to create it than to create an azure vm hosting sql server as with the azure vm we do want to start off by logging into our azure rm account now i've already done this to simplify things and we'll just move on to the rest of the script the next block is where i declare several variables so i don't have to keep typing things over and over again i start off by having a resource group name which will be intro azure sql i have a location of west u.s where i'm going to create my azure sql database my server name is msf-sql intro and my db name is msf-sql introdb now because i need to create a firewall rule to my current local ip address i'm using this rest service call so invoke web request and the uri of the web service this gets me my local ip address once i have that i'm going to create a credential object with my sql administrator credentials these credentials are essentially sa for the server but we can call it whatever we want so we'll go ahead and execute this initial block enter in the credentials and now we'll go ahead and we're ready to begin actually creating the objects first we need to create a resource group to hold all these sql components we'll create it with the resource group name and the location that we declared in our variable area don't worry about the warning this is just azure alerting you to some changes that might be coming in an upcoming release of azure but this is all we need to do to create the resource group which is essentially a container to hold all of our objects now we're ready to create the sql server so i'm going to run new azure rm sql server i need the resource group name and location where i want to create the server my server name my administrator credentials and then finally the version of this azure sql database i want to use which in this case is 12 the most current version of azure sql database we'll go ahead and run this and this actually will create our server that is all it takes to create the actual server but keep in mind we still don't have our database yet we simply have a server that can house multiple databases now we'll go ahead go back to the script and now i want to create my firewall rules i'm going to use the new azure rm sql server firewall rule to create the appropriate access rules and the first one is to allow all azure ips what this means is that by creating this rule anything that is within the azure boundary any sort of azure service can connect to my azure sql database without me having to open up any additional ports or ip addresses the next rule will actually create the firewall rule for my location where i'm at we're going to give it a name and if we go to the end here you can see we have a start ip address and an end ip address and this depart defines the ip address range that i want to declare the firewall rule for we'll go ahead and execute these two rules rule creations and now we have our firewall rules created now remember we can create firewall rules specifically for the azure sql database that we're going to create as opposed to the server firewall rules but for simplicity's sake we're just going to stick with the firewall rules we've created now we're actually ready to create the azure sql database i'll use new azure rm sql database give it a resource group name the server name i'm going to create it on so this has to exist before we can create our database database edition in this case standard the name of our database and the requested service objective name this is the service level we're going to create our database at s0 is a pretty standard way to go good way to start but remember that we can always change this after we've created the database i'll go ahead and create the database now by running that line and there are azure sql database is created and that's it so now that we have an azure sql database let's go ahead and connect to it to simplify things i've created an expression for the ssms command line execution that will connect to the server i just created so servername.database.windows.net we're going to connect to the master database and i'm going to use the credentials that i created and we'll just go ahead and open up ssms with that so now that management studio opens up our object explorer actually looks only a little different we have our connection string at the top and we have a databases folders the only thing we can look at if we open this up you'll see here's our azure sql database which we then further browse into and now we have nodes that we're used to seeing in object explorer in management studio if we're connecting to an on-premise version at this point we're working with a sql server database so we can go here and change our database context from master to msf sql introdb and now that we've changed it we have the ability to run any sort of sql query create tables create store procedures it's just a database at this point and we can do all the things we would normally do in a sql server here so that's it that's really all you need to do to spin up an azure sql database with powershell it's i like it because it's a whole lot less complicated than the azure sql vm right right all right well in this module we took a look at using powershell in the cloud but to manage azure sql databases or to manage sql server vms in azure um let's take a recap and wrap up our course here so in this particular module we took a look at uh the the fact that there are two different deployment models there's the classic model and then there's the resource manager model and one of the benefits of course of the azure rm or resource manager model is that you get this container for all of the assorted objects that also need to get created if you are working with uh with azure especially with vms where you've not only got uh the vm itself you've got storage you've got virtual networks and ip addresses and so having that resource group can be great for being able to clean up quickly when you're done we also took a look at provisioning and deploying sql server in a vm using powershell and the key thing there was once we got that vm up and running it was just like any other windows machine that we were administering uh with sql server running inside it and we used our familiar tools to do that and then we finished up to take a look at using powershell to deploy an azure sql database and uh we saw that there was probably 99 of the things that we needed to do could be done with powershell with the exception of getting that initial server firewall configured then we had to use the portal for that because we didn't have a way to address it at least that was the way when i when i learned it has that changed yeah that's changed that with powershell yeah actually i need to hit the books yes yes when i yeah when i watch the demo well i i probably gotta go back and watch it again uh yeah what i learned when i first learned this stuff there were some steps you had to do first in the portal but i've been schooled yeah well and that's the thing i think distress is you know the cloud and azure is constantly evolving uh moving target it's it's a moving target and the things that are if if you're having struggles one day you know three months later they they i wouldn't be surprised if microsoft has implemented that in fact microsoft's been very responsive with feedback that they're getting about azure and improvements that they're making so keep an eye on what's going on and and just stay educated all right well that brings us to the end of our microsoft virtual academy course on uh powershell for the the sql data professional uh thanks for watching and check some of the additional resources that will provide in the mva console thanks thank you

Channel: Nerd's lesson

Views: 9,123

Rating: 5 out of 5

Keywords: powershell for sql server dba, powershell for sql dba, powershell for sql server, powershell scripting for sql dba powershell for beginners, powershell for administrators, powershell for azure tutorial, azure powershell for beginners, basic powershell for beginners, scripting in powershell for beginners, powershell for beginners course, powershell for dba, powershell for it professionals, powershell for beginners tutorial, powershell for windows administration

Id: SmHpKgKashg

Length: 176min 35sec (10595 seconds)

Published: Wed Feb 17 2021