In this article we’ll be looking firstly at the basics of using PowerShell, and then at how to combine SMO with PowerShell to make PowerSMO<\/b>, and finally at how to use PowerSMO to manage SQL Server.<\/p>\n
You will find that PowerSMO is in the spirit of scripting languages such as VBA or Perl. Ultimately, I think you’ll find PowerSMO easier to use and more powerful, but like any new technology it has a learning curve. The intention of this article is to help push you over the first hump of that curve.<\/p>\n
All of the code examples I include here are available in the zip archive that comes with this article (see the CODE DOWNLOAD link in the box to the right of the article title), as well as on my own pluralsight.com<\/a> web site.<\/p>\n Besides the zip archive, you will need two other things in order to get started using PowerSMO, both of which are free downloads from Microsoft:<\/p>\n The PowerShell installation will add a “Windows PowerShell 1.0” entry to the “All Programs” part of your start menu. It contains a “Windows PowerShell” item you can use to start PowerShell, as shown in Figure 1:<\/p>\n Let’s start with some PowerShell basics. Starting PowerShell produces a command shell window, which looks a bit like the old Windows\/DOS command shell. It will even respond to some of the old DOS commands you know and love, such as dir<\/span><\/b> and echo<\/span><\/b>, as shown in Figure 2:<\/p>\n One of the things that make PowerShell different from the Windows command shell is that it is based on objects<\/b>, not text. The echo<\/span><\/b> example happened to use text objects so this difference was not apparent. However, PowerShell is capable of using any object that can be created by the .NET framework and it is this ability that will later allow us to integrate SMO into PowerShell to make PowerSMO.<\/p>\n Figure 3 shows PowerShell creating an instance of the .NET Random<\/span><\/b> class, and then using it to generate some numbers. I’ll be using simple text grabs going forward rather than full screen shots of the PowerShell.<\/p>\n PS D:\\SimpleTalkSmo> $rand = New-Object “Random” PS D:\\SimpleTalkSmo> $rand.Next() 839188457 PS D:\\SimpleTalkSmo> $rand.Next() 693128074 PS D:\\SimpleTalkSmo> Figure3: Random Object<\/em> New-Object<\/span><\/b> is what PowerShell calls a cmdlet<\/b>; it is basically a command, like dir<\/span><\/b> is in the Windows command shell. The New-Object<\/span><\/b> command creates an instance of a .NET class, in this case, the Random<\/b> class. Variables in PowerShell always have the $<\/strong> character as a prefix. In Figure 3, $rand<\/span><\/strong> is a variable that, in this case, holds an instance of a Random object. PowerShell can call any method of an object using a simple dotted syntax as is shown in $rand.Next()<\/span><\/strong>. Each time you call Next()<\/span><\/strong> on a Random<\/span><\/b> object it returns a different number. Whenever an object is returned, it is typically assigned to a variable or its value is printed on the screen. Figure 3 showed the object produced by New-Object<\/span><\/strong> being assigned to the variable $rand<\/span><\/strong>. You can also assign literal numbers or text to PowerShell variables:<\/p>\n PS C:\\SimpleTalkSmo> $n = 8 Figure 4:<\/i> Numeric and Text Values<\/i><\/p>\n PowerShell supports all of the arithmetic and string operations that you would expect. Figure 5 shows some of these:<\/p>\n PS C:\\SimpleTalkSmo> $n = (3+4)\/2 PS C:\\SimpleTalkSmo> $n 3.5 PS C:\\SimpleTalkSmo> $s = $s + ” Sullivan” PS C:\\SimpleTalkSmo> $s Dan Sullivan PS C:\\SimpleTalkSmo>\u00a0 Figure 5: Simple Operations<\/i> PowerShell also lets you ignore types if you wish. Figure 6 shows string being “added” to a number, but still producing a numeric result:<\/p>\n PS C:\\SimpleTalkSmo> $n = $n +”8″ Figure 6: Type Can Be Ignored<\/i><\/p>\n You can see in Figure 7 that PowerShell is based on objects, not strings. Here we see the type of $n<\/span><\/strong> and $s<\/span><\/strong> being returned:<\/p>\n PS C:\\SimpleTalkSmo> $n.GetType().Name Double PS C:\\SimpleTalkSmo> $s.GetType().Name String PS C:\\SimpleTalkSmo> Figure 7: <\/i>Objects<\/i> Figure 7 also demonstrates another consequence of the fact that PowerShell works with objects, which is that we can access that object’s methods and properties. In this example we used the GetType()<\/span> method to find the type of $n<\/span>, then the Name<\/span> property of its type to find its name. Back in Figure 3 we made an instance of the Random<\/span><\/b> class from .NET. You might not know the documentation for the Random<\/span><\/b> class so PowerShell includes a cmdlet called get-member<\/span> that allows you to investigate what sort of features an object has. Figure 8 shows the use of get-member<\/span> to find out what you can do with the $rand<\/span> object.<\/p>\n PS C:\\SimpleTalkSmo> $rand = New-Object “Random” Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MemberType Definition PS C:\\SimpleTalkSmo><\/p>\n Figure 8: <\/i>get-member <\/i><\/p>\n The text in Figure 8 is chopped off on the right a little bit but you get the idea. You can see the methods supported by $rand<\/span>, including the Next<\/span> method we used in Figure 3. Figure 9 shows the results produced when get-member<\/span> is used with a string variable.<\/p>\n PS C:\\SimpleTalkSmo> $s = “Dan” PS C:\\SimpleTalkSmo> get-member -inputObject $s \u00a0\u00a0 TypeName: System.String Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0MemberType\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Definition —-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0———-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0———- Clone\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Method\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0System.Object Clone() CompareTo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Method\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0System.Int32 CompareTo(Object value), Contains\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Method\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0System.Boolean Contains(String value) … Length\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Property\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0System.Int32 Length {get;} PS C:\\SimpleTalkSmo> Figure 9: <\/i>String Methods and Properties<\/i> In Figure 9, we see that a string has both methods and properties. Note that get-member<\/span> applied to a string produces a lot more results than are shown. A string has a Length<\/span> property which can be used to find out how long it is, as is shown in Figure 10:<\/p>\n PS C:\\SimpleTalkSmo> $s.Length Figure <\/i>10:<\/i> Using a Property<\/i><\/p>\n It’s fairly easy to find out what you can do with a variable by using get-member, but what if you need to know how to use one of the cmdlets in power shell? The get-help<\/span> cmdlet in PowerShell is used to get the documentation of a cmdlet, as is shown in Figure 11:<\/p>\n PS C:\\SimpleTalkSmo> get-help New-Object NAME \u00a0\u00a0\u00a0 New-Object SYNOPSIS \u00a0\u00a0\u00a0 Creates an instance of a .Net or COM object. SYNTAX \u00a0\u00a0\u00a0 New-Object [-typeName] <string> [[-argumentList] <Object[]>] … REMARKS \u00a0\u00a0\u00a0 For more information, type: “get-help New-Object -detailed”. \u00a0\u00a0\u00a0 For technical information, type: “get-help New-Object -full”. PS C:\\SimpleTalkSmo> Figure <\/i>11:<\/i> get-help<\/i> Figure 11 does not show all of the documentation produced, a big chunk in the middle has been replaced with an ellipsis to reduce clutter. You can use you wildcard character, *<\/span>, if you are not sure how to spell the cmdlet you want documentation for. For example you can use “get-help *name*<\/span> to find all the help topics available that include the word “name”.<\/p>\n Pipelines are supported by PowerShell. Pipelines are a common feature of shells and allow the output of one segment to be used as the input in the next segment in the pipeline. Segments of a pipeline are connected together by the |<\/span> character. A segment of the pipeline is often a script block. A script block is a PowerShell script wrapped in curly braces, prefaced by either a %<\/span> or ?<\/span>. There is a special variable, $_<\/span>, that is used to represent the output of the previous segment of the pipeline. Figure 12 shows an example of a simple pipeline:<\/p>\n PS C:\\SimpleTalkSmo> $s = “Dan” Figure <\/i>12: <\/i>Simple Pipeline<\/i><\/p>\n In Figure 12, $s<\/span> is passed into a script block prefaced by the %<\/span> character. A script block prefaced by the\u00a0%<\/span> character is executed as though it were typed in on the command line without a pipeline. When a script block has no following pipeline segment its output is sent to the screen.<\/p>\n The $_<\/span> represents the input of the previous segment, in this case the $s<\/span> variable. The length of the input object is accessed through its Length<\/span> property. There is no following pipeline segment so the result, the number of characters in “Dan”, is output to the screen.<\/p>\n A script block can also be used to test to see whether or not further processing should be applied to the object that is passed in to it. A script block prefixed with the ?<\/span> character executes the script inside of it and interprets the result as a Boolean. If the result is true<\/span> the object is passed onto next segment of the pipeline, otherwise it is thrown away. Figure 13 shows examples of pipelines that test an input object:<\/p>\n PS C:\\SimpleTalkSmo> $s = “Daniel” PS C:\\SimpleTalkSmo> $s | ?{$_.Length -gt 3} | %{$_} Daniel PS C:\\SimpleTalkSmo> $s = “Dan” PS C:\\SimpleTalkSmo> $s | ?{$_.Length -gt 3} | %{$_} PS C:\\SimpleTalkSmo><\/span> Figure <\/i>13:<\/i> Testing Segment Input<\/i> Figure 13 starts off by setting the variable type $s<\/span> to the text “Daniel”. Next it passes $s<\/span> into a script block that is prefaced by a ?<\/span> character. This script block compares the value of the Length<\/span> property of the object that was input to the number three. It does this comparison using a “greater than” test. PowerShell supports all the kinds of comparisons you’d expect but they are all specified using a syntax that begins with a dash followed by a few letters. The comparison is true so $s<\/span> is passed into the code block in the next segment of the pipeline. The next segment of the pipeline evaluates the value of the object, that it executes the simple script $_<\/span>. The result, “Daniel”, is typed out on the screen. The remainder of the example repeats the test but this time with the $s<\/span> variable is set to “Dan”. In this case nothing is typed out on the screen because the length of the variable $s<\/span> is not greater than three. We have barely scratched the surface of the capabilities of PowerShell, but it should be enough to get you going. The resources for learning about PowerShell are at the Microsoft Scripting Center. Browse to https:\/\/technet.microsoft.com\/en-us\/scriptcenter\/bb410849.aspx<\/a>\u00a0and look for the link further down the page that says “Scripting for…”, “Windows PowerShell”.<\/p>\n There is one more feature of PowerShell that we need to look at before digging into PowerSMO; and that’s its support of WMI, Windows<\/b> Management<\/b> Instrumentation<\/b>. WMI provides a standard way to access the administrative information about a Windows system, for example what user accounts are in a system…and so on. Later we are going to use this to automate the creation of SQL logins for accounts in a particular group of Windows system users. Prior to PowerShell, WMI was typically used with a Windows Scripting Host language such VBA. The PowerShell cmdlet get-WMIObject<\/span> removes most of the detail work you would have to do a WMI query with Windows Scripting Host. Figure 14 is a PowerShell script that uses the get-WMIObject<\/span> cmdlet to get all the instances of the Win32_Group<\/span> class in the PARSEC5 Common Information Model or CIM. So, in effect, we are listing all the user groups on my computer (called PARSEC5):<\/p>\n PS C:\\SimpleTalkSmo> $groups = Get-WmiObject -computerName PS C:\\SimpleTalkSmo><\/p>\n Figure <\/i>14:<\/i> Finding User Groups with WMI<\/i><\/p>\n The Common Information Model is the industry-standard\u00a0model of computer systems, on which WMI is based. You might recognize some of the SQL Server groups on this list and groups such as “administrators” or “users”. Note that there is a group named “PowerSQL”. We will be using that group later.<\/p>\n Now that we have finished our introduction to PowerShell and a very brief introduction to WMI we can move on to using PowerSMO.<\/p>\n In order to use PowerSMO you will need the InitPowerSMO.ps1<\/b> script file which is in the .zip archive mentioned at the beginning of this article.<\/p>\n NOTE<\/strong>: The default installation of PowerShell is locked down fairly tightly. You will have to change the configuration of PowerShell to allow the InitPowerSMO.ps1<\/strong> script to be run. You can do this using the set-ExecutionPolicy<\/span> cmdlet as shown in Figure 15:<\/p>\nGetting started<\/h2>\n
\n
A brief tour of PowerShell<\/h2>\n
![\"342-Sullivan001.gif\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/342-Sullivan001.gif\")
\n Figure 1:\u00a0PowerShell Start Menu<\/i><\/i><\/p>\n![\"342-Sullivan002.gif\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/342-Sullivan002.gif\")
\n Figure 2: Command Shell<\/em><\/p>\nUsing strings, variables and objects<\/h3>\n
<\/pre>\n
<\/pre>\n
\n PS C:\\SimpleTalkSmo> $s = “Dan”
\n PS C:\\SimpleTalkSmo> $n
\n 8
\n PS C:\\SimpleTalkSmo> $s
\n Dan
\n PS C:\\SimpleTalkSmo><\/p>\n<\/pre>\n
<\/pre>\n
\n PS C:\\SimpleTalkSmo> $n
\n 11.5
\n PS C:\\SimpleTalkSmo><\/p>\n<\/pre>\n
<\/pre>\n
\n PS C:\\SimpleTalkSmo> get-member -inputObject $rand
\n \u00a0\u00a0 TypeName: System.Random<\/p>\n
\n —-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ———- ———-
\n Equals\u00a0\u00a0\u00a0\u00a0\u00a0 Method\u00a0\u00a0\u00a0\u00a0 System.Boolean Equals(Object obj)
\n GetHashCode Method\u00a0\u00a0\u00a0\u00a0 System.Int32 GetHashCode()
\n GetType\u00a0\u00a0\u00a0\u00a0 Method\u00a0\u00a0\u00a0\u00a0 System.Type GetType()
\n Next\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Method\u00a0\u00a0\u00a0\u00a0 System.Int32 Next(), System.Int32 Ne
\n NextBytes \u00a0\u00a0Method\u00a0\u00a0\u00a0\u00a0 System.Void NextBytes(Byte[] buffer)
\n NextDouble\u00a0 Method\u00a0\u00a0\u00a0\u00a0 System.Double NextDouble()
\n ToString\u00a0\u00a0\u00a0 Method\u00a0\u00a0\u00a0\u00a0 System.String ToString()<\/p>\n<\/pre>\n
<\/pre>\n
\n 12
\n PS C:\\SimpleTalkSmo><\/p>\n<\/pre>\n
Pipelines<\/h3>\n
<\/pre>\n
\n PS C:\\SimpleTalkSmo> $s | %{$_.Length}
\n 3
\n PS C:\\SimpleTalkSmo><\/p>\n<\/pre>\n
PowerShell and WMI<\/h3>\n
<\/pre>\n
\n PARSEC5 Win32_Group
\n PS C:\\SimpleTalkSmo> $groups | %{$_.Name}
\n Administrators
\n Backup Operators
\n Guests
\n Network Configuration Operators
\n Power Users
\n Users
\n PowerSQL
\n SQLServer2005MSFTEUser$PARSEC5$MSSQLSERVER
\n SQLServer2005MSSQLServerADHelperUser$PARSEC5
\n SQLServer2005MSSQLUser$PARSEC5$MSSQLSERVER
\n SQLServer2005NotificationServicesUser$PARSEC5
\n SQLServer2005SQLAgentUser$PARSEC5$MSSQLSERVER
\n SQLServer2005SQLBrowserUser$PARSEC5<\/p>\nSMO + PowerShell = PowerSMO<\/h2>\n
Configuring PowerSMO<\/h3>\n
\n This script uses a number of advanced features of PowerShell that we will not be discussing in this article, but the beginning of this script loads the SMO assemblies into PowerShell<\/i>.<\/p>\n<\/pre>\n
![\"342-Sullivan18.gif\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/342-Sullivan18.gif\")
Figure <\/i>18:<\/i> BOL SMO Documentation<\/i> In Figure 18, we could drill into services or service instances to find out more about these properties. What this means is the documentation for PowerSMO is the same as it is for SMO, it is the BOL. PowerSMO makes helper functions, like