2 Installing dbatools

In this chapter, we’ll cover minimum requirements, various installation methods, and gotchas. Understanding how to install dbatools will not only enable you to use our toolset, it will also enable you to install any other PowerShell module in the PowerShell Gallery.

The old saying, “Before you do anything, you have to do something first,” holds true for installing dbatools and other PowerShell modules. Specifically, you may have to execute the following two commands first:

• Set-ExecutionPolicy

• Set-PSRepository

If you have not yet modified your default execution policy, or trusted Microsoft’s PowerShell Gallery using Set-PSRepository, we’ll help guide you through these steps. If you are already familiar with the PowerShell Gallery and installing dbatools, feel free to skip to the next chapter.

2.1 Minimum requirements

We’re going to start with minimum requirements because not everyone can be on the latest and greatest setup at work. It’s useful to know whether the old workstation we inherited can support dbatools. The good news is that the answer is most likely yes!

dbatools originally started as a migration module, so it was created with requirements that are as low as possible. This allows us to use dbatools in the older environments that are most in need of migrations. Because of PowerShell’s flexibility, dbatools also works in newer environments such as Azure, SQL Server on Linux, and PowerShell on macOS.

2.1.1 Server

Like SSMS, dbatools can connect to super-old versions of SQL Server. When creating dbatools, we actually tried to make it work with SQL Server 7, but an environment that supports SQL Server 7 is not an environment that supports PowerShell. Table 2.1 outlines the versions of SQL Server that we support.

Table 2.1 SQL Server instance support

You may notice that Azure SQL DB, Azure SQL Edge, and Azure Managed Instances are not mentioned on this list. That’s because, at the time of writing, the extent of support for Azure within dbatools has not been evaluated and catalogued. We do build in some support for Azure, which you can read more about in chapter 27.

When it comes to PowerShell requirements on the target server, PowerShell is not even needed for 75% of our commands. If you do use commands that connect to the OS, such as Get-DbaDiskSpace, PowerShell remoting will need to be enabled. You can read more about remoting at dbatools.io/secure.

2.1.2 Workstation

It’s important to note that, like SSMS and Azure Data Studio, we do not have to install dbatools on every server. It is best to centralize administration to the DBA workstations and minimal servers that run scheduled tasks and Agent jobs.

dbatools supports a wide variety of environments, but not every environment is supported for every command. An approximate breakdown of command support by operating system as of dbatools v1.0 is shown in table 2.2.

Table 2.2 OS support

Although dbatools can run on older versions of PowerShell, we recommend version 5.1 and higher. Newer versions of PowerShell are faster and offer a number of security features that are beneficial to enterprise environments. PowerShell Core is ultra fast but has limitations that prevent some commands from working. As such, about 75% of the commands in dbatools will work on PowerShell Core.

Note Throughout the book, we’ll try our best to highlight which commands will not work on Linux and macOS. If there is no notation, then you can assume the command should work on Windows, macOS, and Linux. A general rule of thumb is that if a command uses SQL WMI (SQL Configuration Manager) or has a -ComputerName parameter, it likely does not work on Linux or macOS.

Installing newer versions of PowerShell is as simple as installing an update, specifically, the Windows Management Framework from https://dbatools.io/wmf for v5.1 and aka.ms/pscore6 for PowerShell Core. These shortlinks link to the installer packages for Windows, Linux, and macOS.

2.1.3 Ports

As previously mentioned, we recommend running dbatools against remote servers from a centralized workstation. This means that various network ports between the machine running dbatools and the remote servers must be open and accessible.

Table 2.3 lists the default ports required to support all commands within dbatools. These are common ports that are generally approved to be used on enterprise networks.

Table 2.3 Required ports

Note that if you change the default port for SQL, we support that, too.

You probably recognize SQL Database Engine and SMB, but what about SQL WMI and WS-Management?

SQL WMI

If you’re curious about SQL WMI, this is the protocol used by the SQL Server Configuration Manager. SQL Server Configuration Manager—and SQL WMI by extension—is still available, even if the SQL services are not running. This means that the commands that use SQL WMI can access and modify specific SQL Server properties, even if the instance is offline.

Figure 2.1 shows us updating the service account name and password for the default SQL Server instance on the server SQL2014. If you’re curious, the equivalent dbatools command for the functionality seen in this screenshot is Update- DbaServiceAccount.

WS-Management

Of all of the listed ports, WS-Management is probably the least recognizable to SQL Server pros. WS-Management is the protocol used by PowerShell remoting. PowerShell remoting allows commands to be executed against remote computers and is implemented in commands such as Invoke-Command and Enter-PSSession, as highlighted in the next listing.

Listing 2.1 PowerShell remoting—note the connection to the remote machine, spsql01

PS> Invoke-Command -ComputerName spsql01 -ScriptBlock { $Env:COMPUTERNAME }
SPSQL01

This protocol is exceptionally secure (see dbatools.io/secure) for the following reasons:

• By default, it allows connections only from members of the Administrators group.

• It uses a single port: 5985 (HTTP) or 5986 (HTTPS).

• Regardless of the transport protocol used (HTTP or HTTPS), PowerShell remoting always encrypts all communication after initial authentication with a per-session AES-256 symmetric key.

• Initial authentication is NTLM, Kerberos, and Certificates, so no credentials are ever exposed.

Check out our blog post at dbatools.io/secure to see why remoting is even safer than logging in to a Windows server using the GUI.

2.1.4 Execution policy

Initially, we found execution policies (see sqlps.io/abexecpolicies) hard to understand, and explaining them is a bit tricky. Most people believe execution policies are a security mechanism, when they are really there for safety. But aren’t safety and security the same thing? No.

Execution policies are safety mechanisms that confirm your intention to run a command or script. So, although they can’t prevent a hacker from hacking your computer, they can prevent you from running a script by accident. That’s the difference between safety and security.

PowerShell’s default execution policy varies by operating system (OS), as shown in table 2.4.

You may find that when creating your own scripts, you are blocked by your execution policy. The most common suggestion is to set your policy to RemoteSigned. This is the first command you must run if you have not yet modified your default execution policy.

Table 2.4 Default execution policy

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

Note that this setting will be effective only if your organization does not set the execution policy as a group policy.

Execution policy precedence order determines which execution policy will be used in a given session. Execution policy is processed in the following order:

1. Group Policy: MachinePolicy

2. Group Policy: UserPolicy

3. Execution Policy: Process (powershell.exe -ExecutionPolicy)

4. Execution Policy: CurrentUser

5. Execution Policy: LocalMachine

Later in your scripting career, you may do what we do and set your execution policy to Bypass. This is convenient and no less secure than RemoteSigned, because it keeps the lowered permissions isolated (sqlps.io/bypassvsunres) to just the current running process.

2.2 Signed software

Like most enterprise software, dbatools is digitally signed. This means that you can trust that the module came from us and that the PowerShell code has not been modified after publication. As of this writing, Chrissy, Rob, Jess, and Shawn Melton are the only members with access to the code signing certificate and, therefore, the only four members who make this guarantee.

Earlier, you set your execution policy to RemoteSigned, but what exactly does this mean? Let’s break it down:

• Remote —A script originating from a remote computer such as a website

• Signed —A script that has been signed by a trusted publisher

Basically, scripts that you create on your local machine do not have to be signed, but scripts that originate from other machines must be digitally signed unless they are in Trusted sites, as shown in figure 2.2 (sqlps.io/ietrustedsites).

The whole system behind signing, public key infrastructure, or PKI, is a bit out of scope for this book, but it essentially breaks down as follows:

• We submitted multiple proofs of identity to a globally recognized certificate authority.

• They performed various validations and granted us a globally recognized code signing certificate.

• Microsoft requires that you explicitly trust our code signing certificate anyway, and you will be prompted when installing our module from the PowerShell Gallery.

2.3 Understanding installation paths

Before proceeding to the installation methods, it is important to understand how PowerShell auto-imports modules. Back in the early days, PowerShell would autoload a ton of things when it started up. This gave the impression that PowerShell was slow, especially when compared to the speediness of opening cmd.exe.

One of the ways the PowerShell team addressed this issue was to add support for module autoloading and $Env:PSModulePath. In the next listing, you can see common results for $Env:PSModulePath.

Listing 2.2 Example results

PS> $Env:PSModulePath -Split ";"
C:Program FilesWindowsPowerShellModules
C:WINDOWSsystem32WindowsPowerShellv1.0Modules
C:UsersdbatoolsDocumentsWindowsPowerShellModules
C:Program FilesMicrosoft SQL Server130ToolsPowerShellModules
C:Program Files (x86)Microsoft SQL Server130ToolsPowerShellModules

You may be familiar with MS-DOS or Linux’s PATH variables, and $Env:PSModulePath is similar. This environment variable tells PowerShell where to look for available commands.

Command names within modules contained in this path will autocomplete when tabbed, but the module will not actually load until the command is executed or parameter autocompletion is attempted. This allows PowerShell to launch quickly while still providing an autocompleting index of commands.

Tip You may have heard the term Cmdlet, which is PowerShell-specific terminology. As PowerShell in a Month of Lunches explains, PowerShell supports various types of executable commands. This includes Cmdlets, which are written in C#, and functions, which are written in pure PowerShell. Although the dbatools module provides a mix of both Cmdlets and functions, they are all essentially commands. Throughout the book, we’ll refer to all types of executable commands simply as commands.

On a freshly installed Windows machine, modules will generally be loaded from the following:

• C:WindowsSystem32WindowsPowerShellv1.0Modules

• C:Program FilesWindowsPowerShellModules

• $homeDocumentsWindowsPowerShellModules—user profile Documents folder

Paths can vary by computer. Use the following code to evaluate your own $Env:PSModulePath, noting how -Split splits the path at each semicolon, making the output easier to read.

$Env:PSModulePath -Split ';'

This auto-import is one of the primary reasons we don’t see explicit mentions of Import-Module referenced as often anymore.

2.4 Installation methods

Because we want to ensure dbatools is available in as many environments as possible, we offer several ways to install it. Our preferred method is the PowerShell Gallery, for reasons we’ll outline shortly.

The PowerShell Gallery is not only useful for online installs and updates, but it also provides options for offline installs (dbatools.io/offline) as well.

2.4.1 The PowerShell Gallery

dbatools is a PowerShell module, which is basically a package full of code, DLLs, configuration files, and more. In 2015, Microsoft introduced the PowerShell Gallery to centralize the distribution of such PowerShell packages.

Installing and updating PowerShell modules is a bit of an inception because you do so using another PowerShell module, PowerShellGet. PowerShellGet is included in Windows 10. PowerShellGet can also be installed manually on any machine using PowerShell 3.0 and later. If you find yourself in need of a manual install of PowerShellGet, visit mng.bz/8lxg.

The PowerShell Gallery is not only a centralized repository accessed via PowerShell commands, but it is also an attractive and easy-to-use website that you can access at powershellgallery.com, as shown in figure 2.3.

If your workstation environment supports the PowerShell Gallery, that should be your default for all PowerShell module installs. The Gallery provides a few basic security checks and is the most convenient way to keep modules updated.

In addition, modules delivered by PowerShell Gallery are streamlined for end users. Unlike our GitHub repository, extra development-related files (such as hundreds of unit and integration test files) are not included in the package. This means that installs of dbatools from the PowerShell Gallery will be smaller both in size and the number of files when compared to other installation methods.

2.4.2 Trusting the PowerShell Gallery

Earlier we mentioned that you’ll need to execute two commands before installing dbatools. We’ve already covered Set-ExecutionPolicy, and now we’ll address Set-PSRepository.

Because of its focus on security and trust, Microsoft does not trust its own repository by default; they leave you to be explicit about who you and your organization will trust. If you trust Microsoft’s PowerShell Gallery like we do, you can avoid being repeatedly prompted to approve PowerShell module installations by changing the installation policy with the Set-PSRepository command shown in the next sidebar.

Set-PSRepository -Name PSGallery -InstallationPolicy Trusted

Once you execute this command (or any PowerShellGet command) for the first time, you may be prompted to install NuGet, as shown in the next listing.

Listing 2.3 Explicitly trusting PSGallery may prompt for a NuGet update

PS> Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
 
NuGet provider is required to continue
PowerShellGet requires NuGet provider version '2.8.5.201' or newer to 
interact with NuGet-based repositories. The NuGet provider must be available 
in 'C:Program FilesPackageManagementProviderAssemblies' or 
'C:UsersmanikbAppDataLocalPackageManagementProviderAssemblies'. You 
can also install the NuGet provider by running 'Install-PackageProvider 
-Name NuGet -MinimumVersion 2.8.5.201 -Force'. Do you want PowerShellGet to 
install and import the NuGet provider
now?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

Go ahead and answer Yes. If you’re behind a corporate proxy and experience issues, please visit dbatools.io/proxy for more information on proxy support.

2.4.3 Installing dbatools using the PowerShell Gallery, all users

To install dbatools for all users on your computer, including the SQL Server Agent service account, you must install dbatools using Run as Administrator. This will install dbatools in C:Program FilesWindowsPowerShellModules, as shown in the next sidebar.

Install-Module -Name dbatools

In the same way that Microsoft does not automatically trust its own repository, it also does not automatically trust valid publisher certificates.

warning Importing dbatools after loading Microsoft’s SQL Server and SQLPS module in the same session will cause strangeness to occur, including unexpected output that may not match our examples. We recommend avoiding this scenario if possible.

If your execution policy is AllSigned, you will also have to explicitly import dbatools to get prompted to accept our publisher certificate. If you are prompted, press R to run once, as shown next.

Listing 2.4 Explicitly trusting the dbatools code signing certificate

PS> Import-Module dbatools
 
Do you want to run software from this untrusted publisher?
File dbatools.Types.ps1xml is published by CN=dbatools,O=dbatools, L=Vienna, 
S=Virginia, C=US and is not trusted on your system. Only run scripts from 
trusted publishers.
[V] Never run  [D] Do not run  [R] Run once  [A] Always run  [?] Help 
(default is "D"): R

Once you accept our certificate, you can see our certificate in your Trusted Publishers certificate store shown in figure 2.4. To access your certificate store, run certmgr from PowerShell for a GUI interface.

You can also use PowerShell to see this certificate, as depicted in the next sidebar. Both approaches are valid and convey the same amount of information.

Get-ChildItem Cert:CurrentUserTrustedPublisher | Select-Object *

2.4.4 PowerShell Gallery, local user

Many organizations (and DBAs) believe strongly in the principle of least privilege. PowerShell natively enables you to restrict the availability of a PowerShell module to certain users. Perhaps you have a shared workstation and require DBAs to only be able to use dbatools. Maybe you will use only modules with an administrative account instead of the normal user account that logs on to a workstation. To install dbatools just for the account that is currently running PowerShell and install it in the user profile documents folder $homeDocumentsWindowsPowerShellModules, you can run the following code.

Listing 2.5 Installing dbatools to just a single account

Install-Module -Name dbatools -Scope CurrentUser

Each method of installing dbatools is perfectly valid. Installing the module with Scope CurrentUser means that you do not need administrator privileges to perform installs or updates of the module. The downside is that other users, such as SQL Server Agents or other DBAs, will need to install their own copy of dbatools.

2.4.5 PowerShell Gallery, offline install

Offline installs are often required for secure environments or when you need to install dbatools to be used by a production SQL Server Agent because the SQL Server cannot connect to the internet. For the offline install, some machine has to be online at some point. This is true for both the PowerShell Gallery offline install and other methods, such as saving the zip. You will need a machine that is connected to the internet and has PowerShellGet.

Save-Module -Name dbatools -Path C:	emp

This will save the module in the C: emp directory in a folder called dbatools, as shown in figure 2.5.

You will then need to move the dbatools folder to the secure machine. You may need to zip and unzip it for transport. You will place the dbatools folder and all of its contents in a folder in $ENV:PSModulePath. We recommend that you use one of the previously mentioned folders, shown in table 2.5.

Table 2.5 Module availability by folder

If you’d like a detailed step-by-step for future reference, check out dbatools.io/offline.

2.5 PowerShell Gallery alternatives

When performing a Twitter poll for this book, we asked how people installed dbatools. A whopping 75% said the PowerShell Gallery, as shown in figure 2.6.

The next most popular answer was downloading the zip manually from our GitHub repository, so we’ll cover that method, too.

2.5.1 Downloading a zipped archive

If it’s not clear yet, we love shortlinks, and our zip shortlink, dbatools.io/zip, makes it very easy to remember where you can download the latest version of dbatools: right from the master branch of our GitHub repository. When using this method to install dbatools, be aware of the following two caveats:

• This version of the module will not be digitally signed.

• You’ll need to rename the directories.

Let’s take a closer look at these warnings.

No digital signature

When code is committed to GitHub, the files are modified in a way that invalidates our digital signature. This means that you will not be able to set your execution policy to anything stricter than RemoteSigned.

Directory rename

For dbatools to load properly, the unzipped directory, dbatools-masterdbatools-master, should be renamed dbatools and placed in one of the directories in your $Env:PSModulePath, as shown in figure 2.7.

Once the zip file has been extracted, the directory has been renamed, and the module is placed in the appropriate directory, it should work no differently than if it were installed via Install-Module.

Offline install

Offline installs are often required for secure environments. If you cannot use Save-Module, using the zip installation works as well. As mentioned previously, if you’d like a detailed step-by-step for future reference, you can visit dbatools.io/offline.

2.5.2 Additional methods

We also offer the ability to install and update via a few other methods, including Chocolatey! For details about additional installation methods, please visit dbatools.io/install.

2.6 How to find and use commands, the help system, and docs.dbatools.io

We’ve got a lot of commands, which makes the toolset powerful but potentially overwhelming. To ensure that you can find your way around dbatools, we offer a number of different ways to find commands and functionality. We even include websites!

2.6.1 Get-Command

To find command names that match a pattern, you can use PowerShell’s built-in Get-Command, shown here.

Listing 2.6 Finding command names that match Connection

Get-Command *connection* -Module dbatools

2.6.2 Find-DbaCommand

You can also use our command, Find-DbaCommand, shown in the next listing, which searches not only command names like Get-Command but command synopses and descriptions as well.

Listing 2.7 Finding command descriptions and examples that match connection

Find-DbaCommand connection

You can even use tags. The -Tag parameter, shown in the following code sample, which autocompletes, uses arbitrary tags applied by our team.

Listing 2.8 Finding commands where the tag matches connection

Find-DbaCommand -Tag Connection

Which command is best, Get-Command or Find-DbaCommand? It really depends on your preference; because of the size of our toolset, we wanted to offer additional options, and as a bonus, Find-DbaCommand helps automate building the raw code for docs.dbatools.io.

2.6.3 Get-Help

We try to make PowerShell as accessible as possible, and part of that is providing solid documentation for our end users. Documentation is so important to us that we have tests that ensure the following items exist within every command:

• Synopsis

• Description

• Help for each parameter

• Examples

We also created an attractive, categorized web interface, shown in figure 2.8, to help navigate through our 500+ commands.

Once you’re finished installing, you may find yourself in need of documentation and help. PowerShell makes getting help incredibly easy: use Get-Help, as shown in the next listing.

Listing 2.9 Getting help for Test-DbaConnection

Get-Help Test-DbaConnection

2.6.4 docs.dbatools.io

In addition to our commands index, we also offer an entire website dedicated to documentation. Every command has a web page at docs.dbatools.io, and the website is updated with every release. See figure 2.9.

You can access docs for each command by appending the command name to dbatools.io (e.g., dbatools.io/Start-DbaMigration), or you can use Get-Help, this time with the -Online parameter, as shown in the following code.

Listing 2.10 Getting online help for Start-DbaMigration

Get-Help Start-DbaMigration -Online

2.7 Updating

After installing, it is important to keep dbatools updated. During peak coding season, we sometimes update multiple times per day (after thousands of tests pass, of course), so updating should be a comfortable routine.

2.7.1 PowerShell Gallery

Updating dbatools and any PowerShell module is easiest when PowerShell Gallery is used, as shown in the following code snippet.

Listing 2.11 Updating dbatools using the PowerShell Gallery

Update-Module dbatools

Note If you installed dbatools without using the CurrentUser scope, you will need to run the update as administrator.

2.7.2 Alternative methods

Updating dbatools using other methods is a far less automated process. It is basically a delete and reinstall. We have attempted to ease this with Update-Dbatools, as shown in the next listing, but it’s a big challenge, especially when DLL files are in use.

Listing 2.12 Updating dbatools using our native command

Update-Dbatools

If this command does not work for you, please revisit section 2.4 to delete and reinstall. If you find the DLLs are “stuck,” the command shown in the next code may be necessary.

Listing 2.13 Kill all PowerShell-related processes

Get-Process *powershell* | Stop-Process

Now that you’ve got dbatools installed, it’s time to prep our lab.

2.8 Hands-on lab

Let’s practice what you just read in this chapter:

1. Find all commands that have DbaReg in their name.

2. Using Get-Help, find examples for the command Install-DbaInstance.

¹ Assuming Linux OS inside container