Powershell Snapins with SQL 2012

Published 08 December 11 07:23 PM | MartinBell

Because the only Adventureworks database on codeplex for SQL Server 2012 is a case sensitive database, I thought I would check my change-collation script to see what needed changing. I thought the only issue would be changing SqlServerCmdletSnapin100 to SqlServerCmdletSnapin110 but unfortunately that wasn’t the case Sad smile

Running:

Add-PSSnapin SqlServerCmdletSnapin110

Gave the error message:

Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version 2.

At line:1 char:13

+ Add-PSSnapin <<<< SqlServerCmdletSnapin110

+ CategoryInfo : InvalidArgument:

(SqlServerCmdletSnapin110:String) [Add-PSSnapin], PSArgumentException

+ FullyQualifiedErrorId :

AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

So the snapin hadn’t been registered!  This can be confirmed when nothing is returned using the command:

Add-PSSnapin –Registered SqlServerCmdletSnapin110

So how do you register a snapin? Fortunately example 3 of the help for Add-PSSnapin lays it out for you. I found the dll in:

C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\

So the commands I used were:

set-alias installutil

$env:windir\Microsoft.NET\Framework\v2.0.50727\installutil.exe

installutil "C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll"

installutil "C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll"

get-PSSnapin -registered

add-PSSnapin SQLServer*110

This was great and I got the script running with only one other non SQL 2012 issue that needing fixing is was ready to go.

I wondered why Microsoft had decided not to automatically register the DLL for you? My question was answered by Bob Beauchemin who explained the changes

In SQL Server 2012 the PowerShell components for SQL Server are implemented as a module rather than as a snapin. To install it, just start (vanilla) PowerShell and enter "import-module sqlps" at the prompt (or put it in your profile.ps1) and ignore the warning about unapproved verbs. Or use"import-module sqlps -DisableNameChecking". That's the equivalent of what SQLPS 2012 does by default.

Some of the docs look like they haven't yet been updated, but http://msdn.microsoft.com/en-us/library/cc281962(v=SQL.110).aspx describes the general concepts/changes. You'll also get the new SQLAS modules, and there's some new "subdirectories" for XEvent and SSIS. The sqlps module is in C:\Program Files (x86)\Microsoft SQL Server\110\Tools on a 64-bit install.”

This explains why all my bingoogle searchs for combinations of “snapin” “SQL 2012” “denali” and “pssnapin” turned up nothing!

You can read more about importing the sqlps module here
.
Loading the whole module seems to me a bit of an overkill if you only want the commandlets, but who am I to argue with the new order?

I then decided that it would be a good idea to come up with something to put in my script which determine whether the system required the SQL 2008 snapins or the SQL 2012 module to be loaded. This is what I have come up with:

if ( Get-PSSnapin -Registered | where {$_.name -eq 'SqlServerProviderSnapin100'} )
{
    if( !(Get-PSSnapin | where {$_.name -eq 'SqlServerProviderSnapin100'}))
    { 
        Add-PSSnapin SqlServerProviderSnapin100 | Out-Null
    } ; 
    if( !(Get-PSSnapin | where {$_.name -eq 'SqlServerCmdletSnapin100'}))
    { 
        Add-PSSnapin SqlServerCmdletSnapin100 | Out-Null
    }
}
else
{
    if( !(Get-Module | where {$_.name -eq 'sqlps'}))
    { 
        Import-Module 'sqlps' –DisableNameChecking
    }
}

It assumes SQL 2008 servers will have the SqlServerProviderSnapin100 and SqlServerCmdletSnapin100 registered otherwise it is a SQL 2012 system.

My updated Change-Collation script can be found here

Comments

# Automated Test Restores | Secret SQL said on October 7, 2013 01:01 PM:

Pingback from  Automated Test Restores | Secret SQL

# How To Add Snapins | Information said on October 3, 2016 04:21 AM:

Pingback from  How To Add Snapins | Information

This Blog

SQL Blogs

Syndication