If you’re working with PowerShell and SQL Server one of things you’ll want to to do is load the SQL Server 2008 provider and cmdlets into a regular PowerShell. Michiel Wories, the creator of SMO and sqlps, provides an initialization script in his blog post SQL Server PowerShell is Here! The script will load SQL Server provider, cmdlets, required assemblies and set global variables expected by the SQL Server provider.

Creating the sqlps module

In PowerShell version 2, modules provide an alternative approach to initialization scripts used in PowerShell V1. To turn Michiel’s initialization script into a module simply create a folder called sqlps under \Documents\WindowsPowerShell\Modules and save the script as sqlps.psm1 instead of Initialize-SqlpsEnvironment.ps1.

You can then execute:

import-module sqlps

You’ll notice the following warning:

WARNING: Some imported command names include unapproved verbs which might make them less discoverable. Use the Verbose parameter for more detail or type Get-Verb to see the list of approved verbs.

Running get-command -Module sqlps, you’ll notice the Encode-SqlName and Decode-SqlName cmdlets and since neither is an approved verb – hence the warning. To avoid the warning message when loading your new sqlps module use

import-module sqlps –DisableNameChecking

An Alternative Approach

Rather than turning the original initialization script into a module, we could create a more structured implementation making use of a PowerShell manifest file (psd1) file. Using this approach we’ll need to copy the following snapins related files/folders from C:\Program Files\Microsoft SQL Server\100\Tools\Binn to  Documents\WindowsPowerShell\Modules\sqlps folder.

• en
• Microsoft.SqlServer.Management.PSProvider.dll
• Microsoft.SqlServer.Management.PSSnapins.dll
• SQLProvider.Format.ps1xml
• SQLProvider.Types.ps1xml

Next we’ll create a sqlp.psd1 manifest which contains the instructions for processing our new module:

@{
ModuleVersion="0.0.0.1"
Description="A Wrapper for Microsoft's SQL Server PowerShell Extensions Snapins"
Author="Chad Miller"
Copyright="© 2010, Chad Miller, released under the Ms-PL"
CompanyName="http://sev17.com"
CLRVersion="2.0"
FormatsToProcess="SQLProvider.Format.ps1xml"
NestedModules="Microsoft.SqlServer.Management.PSSnapins.dll","Microsoft.SqlServer.Management.PSProvider.dll"
RequiredAssemblies="Microsoft.SqlServer.Smo","Microsoft.SqlServer.Dmf","Microsoft.SqlServer.SqlWmiManagement","Microsoft.SqlServer.ConnectionInfo","Microsoft.SqlServer.SmoExtended","Microsoft.SqlServer.Management.RegisteredServers","Microsoft.SqlServer.Management.Sdk.Sfc","Microsoft.SqlServer.SqlEnum","Microsoft.SqlServer.RegSvrEnum","Microsoft.SqlServer.WmiEnum","Microsoft.SqlServer.ServiceBrokerEnum","Microsoft.SqlServer.ConnectionInfoExtended","Microsoft.SqlServer.Management.Collector","Microsoft.SqlServer.Management.CollectorEnum"
TypesToProcess="SQLProvider.Types.ps1xml"
ScriptsToProcess="Sqlps.ps1"
}

Notice a sqlps.ps1 script file is referenced which  is used to set the variables needed by the provider:

Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

Since the sqlps licensing terms from Microsoft allow redistribution as long as the original installer is included, I’ve included a sqlps module zip file.

Notes

1. SQL Server Management Studio is not required to run sqlps or the sqlps module demonstrated in this post as long as the required assemblies are installed
2. Like sqlps, Microsoft SQL Server 2008 Management Objects and Microsoft Core XML Services (MSXML) 6.0 are required.
3. If you’d also like to also run sqlps host without install SQL Server Management Studio download the installation from the SQL Server 2008 or R2 Feature Pack.