Apr 08, 2014

Today, I’m going to go into a little more detail with a topic I’ve previously covered, that is, scripting. There have been a few blog posts about PowerShell and I very strongly suggest that it be a topic worthy of your study. Well, how about a little more meat on those bones? Windows PowerShell is a fully developed, object-based scripting language. It is built on the .NET Framework, and has been designed for IT professionals who may not have any experience in scripting, but are still required to control and manage the administration of the Windows operating system and applications that run on Windows. Many command shells, including cmd.exe and certain UNIX shells, operate by executing a command or utility in a new process and presenting the results to the user as text. For this reason, many text processing utilities have been created to support this interaction. You might think of sed, AWK or Perl for example. These shells, such as BASH, have commands built into the shell process. There have been many shell specific utilities created (squillions in fact). Windows PowerShell is very different (of course, it is). It doesn’t process text; it processes objects that are based on the .NET Framework. Like other shells, it comes with a large set of built-in commands, or cmdlets as they’re called. Unlike traditional shells, where the commands range from the very simple (attrib.exe) to the very complex (netsh.exe), Windows PowerShell cmdlets are all very simple. They are single-function command-line tools built into the shell. You can use the cmdlets individually or you can combine them to perform complex tasks. A tremendous feature of Windows PowerShell is its extensibility. PowerShell ships with many built-in modules, but you can install additional modules as required. Modules can be in one of two states: loaded or unloaded. In order to access the cmdlets of a particular module you must, of course, load the module. You can easily view the modules that have been loaded by using the Get-Module command. If the module you want is not loaded, you will use Import-Module to load it. Simple! To add SQL Server functionality to Windows PowerShell, SQL Server 2014 includes the SQLPS module. As we’ve just discussed, it’s a simple matter to import the module into Windows PowerShell to add the SQL Server components. The SQLPS module includes:

• A SQL Server Provider that enables a simple navigation mechanism similar to the paths in the file system. You can even use familiar tools like cd, dir, ren and del (does anyone even remember those old-school DOS commands?)
• A set of SQL Server cmdlets to support a vast array of actions such as running a sqlcmd script that contains Transact-SQL or even XQuery statements.

Let’s have a look at a few examples. You can follow along at home if you wish. First of all, let’s list all of the loaded modules. From SQL Server Management Studio, right-click on the server name and click Start PowerShell. At the PowerShell prompt you would enter: Get-Module Now, given that you entered PowerShell from within SQL Server Managment Studio, you should see SQLPS and SQLASCMDLETS have been loaded by default. Now, let’s see what’s contained within SQLPS, shall we? Enter: Get-Command -module SQLPS What you should see (fingers crossed) is a list of all the cmdlets available to you from within SQLPS. Let’s do a little navigating around. Try this: Get-Help SQLSERVER | more Check out the help information. Use the space bar to see subsequent screens. Did you notice the ‘Task’ section on Navigating SQLSERVER:SQL paths (it’s on about the fourth page)? Try this one now: Get-Location Now the location returned to you. Nice! Now, this: Get-ChildItem Wow, look at that! Let’s change the location: Set-Location SQLServer: You’ve managed to go up the tree a little bit. Of course, now if you use the same Get-ChildItem command, it looks a lot different. So, let’s try this (this works on my particular server): Set-Location SQLServer:SQLMIA-SQL Again, if you use the Get-ChildItem, you should see a list of all the SQL Server instances on that host. Only a little more. Enter this: Set-Location SQLServer:SQLMIA-SQLDefaultDatabase and follow it up with the now familiar: Get-ChildItem. A list of the databases on the default instance will appear. And finally: Get-ChildItem | Select Name, Size, SpaceAvailable, IndexSpaceUsage |format-table -auto That’s right. Some nicely formatted metadata of the databases on that particular instance will appear. Cool! Obviously there is very much more you can do with PowerShell and, in particular, SQLPS. Have some fun poking around, it’s good for you!