PowerSMO!

Last year I wrote a blog article about using what was then called MSH with SQL Server Management Objects http://pluralsight.com/blogs/dan/archive/2005/12/29/17703.aspx. MSH is now called PowerShell and mixing some SMO with it makes PowerSMO!

SMO is a set of object models for SQL Server. With PowerSMO! you can manipulate those object models from the command line or with a script. The two object models probably of most interest to SQL Server developers and DBA’s are the Server and ManagedComputer object models and that’s what this article is going to use them to show how to use PowerSMO!.

This article assumes you are reasonably familiar with SQL Server and have some familiarity with PowerShell. You can look at some of the previous articles I have blogged about PowerShell to get familiar with PowerShell.

There are a number of reasons for using PowerSMO! You can script the management, testing, status, etc. of database objects. PowerSMO! also has pedagogical value too. I’ve been using PowerShell + SMO in the classes I teach because it provides such a quick way too cobble up ad hoc examples. It’s useful in development for the same reason, you can quickly try something out before you commit the time to edit/compile/test a C# program.

To use PowerSMO! you need to start up PowerShell then run the InitPowerSMO.ps1 script. This script is available at http://www.pluralsight.com/dan/samples/InitPowerSMO.zip.

Windows PowerShell
Copyright (C) 2006 Microsoft Corporation. All rights reserved.
PS C:\demos> . "C:\demos\InitPowerSMO.ps1"
PS C:\demos>

This script loads the SMO assemblies into PowerShell and a number of other things that we will look at later.

Let’s start with some basics. The new-object cmd-let is used to make an instance of a .NET class. SMO has a class named ManagedComputer. An instance of ManagedComputer represents the root of one of the object models in SMO, the one that is used to manage the services provided by SQL Server, such as the database engine or full-text search. BTW, watch out for the line-wraps in the examples that follow.

PS C:\demos> $mc = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer"
PS C:\demos> $mc.services | %{$_.name}
MSFTESQL
MSSQLSERVER
SQLBrowser
SQLSERVERAGENT
PS C:\demos>

Here we used an instance of the ManagedComputer class find the SQL Server services on the local computer. We piped these services to a script that prints out the name of the service. Looks like a pretty typical SQL Server installation.

This is handy but, as you can see, it is a bit tedious dealing with the full name of the class that new-object requires. PowerShell deals with this issue in a number of places by using what it calls a “type accelerator” or “type alias”.

For example [wmi] is a type accelerator for System.Management.ManagementObject and you can use it instead of the fully spelled out class name when you need to specify that type. Unfortunately there is no provision in PowerShell for creating your own type aliases. PowerShell supports user defined aliases for cmdlets, functions, etc. but these are not useful as type accelerators.

To save on typing PowerSMO! creates helper functions that can be used to create instances of the classes used by SMO. One of the reasons you see a delay when you run the initPowerSMO.ps1 script is that it has to create all these functions. Below is a script that uses one of these functions and duplicates the operation of the previous example.

PS C:\demos> $mc = Get-SMO_ManagedComputer
PS C:\demos> $mc.Services | %{$_.name}
MSFTESQL
MSSQLSERVER
SQLBrowser
SQLSERVERAGENT
PS C:\demos>

All of these helper functions have a preface of “Get-SMO_” followed by the name of the class that they create.

All that the Get-SMO_ helper functions and new-object are doing is running the constructor for the class requested. In the previous two samples we used the parameter-less constructor to make a ManagedComputer object. When you do this the managed computer is the one PowerShell is running on.

One of the nice things about PowerShell is that you can always ask what kinds of things an object can do by piping it to the get-member cmdlet. For example:

PS C:\demos> $mc | get-member

   TypeName: Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
Name                   MemberType Definition
----                   ---------- ----------
Equals                 Method     System.Boolean Equals(Object obj)
GetHashCode            Method     System.Int32 GetHashCode()
GetSmoObject           Method     Microsoft.SqlServer.Management.Smo.Wmi.Wmi...
...

PowerShell does not have the same level of builtin “What can you do for me?” capability for the constructors of .NET classes. .NET classes often have a number of constructors to choose from and it is often hard to remember them all. PowerSMO! has some helper functions to make it easier to see what constructors there are for a class.

PS C:\demos> Get-SMO_ctors (Get-SMOT_ManagedComputer)
ManagedComputer()
ManagedComputer(String machineName)
ManagedComputer(String machineName, String userName, String password)
ManagedComputer(String machineName, String userName, String password, 
ProviderArchitecture providerArchitecture)
PS C:\demos>

The Get-SMO_ctors function lists the constructors for the type passed, in parentheses to it. For every Get-SMO_ function there is a Get-SMOT_ that returns its type definition. The Get-SMO_ctors function requires a type definition, not the function that makes an instance of the type. Actually the Get-SMO_ctors function will work for any .NET type definition.

PS C:\demos> Get-SMO_ctors ([System.String])
String(Char* value)
String(Char* value, Int32 startIndex, Int32 length)
String(SByte* value)
String(SByte* value, Int32 startIndex, Int32 length)
String(SByte* value, Int32 startIndex, Int32 length, Encoding enc)
String(Char[] value, Int32 startIndex, Int32 length)
String(Char[] value)
String(Char c, Int32 count)
PS C:\demos>

The example above shows using the Get-SMO_ctors function to find the various constructors for System.String class from .NET.

All of the Get-SMO_ function will accept parameters for a constructor. You pass the parameters on the command line as you would for any other PowerShell function.

PS C:\demos> $mc = Get-SMO_ManagedComputer "PARSEC5"
PS C:\demos>

In the example above $mc is attached to a computer name “PARSEC5”.

Some constructors you will want to use require multiple parameters. The ServerConnection class serves a purpose similar to that for a SqlConnection in ADO.NET

PS C:\demos> Get-SMO_ctors (Get-SMOT_ServerConnection)
ServerConnection(SqlConnectionInfo sci)
ServerConnection(SqlConnection sqlConnection)
ServerConnection()
ServerConnection(String serverInstance)
ServerConnection(String serverInstance, String userName, String password)
ServerConnection(String serverInstance, String userName, SecureString password)
PS C:\demos>

Here you can see that there is a constructor that lets you can create a ServerConnection using a SQL login. It needs the name and password for that login.

PS C:\demos> $sc = Get-SMO_ServerConnection "PARSEC5" "ambler" "Ambler"
PS C:\demos> $sc.ConnectionString
server='PARSEC5';uid='ambler';password='Ambler';
multipleactiveresultset =false
PS C:\demos>

As you can see the ServerConnection just uses the user name and password to build the connection string it will need to connect to SQL Server.

A note about the Get-SMO_ function that is a bit different than using the new-object cmdlet. The constructor parameters for the Get-SMO_ functions are separated by spaces, unlike passing constructor parameters to the new-object cmdlet which requires them to be separted by commas.

Ok, let’s use PowerSMO! to create a database. To do this we will need to make use of two classes of SMO object, a Server and a Database. We will start by looking at the constructors for each.

PS C:\demos> Get-SMO_ctors (Get-SMOT_Server)
Server(ServerConnection serverConnection)
Server(String name)
Server()
PS C:\demos> Get-SMO_ctors(Get-SMOT_Database)
Database()
Database(Server server, String name)
PS C:\demos>

We see that to construct a Server can pass in the name of the instance of SQL Server we want to use, and for the Database we will have to pass in a reference to the Server and a the name we want for the new database.

PS C:\demos> $s = Get-SMO_Server "PARSEC5"
PS C:\demos> $db = Get-SMO_Database $s "DBDemoSample"
PS C:\demos> $db.create()
PS C:\demos> $s.Databases["DBDemoSample"].Name
DBDemoSample
PS C:\demos>

Here we have made the Server object for the default SQL Server instance on machine “PARSEC5”. Next we make the Database object by passing the variable that holds the Server object into the database constructor. Just to confirm it actually worked we look at the databases collection in the server and see if we can find a database named “DBDemoSample” and then print out its name. If there was no database named “DBDemoSample” this line would not have printed out anything.

Now let’s do a little database management. Typically when a database is created its recovery model is “Full” This is going to be a development database so we want to change its recovery mode to “Simple”. We will start by confirming our suspicions about its recovery mode.

PS C:\demos> $db = $s.DataBases["DBDemoSample"]
PS C:\demos> $db.DatabaseOptions.RecoveryModel
Full
PS C:\demos>

We use $s to get a reference to the “DBDemoSample” database. From $db we can look at the recovery model in database options and see that, in fact, it is “Full”.

The value of RecoverModel is one of the enumerated values from the Microsoft.SqlServer.Management.Smo.RecoveryModel enum. You can always find the possible values of an enum by making use of .NET reflection in PowerShell.

PS C:\demos> [Microsoft.SqlServer.Management.Smo.RecoveryModel].GetFields() | %{
$_.Name}
value__
Simple
BulkLogged
Full
PS C:\demos>

Again, similar to using new-object, you must type out the entire class name to find the enumerated values of an enum. PowerSMO! provides another helper function for finding the enumerated values.

PS C:\demos> Get-SMO_enum (Get-SMOT_RecoveryModel)
value__
Simple
BulkLogged
Full

The Get-SMO_emum function finds the enumerated values of an enum. It is similar to the Get-SMO_ctors function in that it requires a class definition, not the function that makes an instance. In this example we use the Get-SMOT_RecoveryModel to get the class definition.

Using an enumerated value once you know what it is, is fairly easy. You can just set it as though is were a string value and PowerShell will convert it to the appropriate enumeration.

PS C:\demos> $db.DatabaseOptions.RecoveryModel="Simple"
PS C:\demos> $db.Alter()
PS C:\demos>

Setting a value in a field of a SMO object does not affect the database behind it until you call the Alter() function. After the Alter() function completes the database has the new recovery mode.

Note you need not worry about setting an improper enumeration value, PowerShell will check that for you, as you can see below.

PS C:\demos> $db.DatabaseOptions.RecoveryModel = "Fullx"
Exception setting "RecoveryModel": "Cannot convert value "Fullx" to type "Micro
soft.SqlServer.Management.Smo.RecoveryModel" due to invalid enumeration values.
 Specify one of the following enumeration values and try again. The possible en
umeration values are "Full, BulkLogged, Simple"."
At line:1 char:21
+ $db.DatabaseOptions.R <<<< ecoveryModel = "Fullx"
PS C:\demos>

Now you have seen PowerSMO!. It consists of functions, prefaced by Get-SMO_ that you can use to create instances of SMO objects. It also includes Get-SMOT_ functions that you can use to retrieve the type definition for a particular kind of SMO object. The Get-SMO_ctors function gives you a list of the constructors for a class and the Get-SMO_enum function give you a list of the values for an enum.

If you take a peek inside of the initPowerSMO.ps1 file, except for the Get-SMO_enum and Get-SMO_ctors functions, you won’t see any of the functions we have used in this article. That’s because the initPowerSMO.ps1 script generates these functions from the assemblies that makeup SMO. How it does this will be the topic of a later blog article.

Dan

---

Posted Nov 07 2006, 01:55 PM by dan-sullivan