Denali – PowerShell, Surprise! …not too fast. Beside the fact that PowerShell V2 is required before the installation, I was excited to see that thing were moving forward. But wait a second, still is a mini-shell, and it still doesn’t run some of the nice PowerShell V2 cmdlet such as: Send-MailMessage or Out-GridView. This is sad, you got these two useful cmdlets and you can’t use them unless you customize your environments. Well, there’s no escape from it.
Now, taking the lesson learned from Chad Miller’s blog on how to create your SQLPS module (http://sev17.com/2010/07/making-a-sqlps-module/), you can do the same thing with Denali and create a SQLPSV2 module so we can the new cmdlets the CTP1 have included. Please, go ahead and grab the SQLPS module from his Chad’s blog in order to proceed with the following instructions.
Here’s how we create our new SQLPSv2 module?
1. I locate the Denali SQLPS component, which is in the following path: “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn”
2. Under your “Documents\WindowsPowerShell\Module” folder, create the “SQLPSv2” folder.
3. From the “..\Binn” folder we are going to copy the following things:
a. Copy/Paste the “\en” folder to the “SQLPSv2” folder”.b. Copy/Paste these 2 *dll’s files: “Microsoft.SqlServer.Management.PSProvider.dll” & “Microsoft.SqlServer.Management.PSSnapin.dll”.c. Copy/Paste these 2 *ps1xml files: “SQLProvider.Format.ps1xml” & “SQLProvider.Types.ps1xml”.
4. To complete our new SQLPSv2 module, we are going to copy the from Chad’s solution, the following two files but renaming it to SQLPSv2.*: Sqlps.psd1 –> SqlpsV2.psd1 and Sqlps.ps1 –> SqlpsV2.ps1.
At the end, you will have you Module folder looking like the following sample:
| Directory: C:\Users\User01\Documents\WindowsPowerShell\Modules\SQLPSV2 Mode LastWriteTime Length Name —- ————- —— —- d—- 11/10/2010 9:59 PM en -a— 9/25/2010 8:32 PM 92000 Microsoft.SqlServer.Management.PSProvider.dll -a— 9/25/2010 8:32 PM 83808 Microsoft.SqlServer.Management.PSSnapins.dll -a— 9/21/2010 6:14 PM 84619 SQLProvider.Format.ps1xml -a— 9/21/2010 6:14 PM 15552 SQLProvider.Types.ps1xml -a— 7/8/2010 4:38 PM 374 SqlpsV2.ps1 -a— 11/10/2010 10:05 PM 1062 SqlpsV2.psd1 |
Now, let us test if this works, by opening your PowerShell Console or ISE editor, then typing “Import-Module SQLPSv2 –DisableNameChecking”
| PS C:\> PS C:\> Import-module SQLPSv2 -DisableNameChecking PS C:\> PS C:\> GCM *sql* -CommandType Cmdlet | Select nameName —- Add-SqlAvailabilityGroupDatabase Decode-SqlName Encode-SqlName Invoke-Sqlcmd Join-SqlAvailabilityGroup New-SqlAvailabilityGroup New-SqlAvailabilityReplica Remove-SqlAvailabilityGroup Resume-SqlAvailabilityGroupDatabase Suspend-SqlAvailabilityGroupDatabase Switch-SqlAvailabilityGroup PS C:\> cd SQLServer:\SQL\MAX-PCWIN1\MSQLDENALI01\Databases
PS SQLSERVER:\SQL\MAX-PCWIN1\MSQLDENALI01\Databases> |
If you want to download my solution, just click the link below:
Stay tuned for more on SQL Server Denali CTP1 and SQL PowerShell.
Happy PowerShelling!!