SQLServer and PowerShell a perfect companion!

Finally, after three years, I’ve seen a lot more DBA’s blogging about their solutions combining T-SQL and PowerShell.  KUDOS To Everyone!!!  One thing PowerShell has bring to the table is the ease of getting things done.  Still,  there’s a learning curve everyone has to overcome.  The truth is, the more you use it, the harder is to let go.  So, here’s some things that might help the DBA’s:

1. Be careful with PowerShell ISE.  It does have a bug that you won’t notice until you may try to do a copy/paste of your code into a different editor or into your PowerShell Console.  And, this it due to the editor saving  as a “UNICODE” and not ASCII.  You will seen your lines of code in one single line when viewing the script on a different editor, such as notepad .  Again, just be careful.

image

By the way, this bug has been document in Microsoft Connect and there’s a few blogs about a work around: http://www.nivot.org/2010/05/21/PowerShellISEHackingChangeDefaultSaveEncodingToASCII.aspx  http://dougfinke.com/blog/index.php/2009/07/20/how-to-work-around-a-powershell-integrated-scripting-environment-bug/

2. Do you know? If you don’t want to install SQL Engine but want to take advantage to use PowerShell and manage SQL Server, then just install “SQL Server Management Studio 2008 R2” and you can use SQLPS.   Yes! Why adding unnecessary processes to consume your system resources.   Here’s the link: http://www.microsoft.com/downloads/en/details.aspx?familyid=56AD557C-03E6-4369-9C1D-E81B33D8026B&displaylang=en

3. SQL Server 2008 R2 may not have lots of commands.  But, try to use Invoke-SQLCmd,  this is a very powerful cmdlet specially when you learn to use it to extract data using T-SQL and Stored-Procedures that generate results.  Keep in mind, you may export the results to different file format.    Check out this example:

## Make sure SQLPS is loaded in your session$s = Invoke-SQLCmd -database master -Query ‘exec sp_who’

$s | Select spid, status, loginame, cmd, blk | out-gridview

image

This is a good sample of collecting SQL Servers information into a .NET variable and displaying the results using “Out-GridView”.  Also, you could have use “Out-file” command and save it in your drive.  PowerShell give you lots of options to handle information.  Think out side of the box!

4. Don’t be afraid to learning new technologies, specially .NET Framework.  You are probably getting into it if you’re working building SSIS packages.  PowerShell syntax looks very similar to C# and you can extend scripting with .NET Framework.   A good example would be if you want to add to send an email from your script.   The issue is, you can’t run any of the new PowerShell V2 commands under SQLPS and/or SQL Agent.  You will need to use straight .NET code to by pass this limitation.  SQLPS is PowerShell V1 mini-shell (more info:http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!842.entry ).  

Example:

## – This is the .NET Framework way to create a send email step and can be
##use for both PowerShell V1/V2
## Send email with monitoring results
$mailmess = new-object system.net.mail.mailmessage
$mailmess.From = “FromUser@Company.com”
$mailmess.To.Add(“ToUser@Company.com”)
$mailmess.Subject = “Type Any Subject here…”
$mailmess.Body = “Type Any Detail here…”;
$mailmess.Attachments.Add($Filename)
$mailclient = New-Object System.Net.Mail.SmtpClient
$mailclient.host = “Your.SMTP.PostOffice”
$mailclient.send($mailmess)

## – This is the PowerShell V2 cmdlet to send email. ( will not run under SQLPS.exe
Send-MailMessage -from “FromUser@Company.com” -to “ToUser@Company.com”  `
    -Cc “CCtoUser@Company.com” `
    -subject “Type Any Subject here…” `
    -Body “Type Any Detail here…” `
    -SmtpServer ‘Your.SMTP.PostOffice’
## Now, If you are using SQLPS.exe (only) then you need to use the .NET Framework.?
## This applies when running the SQLPS.exe console and/or writting the code in the
## SQL Server Agent PowerShell jobstep.

## You can only run PowerShell V2 scripts in the SQL Server Agent if you include to
## execute PowerShell.exe with the script file.  This way the script is running
## under PowerShell v2 process.
##
##  sample for Job Step:
##  PowerShell -nologo ‘C:\Developer\PSscripts\PSSQLMonitoring_Summary.ps1’
##

There’s more but I think I will leave some for later.  For those in the Florida area, I will seen you soon at the next SQLSaturday #49 in Orlando and at the SW Florida .NET Developers Group in Fort Myers.

Happy PowerShelling!!