Using PowerShell with SQL Server Management Objects (SMO)

By: Edwin Sarmiento   |   Updated: 2009-05-06   |   Comments (6)   |   Related: 1 | 2 | 3 | > PowerShell

Problem

I have been hearing about Microsoft's scripting language called Windows PowerShell which is extensively used nowadays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA.

Solution

To continue this series on Introduction to Windows PowerShell for the SQL Server DBA, this tip will look at writing and running PowerShell scripts. In Part 1, we have introduced Windows PowerShell, the use of cmdlets and aliases and how we can take full advantage of the help available from the environment. Part 2 talks about using the pipeline and formatting results in PowerShell. In this tip, we will start writing PowerShell scripts and introduce SQL Server Management Objects (SMO).

Writing and Running PowerShell scripts

Since you can now write a basic command by piping cmdlets in Windows PowerShell, it's time to write a script. A script is a sequence of commands and PowerShell cmdlets written together to create more complex tasks. PowerShell script files should have a .PS1extension. While you might be wondering about the "1" in the file extension, it was intended to be for script versioning. Since PowerShell v2.0 is still in CTP, we are still not sure whether the extension would change, but currently any script with a .PS1 extension will work in PowerShell v2.0.

You can write a script using any text editor like Notepad or TextPad although there are third-party scripting tools out there like PowerShell Plus from Idera and PowerGUI from Quest Software. PowerShell v2.0 CTP comes with a Graphical Windows PowerShell that you can use as your development environment for PowerShell. The choice of a development environment for Windows PowerShell is totally up to you.

You can start writing scripts by saving the commands you've learned in Part 1 and Part 2 on a PS1file. To run the script, you can call it from within Windows PowerShell by specifying either its full or relative path. By default, you wouldn't be able to execute scripts in Windows PowerShell due to the security policy.

To enable a script to run, you have to modify the execution policy within Windows PowerShell by calling the Set-ExecutionPolicy cmdlet and passing the execution policy as a parameter. Different execution policies are described here, but the recommended setting is to use RemoteSigned as this will allow you to run scripts locally but will require a digital signature from a trusted publisher on scripts that come from a remote source. After setting the execution policy, you can now run your scripts from the PowerShell console

Notice the period at the beginning of the script together with the backslash? That simply tells PowerShell to execute the script in the current scope. This only means that you have to type the complete file path in order for you to run a script or, in this particular case since the script is in the current directory, you can start it with the".\" notation

Passing Variable Parameters To A Script

In order for your scripts to be dynamic, you have to find ways to pass different values to it. Using parameters with a script allow additional data to be passed to the script in a structured way. You use the $args[i] variable to refer to arguments that are passed to a script. The [i] simply means that you can use an array of values with an index of [0] in case you want to pass multiple parameters to your script. Translating this to the example we used in Part 2, let's say we want the script that returns all the processes with PrivateMemorySize greater than 50MB to be dynamic - a script that will return all the processes for which property and value we specify. We define variables that will contain the parameters that we will pass to the script and use that variable within the script

Notice the use of the pound/sharp (#) symbol to let PowerShell know that the current line is a comment. It is recommended to write as much comments in your scripts as possible. Also, the way to declare a variable in PowerShell starts with a $ sign. Bear in mind that PowerShell variables are objects, unlike in other scripting languages like VBScript where variables are simply containers for values. This means that you can pass objects to a script variable the same way you can pipe the results of a cmdlet to another cmdlet. The command in the script is exactly the same as what we had in Part 2 with a little variation. We used the variable $attrib instead of a fixed property like PrivateMemorySize and the variable $val instead of a fixed value

Calling the script and passing parameter values to it:

Notice how I used PrivateMemorySet and 50MB as the values for $attrib and $val, respectively, on the first call to the script while using WorkingSet and 100MB on the second call. This makes your script dynamic simply by using variables and parameters.

Server Management Objects (SMO): Your Gateway to SQL Server

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing SQL Server. Simply put, its a way to allow programmatic management of SQL Server instances. SQL Server client tools like Management Studio were created using SMO. You can use SMO with other scripting or programming languages that can (note that scripting languages like VBScript uses Component Object Model automation and will not be able to see SMO since it uses .NET) to build SQL Server management applications. While built for SQL Server 2005 using the .NET Framework 2.0, it is compatible with SQL Server 2000 and 2008, allowing you to manage multi-version instances across your enterprise. One thing that you should note though is that SMO does not support compatibility levels below 80. I've seen databases running on SQL Server 2005 but are still on compatibility 70. Hence, you will not be able to manage those databases using SMO.

The first thing you need to do is load the appropriate .NET assemblies used by SMO.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

You do have to check the versions of the SMO assemblies that you load into memory as this might cause confusion especially when you have multiple versions running on your machine. There are other assemblies that are associated with SMO for managing SQL Server, but most of the time this would suffice. After loading the SMO assemblies, you can now create an instance of the Server object to represent the SQL Server instance that you will be connecting to, passing the instance name. In the line below, I will be assigning the results to a variable $serverInstanc, so we can manipulate the results the way we have done in the previous examples.

$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "InstanceName"

You can then pipe the results to the Get-Member cmdlet to see the different properties and methods for the Server object.

One of the most common task we do as SQL Server DBAs is to audit our SQL Server installations. We can apply what we have learned from this tip to write a script to audit our SQL Server installations, retrieving the instance name, the edition, the version and the service pack level.

I used a few cmdlets in this script for formatting purposes. The Out-Null cmdlet simply deletes the output instead of sending it to the console. We don't really need the output but rather we need the object returned by the first line so we just pipe it to the Out-Null cmdlet. Next, I used the Write-Host cmdlet to display the results in the console window for demonstration purposes. You can use the formatting options mentioned in Part 2 to generate a report to be exported in either HTML or CSV. Notice how I used the same script to audit different versions of SQL Server, from 2000 to 2008.

You can also call the Information class to display all the non-configurable information about the instance you are connecting to. Since we only need a few of them, we just specify them explicitly in the script.

Next Steps

We have just scratched the tip of the iceberg on PowerShell and SMO. In future tips, we will look at the common DBA tasks and translate them to PowerShell with SMO

• Download and install Windows PowerShell
• Check out the PowerShell Week webcasts from Microsoft TechNet
• Read more on the SMO Class Library to translate SQL Server objects that we are already familiar with to SMO objects

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips