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'"