VSTSC's Community Server

Community Server: The platform that enables you to build rich, interactive communities.
VSTeamSystemCentral: Your Team System community!

Welcome to VSTSC's Community Server Sign in | Join | Help
in Search

Tim Benninghoff

A SQL Server DBA attempts a personal IT project and documents the (mis)adventures.

Installing SQL Server 2008 PowerShell snap-ins for SQL Server 2005

If your company is like mine, you are largely at the mercy of your software vendors as to when you'll be upgrading to SQL Server 2008.  But, if you're like me, you want to start taking advantage of the features of the SQLPS mini-shell in your regular PowerShell today, particularly with administering your SQL Server 2005 instances.  The good news is, it's available today, and I'm going to show you how to setup it up from information I've pieced together from across the interwebs.

 Now, for a word of caution.  In my particular case, I run PowerShell only on my own desktop, which only has the SQL Server 2005 client tools installed.  I have neither the SQL Server 2008 components nor PowerShell installed on any of my SQL Server machines.  So, again, these instructions assume that you already have PowerShell 1.0 and the SQL Server 2005 client tools installed.

In Buck Woody's Advanced PowerShell for SQL Server presentation at the 2008 PASS Summit,  he told us about being able to use the SQL Server 2008 PowerShell features without having to install SQL Server 2008.  You will, however, need to install some components which are available as part of the Microsoft SQL Server 2008 Feature Pack.  The specific components you will need to install, in order, and which are all available for download from the feature pack page, are:

Microsoft SQL Server System CLR Types
Microsoft Core XML Services (MSXML) 6.0  (Note:  This component is already part of SQL Server 2005, it will likely not need to be installed.  I provide it here for completeness of prerequisites).
Microsoft SQL Server 2008 Native Client  (Note:  I only installed the client.  I did not install the SDK as well.)
Microsoft SQL Server 2008 Management Objects
Microsoft Windows PowerShell Extensions for SQL Server

Once you've installed these components, you could jump right in and fire up SQLPS from a command prompt and start navigating through your SQL Server 2005 instances as a PowerShell drive, but then you'd miss out on using alot of your regular PowerShell cmdlets in combination with the new SQL Server 2008 features.  But, to make these Snap-Ins available in our regular PowerShell shell, we'll need to install them.

At this point, if you were to start up PowerShell and run the cmdlet 'get-PSSnapin -registered', you wouldn't see any results for the SQL Server 2008 snap-ins.  As I found at the PowerShell Scripts page over at Codeplex, you'll need to register the dlls with PowerShell.  In our particular case, you'll need to run the following within PowerShell:

set-alias installutil $env:windir\microsoft.net\framework\v2.0.50727\installutil
installutil -i "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSProvider.dll"
installutil -i "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll"

Now when you run get-PSSnapin -registered, you should see the following:  

Name : SqlServerCmdletSnapin100
PSVersion : 1.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.

Name : SqlServerProviderSnapin100
PSVersion : 1.0
Description : SQL Server Provider

We're not quite done yet. You'll still need to add the Snap-Ins. The SQL Server Perceptions blog has a good explanation of why we have to do this, as well as instructions on how to set it up manually. In our case, to add them manually we would:


Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

 

However, I like to use the method suggested by Buck Woody in his presentation, as discussed on Michiel Wories' Weblog. Taking the code provided on that blog, I create a PowerShell startup script called Initialize-SqlpsEnvironment.ps1 and place it in my PowerShell folder at C:\PowerShell\.  I run this script, first thing in the morning when I startup PowerShell by entering the following command from within PowerShell (as the comments of the code suggest):

Powershell -NoExit -Command "& 'C:\PowerShell\Initialize-SqlPsEnvironment.ps1'"


 

Published Sunday, December 21, 2008 12:51 PM by timbenninghoff
Filed under: ,

Comments

 

Tim Benninghoff said:

A request came in from one of the developers asking if there was a way to script out all of the triggers

January 22, 2009 10:58 AM
 

joetig said:

I am attempting to follow you instructions for install on my Vista64 running sql 2k5. Everything works fine utill I attempt to install the snapins dll.

installutil -i "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll"

I encounter an error and it rolls back. Here is the log file results:

Installing assembly 'C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll'.

Affected parameters are:

  logtoconsole =

  assemblypath = C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll

  i =

  logfile = C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.InstallLog

An exception occurred while trying to find the installers in the C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll assembly.

System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

Aborting installation for C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll.

Rolling back assembly 'C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll'.

Affected parameters are:

  logtoconsole =

  assemblypath = C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll

  i =

  logfile = C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.InstallLog

An exception occurred while trying to find the installers in the C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll assembly.

System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

Aborting installation for C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll.

Thanks

Joe

February 21, 2009 11:24 AM
 

timbenninghoff said:

I ran into similar problem when I was first installing.  For me, I happened to have not installed one of the prerequisite components from the SQL Server 2008 Feature Pack.  In my case I had failed to install Microsoft SQL Server System CLR Types.  You may want to double-check that you have all of the prerequisites installed.

March 3, 2009 12:04 PM
Anonymous comments are disabled
Powered by Community Server (Personal Edition), by Telligent Systems