SQLPSX & Sql Server 2012

For monitoring of our production servers we rely on Powershell, and consequently SQLPSX.  We recently (last night) deployed our first Sql Server 2012 server into production, and apparently our custom powershell monitoring was less than happy with this.  It was gassing on about ‘Error converting data type varchar to uniqueidentifier’ and such. 

Luckily, the fix was pretty easy.  In the SqlServer module for SQLPSX in the SQLServer.psm1 file, there is a reference to the various version 10 smo libraries:

try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}

try
{
    try {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
    catch {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended" -EA Stop}
}
catch {Write-Warning "SMOExtended not available"}

I simply changed these references (make sure you get all three!) to reference version 11 and voila!  Everything is back to normal. 

try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}

try
{
    try {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
    catch {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended" -EA Stop}
}
catch {Write-Warning "SMOExtended not available"}

I’ve not noticed any ill-effects (as of yet) to modifying these libraries.  I’ve not tried modifying any of the versions in any of the other modules either, but I’m pretty confident that changing them would work fine.  MS is extremely good and keeping their API’s backwards compatible.

Obviously, make sure you have the Sql Server 2012 Shared Management Objects (SMO) installed.  You can download them in the Sql Server 2012 Feature Pack.

As always, change at your own risk.  Worked for me, might not for you.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: