Exploring the SQL Server PowerShell hierarchy

SQL Server started shipping with the SQLPS module in SQL Server 2012. The SQLPS module allows PowerShell to access SQL Server-specific cmdlets and functions, and also loads commonly used assemblies when working with SQL Server. This continues to be the case in SQL Server 2014.

Launching PowerShell from SQL Server Management Studio (SSMS) launches a Windows PowerShell session which imports the SQLPS module automatically, and sets the current context to the item the PowerShell session was launched from. Database administrators and developers can then start navigating the object hierarchy from there.

Getting ready

To follow this recipe, you should log in to SQL Server 2014 Management Studio.

How to do it...

In this recipe, we will navigate the SQL Server PowerShell hierarchy by launching a PowerShell session from SQL Server Management Studio:

1. Right-click on your instance node.
2. Click on Start PowerShell.
   

   Note that this will launch a PowerShell session and load the SQLPS module. This window looks similar to a command prompt, with a prompt set to the SQL Server object you launched this window from. In the following screenshot, ROGUE refers to the name of my local machine:

   

   Note the starting path in this window. The screen now shows how you could get to the default instance if you were to navigate using the PowerShell console or ISE:

   PS SQLSERVER:\SQL\<SQL instance name>\DEFAULT>

3. Type dir. This should give you a list of all objects directly accessible from the current server instance; in our case, from the default SQL Server instance ROGUE. Note that dir is an alias for the cmdlet Get-ChildItem.
   

   Tip

   Downloading the example code

   You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

   This is similar to the objects you can find under the instance node in Object Explorer in SQL Server Management Studio.

   
4. While our PowerShell window is open, let's explore the SQL Server PSDrive or the SQL Server data store which PowerShell treats as a series of items. Type cd \. This will change the path to the root of the current drive, which is our SQL Server PSDrive.
5. Type dir. This will list all items accessible from the root SQL Server PSDrive. You should see something similar to the following screen:
   
6. Close this window.
7. Go back to Management Studio and right-click on one of your user databases.
8. Click on Start PowerShell. Note that this will launch another PowerShell session with a path that points to the database you right-clicked from:
   

   Note the starting path of this window is different from the starting path where you first launched PowerShell in the earlier steps. If you type dir from this location, you will see all items that are under the AdventureWorks2014 database.

   

   You can see some of the items enumerated in this screenshot in SQL Server Management Studio Object Explorer, if you expand the AdventureWorks2014 database node:

   

How it works...

When PowerShell is launched through SSMS, a context-sensitive PowerShell session is created and it automatically loads the SQLPS module. This will be evident in the prompt, which by default shows the current path of the object from which the Start PowerShell menu item was clicked from.

The SQLPS module was not always loaded when PowerShell was launched from SSMS. With SQL Server 2008/2008 R2 it was shipped with a SQLPS utility, which is also referred to as a mini shell. When you started PowerShell from SSMS, it was not a full PowerShell console that was launched. It was a constrained, closed shell preloaded with SQL Server extensions was loaded. This shell was meant to be used for SQL Server only, which proved to be quite limiting because DBAs and developers often need to load additional snapins and modules in order to integrate SQL Server with other systems through PowerShell. At that time, the alternative way was to launch a full-fledged PowerShell session and depending on your PowerShell version, either load snapins or load the SQLPS module.

Since SQL Server 2012, the original constrained mini shell has been deprecated. When you launch a PowerShell session from SSMS in SQL Server 2012 onwards, what is launched is the full-fledged PowerShell console, with the updated SQLPS module loaded by default.

Once the SQLPS module is loaded, SQL Server becomes exposed as a PowerShell Drive (PSDrive), which allows traversing of objects as if they are folders and files. Familiar commands for traversing directories are supported in this provider, such as dir or ls. Note that these familiar commands are often just aliases to the real cmdlet name, in this case, Get-ChildItem.

When you launch PowerShell from SSMS, you can immediately start navigating the SQL Server PowerShell hierarchy.