May 26th, 2009

Hey, Scripting Guy! Why Should I Use Windows PowerShell with SQL Server 2008?

Hey, Scripting Guy! Question

Hey, Scripting Guy! I recently saw that SQL Server 2008 has Windows PowerShell built into it. I looked at, but I am not really impressed. Why should I be interested in using Windows PowerShell with SQL Server 2008?

– JJ

SpacerHey, Scripting Guy! Answer

Hi JJ,

For some unknown reason, it has turned cool down here in Charlotte, North Carolina, in the United States. Not that Ed is complaining. However, it does give us one answer to your question: “Because it is cool.” This may not be the best answer, but then Ed has not been playing around with SQL Server for several years.

This week we are examining using Windows PowerShell to work with SQL Server 2008. The SQL Server 2008 product page is an excellent starting point. From here, you can download trial versions of the real product and free versions of SQL Server 2008 Express Edition. The Script Center Database Hub is a great place to start examining scripting and working with databases.

Because Ed does not consider himself to be a SQL Server 2008 guru, we decided to pass this question off to Buck Woody, SQL Server Technical Specialist for the Microsoft Corporation. He is a former Program Manager on the SQL Server team and the author of seven books about SQL Server. (Compared to Buck, Ed cannot even spell SQL. Oh, wait, he just did.)

 

For those of you with the job title of “database administrator” (or DBA), you may wonder why in the world you would have to add another scripting tool to your toolbox. After all, you have the Transact-SQL language available to you, and many environments (such as SSMS or SQLCMD) to run it in. Why not just stick with one of those to manage your systems? Well, wonder no more. You have to learn Windows PowerShell, and you need to start using it. Trust me on this one; you will be happy you took the time.

The primary reason that you should use Windows PowerShell with SQL Server is that it does not restrict you to just SQL Server. Have you ever needed to check the Windows Event Logs together with the SQL Error Logs? Try that in Transact-SQL! (Actually, don’t.) Do you need to use a list of server names to change service passwords, run a query, create an HTML file from the results, and send e-mail when you are finished? Try all that with Transact-SQL. (Actually, don’t.) OK, you get the idea.

If you are a long-time reader of “Hey, Scripting Guys!” articles, you are probably already an old hand at Windows PowerShell. But if you are a database professional, this might be the first time that you have heard about it. So let us take a quick rundown of what it is, and how easy it is to obtain it and install it.

Windows PowerShell can be used in several ways. First, you can work with it exactly like the cmd.exe or command.com shell. You type commands, and after you press ENTER, the result is displayed on the screen. Second, you can run Windows PowerShell such as batch files or SQL Server scripts. You record the scripts as text files and call them to run.

In SQL Server 2008, Windows PowerShell becomes a first-class citizen to manage a SQL Server Instance. In fact, in SQL Server 2008 you can even create a SQL Server Agent Job Step that contains-you guessed it-a set of Windows PowerShell commands. But you are not limited to SQL Server 2008. Windows PowerShell can work with SQL Server 2008, 2005, and even 2000. But we are getting a little ahead of ourselves.

Now that you know the basics, you have to know where you can get Windows PowerShell and how to install it. Instead of rehashing those instructions, we’ll encourage you to head on over to our Downloading Windows PowerShell page to learn how.

There are only a few things you need to know about Windows PowerShell to get started. The first is that the commands you run are called cmdlets. Cmdlets are in the form of verb-noun, such as Get-Process. The verb part gives you some idea of what the cmdlet will do, and the noun part gives you an idea what it will affect. In fact, the first cmdlet you should learn is Get-Help. That will show how to use Help for Windows PowerShell. As soon as you start learning some of the cmdlets, you can get help for any of them by typing Get-Help cmdletname.

The second thing that is important to know is that you can send the results of one command to another, from left to right. You can do this by using the “|” symbol. This is known as the pipeline. You have probably done this at a command prompt before:

DIR | MORE

You are taking the DIR command and sending what it produces (the directory listing) to the MORE command. This limits the display of information to one screen at a time. You can do the same thing in Windows PowerShell. However, you can keep doing it repeatedly. For the database professional, you can think of the SQL-language SELECT statement with a JOIN statement. When you use the JOIN statement, you are limiting the data in each step. Using a pipeline is like that. Let us take a practical look at what you can do with a series of commands and some pipelines.

I want to find the drive on my system that has the most space free. Do not worry about the cmdlets just yet. We will talk more about those later. The first cmdlet uses Windows Management Instrumentation (WMI) to open disk drive information. That command is then sent through a pipe to another command that sorts the drive information by free space in descending order. The results of those two commands are sent to another command that grabs the drive letter, but only the first one:

Get-WMIObject Win32_LogicalDisk | 
sort-object -Property freespace -desc | select-object DeviceID  -first 1

The last thing that you have to know is that you can set almost anything to a variable. Variables start with the dollar-sign ($), and when you create one, you can use it instead of the results that you ran with all the commands on the right side. Some are really simple, such as this:

$a = C:

Now you can use the variable wherever you want:

DIR $a

But you can also assign the results of a set of commands to a variable. Let us take that command we ran a moment ago and assign the results to a variable:

$a = Get-WMIObject Win32_LogicalDisk | 
sort-object -Property freespace -desc | 
select-object DeviceID  -first 1

The interesting thing is that the variable we just made is not just the text from before; it is a reference to the actual object itself. To see what we can do with the object, we can use both a cmdlet and the pipe together with the variable to see what properties we can ask for:

$a | get-member

From the result, we can see that there are various methods (actions we can perform) and properties (things we can see or set). We can do that by typing the variable, a period, and then one of the methods or properties. So if we want the drive letter contained in this object, we type this:

$a.DeviceID

And now the payoff-showing the directory of the drive with the most space:

$a = Get-WMIObject Win32_LogicalDisk | 
sort-object -Property freespace -desc |
select-object DeviceID  -first 1
DIR $a.DeviceID

And there you have it.

Okay, enough with the introduction. By now you are seasoned at working with cmdlets, piping, and variables. All we have to do now is to put that together with SQL Server, and you will be creating useful scripts in no time.

Because Windows PowerShell is built by using the .NET Framework, you have access to two libraries that you have probably used before: Database Management Objects (or DMO), which was used in SQL Server 2000, and Server Management Objects (or SMO), which is used in SQL Server 2005 and later. We will focus on SMO because it works on SQL Server 2008, 2005, and even 2000.

To begin using SMO, you have to install it. The simplest installation path is just to install the Client Connectivity Tools from the SQL Server 2008 or SQL Server 2005 DVD. You can also download the SMO library all by itself from Microsoft.

With the SMO library installed on your computer, you need to load it into windows PowerShell. Here is how you add the library for SMO into Windows PowerShell after the SMO library has been installed on your computer:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

Now you have all the objects inside the SMO model available to Windows PowerShell. But what does that mean? Well, first you have to know what is in the SMO model. It is fairly big. Therefore, we will not cover it all here. But the general idea is that there are various parent objects that own children objects. You can look up the whole model in SQL Server’s Books Online. But there is an easier way to visualize the SMO hierarchy. Just open SQL Server Management Studio (SSMS) and examine the left pane. This is known as the object browser. If you are a database administrator, you will recognize all the objects there. In fact, SSMS uses SMO to retrieve those objects. So you now have access to all the objects in Windows PowerShell that you have in SSMS.

You can tell from the views above that instances of SQL Server are at the top level. These instances own databases, which in turn own tables that in turn own indexes. (The knee bone’s connected to the…thigh bone….)

So how does all this relate to Windows PowerShell? As soon as you have loaded the SMO library, you can start to create those instance and database objects to work with. If you create a new instance object, you can work with it or anything it owns by adding a period to the end of it. Let us create another variable, this time by using the SMO library we instantiated a moment ago. Assuming that you are running both Windows PowerShell and SQL Server on the same system, you can create a new connection to a server:

$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"

If the SQL Server is located elsewhere, just change (local) to the name of the server and Instance name, if it is not the default. We are also assuming that your SQL Server is using Integrated Authentication and that your Windows account can access the instance of SQL Server.

Now that we have this variable, what can we do with it? Let us use the same Get-Member cmdlet again:

$sqlServer | get-member

Whoa, that is lots of stuff! Let us try that again, this time with a pipe to the MORE command:

$sqlServer | get-member | MORE

Nice. Now we can see many methods (which we will leave alone for the moment) and many properties. One of the most interesting properties we can examine is the server’s, well, Properties. Let us try that:

$sqlServer.Properties

Hmmm. Quite a bit here. We will have to use the MORE command again to see them all.

Tomorrow, we will consider how we can refine this down even more, and then begin to do some really useful work inside the database server.

 

Thank you, Buck. That was an excellent introduction to using Windows PowerShell on SQL Server 2008. It makes me want to download SQL Server 2008 Express and start to play with it. I may start to use it for my database needs. JJ, thank you for a great question, it gave us an excuse to get back in touch with Buck Woody again. Join us tomorrow as SQL Week continues. Until then, keep cool.

 

Ed Wilson and Craig Liebendorfer, Scripting Guys

Author

0 comments

Discussion are closed.