SQL Server 2014 with Powershell v5 Cookbook

Chapter 1. Getting Started with SQL Server and PowerShell

In this chapter, we will cover:

Working with the sample code
Installing SQL Server using PowerShell
Installing SQL Server Management Objects
Loading SMO assemblies
Exploring the SQL Server PowerShell hierarchy
Discovering SQL-related cmdlets and modules
Creating a SQL Server Instance Object
Exploring SMO Server Objects

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 $instanceName

Adding 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:>

Working with the sample code

This recipe simply walks you through how you can work with the scripts in this book. Samples in this book have been created and tested against SQL Server 2014 on Windows Server 2012 R2.

How to do it...

If you want to use your current machine without creating a separate VM, as illustrated in Create a SQL Server VM section in Appendix B, follow these steps to prepare your machine:

Install SQL Server 2014 on your current operating system—either Windows 7 or Windows Server 2012 R2. A list of supported operating systems for SQL Server 2014 is available at http://msdn.microsoft.com/en-us/library/ms143506.aspx.
Microsoft provides really good documentation (both MSDN and TechNet) on how to install SQL Server, and the different ways you can install SQL Server. I encourage you to read the installation tutorial, as well as the installation notes that come with your software (https://msdn.microsoft.com/en-us/library/ms143219.aspx)
Install PowerShell v5. At the time of writing this book, only the Windows Management Framework (WMF) 5.0 Production Preview was available. You can download it from http://www.microsoft.com/en-us/download/details.aspx?id=48729. The installation instructions are also bundled in the download. At the time of writing this book, WMF 5.0 Production Preview can be installed on Windows 7 SP1, Windows 8.1 Pro/Enterprise, Windows Server 2008 R2, Windows Server 2012, and Windows Server 2012 R2.
By the time this book is in your hands, PowerShell v5 might be available and be bundled in the newer Microsoft operating systems. This download includes, as stated in the download page, updates to Windows PowerShell, Desired State Configuration (DSC) of Windows PowerShell and Windows PowerShell ISE. It also includes Package Management and Network Switch cmdlets.
If you are planning to use the console, set the execution policy to RemoteSigned in the console. This setting will enable us to run the scripts presented in this book.
1. Right-click on Windows PowerShell on your taskbar and choose Run as Administrator.
2. Set execution policy to RemoteSigned by executing the following on the console:
```
Set-ExecutionPolicy RemoteSigned
```
If you are planning to use the PowerShell Integrated Scripting Environment (ISE), set the execution policy to RemoteSigned. We will be using the improved ISE in many samples in this book.
1. Right-click on Windows PowerShell on your taskbar and choose Run ISE as Administrator.
2. Set the execution policy to RemoteSigned by executing the following on the script editor:
```
Set-ExecutionPolicy RemoteSigned
```

Installing SQL Server using PowerShell

If you're really eager to dive into PowerShell and start installing SQL Server, this recipe will give you a taste of installing SQL Server with PowerShell using the SQL Server setup.exe file and a configuration file.

Getting ready

Get your SQL Server binaries ready. If you have it burned on a DVD, place your copy in the DVD drive. If you have it as an ISO or image file, mount the files now.

You will also need to identify the service accounts you want to use for the SQL Server services you want to install, as well as the locations for all the files that SQL Server will save on your system. In order to perform a completely automated install, the following script will need to be adjusted to use the default service account credentials, or specify the usernames and passwords within the $command variable.

In this exercise, we will generate a configuration (.ini) first, and then use this for the installation.

How to do it...

The steps to install a standalone SQL Server instance are as follows:

Generate the configuration file using the following steps:
1. Load your SQL Server install disk or image and launch the setup.exe file.
2. Go through the Wizard and enter all the configuration values.
3. Once you get to the Ready to Install screen, note the Configuration file path:
4. Cancel the installation using the Wizard.
Change the configuration file to enable a silent install. Open the .ini file and make the following changes:
1. Change the QUIET setting to True:
```
QUIET="True"
```
2. Comment out the UIMODE setting by putting a semicolon before it:
```
;UIMODE="Normal"
```
3. Add the IAcceptSQLServerLicenseTerms value:
```
IAcceptSQLServerLicenseTerms="True"
```
Save your .ini file.
Run your PowerShell ISE as administrator.

Add the following code to your script editor:

#change this to the location of your configuration file
$configfile = "C:\Configurations\SQL_ConfigurationFile.ini"

#we are still using the setup.exe that comes with
#the SQL Server bits
#adjust the path below to where your setup.exe is
$command = "D:\setup.exe /ConfigurationFile=$($configfile)"

#run the command
Invoke-Expression -Command $command

Change the location of the $configfile variable to the location where you saved your .ini file. Change the location of the executable as well. In the preceding script, the executable is in the D:\ directory.
Execute the code.

How it works...

SQL Server can be installed different ways:

Using Wizard: You may choose to install SQL Server using the wizard-driven GUI approach, which starts by double-clicking on the setup.exe file that comes with your SQL Server binary (https://technet.microsoft.com/en-us/library/ms143219.aspx).
Via command prompt: You can also install SQL Server using the command prompt by invoking setup.exe from the command prompt, and providing all the configuration values in the proper setup parameters (https://technet.microsoft.com/en-us/library/ms144259.aspx).
Via configuration file: You can install SQL Server still by using the setup executable, but instead of providing all the values in the command prompt, use a configuration file that will host all the configuration values. Visit https://technet.microsoft.com/en-us/library/dd239405.aspx for more information.
Via SysPrep: Install SQL Server using SysPrep. It is Microsoft's system preparation tool that allows administrators to deploy an image to multiple servers and/or workstations. Visit https://technet.microsoft.com/en-us/library/ee210664.aspx for more information.

In the recipe, we went with the third option and installed SQL Server using a configuration file. We are simply going to wrap a few components in PowerShell. You might be asking, "Why not script the whole process in PowerShell instead of using the executable and configuration file?" The answer is, we can do so, and there may be cases where that's the best approach. However, for simple and straightforward installations, it will be easiest to reuse as much of SQL Server's robust, tried-and-true installation process and wrap it inside PowerShell.

The SQL Server configuration file, which has the .ini extension, is a text file that contains installation parameter key-value pairs based on your entries and selections within the wizard. The format you will find in the file looks like this:

;comment or description
PARAMETERNAME = "value"

Some of the common parameters that will be specified in the configuration file include the following:

Parameter	Description
`ACTION`	This is required to start the installation. It accepts only a single value of `Install`.
`IACCEPTSQLSERVERLICENSETERMS`	This is required for unattended installations, and it accepts End User License Agreement.
`UPDATEENABLED`	This specifies whether the installation should discover and include product updates, and it accepts `True` or `False`.
`FEATURES`	This specifies components to install, for example, `SQLENGINE`, `AS`, `RS`, `IS`, `SSMS`, or `REPLICATION`.
`INSTANCENAME`	This is a SQL Server instance name.
`AGTSVCACCOUNT`	This is a SQL Agent service account.
`AGTSVCSTARTUPTYPE`	This is a SQL Agent startup type, and it accepts any of the following values: `Automatic`, `Manual`, or `Disabled`.
`SQLCOLLATION`	This is the SQL Server instance collation.
`SQLSVCACCOUNT`	This is the SQL Server database engine service account.
`SQLSYSADMINACCOUNTS`	These are the SQL Server system admin accounts.
`TCPENABLED`	This specifies whether an instance has TCP enabled.

The list of supported settings is outlined at https://technet.microsoft.com/en-us/library/ms144259.aspx.

You can create the .ini file from scratch, but it would be best to at least start with the configuration file you get with the wizard. From here, you can adjust and provide additional settings.

Once we've finalized the .ini file, the next step is to compose the actual command that needs to be executed. In the following code, we are simply creating a string that contains the path to the setup.exe and passing in a single parameter for the ConfigurationFile:

$command = "D:\setup.exe /ConfigurationFile=$($configfile)"

Alternatively, you can also dynamically build the contents .ini file using PowerShell and then pass this configuration file to setup.exe, just like how we built $command previously.

Once the command string is ready, we can use the Invoke-Expression PowerShell cmdlet to run the expression contained by the $command variable:

Invoke-Expression -Command $command

Instead of using the .ini file, you can also dynamically build all the parameters in a long string based on specific conditions or cases. You can take advantage of PowerShell's logic operators and other constructs when you do this. You should be able to compose the complete command and use Invoke-Expression to perform the actual installation:

$command = 'D:\setup.exe /ACTION=Install /Q /INSTANCENAME="SQL01"
/IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQLENGINE,REPLICATION SQLSYSADMINACCOUNTS="QUERYWORKS\Administrator"'

There's more...

You can also take advantage of Desired State Configuration (DSC), which was introduced in PowerShell v4 and works with Windows Server 2012 R2, to install SQL Server.

DSC is a set of language extensions that will allow you to specify a desired state, or a set of ideal configurations, for your servers. This simplifies the configuration of new SQL Server instances, because all you have to do is to identify the desired state for your SQL Server installations and reuse the script for every deployment.

These are the simplified steps to take advantage of DSC:

Write a configuration script.
Run the configuration script to create a Management Object Framework (MOF).
Copy the MOF to the server you're installing SQL Server to. After the installation, at some point, you will want your server to pull the updated MOF automatically.
Apply the configuration to the target server and start the installation process.

The PowerShell team made the xSqlPs PowerShell module available, which is currently an experimental module, in the Technet Script Center (https://gallery.technet.microsoft.com/scriptcenter/xSqlps-PowerShell-Module-aed9426c). Here is a description of the xSqlPs module from the site:

The xSqlPs module is a part of the Windows PowerShell Desired State Configuration resource kit, which is a collection of DSC resources produced by the PowerShell team. This module contains the xSqlServerInstall, xSqlHAService, xSqlHAEndpoint, xSqlHAGroup, and xWaitForSqlHAGroup resources.

To install SQL Server, you will need to work with xSqlServerInstall. The PowerShell team has provided an excellent tutorial on how to use this DSC resource. This is a good starting script for a SQL Server Enterprise installation, and you can adjust it as needed. By the time this book is in your hands, the scripts in the module may have already been updated, or moved from an experimental to stable state. Please note that these scripts are also provided as is, with no support or warranty from Microsoft.

Note

If you are looking for a good tutorial on DSC, check out the Microsoft Virtual Academy site (http://www.microsoftvirtualacademy.com/liveevents/getting-started-with-powershell-desired-state-configuration-dsc).

Installing SQL Server Management Objects

SQL Server Management Objects (SMO) was introduced with SQL Server 2005 to allow SQL Server to be accessed and managed programmatically. SMO can be used in any .NET language, including C#, VB.NET, and PowerShell. Since SQL Server does not ship with many cmdlets, SMO is the key to automating most SQL Server tasks. SMO is also backwards compatible with previous versions of SQL Server, extending support all the way to SQL Server 2000.

SMO comprises two distinct classes: the Instance classes and the Utility classes.

The Instance classes are the SQL Server objects. Properties of objects such as the server, databases, and tables can be accessed and managed using the instance classes.

The Utility classes are helper or utility classes that accomplish common SQL Server tasks. These classes belong to one of four groups: Transfer, Backup, and Restore classes, or the Scripter class.

To gain access to the SMO libraries, SMO needs to be installed and the SQL Server-related assemblies need to be loaded.

Getting ready

There are a few ways to install SMO:

If you are installing SQL Server 2014 or already have SQL Server 2014, SMO can be installed by installing Client Tools SDK. Get your install disk or image ready.
If you want just SMO installed without installing SQL Server, download the SQL Server Feature 2014 Pack.

How to do it...

If you are installing SQL Server or already have SQL Server, perform the following steps:

Load up your SQL Server install disk or image, and launch the setup.exe file.
Select New SQL Server standalone installation or add features to an existing installation.
Choose your installation type and click on Next.
In the Feature Selection window, make sure you select Client Tools SDK.
Complete your installation

After this, you should already have all the binaries needed to use SMO.

If you are not installing SQL Server, you must install SMO using the SQL Server Feature Pack on the machine you are using SMO with. The steps are as follows:

Open your web browser. Go to your favorite search engine and search for SQL Server 2014 Feature Pack.
Download the package.
Double-click on the SharedManagementObjects.msi to install.

There's more...

By default, the SMO assemblies in SQL Server 2014 will be installed in <SQL Server Install Directory>\120\SDK\Assemblies. This is shown in the following screenshot:

Loading SMO assemblies

Before you can use the SMO library, the assemblies need to be loaded. With the introduction of the SQLPS module, this step is easier than ever.

Getting ready

In this recipe, we assume you have already installed SMO on your machine.

How to do it...

To load SMO assemblies via the SQLPS module, perform the following steps:

Open up your PowerShell console, PowerShell ISE, or your favorite PowerShell Editor.
Type the import-module command as follows:
```
Import-Module SQLPS
```
Confirm that the module is loaded by running the following. This should give the name of the module if it is loaded:
```
Get-Module
```

How it works...

The way to load SMO assemblies has changed between different versions of PowerShell and SQL Server. Before the SQLPS module and in PowerShell v1, loading assemblies could be done explicitly using the Load() or LoadWithPartialName() methods. The LoadWithPartialName() accepts the partial name of the assembly and loads from the application directory or the Global Assembly Cache (GAC):

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

Although you may still see LoadWithPartialName() in some older scripts, this method is now obsolete and should not be used with any new development.

The method Load() requires the fully qualified name of the assembly:

 [void][Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

In PowerShell v2, assemblies can be added by using Add-Type:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

When the SQLPS module was shipped with SQL Server 2012, loading these assemblies one by one became unnecessary, as long as the SQLPS module is loaded using the following code:

Import-Module SQLPS

There may be cases where you will still want to load specific DLL versions if you are dealing with specific SQL Server versions. Alternatively, you might want to load only specific assemblies without loading the whole SQLPS module. In this case, the Add-Type command is still the viable method of bringing the assemblies in.

There's more...

When you import the SQLPS module, you might see an error about conflicting or unapproved verbs:

Note

WARNING: The names of some imported commands from the module SQLPS include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter.

For a list of approved verbs, type Get-Verb.

This means there are some cmdlets that do not conform to the PowerShell naming convention, but the module and its containing cmdlets are still all loaded into your host. To suppress this warning, import the SQLPS module with the –DisableNameChecking parameter.

Note

Learn how to load SMO assemblies using PowerShell from the MSDN at https://msdn.microsoft.com/en-us/library/hh245202(v=sql.120).aspx.

Exploring the SQL Server PowerShell hierarchy

SQL Server started shipping with the SQLPS module in SQL Server 2012. The SQLPS module allows PowerShell to access SQL Server-specific cmdlets and functions, and also loads commonly used assemblies when working with SQL Server. This continues to be the case in SQL Server 2014.

Launching PowerShell from SQL Server Management Studio (SSMS) launches a Windows PowerShell session which imports the SQLPS module automatically, and sets the current context to the item the PowerShell session was launched from. Database administrators and developers can then start navigating the object hierarchy from there.

Getting ready

To follow this recipe, you should log in to SQL Server 2014 Management Studio.

How to do it...

In this recipe, we will navigate the SQL Server PowerShell hierarchy by launching a PowerShell session from SQL Server Management Studio:

Right-click on your instance node.
Click on Start PowerShell.
Note that this will launch a PowerShell session and load the SQLPS module. This window looks similar to a command prompt, with a prompt set to the SQL Server object you launched this window from. In the following screenshot, ROGUE refers to the name of my local machine:
Note the starting path in this window. The screen now shows how you could get to the default instance if you were to navigate using the PowerShell console or ISE:
```
PS SQLSERVER:\SQL\<SQL instance name>\DEFAULT>
```
Type dir. This should give you a list of all objects directly accessible from the current server instance; in our case, from the default SQL Server instance ROGUE. Note that dir is an alias for the cmdlet Get-ChildItem.
Tip
Downloading the example code
You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
This is similar to the objects you can find under the instance node in Object Explorer in SQL Server Management Studio.
While our PowerShell window is open, let's explore the SQL Server PSDrive or the SQL Server data store which PowerShell treats as a series of items. Type cd \. This will change the path to the root of the current drive, which is our SQL Server PSDrive.
Type dir. This will list all items accessible from the root SQL Server PSDrive. You should see something similar to the following screen:
Close this window.
Go back to Management Studio and right-click on one of your user databases.
Click on Start PowerShell. Note that this will launch another PowerShell session with a path that points to the database you right-clicked from:
Note the starting path of this window is different from the starting path where you first launched PowerShell in the earlier steps. If you type dir from this location, you will see all items that are under the AdventureWorks2014 database.
You can see some of the items enumerated in this screenshot in SQL Server Management Studio Object Explorer, if you expand the AdventureWorks2014 database node:

How it works...

When PowerShell is launched through SSMS, a context-sensitive PowerShell session is created and it automatically loads the SQLPS module. This will be evident in the prompt, which by default shows the current path of the object from which the Start PowerShell menu item was clicked from.

The SQLPS module was not always loaded when PowerShell was launched from SSMS. With SQL Server 2008/2008 R2 it was shipped with a SQLPS utility, which is also referred to as a mini shell. When you started PowerShell from SSMS, it was not a full PowerShell console that was launched. It was a constrained, closed shell preloaded with SQL Server extensions was loaded. This shell was meant to be used for SQL Server only, which proved to be quite limiting because DBAs and developers often need to load additional snapins and modules in order to integrate SQL Server with other systems through PowerShell. At that time, the alternative way was to launch a full-fledged PowerShell session and depending on your PowerShell version, either load snapins or load the SQLPS module.

Since SQL Server 2012, the original constrained mini shell has been deprecated. When you launch a PowerShell session from SSMS in SQL Server 2012 onwards, what is launched is the full-fledged PowerShell console, with the updated SQLPS module loaded by default.

Once the SQLPS module is loaded, SQL Server becomes exposed as a PowerShell Drive (PSDrive), which allows traversing of objects as if they are folders and files. Familiar commands for traversing directories are supported in this provider, such as dir or ls. Note that these familiar commands are often just aliases to the real cmdlet name, in this case, Get-ChildItem.

When you launch PowerShell from SSMS, you can immediately start navigating the SQL Server PowerShell hierarchy.

Discovering SQL-related cmdlets and modules

In order to be good at working with SQL Server and PowerShell, knowing how to explore and discover cmdlets, snapins, and modules is necessary.

Getting ready

Launch PowerShell ISE as administrator. If you prefer the console, you can also launch that instead, but ensure you are running it as administrator.

How to do it...

In this recipe, we will explore SQL-related cmdlets and modules:

To find out how many SQL-related cmdlets are in your system, type the following in your PowerShell editor and run:

#how many commands from modules that
#have SQL in the name
(Get-Command -Module "*SQL*" –CommandType Cmdlet).Count

To list the SQL-related cmdlets, type the following in your PowerShell editor and run:

#list all the SQL-related commands
Get-Command -Module "*SQL*" –CommandType Cmdlet |
Select-Object CommandType, Name, ModuleName |
Sort-Object -Property ModuleName, CommandType, Name |
Format-Table –AutoSize

To see which of these modules are loaded in your PowerShell session, type the following in your editor and run:
```
Get-Module -Name "*SQL*"
```
If you have already used any of the cmdlets in the previous step, then you should see both SQLPS and SQLASCMDLETS. Otherwise, you will need to load these modules before you can use them.
To explicitly load these modules, type the following and run:
```
Import-Module -Name "SQLPS"
```

Note that SQLASCMDLETS will be loaded when you load SQLPS.

How it works...

At the core of PowerShell, we have cmdlets. A cmdlet (pronounced commandlet) is defined in MSDN as lightweight command that is used in the Windows PowerShell environment. It can be a compiled, reusable .NET code or an advanced function, or it can be a workflow that typically performs a very specific task. All cmdlets follow the verb-noun naming notation.

PowerShell ships with many cmdlets. In addition, many applications now also ship with their own cmdlets. For example, SharePoint has a fair number of PowerShell cmdlets that help with installation, configuration, and administration of the farm, sites, and everything in between. A list of cmdlets for SharePoint 2013 can be found at https://technet.microsoft.com/en-us/library/ff678226.aspx.

A legacy way of extending PowerShell is by registering additional snapins. A Snapin is a binary, or a DLL, that can contain a cmdlet. You can create your own snapin by building your own .NET source, compiling, and registering the snapin. You will always need to register snapins before you can use them. Snapins are a popular way of extending PowerShell.

The following table summarizes common tasks with snapins:

Task	Syntax
List loaded Snapins	`Get-PSSnapin`
List Installed Snapins	`Get-PSSnapin -Registered`
Show commands in a Snapin	`Get-Command -Module "SnapinName"`
Load a specific Snapin	`Add-PSSnapin "SnapinName"`

Since PowerShell v2, modules are introduced as the improved and preferred method of extending PowerShell. A module is a package that can contain cmdlets, providers, functions, variables, and aliases. In PowerShell v2, modules are not loaded by default, so required modules need to be explicitly imported.

Common tasks with modules are summarized in the following table:

Task	Syntax
List loaded Modules	`Get-Module`
List Installed Modules	`Get-Module -ListAvailable`
Show commands in a Module	`Get-Command -Module "ModuleName"`
Load a specific Module	`Import-Module -Name "ModuleName"`

One of the improved features of PowerShell v3 onwards is support for autoloading modules. You do not need to always explicitly load modules before using the contained cmdlets. Using the cmdlets in your script is enough to trigger PowerShell to load the module that contains it.

SQL Server 2014 modules are located at PowerShell | Modules in the install directory.

There's more...

You can get a list of SQLPS and SQLASCMDLETS by running the following command:

Get-Command -CommandType Cmdlet -Module SQLPS,SQLASCMDLETS|
Select-Object Name, Module |
Sort-Object Module, Name |
Format-Table -AutoSize

Here's the list of cmdlets as of this version of SQL Server 2014:

CommandType Name                                     ModuleName
----------- ----                                     ----------
     Cmdlet Add-RoleMember                           SQLASCMDLETS
     Cmdlet Backup-ASDatabase                        SQLASCMDLETS
     Cmdlet Invoke-ASCmd                             SQLASCMDLETS
     Cmdlet Invoke-ProcessCube                       SQLASCMDLETS
     Cmdlet Invoke-ProcessDimension                  SQLASCMDLETS
     Cmdlet Invoke-ProcessPartition                  SQLASCMDLETS
     Cmdlet Merge-Partition                          SQLASCMDLETS
     Cmdlet New-RestoreFolder                        SQLASCMDLETS
     Cmdlet New-RestoreLocation                      SQLASCMDLETS
     Cmdlet Remove-RoleMember                        SQLASCMDLETS
     Cmdlet Restore-ASDatabase                       SQLASCMDLETS
     Cmdlet Add-SqlAvailabilityDatabase              SQLPS
     Cmdlet Add-SqlAvailabilityGroupListenerStaticIp SQLPS
     Cmdlet Add-SqlFirewallRule                      SQLPS
     Cmdlet Backup-SqlDatabase                       SQLPS
     Cmdlet Convert-UrnToPath                        SQLPS
     Cmdlet Decode-SqlName                           SQLPS
     Cmdlet Disable-SqlAlwaysOn                      SQLPS
     Cmdlet Enable-SqlAlwaysOn                       SQLPS
     Cmdlet Encode-SqlName                           SQLPS
     Cmdlet Get-SqlCredential                        SQLPS
     Cmdlet Get-SqlDatabase                          SQLPS
     Cmdlet Get-SqlInstance                          SQLPS
     Cmdlet Get-SqlSmartAdmin                        SQLPS
     Cmdlet Invoke-PolicyEvaluation                  SQLPS
     Cmdlet Invoke-Sqlcmd                            SQLPS
     Cmdlet Join-SqlAvailabilityGroup                SQLPS
     Cmdlet New-SqlAvailabilityGroup                 SQLPS
     Cmdlet New-SqlAvailabilityGroupListener         SQLPS
     Cmdlet New-SqlAvailabilityReplica               SQLPS
     Cmdlet New-SqlBackupEncryptionOption            SQLPS
     Cmdlet New-SqlCredential                        SQLPS
     Cmdlet New-SqlHADREndpoint                      SQLPS
     Cmdlet Remove-SqlAvailabilityDatabase           SQLPS
     Cmdlet Remove-SqlAvailabilityGroup              SQLPS
     Cmdlet Remove-SqlAvailabilityReplica            SQLPS
     Cmdlet Remove-SqlCredential                     SQLPS
     Cmdlet Remove-SqlFirewallRule                   SQLPS
     Cmdlet Restore-SqlDatabase                      SQLPS
     Cmdlet Resume-SqlAvailabilityDatabase           SQLPS
     Cmdlet Set-SqlAuthenticationMode                SQLPS
     Cmdlet Set-SqlAvailabilityGroup                 SQLPS
     Cmdlet Set-SqlAvailabilityGroupListener         SQLPS
     Cmdlet Set-SqlAvailabilityReplica               SQLPS
     Cmdlet Set-SqlCredential                        SQLPS
     Cmdlet Set-SqlHADREndpoint                      SQLPS
     Cmdlet Set-SqlNetworkConfiguration              SQLPS
     Cmdlet Set-SqlSmartAdmin                        SQLPS
     Cmdlet Start-SqlInstance                        SQLPS
     Cmdlet Stop-SqlInstance                         SQLPS
     Cmdlet Suspend-SqlAvailabilityDatabase          SQLPS
     Cmdlet Switch-SqlAvailabilityGroup              SQLPS
     Cmdlet Test-SqlAvailabilityGroup                SQLPS
     Cmdlet Test-SqlAvailabilityReplica              SQLPS
     Cmdlet Test-SqlDatabaseReplicaState             SQLPS
     Cmdlet Test-SqlSmartAdmin                       SQLPS

To learn more about these cmdlets, use the Get-Help cmdlet. For example, here's the command to learn more about Invoke-Sqlcmd:

Get-Help Invoke-Sqlcmd
Get-Help Invoke-Sqlcmd -Detailed
Get-Help Invoke-Sqlcmd -Examples
Get-Help Invoke-Sqlcmd -Full

You can also check out the MSDN article on SQL Server Database Engine Cmdlets at http://msdn.microsoft.com/en-us/library/cc281847.aspx.

When you load the SQLPS module, several assemblies are loaded into your host.

To get a list of SQLServer-related assemblies loaded with the SQLPS module, use the following script, which will work in both PowerShell v2 and v3:

Import-Module SQLPS –DisableNameChecking

[AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object {$_.FullName -match "SqlServer" } |
Select-Object FullName

If you want to run on v3 or newer versions, you can take advantage of the simplified syntax:

Import-Module SQLPS –DisableNameChecking

[AppDomain]::CurrentDomain.GetAssemblies() |
Where-Object FullName -Match "SqlServer" |
Select-Object FullName

This will show you all the loaded assemblies, including their public key tokens:

Creating a SQL Server Instance Object

Most of what you will need to do in SQL Server will require a connection to an instance. One way to do this is by creating an instance object via SMO.

Getting ready

Open up your PowerShell console, PowerShell ISE, or your favorite PowerShell editor.

You will need to note what your instance name is. If you have a default instance, you can use your machine name. If you have a named instance, the format will be <machine name>\<instance name>.

How to do it...

If you are connecting to your instance using Windows authentication and using your current Windows login, the steps are as follows:

Import the SQLPS module:

#import SQLPS module
Import-Module SQLPS –DisableNameChecking
$VerbosePreference = "SilentlyContinue"

Store your instance name in a variable:

#create a variable for your instance name
$instanceName = "localhost"

If you are connecting to your instance using Windows authentication from the account you are logged in as:

#create your server instance
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

If you are connecting using SQL Authentication, you will need to know the username and password that you will use to authenticate. In this case, you will need to add the following code, which will set the connection to mixed mode and prompt for the username and password:

#set connection to mixed mode
$server.ConnectionContext.set_LoginSecure($false)

#set the login name
#of course we don't want to hardcode credentials here
#so we will prompt the user
#note password is passed as a SecureString type
$credentials = Get-Credential

#remove leading backslash in username
$login = $credentials.UserName $server.ConnectionContext.set_Login($login)
$server.ConnectionContext.set_SecurePassword($credentials.Password)

#check connection string
#note though that this outputs your password in clear text
$server.ConnectionContext.ConnectionString

Write-Verbose "Connected to $($server.Name)"
Write-Verbose "Logged in as $($server.ConnectionContext. Login)"

How it works...

Before you can access or manipulate SQL Server programmatically, you will often need to create references to its objects. At the most basic level is the server.

The server instance is using the type Microsoft.SqlServer.Management.Smo.Server. By default, connections to the server are using a trusted connection, meaning it uses the Windows account you're currently using when you log into the server. So all it needs is the instance name in its argument list:

#create your server instance
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

However, if you need to connect using a SQL login, you will need to set the ConnectionContext.LoginSecure property of the SMO Server class setting to false:

#set connection to mixed mode
#note that this authentication will fail if mixed mode
#is not enabled in SQL Server
$server.ConnectionContext.set_LoginSecure($false)

You will also need to explicitly set the username and the password. The best way to accomplish this is to prompt the user for the credentials:

#prompt
$credentials = Get-Credential

The Get-Credential cmdlet will display a popup window that will capture the login and password entered by the user:

$login = $credentials.UserName

Once we have the login, we can pass it to the set_Login method. The password is already a SecureString type, which means it is encrypted. This is the data type required by the set_SecurePassword method, so no further conversion is needed. The commands are as follows:

$server.ConnectionContext.set_Login($login)
$server.ConnectionContext.set_SecurePassword($credentials.Password)

Should you want to hardcode the username and just prompt for the password, you can also do this:

$login = "belle"

#prompt
$credentials = Get-Credential –Credential $login

In the script, you will also notice that we are using Write-Verbose instead of Write-Host to display our results. This is because we want to control the output without always going back to our script and removing the Write-Host command.

By default, the script will not display any output, that is, the $VerbosePreference special variable is set to SilentlyContinue. If you want to run the script in verbose mode, you simply need to add this in the beginning of your script:

$VerbosePreference = "Continue"

When you are done, you just need to change the value to SilentlyContinue:

$VerbosePreference = "SilentlyContinue"

Exploring SMO Server Objects

SMO comes with a hierarchy of objects that are accessible programmatically. For example, when we create an SMO server variable, we can then access databases, logins, and database level triggers. Once we get a handle of individual databases, we can then traverse the tables, stored procedures and views that it contains. Since many tasks involve SMO objects, you will be at an advantage if you know how to discover and navigate these objects.

Getting ready

Open up your PowerShell console, PowerShell ISE, or your favorite PowerShell editor.

You will also need to note what your instance name is. If you have a default instance, you can use your machine name. If you have a named instance, the format will be <machine name>\<instance name>.

How to do it...

In this recipe, we will start exploring the hierarchy of objects with SMO:

Import the SQLPS module as follows:

Import-Module SQLPS -DisableNameChecking

Create a server instance as follows:

$instanceName = "localhost"

#code below all in one line
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

Get the SMO objects directly accessible from the $server object:
```
$server |
Get-Member -MemberType "Property" |
Where-Object Definition -Like "*Smo*"
```
Note
If you are using PowerShell v2, you will have to change the Where-Object cmdlet usage to use the curly braces {} and the $_ variable:
```
Where-Object {$_.Definition -like "Smo*" }
```

Now, let's check SMO objects under databases:

$server.Databases |
Get-Member -MemberType "Property" |
Where-Object Definition -Like "*Smo*"

To check out the tables, you can type and execute the following:

$server.Databases["AdventureWorks2014"].Tables |
Get-Member -MemberType "Property" |
Where-Object Definition -Like "*Smo*"

How it works...

SMO contains a hierarchy of objects. At the very top there is a server object, which in turn contains objects such as Databases, Configuration, SqlMail, LoginCollection, and so on. These objects in turn contain other objects, for example, Databases is a collection that contains Database objects, and a Database contains Tables.

Note

You can check out the SMO Object Model Diagram from the MSDN at https://msdn.microsoft.com/en-ca/library/ms162209.aspx.

One way to navigate through the hierarchy is by creating a server instance first. From here, you can use Get-Member to figure out which properties belong to that object. Once you find out, you can start creating additional variables for the member objects and then use Get-Member on them. Lather, rinse, and repeat.