Begin to extend SQL Server 2008 (SQLPS) with PowerShell V2

for the last 1.5 year, in all my “PowerShell and SQL Server” presentations, I have shown some basic samples on how to use both technologies together.  Let me be clear, in SQL Server 2008(including R2), SQLPS.exe is a PS mini-shell built on PS v1 which means that PowerShell V2 additional cmdlets and features wouldn’t be available from within SQLPS.  For this reason you can’t use PowerShell V2 scripts in a SQL Agent job.

Now, looking outside of the box, thanks to SQL Server 2008, you can load the SQL snapins/provider & use PS V2 to extend management on your SQL box.  To load the SQL provider with PowerShell v2, follow the instructions from Michiel Wories Blog: http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx

Please, if you want to understand how this work:

1. Try to build a Virtual machine with the OS you’re currently familiar(Windows XP/Vista/7),

2. Make sure all Windows Update are loaded,

3. Make sure PowerShell is upgraded to V2,

4. Install SQL Server 2008 or the latest R2.

5. Then, use the Michiel blog to copy/paste the SQL provider code to your “Microsoft.PowerShell_profile.ps1”.

Or, you can skip steps 1 –> 3, and use the Michiel blog to copy/paste the SQL provider code to your “Microsoft.PowerShell_profile.ps1” so that, every time you start PowerShell Console, you will have access to your SQL Servers (2000,2005,2008) with the following cmdlet:

“PS> cd SQLServer:\SQL\Servername\default_or_InstanceName\databases

And, use V2 cmdlets:

PS> Send-MailMessage -to "mtrinidad@Company.com" -from "SQL2k8r2@Company.com" -Cc "mtrinidad@Company.com" -subject "SQL Agent Job – Test PowerShell is done!!" -Body "I am done… the system is available." -SmtpServer ‘PostOffice.Company.com’

Look for the location of your user profile folder for “PowerShell”:

Open the PowerShell profile file with your favorite PowerShell Editor: (here’s Microsoft PowerShell ISE)

Here’s what you can expect to see after copy/pasting the SQL Provider code:

Sample of the PowerShell and SQL Server result:

Now, there some things to keep in mind:

1. The Microsoft_PowerShell_Profile.ps1 loads the same script for both 32 and 64bit PowerShell. So you will see a message like to one below to let you know that my 32bit version won’t load my 64bit SQL snapins/provider.

2. Also, when you connect to a SQL Server through the SQL provider, the connection isn’t persistent. So, you need to close/open an new PowerShell session.

3. Keep in mind, this SQL Snapins/provider was meant to use for SQL Servers 2008 moving forward. Due to the different SQL builds, they aren’t backward compatible. But thanks to SMO, for most of the time when connecting to other versions, you will get most of the results back to you.

4. When creating a SQL Agent job adding a PowerShell script to a new step. Make sure to use V1 code. For now, use V2 scripts, you will have to add cmd to call “PowerShell c:\MyScriptHere.ps1” format and run/scheduled the job.

When you start using the combination of SQL Snapins/provider with PowerShell V2 then you are extending your SQL Management skills with PowerShell scripting.

Resource links:

1.Michiel Wories on SQL Snapins/Provider: http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx

2. Buck Woody on SQL Server and PowerShell: http://blogs.msdn.com/buckwoody/

3. About SQLPS scripts: http://blogs.msdn.com/sethus/default.asp

4. Chad Miller Blog: (Check out his CodePlex project on SQL Server extensions): http://chadwickmiller.spaces.live.com/ AND http://sqlpsx.codeplex.com/

Advertisements

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Begin to extend SQL Server 2008 (SQLPS) with PowerShell V2

  1. Hey there! Do you use Twitter? I’d like to follow you if that would be okay. I’m undoubtedly enjoying your blog and look
    forward to new posts.

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