Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

Listing notable PowerShell features


Before you dive deeper into PowerShell, it's worth noting down the features that have been added to PowerShell over the last few versions. They are as follows:

Improvements to the Integrated Scripting Environment (ISE): Many of the improvements such as Intellisense, autocomplete (tab completion), command window, and live syntax checking were introduced in PowerShell V3. Although subtle, the ISE has kept on improving through the versions.

OneGet: PowerShell's OneGet has been added in PowerShell V5 to ease the process of finding, listing, managing, and installing packages from web-based repositories and installing them. If you have worked with *nix machines and environments before, this new feature is similar to apt-get or Red Hat Package Manager(RPM).

Note

In Windows 10, this module has been renamed as PackageManagement. To learn more about this module, visit http://blogs.technet.com/b/packagemanagement/archive/2015/04/29/introducing-packagemanagement-in-windows-10.aspx.

To use OneGet, simply import the module:

Import-Module –Name OneGet

The OneGet feature by default, works with the default provider, PowerShellGallery.

Note

To learn more about the PowerShellGallery, visit https://www.powershellgallery.com/pages/GettingStarted.

It can also work with the Chocolatey repository (https://chocolatey.org/), a community-driven machine package manager. The PowerShell team does mention that support for additional repositories are in their roadmap.

To search for packages that can be installed using OneGet, you can run the following command:

Find-Package –Source Chocolatey

To install a package, MagicDisc, you can use the following command:

Find-Package -Name MagicDisc | Install-Package

PowerShell will also keep you updated on the progress of the installation in a command-line fashion:

To check how many packages are available to be installed via OneGet, you can use the following command:

@(Find-Package).Count

At the time of writing this appendix, there are 2,649 packages available.

With this great power (OneGet), comes great responsibility. Make sure that you understand what this feature does for you, what the security risks are, and evaluate packages before you download and install them.

Note

BoeProx, PowerShell MVP, has a blog post that discusses OneGet in more detail. You can refer to http://learn-powershell.net/2014/04/03/checking-out-oneget-in-powershell-v5/.

PowerShellGet: The PowerShellGet package is very similar to OneGet. The PowerShellGet package is a wrapper around OneGet and provides more PowerShell-specific package management. At the time of writing this book, you can search for modules using Find-Module of the PowerShellGet package and look for the Desired State Configuration(DSC) sources using Find-DscResource of the PowerShellGet package.

PowerShell Classes: While PowerShell has always been object-based, there has been no true support for creating true classes, until now. Starting V5, PowerShell supports the creation of user-defined classes using constructs that you would use in an object-oriented programming language. Here is a very simple example that creates a class with one property and one method:

class CustomClass
{
   [Int] $NumProcessors

CustomClass([Int]$p_NumProcessors)
   {
      $this.NumProcessors = $p_NumProcessors
   }

   #method
   [void] SetNumProcessors([Int] $p_NewNum)
   {
      $this.NumProcessors = $p_NewNum
   }
}

$myClass = [CustomClass]::new(4)

#display
$myClass

#change
$myClass.SetNumProcessors(8)

PowerShell debugging: PowerShell V5 comes with a number of improvements that will help you with debugging:

  • You can now break into the debugger from either the console (Ctrl + B) or from the ISE (Ctrl + B)

  • You can do remote debugging from the ISE

  • You can debug runspaces using runspace-specific cmdlets:

    • Get-Runspace

    • Debug-Runspace

    • Enable-RunspaceDebug

    • Disable-RunspaceDebug

    • Get-RunspaceDebug

Network Switch cmdlets: PowerShell V5 introduces a number of cmdlets that allow you to configure, manage, and support Windows Server 2012 R2 logo-certified network switches. Here is a partial list of Network Switch-related cmdlets in PowerShell V5:

Transcription and logging: The Start-Transcript and Stop-Transcript cmdlets help you record and capture all the commands that you issue in a PowerShell session. These are supported in the console but not in the ISE. In PowerShell V5, these two cmdlets work within the ISE as well.

Archive cmdlets: There is no need for you to go out and look for another tool to help you zip and unzip your files and folders. PowerShell V5 comes with two cmdlets that will help you do this from PowerShell: Compress-Archive and Expand-Archive. Here is a simple example of how you can compress files from a folder:

#code all in one line
Compress-Archive -Path C:\MyFiles\* -DestinationPathC:\Temp\MyFiles.zip

Desired State Configuration: An introduction of Desired State Configuration (DSC) was one of the highlights of PowerShell V4. DSC is defined in MSDN (https://msdn.microsoft.com/en-ca/library/dn249912.aspx) as "a new management platform in Windows PowerShell that enables deploying and managing configuration data for software services and managing the environment in which these services run." DSC has extended PowerShell to allow you to declaratively specify your "desired configurations". To get a list of functions and cmdlets related to DSC, you can use the following command:

Get-Command –Module PSDesiredStateConfiguration

The current list is as follows:

Updateable help: Instead of bundling the help files with the PowerShell installation, which often remains static and easily becomes outdated, PowerShell help files can be downloaded and updated on demand in PowerShell V3. Using the Update-Help cmdlet, the most up-to-date help files will be installed on your system.

Workflows: Introduced in PowerShell V3, PowerShell Workflows (PSWF), as stated in MSDN (http://msdn.microsoft.com/en-us/library/jj134242.aspx), "help automate the distribution, orchestration, and completion of multi-computer tasks, freeing users and administrators to focus on higher-level tasks". PSWF leverages Windows Workflow Foundation 4.0 for the declarative framework but using familiar PowerShell syntax and constructs. Check out the MSDN article on how to get started with Windows PowerShell Workflow.

Robust sessions: In PowerShell V3, sessions can be retained amidst network interruptions. These sessions will remain open until they time out.

Module auto loading: In PowerShell V3, modules can be autoloaded, that is, they don't have to be explicitly loaded. If you use a cmdlet that belongs to a module that hasn't been loaded yet, this will trigger PowerShell to search PSModulePath and load the first module that contains this cmdlet. This is something we can easily test:

#=====================================
#TEST 1
#=====================================
#check current modules in session
Get-Module

#use cmdlet from CimCmdlets module, which
#is not loaded yet
Get-CimInstancewin32_bios

#note new module loaded CimCmdlets
Get-Module

#=====================================
#TEST 2
#=====================================
#use cmdlet from SQLPS module, which
#is not loaded yet
Invoke-Sqlcmd -Query "SELECT GETDATE()" -ServerInstance"localhost"

#note new modules loaded SQLPS and SQLASCmdlets
Get-Module

PSCustomObject: In PowerShell V3, you can create custom objects more cleanly using PSCustomObject. Creating custom objects are quite useful especially when you need to bring together properties from different objects or components. Here is a simple example:

[PSCustomObject] @{
   Database = $db.Name
FileGroup = $fg.Name
FileName = $file.FileName
}

Web service support: PowerShell V3 introduced the Invoke-WebRequest cmdlet that sends HTTP or HTTPS requests to a web service and returns the object-based content that can be easily manipulated in PowerShell. You can download websites using PowerShell (check out Lee Holmes' article on this at http://www.leeholmes.com/blog/2012/03/31/how-to-download-an-entire-wordpress-blog/).

Simplified language syntax: In PowerShell V3, some syntax has been simplified.

What you used to write in V1 and V2 with curly braces and $_ like this:

Get-Service | Where-Object { $_.Status -eq'Running' }

This can now be rewritten in V3 onward as follows:

Get-Service | Where-Object Status -eq'Running'

Note

Read more about the Windows Management Framework V5.0 announcement at http://blogs.technet.com/b/windowsserver/archive/2014/04/03/windows-management-framework-v5-preview.aspx.