Get Started With PowerShell for SQL Server

all right give a minute for everyone to be able to connect in all right well thanks everyone for joining us today we need to go through a little bit of the past me in the news real quick with you before we get started today Mike is it in presenter mode and advancing the bar Polly yes it is perfect all right if you want to go ahead and start the recording okay I’ve got it started or all right well again welcome everyone before we get started one to go you through a couple of things one is if you’re planning to go to the past summit and you haven’t done so yet I haven’t registered yet and you’re looking for a discount code please use you discount code for the past PowerShell virtual chapter which is on the screen right now it’s also an email that you received today that’ll say 250 bucks and if we get enough registrations with that code it’ll do something good for the chapters next 24 hours of paths coming up again soon I’m going to be moderating a couple sessions and the cool thing about this one is that it is all for new speakers who have not done 24 hours of pass before and so we’re getting some new community voices involved it’s a quick reminder besides the powershell virtual chapter there’s lots of other chapters out there definitely encourage you to check them out there some memoir by discipline and some of them are by language so here’s the biggest list that i have of them here are some these virtual chapter meetings that are coming off and I’d like to point out that just like us several of these chapters record their meetings and post it to their YouTube channel so even if you already missed the meeting you can probably go back and find it on their YouTube channel couple sequel Saturdays coming up to highlight Chattanooga I’ll be speaking at that one and Vancouver to watch this lava I hope I pronounced that right and gets me a cup over there think they’re booking here oh and by the way they want us to remind you to update your volunteer profile if you’re interested in volunteering with the past organization we certainly certainly use more volunteers and with that all said I’m going to go ahead and turn it over to Mike Mike is a powershell MVP and might get a snow more version of this session a couple of years ago for the powershell virtual chapter and we’ve had we posted that to our YouTube channel earlier this week so that’s out there for you to be able to grab and recording today will be posted next Monday so it’s to minimize the number of emails that you get from YouTube come on seven week and leave all the curtain slides going to be available as well Mike you want to take it away yeah sure will Thank You Aaron okay should be able to see my screen now but I’m gonna go ahead and get out and let you take it from her Thank You Aaron okay you know this presentation is on getting started with PowerShell for sequel server so really it’s designed for people who are just getting started with PowerShell so it’s beginner level session it’s certainly not a deep dive in the PowerShell or sequel server but I’ve got some tricks here that hopefully will be something new that you haven’t seen before so even if you’ve been using PowerShell for a while it’ll be

something new for everybody okay so as Erin said I’m a Microsoft MVP on Windows PowerShell I’m also a safety on technologies MVP leader and co-founder of the Mississippi PowerShell user group co-author a Windows PowerShell TFM fourth edition and author Chapter six and the power show deep downs book and winner of the advanced category from the 2013 scripting games let’s talk about a little bit about what we’ll cover in this session talk about basics discoverability objects methods properties and even though we’re going to be talking about methods and properties you don’t have to be a developer to use PowerShell it’s actually designed for system administrators but it’s issues by developers and IT pros so we’ll talk about the pipeline talk about extending PowerShell briefly talk about remoting briefly through in some best practices here and there and of course we’ve got to talk about sequel server so what is PowerShell Microsoft TechNet provides a great definition for for PowerShell and it’s a task-based command-line shell and scripting language designed especially for system administration and that’s where my comment about being design for system administrators came from so supported PowerShell versions by operating system what I want you to notice on this and I’ve dropped Windows XP off of this since it’s no longer sported and coming up next month server 2003 and no longer be supported either so once that once Server 2003 is off the list then the only operating system that’ll be only built around PowerShell version 2 will be Vista so the three people who were running Vista will only be ought to run PowerShell version 2 and then Server 2008 can run 3 and everything else can run version 4 and you can see that default version is is older and of course before you update the version of power show on your machine you should test it you should test anytime a lot of times vendors will tell you not to update the version of power show on your machine but sometimes they don’t care about the version of PowerShell it’s because a specific version of framework is required for every version of PowerShell so what they what they actually mean is a lot of times they mean that it won’t the dotnet framework updated also notice the default execution policy beginning with Server 2012 r2 is remote signed and beginning with Server 2012 the remoting is enabled by default so you no longer have to worry about enabling PowerShell remoting with Server 2012 and 2012 r2 ok so you know if you’re brand new with PowerShell how do you start it so 1 to 7 Server 2008 r2 and prior operating systems it uh it’s in the Start menu and this is from a 64-bit machine I recommend using the 64-bit shortcuts unless you run into a snap-in or module that has problems with the 64-bit version but in all the years I’ve been running power show I’ve never had an issue with that normally 32-bit snap-ins and modules were unfun in the 64-bit version of PowerShell so if you’ve got windows 8 and Server 2012 and higher its acts PowerShell is accessed from the Start screen you just go to the Start screen which I believe used to be called the Metro interface start typing the word powershell it’ll find it for you so background information PowerShell commands are called commandlets and it’s pronounced command late-night cmdlets so commandlets are in the form of singular verb – now in commands it’s very important to run PowerShell as a local administrator when you open PowerShell you’re actually elevating it elevated because it’s not able to participate in user access control now that you have to do that – like update the hilt for PowerShell or do things like stop or start a service on your local computer but if you don’t have local admin permissions it’s not required to run something remotely against a remote machine it’s really only required if you’re doing something that that makes that requires elevated privileges to do that and I’ll demonstrate that so starting with PowerShell version 3 you’ve got updatable help so the first time you try to get help on a command it’s gonna say hey do you want to update the help system because help no longer ships and box it just ships with very basic information and the help is periodically updated so you do want to run the update help command ‘let to update your help periodically ok so one of the questions always get from people who are just getting started with PowerShell is how do I figure out what the commands are so

there’s three core commands for discovering how to use PowerShell that’s get command and the way I use it that’s the command I use to find what commands are available now there’s some overlap between git command and get help but I’ll use get help to figure out how to use commands once I find the command and then get member helps you discover objects properties and methods so how did I get the sequel’s server module for with the sequel command lights for managing and sequel server and this is showing sequel 2014 it’s actually installed with the with a management the basic management tools and I would think if your DBA then you probably have at least the basic management tools installed on your management workstation or on your workstation or at least a jump Server because what I really recommend is managing from your desktop because even in the demo environment I have server core for the operating system so there’s no GUI on the on the server’s themselves ok so let’s go ahead and jump into the demo you can see here in the screenshot there’s actually 57 commandlets sequel and you also get a sequel analysis services module that’s installed but there’s 57 commandlets now and with and that’s what sequel 2014 but with 2008 r2 there were only like a half dozen commandlets and I actually have a sequel 2008 r2 and a 2014 demo box that I’ll be be showing okay so let’s pull up our VM ok so we’re on a machine called PC PC 10 and PowerShell has never been run on this machine at all so what you want to do is go to the Start screen start start typing in PowerShell and if you run PowerShell just by clicking on me even though I’m a local administrator it’s gonna run it as a non dead man we’ll go ahead and and book the font up we’re not going to spend a lot of time in the console we’ll spend most of our time in the in the integrated scripting environment ok so notice the title bar does not say Administrator so what I will do I can get service get the bit service and I want to stop that service and notice I just press tab and I was tabbing through the commands until I found the one I want it so you don’t have to type everything and it would also correct the case so if I started typing service I can just hit tab and it will fix that for me so if it doesn’t have expand you probably typed something wrong but there are certain places in PowerShell where tabbed expansion and intellisense doesn’t work so if I try to stop the service I’m going to get an error and what I would recommend is always read the error message because usually it will give you a meaningful error but this one does not so what I need to do is actually run PowerShell in an elevated prompt so I’ll go back to the Start screen start typing in PowerShell and what I’d you really like to do I could actually say run as administrator but I like to pin it to the taskbar I’ll go back to the desktop you’ll notice it’s pinned to the to the taskbar I hold down shift I’ll right-click select properties go to advanced and click the run as administrator checkmark and that’ll make it always run as administrator so I’ll never forget I’ll never get deep into a command and then find out that hey I didn’t run PowerShell as an administrator so only ran a couple of commands there but you can actually press f7 to to pull up the previous commands so if I were in my get serviced command again and name is a parameter and bits you can actually tap expand through the values as well and that was either added in PowerShell 3 or 4 so 4 run that command in are pressed f7 you could see I could go back to one of those commands and and actually run that command but what we want to do is go ahead and launch the the PowerShell ISE or the integrated scripting environment and there’s an alias IOC so I can just type in IOC in the PowerShell console it’ll start the the PowerShell

ISE ok there’s actually a little down arrow here we’re gonna bump our font be one of the first things we’ll do because the font is very small by default with this high resolution I’ll close this command window and I have a script I actually want to paste in here as I said we will bump our font up that’s reminders I actually have a scrape here it’s heavily commented to I’ll provide dents so I think we probably need to go up to about 175 for this demo so you can do that with the little slider over here or you can actually do it with PowerShell so it’s no reason to do it with a point and clicking in the in the GUI if you can do it with PowerShell okay so I’ve got an example of a multi-line comment it’s actually uh angle braces a pound symbol or it’s also known as a hash symbol or octothorpe and you get the idea enough to other tab the the comments and that’s not required but just so you could see the comment line it’s better there’s multiple VMs that we’re using in this environment we’ll go ahead and set our location to the demo folder and we’re we’re done with that portion of the that region and I’ve broken my code down into regions and control-m actually contracts and expands those regions so you can see they’re all contracted right now so before we begin so one of the first things you want to know when you start PowerShell is what version of PowerShell am i running there’s an automatic variable called PS version table and you can see I’m actually running PS version 4.2 now typically add this dot PS version to the end of the automatic variable because it gives me a more concise view of what PowerShell version I’m running so if you have if that doesn’t work you’ve got PowerShell version 1 you need to update I recommend for if you can run it if you can’t three is almost as good it has almost a DSC desired state configuration was the big enhancement to PowerShell version four but if you if you actually open up the integrated scripting environment and you have three three panes by default that are all have a white background then you actually have PowerShell version two that’s another way tonight but anyway the newer versions of power shower are distributed as part of the one dis management framework and you do need a specific version of the the full version of the dotnet framework which I believe I’ve already mentioned and that’ll be just read the read the documentation that comes with the Deb EMF framework it’ll tell you the prerequisites ok so let’s talk about execution policy so execution policy the default on of course this is a Windows 8.1 machine so the default execution policy is restricted if I wanted to do something like import the sequel PS module then it won’t even import with the execution policy being set to restricted let’s let’s create a little script here and this is a you know I just want multiple lines so I’m just going to store list of services and a variable and then output output that so that’s all it does nothing nothing fancy so we’ll save that and we’ll put it in our demo folder and we’ll call it services so now we can actually we can select all the code in here and we can run it and it appears to run fine but when I try to run all I’ll get an error message and because that’s actually running it as a scrip and it’s no different than if I went to my demo folder here if I start typing in services I can hit tab it’ll put everything I need there the slash or the dot slash and the ps1 on the end of it so and it tells me like I said a meaningful error message it tells me that the execution policy is is a deciphered running of scripts as disabled on the system so what I need to do and what I recommend is remote sign for the execution policy since that’s what Microsoft has moved to with Server 2012 r2 so I’ll go ahead and run that it tells you their security risk with setting this I’ll show you how to read more about that now if you still run into an issue where you problems with execution policy there is actually several different execution

policies and you can add the list parameter to that command and you can see there’s actually a user policy machine policy or process and current user if you’re not an admin you you may not have rights to set the local machine one but if it’s undefined then you can probably set the current user and and possibly still be able to run scripts but it’s not something I’ve tested either so let’s talk a little bit about discoverability and this has to do with how do I figure out what the commands are we’re gonna move through this fairly quickly because as Erin said I have a previous video that’s on the on the PowerShell virtual chapter of pass YouTube site and I’ve got a link to that video in this article that’ll that’ll drill down for a whole hour into these three commands so the first time you’re gonna run get hit help on my command so what I’m trying to do I’m running get help and name as a parameter and get command is actually the value I provided for that parameter so I’m trying to get help I’m trying to learn how to use get command so it’s gonna say hey do you want to run update help and you only get this like the first time you ever run this on a machine that it that you’ve never run get help on so I’m gonna say no because this VM is not connected to the Internet and what I’ve previously done is I’ve saved the help so if you have a machine that’s not connected to the Internet you can use the save help command late to save the help to like a local repository or a file share is what it really is and then you can actually run update help and give it a source pass so we’ll go ahead and do that it’s will update our help okay and I want you to notice what the previous command it’ll tell you here it says get help cannot find the help files for this command lid on this computer so that’s that that gives you an idea that hey there’s an issue but if I go back now I run the same command again it’ll give me a lot more information so pretty much everything you want to know about PowerShell is on the help system what I typically do with newer versions of PowerShell I believe show-window was added with three could have been four but I’ll typically use show-window and what I did there actually highlighted part of the command and ran it so of course that’ll generate an error message you can you can have your cursor on a line and actually run and it’ll run that entire line of code without having to select the code and the other benefit of this view is I can also zoom in but I have multiple monitors so this window is is not tying up my command prompt so I can drag this over to an alter alternate monitor and I can be looking at the help without and still be able to work in PowerShell that’s one of the reasons I really like that view I want to look at that one more time and notice it remembered the the same that we had we’ve actually got six different parameter sets and each one of these parameter sets has got at least one parameter parameter that’s mutually exclusive to the other ones meaning this one has full and this one has detailed so I can’t use full and detailed in the same command and all these all the square brackets they all mean something and in the other video we referenced it it’s got more detail about that because that’s something that it’s hard to remember well once you figure it out it’s actually fairly easy and the details of that can also be found in each parameter but it’s if you have to scroll down through tons of parameters it takes a little more time than than just looking at the syntax portion okay so help is a function that pipes get helped to more dot exe but in the integrated scripting environment it works just like help so you’ll it’s and help is not an alias it’s a function because man is another command that’s an alias to help but you’ll typically see me type in health and then name is a positional parameter so I’ll admit name and I’ll just say like help get member and then show command or show window so that’ll bring up the same thing and it’s just a little shortcut so then one thing I want to show you also is maybe you’ve got a scrip it might be somebody script that’s like 500 lines long you see a command in there that you’re not used to seeing you

could put the cursor just behind the command or highlight just the command so I’ll put it just behind the command you can press f1 and that’ll actually bring up the show the show window version of help without having to go and key all that stuff in so it’s kind of a little shortcut that’ll help you out ok so so I’ve talked a little bit about aliases and if you’re gonna share code don’t use aliases and I also talked about positional parameters don’t use positional parameters in code that you’re gonna share it’s okay if using a one-liner because typically you’ll type it in and in the console is where they’re normally used you’ll close out the console the codes gone nobody ever sees it so that’s fun so I’ll run this command this shows a few common aliases that that I even use so there’s an alias forget command there’s an alias forget member and there’s an alias for help but generally I’ll just use help so and even if you know typically you’ll see online that aliases are acceptable in one-liners but what I would add to that is if you’re sharing the one-liner then you still need to use full command that names and parameter names okay so you can actually you can run hilt about under store score star and there’s all these help about topics so there’s like a whole encyclopedia set of documentation inside PowerShell we modified our execution policy earlier so if we want to learn more about that we can say help about execution policies show window and that’s one of the commands in this list so that’ll bring this up it’ll tell us about restricted it’ll tell us about remote signed and it’ll tell us what it will run in wood it won’t run and so on so why so if you want to learn more about PowerShell the about health help topics is a good place to start so maybe you’re used to using the graphical user interface show command is a command that was introduced in PowerShell 3 or 4 a newer version but you can run this it’ll bring up a little GUI what you can do is I’ll show you in this console window that if I type 10 until telnet slash question mark by default of course on newer operating systems telnet the telnet client is not installed by default so we’ll jump back to this little show command window and it’s it’s also the little command bar that was on the right hand side when we started I noticed fonts really small and unfortunately in this window there’s not a way to to make it larger so if I start typing in a command name it’ll actually find the command so I can enable Windows optional feature it’s a command that exists on desktop operating systems and it’s similar to on servers there’s an enable windows feature but this is part of the this is a module and this exists on desktops and servers so what I can do is show details I want to use the online parameter and feature name I know it just happens to be called the telnet client so I can either copy that command or run it so I can just click run here you’ll notice it’s running that command without me typing any powershell whatsoever and not a finished here in just a second do we have any questions so far now you’re doing good on questions so far okay so now if I go back to my my PowerShell console I can see I just installed the telnet client on this machine with PowerShell remoting if I wanted to I could install that on every machine in our domain okay so what you can also do is a similar trick to to looking at the help with putting a cursor at the end of a command or highlight in a command you can put it at the end of a command and I’ll just do it with this command that’s got more typed in I can print I can actually hit control f1 that’ll bring that command up in the show window interface automatically so you can see it’s already got windows optional feature in there and then I can actually work on that command in the show window interface so it’s a little trick if you’re looking at somebody else’s script that how else can I use this command okay so there’s a link here to that video that was posted I believe it was earlier this week or maybe last week it’s PowerShell fundamentals for beginners it’s presentation I did a couple years ago so I’ll go ahead and

collapse this region talk about the pipeline a little bit and I know at least so far this is not sequel related we will get to some sequel content but the basics that’s the awesome thing about PowerShell it doesn’t matter if you’re an ad guy or exchange guys sequel guy even in our data centers we have equal logic storage area networks to have powershell commandlets and i mean i provision our storage with PowerShell so it doesn’t matter the basics are all the same so if you’re a sequel gun you need to go do something on something else with PowerShell you can you can figure it out fairly easily it’s what I’ve done but this command is I’ve actually returned one service the the windows time service there’s only like three three different properties that were returned there’s a with a lot of PowerShell commands there’s a default view you might say and there’s a lot more information that exists behind the scenes and you can use get member to to see that information so you can see there are several different properties here there’s also methods and methods are something you would take action set on on that service so I could actually stop the service or start the service and I know there’s a command let’s top service and start service but you may run into a command late that doesn’t have a corresponding command let to to take the action you want to take and there could be a method and I’ll demonstrate that so if you want to see the values of all these properties what you can do is actually pipe that command to format list and property star and you’ll see these commands shortcutted you know on the internet because there are shorter versions of this that the readability is it’s kind of compromised so now you can see there’s a number of different I can see can I pause and continue the service can I stop it can I shut it down and so on so there’s a lot more properties and I knew I’ve also worked with the SharePoint commandlets and the SharePoint command like there’s like tons of properties they’ll be like it seems like a hundred properties and they only show like maybe two or three by default so sometimes you have to use the force parameter so PowerShell is going out what’s called Profiles and we’re gonna not gonna spend a lot of time on profiles but this would be the profile that would that would run for the the PowerShell ISE so if I wanted to put something in there and run it by default if I wanted to set my location to a certain folder or clear the screen or so on I could do that well I’ll sit in profile at a format list property star well guess what it it didn’t show me any more information well I can use the force parameter because sometimes you just have to force things and I can see there’s actually there’s several different profiles so it’ll tell me there’s all users there’s a current host there’s current user there’s a current user all host and there’s actually six different profiles by default because if you run the same command in the PowerShell console two of the profiles will be different so if I ran the same command here two of these will be different but what I wanted you to see is sometimes you will have to use force to see all the properties that exist that’s that’s the moral of the story so let’s get back to the pipeline so when I ran this command get member what impacted to get member one of the most important things here that I didn’t show you to start with is it produces a service controller object so you’ll see the type name is that a service controller so what I want to know is what commands accept a service controller object so Get Command has a parameter to tell me to tell me that information so I can say get command parameter type service controller so all these have parameters that accept service controller objects now that doesn’t mean it accepts it via pipeline input you like if you if you are in the same command and get a list of all the commands that it’s set to the string not all those accept it via pipeline input there’s a several different ways but for an somebody new to powershell the easiest way to fund that information to make sure it accepts it via pipeline input is to take one of these commands and look at the help so what I’ll do I’ll come down here I’ll notice this is the one that’s it yes sir sorry to interrupt you just want to let you know we got a question or two

in the to you whenever you get a minute okay let’s go ahead and cover them now you can go ahead and finish your thought there and I’ve assigned one of them to you right now if you can it maksakov had a question he went clarification for around the execution policy is it different between the command prompt and the ISE okay and I’m not really sure I understand the question but if you set the execution policy and one it actually sets it in the other so when I said it didn’t even though have it closed and reopened the the console it is set to remote signed if if I do a list there’s actually not a different one for the for the IC or the console there are different ones for like the current user and current process and there’s a user and machine policy one but but no difference between the IC and console so once I set it on local machine even if I close this out it’ll still be set to the same policy that it’s assigned to you great thanks Mike okay so you’ll notice I was able to go back to it’s kind of like I mentioned earlier having multiple screens so I was able to go to go back to the decoding view and then I can jump back to the help again so you know when you’re generally looking at help you’re tying up your console or your eyes see if you’re not using show window okay so what we where we were at is we wanted to find objects that accept to service controller so we can see input object accepts a service controller and it sets it by pipeline or yeah it’s a pipeline input so it does accept it by pipeline but specifically by value so what that means is it’s going to accept it’s going to accept cert the service controllers it doesn’t care what the name of the property is if it comes through as and the name of it is Mike or Erin or whatever if it’s a service controller it’s going to bind it to to this prop when it comes through the pipeline and I’ll show you an example of that so this is this is actually by value by service controller net even if there’s two different ways that it can bind and I’ll show you a second way here in a second so there’s also I’ve combined a few things here there’s a what-if parameter you can use so what I’ve done is actually sent the services bits and the windows time service through the pipeline to stop service and I used what if because if you’re making something that’s doing system level changes I recommend using the what-if parameter and most commandlets that do make changes support it because it can save your job if you pipe all the services on the Box to stop service on a production sequel server that’s probably not going to be a good day and if you use the what-if parameter you can see that oh that’s not really what I meant to do it’s kind of like doing an update or delete and sequel and forgetting all you know to say where whatever and you delete everything also most commandlets that support what if support confirm so it’ll go through and say are you sure you want to stop the service now are you sure you want to stop this other service and I can say no to all we’ll talk about by value and this is a string if we go back to the help there was another command let that or not another command let another parameter that it’s EPS pipeline input and this one it sips it by property name and by value now by value is always the first method it tries and if it can’t bind to anything by value it’ll try to bind by property name and we’re just kind of hitting over this high level not a deep dive into the pipe on either just wanted to really make you aware that this is kind of how it works and once you start working with this you’ll you’ll get the feel of it better you know it’s the only way so something comes through as a service controller regardless a name that goes to input object if something comes through as a string it doesn’t matter you can pipe get process to stop service and because get process has a name it will try and all those process names to this name parameter of stop service and if you happen to have a process name that matches the service name it’ll stop it otherwise you’ll your screen and be bleeding red so now will send something through the pipeline as a string so you’ll notice I have a collection or

array of strings here and want to send that to get member and I look at the the type of object it produces it’s a system dot string and typically you would just say it’s a string so I’ll send that to stop service it’ll bind it with no problem and also specified what if okay so plan B is by property name so we’ll create a new object we’ll take a look at what’s in that object we’ve actually the object the name is his name and W 32 time and bits are the two the two values that we have in there so if we pipe that to get member we can see it’s a PS custom object so it’s not a string and it’s not a service controller so if I send that to stop service it will actually work now if I take this and I put like let’s just say these were process names so if we call that process name we look at the object nothing’s changed except for the name of process name we send that to get member we can see it’s a PS custom object guess what it’s not going to work this time because it’s going to try to bind by value and this is not a string it’s a non service controller so then it’s going to try to bind by property name and there’s no property names of name so when I try that of course I get an error message so that’s how by property name works okay so figuring out objects and and properties and methods when I run stop service bits and I actually go ahead and really stop it this time it didn’t produce any output so there’s a number of commandlets that don’t produce output if I take one of those commandlets and try to send it to get member yes what it’s going to generate an error message because you can only send Commandments that produce objects to get member now a number of commands that don’t produce output have what’s called a pass-through parameter which means it will pass the objects through the pipeline so I could specify the passed through parameter and then pipe it to get member and then see what type of object it produces and see the the properties and the methods okay there’s there’s a misconception that a lot of people have that say says I’m gonna let you run get commandlets command let’s let’s start with yet and they think it’s like a select statement in sequel so run like get the windows time service and it’s actually running so I can call a method on that service and stop the service so I just stopped it without ever calling the stop service command line I can also start it so I took action on that object or on that service so I was able to stop and start the service okay I am gonna jump to another VM at this point and do we have any more questions Erin yeah we’ve got some in the queue for you assign them to you if you’re able to see them I’m not sure I can see them the the window is really really small yeah welcome to my world hi in I don’t know why they don’t let us make it bigger but you can pop it out but that’s it okay yeah dental hell I’ll bring I’ve got multiple monitors so uh maybe that’ll work okay here we go let’s answer a few questions at this point no no powershell version 5 in this session i’ve done some other stuff on powershell 5 actually presented a session at the powershell summit this year in charlotte on PowerShell files and I’ve also presented a number of sessions on what’s new and partial 5 but that’s kind of a moving target but what I wanted to present today was uh what you could use you know in your production environment today you could go home with but of course test it before you use it in production let’s see just going through the questions real quick here saw the one on execution policy well we’ll come back to a few questions here in a minute okay and the good thing about regions also this is the same prep that I ran on the other machine but what I could actually do is actually just run the selection and it’ll run the entire prep so we’re gonna bump this one also up to 175 so it’s a little bit larger okay so

how do I had our Ren commands against sequel in PowerShell of course you need the sequel PS module then I’ve already covered how to how to get that if you run get module and I’ve actually already added the sequel PS module I am gonna remove that so if I run remove module sequel PS you’ll actually remove it now will tell you that sequel will change your Pat you’re actually actual location and if you’re in the sequel server PS drive and you try to remove it you’ll be in an error because it’s in use of course okay so right now I don’t have the sequel command lights loaded the sequel analysis service is still loaded in that’s fine so what you can run is get module list available and it will show you all the commands the all the modules that are actually installed on your machine that exist in the PS module path and these are the ones that are not necessarily loaded to see the ones that are currently loaded you would run just get module so there’s only a fraction of the ones loaded now sternum with PowerShell version 3 if I use a command late in any of those modules it will auto load the module that functionality it says it does not work with with snap-ins so even if you have something in a in a comment which this is a single line comment I can actually highlight that command and still run that command so if you have something that’s going to run it’s going to be really dangerous and you don’t want somebody to accidentally run the entire script unless you’re gonna put break or something at the top of your script you could comment it out and then just select it and run it okay this is not a very pretty formatted output here so I’ll just split it on the semicolon so we can see what paths these modules would have to exist in when the sequel PS module is installed it does add a path to this this environment variable so to import the sequel PS module I could use a command like I previously said orchid just import the module go ahead and import the module this this will take adjust just a second now one thing I will tell you if you start using cqs emotes or sequel server management objects the dll’s that need to be imported or actually imported with this command now if you have an older version like 2008 r2 when you import the snap-in it’s not imported and if you you want to use s mo you actually have to add this now the DLLs manually you’ll know she get this error message it changes your location there’s some ways to work around that like a push and pop location or a couple of the things I’ll use so I’m going to go ahead and set my location back to the demo folder a lot of people freaked out about this warning there’s a couple of commandlets that don’t use approved verbs and they don’t understand why this happens the reason this happens is back in the snap-in days they didn’t check for pre verbs so there’s a I believe it’s encode and decode are the two verbs that the two the sequel commands are using so when they move to a module they just have the existing commandlets and now that’s being checked but what the sequel team could do if they wanted to is rename the commands and use the pre verbs and then alias the old commands way leus is and that would make them backwards compatible while making them meet the requirements but uh that’s a subject for another day so how do I figure out what the commands are in that module so I can run git command module sequel PS I can see these are all the commands there’s like 50-something of them well actually there’s not 50-something of these because these are only the sequel PS ones if I say sequel star because wildcards are supportive with that parameter I can see there’s several and Alice equal analysis services commandlets that we’re at it and there’s total sequel commandlets there’s 50 something okay so we’re going to relating real quick we’re going to enter a one-to-one remoting session to our sequel 0 to server we’re going to change our location we’re going to look at the we’re actually going to return just the computer name to make sure we’re in the right computer so this has sequel 2008 r2 if I run get PS snap in you’ll notice there’s a number of snap ins loaded button not the sequel ones so I can see other command other modules that are loaded but that are available to be imported and instead of the import verb they actually use the adverb so it’s a little bit different so I can use add PS snap in named sequel server star because there’s two snap ins there’s a provider and am one with Commandments same command to see what

Commandments exist I can see there’s only five commandlets and this encode and decode sequel name or the couple I was talking about the reason you get the message so I just wanted to show you the difference you can see they’ve gone from like five commands to 50 something so even the sequel team is heavily invested in in PowerShell so we’ll exit that command or exit that one to one remoting session okay so wonder many remoting is is perform with the invoke command command late and I’ll just do something simple here I can run any command in this that exists on the target machines I’m checking the PowerShell version on sequel 0 to 0 4 + DC 0 1 which are three servers ok so that we’ve only got a few more minutes here so you know if you’ve got an existing T sequel code you can easily run that inside PowerShell there’s really four different ways you can access a sequel server with PowerShell ones with invoke sequel command using your existing transact sequel you can also run a the same way you can run transact sequel you can run store procedures now one issue is if stored procedures such as SB 2:2 it has two columns that that the column name is bid so PowerShell converts that to objects and you can’t have two objects with the same names of course that want to generate an error message that’s not really a problem we can just write our own we can just use that soummo in this scenario and we’ll just write our own SP who – in a simple command and what I would recommend also is when you start writing PowerShell you don’t want 50,000 scripts that do the same thing with a different server name hard-coded in it there’s ways and what we will not cover it in this session but write your commands are their dynamic they take parameters you can parameterize the computer name so there’s also what’s called PowerShell providers and it’s where you can work with PowerShell as if it were a file system so you can see there’s a number of drives is what I call them so I can get a list of databases from one of my sequel servers as if it were a file system because dr is navy as forget child item there’s also an environment drive that shows all the environment variables so if you’re going to hard-code something in a script such as the computer name consider using an environment variable if it if you want in the local computer name there’s also a drive for all the variables so if you defined a variable or any of the default variables will show up there okay so as we saw there’s like 50 something commands now so so the way is to use PowerShell to access your sequel server is through the invoke sequel command command tlit using your existing transit sequel through the provider the sequel management objects and also through those commandlets and not really all those commandlets are I mean I can’t see the code but it’s probably just wrappers on top of SMA or something else okay so what we’re going to do now so what can you do with all this comes a lot of what I’ve shown sure you can do it in sequel so we have a no you in Active Directory there’s nothing in this adventure work so you so we’re going to go ahead and import our Active Directory module and I know the sequel ones already imported so it won’t reinforce our import there’s a force parameter so you don’t have to remove and read the sequel PS module you can just import it with force parameter and the Active Directory module will take just a second there so we’ve got it we’re actually going to query the adventure works database we’re going to do several several joins and I’ll show you the output that I will get so there’s actually 290 users that exist in the adventure works database and for the purposes of this let’s just pretend that’s our HR database maybe we’ve merged with another company we can see the so you in Active Directory has 0 users so what I’m going to do I’m not only going to create 290 Active Directory user accounts from the adventure works database I’m going to measure the command and see how long it takes to create them ok so it took it took 2 seconds so now if we query that Active Directory oh you again you can see there’s 290 Active Directory user accounts we’ll go over here we’ll do a refresh not only did we create 290 Active Directory user accounts we filled in their name their display name their

telephone number we filled in their address and we filled in their job title and it’s all 100% accurate based on the information in the in the adventure works database so yeah I to get some temps to to do that accurately so that’s kind of the power of PowerShell and at this point let’s see I want to jump back to my slide deck I’ve got a couple of resource slides and then then if we have any time we’ll take questions and if I don’t have time for your question I’ve got a resource slide that’s got my Twitter handle and Linkedin and and so on just hit me up I take questions offline okay so resources for learning powershell or even to get help if you’re an experienced powershell script er blogs is one of the main things i’ll use there’s a number of blogs that that guys that I feel write really good parish okayed but that’s opinionated so if you go to my blog I have a blog role and it’s it’s people that are consistently writing blogs like every month or every week or whatever and it’s high-quality code also user groups are on the mississippi power show user group we have videos very similar to this powershell dot org they have awesome forums so if you have questions even they could get to the question faster than then probably you could even send me an email powershell magazine is another great site the hey scripting guy blog i’ve written some guest blog articles for all three of these places is great the Microsoft Virtual Academy they have some great videos on on PowerShell I’m sure they probably have some ones equal server as well but I’ve seen the PowerShell ones they’re free everything I mentioned so far is free the Pluralsight ones are it’s a paid subscription so if you’re interested in even more videos I would go there and there’s a community really getting started on github Microsoft has put a lot of their their DSC resources on github and I’ve actually fixed even the X equal server one if you didn’t specify like whatever you wanted to any options for sequel server and upper case it would actually give you an error so I did a pull request and actually did a fourth the repository and then I fix the code and then I did a pull request so if you go download the DSC resource that’s the x equals server one you’ll get some code that I’ve written and I even put a comment in there so this is my resource slide and this should be the last slide it’s my Belize month yes sorry my car fragments calm or you can get there by mr. Parrish Oh calm you find me on Twitter or LinkedIn if you want my email there’s a security riddle my about page and then also the Mississippi PowerShell user group can be found with MS PowerShell calm and I’ve got the pictures of the books that I’ve I’ve contributed to they’re all right great thanks Mike we’ve got some questions in the queue if you want to take them real quick sure sign one to you okay yeah I see how you sign them now yep yeah that’s correct oh I see actually it looks like you answered some of these yep yeah sure dude so invoke sequel command how do you make sure the connection gets closed I don’t know the official answer to that off the top of my head but I can tell you how how to force it closed if you close PowerShell it’s gone close the connection so if you n if you had a script that you ran with something like a scheduled tasks for some reason when it finished it would close PowerShell and it would close the connection but I don’t generally when I’m working interactively with a sequel server closing the connection is something that I haven’t been as concerned with let’s see let’s check any of the other ones with the if I have any others their questions about or the scripts gonna be available things like that oh yeah yeah definitely what I did I don’t know if you I’m sure you saw that these are heavily commented and I did that so that you could put the script in perspective when I provided it so what I’ll provide is a PDF of the slides and then I also provide two scripts and from both the machines and

it all being a zip file and I’ll give it to Aaron and what I’ll probably do is I typically publish a blog with the content as well so when he published this the video next week I’ll publish a blog with the embedded video and then also a link to that but you can get on from from Aaron you know from the PowerShell virtual chapter of past site as well okay yeah I do some comments from Cummings from Theresa great presentation like I’m actually worried before max left mr. Ned said great session a lot of good info max Trinidad runs the the Florida PowerShell user group he’s a PowerShell MVP and he does videos similar to this also so so there’s there’s tons of free resources out there so the there’s no excuse of of why you can’t learn PowerShell and Tom is also not an excuse either if you work out or something walk on the treadmill fire up the video while you’re walking on the treadmill yep alright great well if that’s it you know lots of questions are just congratulations and great presentation comments so I think that’s it I will go ahead and end it here that’s good with you and let people get back to their working day sounds great I appreciate you for having me on to present this and I hope everybody has a great day thank you bye see you later you