Book Image

PowerShell for SQL Server Essentials

By : Donabel Santos
Book Image

PowerShell for SQL Server Essentials

By: Donabel Santos

Overview of this book

Table of Contents (15 chapters)
PowerShell for SQL Server Essentials
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Implementing Reusability with Functions and Modules
Index

The PowerShell environment


There are two environments that come with PowerShell when you install it: the PowerShell console and the PowerShell Integrated Scripting Environment (ISE). These environments have improved a lot since the first version and should be more than sufficient for you to start working with PowerShell. If you prefer a different environment, there are other PowerShell editors out there. Some editors are free and some commercial. Some vendors that provide PowerShell editors are Idera (PowerShell Plus), Dell (PowerGUI), and SAPIEN Technologies (PowerShell Studio 2014).

Note

This book uses the current released version at the time of writing, which is PowerShell v4. The screenshots you will see in this book reflect the screens in PowerShell v4.

In a 64-bit system, PowerShell will come in two flavors: 32 bit and 64 bit. The 32-bit version has the label suffix (x86). Note that 64-bit add-ons and snap-ins for PowerShell will only load in the 64-bit console or ISE. The following screenshot shows the result of searching PowerShell in Windows:

The PowerShell console

The PowerShell console is very similar to the Command Prompt. By default, the interface is blue, compared to the usual black of the Command Prompt:

The PowerShell console is great for administrators and IT professionals who prefer to work on a purely command-line environment. The console is also great for running predefined scripts either manually or through a job via the Windows task scheduler or SQL Server Agent.

The PowerShell ISE

A standard installation of PowerShell also comes with an Integrated Scripting Environment (ISE). The PowerShell ISE is a more Graphical User Interface (GUI) way of working with PowerShell and comes with a few handy features, including IntelliSense and syntax help, as shown in the following screenshot:

Some of the compelling features that the ISE has are listed as follows:

  • The script editor and PowerShell console in a single environment

  • The autocomplete and on-hover usage/syntax guide

  • A command pane that allows you to visually fill in parameters and transfer the syntax over to your editor

  • Multiple tabs that allows the opening of multiple scripts at the same time

  • A zoom slider, which is great for presentations or just basic readability

We will use the PowerShell ISE for most examples in this book.

Running PowerShell as an administrator

Most of the time, you will use PowerShell to perform administrative tasks, so you will need to run it as an administrator. You can do this by right-clicking on the application (console or ISE) and clicking on Run as administrator.

You will know you've successfully run the application as the administrator by looking at the title bar. It should show Administrator: Windows PowerShell:

If you do not run your PowerShell environment as the administrator, you might not have sufficient permission to run some of your commands or scripts. You will most likely get Access Denied errors.

A useful trick to identify whether you are running the shell as the administrator is to change the appearance of the shell based on the elevation status of the session. This can be accomplished by adding a snippet of code to your profile that checks whether the session is run by an administrator and then changing some properties accordingly.

First you need to check whether your profile exists. You can check the path to your profile by typing the following command:

$profile

If this file doesn't exist, you can simply create it by typing the following:

New-Item -ItemType File $profile -Force

The $profile command is equivalent to $profile.CurrentUserCurrentHost, which means the settings you provided will only work on the current host. Note that your console and ISE will each have its own profile, so you may need to create one for each. The values you can specify with the profile are AllUUsersAllHosts, AllUsersCurrentHost, CurrentUserAllHosts, and CurrentUserCurrentHost.

Here is a simple snippet you can add to your profile that changes the background and foreground color of your shell if you running the shell as an administrator:

if ($host.UI.RawUI.WindowTitle -match "Administrator")
{
   $host.UI.RawUI.BackgroundColor = "DarkRed"
   $host.UI.RawUI.ForegroundColor = "White"
}

The execution policy

At the risk of sounding like a dictionary, I will define execution policy as the policy applied to determine whether a script can be executed by PowerShell. Execution policies do not make the scripts more secure. They simply lay the ground rules before a script is executed.

The available execution policies are provided in the following table:

Policy

Runs a command?

Runs a local script?

Runs a remote script?

Restricted

Yes

No

No

AllSigned

Yes

Must be signed

Must be signed

RemoteSigned

Yes

Yes

Must be signed

Unrestricted

Yes

Yes

Yes—prompts before running downloaded scripts

Bypass

Yes

Yes

Yes—no warnings or prompts

The default execution policy depends on the operating system you are using. For Windows 8, Windows Server 2012, and Windows 8.1, the default policy is Restricted. For Windows Server 2012 R2, it is RemoteSigned.

Should you need to sign your scripts, you can refer to Scott Hanselman's blog post available at http://www.hanselman.com/blog/SigningPowerShellScripts.aspx. Although this was written a few years ago, the content is still relevant. Patrick Fegan from Risual also has a good, more recent tutorial on self-signing PowerShell scripts at http://consulting.risualblogs.com/blog/2013/09/20/signing-powershell-scripts/.

Note

To get more information about execution policies, including risks and suggestions on how to manage them, you can type Get-Help about_Execution_Policies in the command-line window, or you can visit the TechNet page at http://technet.microsoft.com/en-us/library/hh847748.aspx for more detailed descriptions.

If you want to check which execution policy you are running on, you can use the following command:

Get-ExecutionPolicy

If you want to change it, use the following command:

Set-ExecutionPolicy

The following is a screenshot of what you can expect when you run these two cmdlets:

It would be good to read more on execution policies, evaluate the risks that come with the different settings, and evaluate your needs before deciding which setting you should use.