Introduction
If you have been working with Microsoft products, or have been managing or developing on the Microsoft platform, you might be familiar with PowerShell. If not, I can predict that you are bound to encounter it sooner than later. Since you are holding this book, my prediction just came true.
PowerShell is Microsoft's automation platform, which includes both a shell (often referred to as console) and a scripting language that allows one to streamline, integrate, and automate multiple tasks and applications. But why learn another technology and language? Why bother? If you have experience as a system administrator pre-PowerShell, you probably know the pain of trying to integrate heterogeneous systems using some kind of scripting.
Windows, at its core, is very Graphical User Interface (GUI)-driven with a lot of point-and-click, but this point-and-click is not so great when you have to do it several times over as you try to tie together systems and automate tasks.
Historically, the scripting solution would have involved a multitude of languages, including VBScript, Jscript, Perl, or Python; a batch file; and even a little bit of C, C++, or C#. System administrators had to be really creative and resourceful—duct taping a solution using a mishmash of languages in the absence of a real solution. It was messy, not flexible, and painful to maintain.
Enter PowerShell! PowerShell allows an administrator, or developer, to do more tasks in a faster, easier, and better way using a scripting language, now understood by many, in the Microsoft family of applications. PowerShell is now the one language you need to know if you want to automate and integrate either, within one application (for example, SQL Server), or between Microsoft and even non-Microsoft applications. Since many Microsoft products such as Windows Server, Exchange, SharePoint, and SQL Server have support for PowerShell, getting one system to talk to another is just a matter of discovering what cmdlets, functions, or modules need to be pulled into the script. The good thing is, even if the product does not have support for PowerShell yet, it most likely has .NET or .com support, which PowerShell can easily use.
PowerShell has become a major player in the automation and integration arena, and it will continue to be, for the foreseeable future.
Working with SQL Server and PowerShell
Before we dive into the recipes, let's go over a few important concepts and terminologies that will help you understand how SQL Server and PowerShell can work together.
Running as an administrator
Most of our recipes will perform possible queries and changes in your SQL Server instance or Windows Server. This will require elevated privileges both on your database side and in the PowerShell side. To ensure you can run the recipes in this book without getting access errors, you will need to execute the console or the ISE as administrator. One way to do this is by right-clicking on the PowerShell icon in your task bar and selecting to run either program as administrator.
You can confirm that you've launched either program as administrator by checking the title bar. You should see Administrator added to your title bar.
Execution Policy
The Execution Policy settings in PowerShell determine what is allowed or not allowed to be run in PowerShell.
Note
See Execution Policy section in, Appendix A, PowerShell Primer, for further explanation of different execution policies.
For security reasons, PowerShell will not run automatically unless it is authorized in the settings. This is to prevent scripts from different sources, for example, the ones downloaded from the Internet, from potentially running malicious or destructive code.
To run the recipes in this book, you will need at least a RemoteSigned setting. To get this, run the following code:
Set-ExecutionPolicy RemoteSigned
Running scripts
If you save your PowerShell code in a file, you need to ensure it has a .ps1 extension. Otherwise, PowerShell will not run it. Unlike traditional scripts, you cannot run a PowerShell script by double clicking the file. Instead, you can run this script from the PowerShell console simply by calling the name. For example, if you have a script called myscript.ps1 located in the C:\Scripts directory, you can provide the full path to the file to invoke it:
PS C:\> C:\Scripts\myscript.ps1
You can also change your directory to where the script is saved, and invoke it like this (notice there is a dot and backslash in front of the file):
PS C:\Scripts> .\myscript.ps1
If the file or path to the file has spaces, then you will need to enclose the full path and file name in single or double quotes. Before PowerShell v3, you would need to use the call (&) operator prior to the script name. From PowerShell v3 onwards, you do not need to specify the call operator anymore, but it will still work if you do:
PS C:\Scripts> & '.\my script.ps1'
If you want to retain the variables and functions included in the script in memory, so that it's available in your session globally, then you will need to dot source the script. Dot source means prepending the filename, or path to the file, with a dot and a space:
PS C:\Scripts> . .\myscript.ps1 PS C:\Scripts> . '.\my script.ps1'
Note
To learn more about how to invoke code and executables in PowerShell, see http://social.technet.microsoft.com/wiki/contents/articles/7703.powershell-running-executables.aspx.
Running different PowerShell versions
If you are running PowerShell v3, v4, or v5, you can choose to run an older version of PowerShell. To do this, simply invoke the shell or start your session with a -Version parameter and provide the version you want to use:
Powershell.exe -Version 2
You can check that the change was made by using the $PSVersionTable variable. You should now see the PSVersion value reverted to the value you provided to the –Version parameter:
Line continuation
Understanding how line continuation works in PowerShell will be crucial when working with the recipes in this book.
You will encounter a line of PowerShell code that may be wider than the width of the page you are reading. For example, consider the following code:
#create your SQL Server SMO instance $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
The preceding code, which creates a SQL Server instance, is meant to be written in a single line (no line breaks), otherwise it will not execute. However, to make the line more readable, some snippets may be broken into multiple lines as long as the line continuation character, a backtick (`), is provided at the end of the line just before the carriage return. Check out the following code:
$server = New-Object `
-TypeName `
Microsoft.SqlServer.Management.Smo.Server `
-ArgumentList $instanceNameAdding the line breaks cleans up the code a little bit and makes it more readable. But do you see the backtick at the end of each line? You probably have to squint to see it. It's probably not obvious that the backtick is the last character before the carriage return.
Note
The backtick (`) character in the U.S. keyboard is the key above the left Tab key, and to the left of the number 1 key. It shares the key with the tilde (~) sign. Check this post for the visual location of the backtick key in localized keyboards: http://superuser.com/questions/254076/how-do-i-type-the-tick-and-backtick-characters-on-windows.
For this book, I will try to avoid backticks for line continuation. Please assume that a long line of code in the recipes, although wrapping in your page, should be written as just a single line. You can also confirm the syntax by downloading the code from the Packt Publishing website.
Where possible, I will break down the code into more readable chunks without using the backtick. For example, consider a long line of code like this:
$server.Databases | Get-Member -MemberType "Property" | Where-Object Definition -Like "*Smo*"
The preceding line can be rewritten into multiple lines using the pipe (|) operator at the end of each line:
$server.Databases | Get-Member -MemberType "Property" | Where-Object Definition -Like "*Smo*"
If I have to use the backtick, I will call your attention to it in the code comments.
PowerShell modules
Modules are a way to extend PowerShell. Modules can add cmdlets and providers, and load functions, variables, aliases and other tools to your session.
For our recipes, we will use the SQLPS module a lot. To load this module, you can use the Import-Module cmdlet:
Import-Module SQLPS
Note that running this command will change your current working directory to:
PS SQLSERVER:>
