Max - Put It Together

With the introduction of SQL Server 2008, Microsoft SQL Server Team introduce SQLPS.exe.   SQLPS.EXE mini-shell is based on PowerShell v 1.0 and includes the SQL Server provider and snapins.  Also, SQL Server 2008 setup will install the SMO (SQLServer Management objects) for you.

What is SQLPS?

The sqlps utility starts the PowerShell environment (PowerShell.exe) with the SQL Server PowerShell snap-ins loaded and registered. The SQL Server PowerShell snap-ins are as follows:

• Microsoft.SqlServer.Management.PSProvider.dll
  Implements the SQL Server PowerShell provider and associated cmdlets such as Encode-SqlName and Decode-SqlName.
• Microsoft.SqlServer.Management.PSSnapin.dll
  Implements the Invoke-Sqlcmd cmdlet.

You can use sqlps to do the following:

• Interactively run PowerShell commands.
• Run PowerShell script files.
• Run SQL Server cmdlets. Only three:
• Encode-SqlName
• Decode-SqlName
• Invoke-SqlCmd

Use the SQL Server provider paths to navigate through the hierarchy of SQL Server objects.

Back to PowerShell SQL jobs:

There’s at least two ways to create a PowerShell job in SQL Server 2008 when creating a Job step:

1. Create new jobstep as a job type “Operating System (CMDExec)” then type the DOS Commands or Cmd batch file.

2. Or, create the jobstep as a job type “PowerShell” then type the PowerShell Cmdlets or PS

Script: (sample)

Both methods will result in generating a simple text report:

Here’s a list of PowerShell.exe parameters you can use at the DOS command line:

The Benefit and the Drawback:

1. The Benefit: Using the “Operating System (CmdExec)”, you can extend your PowerShell v2 by adding the SQL Provider and Snapin into your PowerShell user profile. By Default, the user profile is loaded automatically when executing PowerShell from the DOS command prompt.

2. The Drawback: When, you add your code in a PowerShell job type, SQL Job Agent uses the SQLPS.exe subsystem which is based on PowerShell v1. So, you won’t be able to use PowerShell new cmdlets, such as “Send-MailMessage”. But, you can build your own .NET code to build your own email solution in PowerShell.  Or, just look for the code on the internet…  Well, here’s an example of building an email .NET solution for PowerShell v1:

[System.Reflection.Assembly]::LoadWithPartialName("System.Net.MAIL")
$mailmess = new-object system.net.mail.mailmessage
$mailmess.From = "PowerShell@YourServer.com"
$mailmess.To.Add("Somebody@YourServer.com")
$mailmess.Cc.Add("maxt@putittogether.net")
$mailmess.Subject = "Test send message .NET mail."
$mailmess.Body = "Should work!!!!!!!!!";
$mailclient = New-Object System.Net.Mail.SmtpClient
$mailclient.host = "PostOffice.YourServer.com"
$mailclient.send($mailmess)

Finally, after testing the PowerShell scripts, now we are ready to scheduling the job or mix with other jobs.

Go ahead and keep working with PowerShell!!

It Really ROCKS!!

SQLPS.exe (PowerShell mini-shell) means business.  You want a quick way to get information about your SQL Server logins, then here it is:

   1: cd SQLServer:\SQL\YourSQLServer_Name\Default_or_YourSQLinstance_Name\Logins

   2: dir

TWO LINES…. is that GREAT!!  

The more you discover how productive PowerShell can be, then you go crazy and want to learn more.  Hey!!  Learning to use SMO with PowerShell add more ammunition to your scripting arsenal.

Here’s a screenshot showing the logins results:

Now, to keep growing these two liner… you could add the list of SQL Server and do a “Foreach” cmdlet to build a nice report.

This is just a starting point to start using SQLPS and extending it with PowerShell v2.

Enjoy!!

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/

Here’s another few PowerShell short script to be executed under SQLPS.exe and/or if you already have the SQL Server provider loading on your WindowsPowerShell folder under your user profile. Look for the script “Microsoft.PowerShell_profile.ps1”.

Requirement to run script:

1. 1. Must have SQL Server 2008 in order the use the SQL Server provider.
2. 2. You could run this script in SQLPS.exe
3. 3. Optional-> load your SQL provider in your PowerShell V2 profile. Here's how: http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx (Add this code in your WindowsPowerShell profile)
4. 4. Results will be display on screen but you could add code to save result to a file. (" | Out-File ...")

This script will list all my SQL Agent jobs on one server or more servers. Remember you can expand this script to do more. This is a teaser code:

   1: ## Sample Listing SQL Agent jobs results

   2: cd SQLSERVER:\SQL\YourServername\YourDefaultOrInstanceName\jobserver\jobs\

   3: $SQLjob = $null ; $jobHist = $null ; $jobName = $null

   4: $SQLjob = dir

   5:  

   6: foreach($job in $SQLjob){

   7:     $jobHist += $job | select Parent, name, lastRunDate, lastRunOutcome | ft -auto

   8:     foreach($jobName in $job){

   9:         $jobFolder = "$jobName" + '.0\Jobsteps'

  10:         cd $jobFolder

  11:         $jobHist += dir | select SubSystem, Parent, name, lastRunDate, lastRunOutcome | ft -auto

  12:     }

  13:     cd SQLSERVER:\SQL\YourServername\YourDefaultOrInstanceName\jobserver\jobs\

  14: }

  15: cd\

Sample results:

More hints: (only if the SQL Provider is loaded)

1. Include the Instance-name or use “Default”.

2. With the right permission use cmdlet: cd SqlServer:\SQL\ Your-SQLServerName to move between SQL servers from one PowerShell console session.

3. This script will run under Windows Authentication.

4. MOST Important… While using both SQL Provider and/or SQLPS.exe you need to close/reopen the PowerShell session to refresh the information.

I always was curious about Windows Home Server (WHS) and during a Microsoft LiveMeeting on WHS with Jonas Svensson talked about wanted to see some  PowerShell in WHS.  Well, here’s a start.

I’m showing my Windows 7 (64bit) using Windows Virtual PC. I have been able to build my virtual network with Windows 2008 STD SP1(32bit), and Windows Home Server.

Installing PowerShell on the Windows Home Server:

What’s needed?

1. Make sure that WHS has all the Windows Updates (including the SP2)

2. Install the WMF (Windows Management Framework) for Windows Server 2003:

(Windows Management Framework Core (WinRM 2.0 and Windows PowerShell 2.0))

http://support.microsoft.com/kb/968929

What to do after PowerShell is installed?

1. If you’re creating a Windows 7 virtual machine, *install “Integration Features”.

2. Open the PowerShell Console.

3. Type “Enable-PSRemoting”, answer with a “Y”. This will setup your PowerShell remoting features to allow connectivity to other machines to run admin and/or other task oriented scripts.

4. Install WMF (Windows Management Framework) on all other non-Windows 7 computer and repeat step #3. (don’t forget to pick the correct installation package for your Windows version)

*Note: Few things about installing the Windows Virtual PC “Integration Features”:

1. It only support Windows 7, Vista, and XP.

2. It will BSD will happened on Windows 2008 SP1 virtual machine.

3. Installation will complete in Windows Home Server (it did work for me).

Where’s PowerShell Installed??

You will find PowerShell under “Start | All Programs | Accessories | Windows PowerShell”

What’s included with PowerShell?

1. The PowerShell Console prompt.

2. The free PowerShell editor or “PowerShell ISE (Integrated-Scripting-Environment)”.

PowerShell come with a very extensive documentation at your fingertips. Use “Help” in the PowerShell command prompt:

a. Try typing: Help Get-service –detailed

b. Or just type: Help

Welcome to PowerShell!!

Expanding your knowledge… Want to learn more about PowerShell?

Here’s some links:

1. http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx

2. http://edge.technet.com/tags/PSV2/

3. http://channel9.msdn.com/Search/?Term=PowerShell

4. http://powershellcommunity.org/Scripts.aspx

5. http://powershell.com/cs/

6. http://www.sapien.com/

7. http://www.powergui.org/index.jspa

Please, check the internet for more PowerShell information….  the truth is out there!!