What’s new in the SQL Server PowerShell in the SQL Server 2014 CTP1 ?


These days I decided to play around with SQL Server 2014, and of course, one of the first things I went to check was the  PowerShell Host to see if there was any difference in SQL Server 2012.

I remembered a script from a good friend and PowerShell Jedi Shay Levy in which he compares , at the time that PowerShell V3 was to be released, the differences between PowerShell V2 and V3. I used this same script with some simple changes, and you can found the original version in :

How to find out what’s new in PowerShell vNext

 

1 Test : The Version of the host :

SQL Server 2012 : 2.0

PowerShell2012HostVersion

 

SQL Server 2014 CTP1 : 2.0

 PowerShell2014HostVersion

PowerShell version is the same, of PowerShell 2.0

 

2 Test : Compare to check new/removed/changed  cmdlets / parameters (using the changed version of the Shay’s script) :

   1: # run in SQL2012 box, export all core cmdlets, name and parameters

   2: Get-Command -Module SQLPS | Select-Object -Property Name,@{Name='Parameters';Expression={(Get-Command $_).Parameters.Keys}} | Export-Clixml c:\temp\2012SQLPS.xml

   3: Get-Command -Module SQLASCMDLETS | Select-Object -Property Name,@{Name='Parameters';Expression={(Get-Command $_).Parameters.Keys}} | Export-Clixml c:\temp\2012AS.xml

   4:  

   5:  

   6: # run in SQL2014 box, export all core cmdlets, name and parameters

   7: Get-Command -Module SQLPS | Select-Object -Property Name,@{Name='Parameter';Expression={(Get-Command $_).Parameters.Keys}} | Export-Clixml c:\temp\2014SQLPS.xml

   8: Get-Command -Module SQLASCMDLETS | Select-Object -Property Name,@{Name='Parameters';Expression={(Get-Command $_).Parameters.Keys}} | Export-Clixml c:\temp\2014AS.xml

   9:  

  10: #Comparing SQLPS

  11: # run either in V2012 or V2014 console

  12: $V2012 = Import-CliXml C:\TempShare\CompareSQLPS\2012SQLPS.xml | Sort-Object -Property Name

  13: $V2014 = Import-CliXml C:\TempShare\CompareSQLPS\2014SQLPS.xml | Sort-Object -Property Name

  14:  

  15: Compare-Object $V2012 $V2014 -Property Name -IncludeEqual -PassThru | ForEach-Object {

  16:  

  17:     $Command = $_

  18:  

  19:     if($_.SideIndicator -eq ‘==’)

  20:     {

  21:         $Command = $_

  22:  

  23:         $cV2012 = $V2012 | Where-Object {$_.Name -eq $Command.Name} | Select-Object -ExpandProperty Parameters

  24:         $cV2014 = $V2014 | Where-Object {$_.Name -eq $Command.Name} | Select-Object -ExpandProperty Parameters

  25:  

  26:         $compare = Compare-Object $cV2012 $cV2014

  27:  

  28:         if($compare)

  29:         {

  30:             try

  31:             {

  32:                 $NewParameters = $compare | Where-Object {$_.SideIndicator -eq ‘=>’} | ForEach-Object {$_.InputObject + ‘ (+)’}

  33:                 $RemovedParameters = $compare | Where-Object {$_.SideIndicator -eq ‘<=’} | ForEach-Object {$_.InputObject + ‘ (-)’}

  34:  

  35:                 “$($command.Name) (!)”

  36:                 $NewParameters + $RemovedParameters | Sort-Object | ForEach-Object { “`t$_”}

  37:                 “`n”

  38:             }

  39:             catch{}

  40:         }

  41:     }

  42:     elseif($_.SideIndicator -eq ‘=>’)

  43:     {

  44:         “$($Command.name) (+)`n”

  45:     }

  46:     else

  47:     {

  48:         “$($Command.name) (-)`n”

  49:     }

  50: }

  51:  

  52: #Comparing SQLASCMDLETS

  53: # run either in V2012 or V2014 console

  54: $V2012 = Import-CliXml C:\TempShare\CompareSQLPS\2012AS.xml | Sort-Object -Property Name

  55: $V2014 = Import-CliXml C:\TempShare\CompareSQLPS\2014AS.xml | Sort-Object -Property Name

  56:  

  57: Compare-Object $V2012 $V2014 -Property Name -IncludeEqual -PassThru | ForEach-Object {

  58:  

  59:     $Command = $_

  60:  

  61:     if($_.SideIndicator -eq ‘==’)

  62:     {

  63:         $Command = $_

  64:  

  65:         $cV2012 = $V2012 | Where-Object {$_.Name -eq $Command.Name} | Select-Object -ExpandProperty Parameters

  66:         $cV2014 = $V2014 | Where-Object {$_.Name -eq $Command.Name} | Select-Object -ExpandProperty Parameters

  67:  

  68:         $compare = Compare-Object $cV2012 $cV2014

  69:  

  70:         if($compare)

  71:         {

  72:             try

  73:             {

  74:                 $NewParameters = $compare | Where-Object {$_.SideIndicator -eq ‘=>’} | ForEach-Object {$_.InputObject + ‘ (+)’}

  75:                 $RemovedParameters = $compare | Where-Object {$_.SideIndicator -eq ‘<=’} | ForEach-Object {$_.InputObject + ‘ (-)’}

  76:  

  77:                 “$($command.Name) (!)”

  78:                 $NewParameters + $RemovedParameters | Sort-Object | ForEach-Object { “`t$_”}

  79:                 “`n”

  80:             }

  81:             catch{}

  82:         }

  83:     }

  84:     elseif($_.SideIndicator -eq ‘=>’)

  85:     {

  86:         “$($Command.name) (+)`n”

  87:     }

  88:     else

  89:     {

  90:         “$($Command.name) (-)`n”

  91:     }

  92: }

SQLPS module :

Backup-SqlDatabase (!)
    BackupContainer (+)
    SqlCredential (+)

Invoke-Sqlcmd (!)
    IncludeSqlUserErrors (+)

Restore-SqlDatabase (!)
    SqlCredential (+)

Get-SqlCredential (+)

New-SqlCredential (+)

Remove-SqlCredential (+)

Set-SqlCredential (+)

As we can see :

Backup-Sqldatabase was changed, added 2 new parameters : BackupContainer and Sqlcredential

Invoke-Sqlcmd was changed added a new parameter : IncludeSqlUserErrors

Restore-Sqldatabase was changed added a new parameter SqlCredential

Added 4 new cmdlets (to support the new parameter Sqlcredential) : Get-SqlCredential,New-SqlCredential,Remove-SqlCredential and Set-SqlCredential

 

SQLASCmdlets : Nothing changed

 

I did the test directly in the host of the PowerShell in SQL  Server. In my box I am using Windows 2012 R2 Preview, with PowerShell 4.0 .

A cool stuff is that you may have benefits of the new common parameter added at the PowerShell V4.0 , PipelineVariable,  if you import the module SQLPS in the PowerShell host it self.

Shay pointed me this very ultra F Mothe…parameter and Keith is explaining in here :

PowerShell V4 – PipelineVariable Common Parameter

If you do that and want to run your script scheduled in the SQL Agent, just run as cmd and call Powershell 🙂

That is guys. Remembering that is the CTP1 and some things still can be changed.

🙂

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Algo que Esqueci de Categorizar. Bookmark the permalink.

4 Responses to What’s new in the SQL Server PowerShell in the SQL Server 2014 CTP1 ?

  1. @sqlchow says:

    Interesting, wondering if the SqlCredential parameters are added to support azure stuff?

    • Nice question my friend. Unfortunately I still not (ashamed) in the azure world hehehe

      Laerte Junior PASS Regional Mentor | Blog | Twitter | Linkedin | Simple-Talk Author Codeplex – SQLPSX Developer | SQLRockStar

  2. Greg Low says:

    It’s for the backup to url and restore from url options that were added to sql 2012 recently in the sp

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s