Discovering SQL-related cmdlets and modules

In order to be effective at working with SQL Server and PowerShell, knowing how to explore and discover cmdlets, snap-ins, and modules is in order.

Getting ready

Log in to your SQL Server instance, and launch PowerShell ISE. If you prefer the console, you can also launch that instead.

How to do it...

In this recipe we will list the SQL-Server related commands and cmdlets.

1. To discover SQL-related cmdlets, type the following in your PowerShell editor and run:

   #how many commands from modules that
   #have SQL in the name
   Get-Command -Module "*SQL*" | Measure-Object
   
   #list all the SQL-related commands
   Get-Command -Module "*SQL*" | 
   Select CommandType, Name, ModuleName | 
   Sort -Property ModuleName, CommandType, Name | 
   Format-Table -AutoSize

   After you execute the line, your output window should look similar to the following screenshot:

   

2. To see which of these modules are loaded, type the following in your editor and run:

   Get-Module -Name "*SQL*"

   If you have already used any of the cmdlets in the previous step, then you should see both SQLPS and SQLASCMDLETS. Otherwise, you will need to load these modules before you can use them.

3. To explicitly load these modules, type the following and run:

   Import-Module -Name "SQLPS"

   Note that SQLASCMDLETS will be loaded when you load SQLPS.

How it works...

At the core of PowerShell are cmdlets. A cmdlet (pronounced commandlet) can be a compiled, reusable .NET code, or an advanced function, or a workflow that typically performs a very specific task. All cmdlets follow the verb-noun naming notation.

PowerShell ships with many cmdlets and can be further extended if the shipped cmdlets are not sufficient for your purposes.

A legacy way of extending PowerShell is by registering additional snap-ins. A snap-in is a binary, or a DLL, that contains cmdlets. You can create your own by building your own .NET source, compiling, and registering the snap-in. You will always need to register snap-ins before you can use them. Snap-ins are a popular way of extending PowerShell.

The following table summarizes common tasks with snap-ins:

Task	Syntax
List loaded snap-ins	Get-PSSnapin
List installed snap-ins	Get-PSSnapin -Registered
Show commands in a snap-in	Get-Command -Module "SnapinName"
Load a specific snap-in	Add-PSSnapin "SnapinName"

When starting, PowerShell V2, modules are available as the improved and preferred method of extending PowerShell.

A module is a package that can contain cmdlets, providers, functions, variables, and aliases. In PowerShell V2, modules are not loaded by default, so required modules need to be explicitly imported.

Common tasks with modules are summarized in the following table:

Task	Syntax
List loaded modules	Get-Module
List installed modules	Get-Module -ListAvailable
Show commands in a module	Get-Command -Module "ModuleName"
Load a specific module	Import-Module -Name "ModuleName"

One of the improved features with PowerShell V3 is that it supports autoloading modules. You do not need to always explicitly load modules before using the contained cmdlets. Using the cmdlet in your script is enough to trigger PowerShell to load the module that contains it.

The SQL Server 2012 modules are located in the PowerShell/Modules folder of the Install directory:

There's more...

The following table shows the list of the SQLPS and SQLASCMDLETS cmdlets of this version:

CommandType Name	ModuleName
Cmdlet Add-RoleMember	SQLASCMDLETS
Cmdlet Backup-ASDatabase	SQLASCMDLETS
Cmdlet Invoke-ASCmd	SQLASCMDLETS
Cmdlet Invoke-ProcessCube	SQLASCMDLETS
Cmdlet Invoke-ProcessDimension	SQLASCMDLETS
Cmdlet Invoke-ProcessPartition	SQLASCMDLETS
Cmdlet Merge-Partition	SQLASCMDLETS
Cmdlet New-RestoreFolder	SQLASCMDLETS
Cmdlet New-RestoreLocation	SQLASCMDLETS
Cmdlet Remove-RoleMember	SQLASCMDLETS
Cmdlet Restore-ASDatabase	SQLASCMDLETS
Cmdlet Add-SqlAvailabilityDatabase	SQLPS
Cmdlet Add-SqlAvailabilityGroupListenerStaticIp	SQLPS
Cmdlet Backup-SqlDatabase	SQLPS
Cmdlet Convert-UrnToPath	SQLPS
Cmdlet Decode-SqlName	SQLPS
Cmdlet Disable-SqlHADRService	SQLPS
Cmdlet Enable-SqlHADRService	SQLPS
Cmdlet Encode-SqlName	SQLPS
Cmdlet Invoke-PolicyEvaluation	SQLPS
Cmdlet Invoke-Sqlcmd	SQLPS
Cmdlet Join-SqlAvailabilityGroup	SQLPS
Cmdlet New-SqlAvailabilityGroup	SQLPS
Cmdlet New-SqlAvailabilityGroupListener	SQLPS
Cmdlet New-SqlAvailabilityReplica	SQLPS
Cmdlet New-SqlHADREndpoint	SQLPS
Cmdlet Remove-SqlAvailabilityDatabase	SQLPS
Cmdlet Remove-SqlAvailabilityGroup	SQLPS
Cmdlet Remove-SqlAvailabilityReplica	SQLPS
Cmdlet Restore-SqlDatabase	SQLPS
Cmdlet Resume-SqlAvailabilityDatabase	SQLPS
Cmdlet Set-SqlAvailabilityGroup	SQLPS
Cmdlet Set-SqlAvailabilityGroupListener	SQLPS
Cmdlet Set-SqlAvailabilityReplica	SQLPS
Cmdlet Set-SqlHADREndpoint	SQLPS
Cmdlet Suspend-SqlAvailabilityDatabase	SQLPS
Cmdlet Switch-SqlAvailabilityGroup	SQLPS
Cmdlet Test-SqlAvailabilityGroup	SQLPS
Cmdlet Test-SqlAvailabilityReplica	SQLPS
Test-SqlDatabaseReplicaState	SQLPS

To learn more about these cmdlets, use the Get-Help cmdlet. For example:

Get-Help "Invoke-Sqlcmd" 
Get-Help "Invoke-Sqlcmd" -Detailed
Get-Help "Invoke-Sqlcmd" -Examples
Get-Help "Invoke-Sqlcmd" -Full

You can also check out the MSDN article on SQL Server database engine cmdlets:

http://msdn.microsoft.com/en-us/library/cc281847.aspx

When you load the SQLPS module, several assemblies are loaded into your host.

To get a list of SQL Server-related assemblies loaded with the SQLPS module, use the following script, which will work in both PowerShell V2 and V3:

Import-Module SQLPS –DisableNameChecking

[appdomain]::CurrentDomain.GetAssemblies() | 
Where {$_.FullName -match "SqlServer" } | 
Select FullName

If you want to run on a strictly V3 environment, you can take advantage of the simplified syntax:

Import-Module SQLPS –DisableNameChecking

[appdomain]::CurrentDomain.GetAssemblies() | 
Where FullName -match "SqlServer" | 
Select FullName

This will show you all the loaded assemblies, including their public key tokens:

More information on running PowerShell scripts

By default, PowerShell is running in restricted mode, in other words, it does not run scripts. To run our scripts from the book, we will set the execution policy to RemoteSigned as follows:

Set-ExecutionPolicy RemoteSigned

Note

See the Execution policy section in Appendix B, PowerShell Primer, for further explanation of different execution policies.

If you save your PowerShell code in a file, you need to ensure it has a .ps1 extension otherwise PowerShell will not run it. Ideally the filename you give your script does not have spaces. You can run this script from the PowerShell console simply by calling the name. For example if you have a script called myscript.ps1 located in the C:\ directory, this is how you would invoke it:

PS C:\> .\myscript.ps1

If the file or path to the file has spaces, then you will need to enclose the full path and file name in single or double quotes, and use the call (&) operator:

PS C:\>&'.\my script.ps1'

If you want to retain the variables and functions included in the script, in memory, thus making them available globally in your session, then you will need to dot source the script. To dot source is literally to prefix the filename, or the path to the file, with a dot and a space:

PS C:\> . .\myscript.ps1
PS C:\> . '.\my script.ps1'

More information on mixed assembly error

You may encounter an error when running some commands that are built using older .NET versions. Interestingly, you may see this while running your script in the PowerShell ISE, but not necessarily in the shell.

Invoke-Sqlcmd: Mixed mode assembly is built against version 'V2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

A few steps are required to solve this issue:

1. Open Windows Explorer.

2. Identify the Windows PowerShell ISE install folder path. You can find this out by going to Start | All Programs | Accessories | Windows | PowerShell, and then right-clicking on the Windows PowerShell ISE menu item and choosing Properties.

   For the 32-bit ISE, this is the default path:

   %windir%\sysWOW64\WindowsPowerShell\v1.0\PowerShell_ISE.exe

   For the 64-bit ISE, this is the default path:

   %windir%\system32\WindowsPowerShell\v1.0\PowerShell_ISE.exe

3. Go to the PowerShell ISE Install folder.

4. Create an empty file called powershell_ise.exe.config.

5. Add the following snippet to the content and save the file:

   <?xml version="1.0" encoding="utf-8" ?>
   <configuration>
   <startup useLegacyV2RuntimeActivationPolicy="true">
   <supportedRuntime version="v4.0" />
   </startup>
   
   <runtime>
   <generatePublisherEvidence enabled="false" />
   </runtime>
   </configuration>

6. Reopen PowerShell ISE and retry the command that failed.