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 TypesMicrosoft 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 ObjectsMicrosoft 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:

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):

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.