Learn dbatools in a Month of Lunches: Automating SQL server tasks with PowerShell commands

1 Before you begin

1.1 Why data professionals can’t afford to ignore PowerShell

Data is now one of the most valuable assets in the world, so data professionals need a broad set of skills and are expected to be able to accomplish a wide number of tasks, including the following and many more:

Build SQL Server instances

Develop extract, transform, and load (ETL) solutions

Ensure SQL Server instances are correctly configured

Monitor and respond to alerts

Troubleshoot performance and access issues

Perform OS and SQL upgrades

Deploy changes to schemas

Evaluate index usage and settings

In the process of performing our role, we interact with a ton of technologies: SQL Server, virtualization (Hyper-V or VMware), operating systems (Windows or Linux), containers, clusters (including Kubernetes clusters), networking, storage, Active Directory, certificates, and the cloud, to name a few.

In the majority of cases, we will be working with more than one SQL Server instance—sometimes two, sometimes 10,000 or more.

Although we can achieve pretty much everything via GUI consoles for any of those technologies, the following two problems come immediately to mind with this approach:

The amount of time wasted

The inconsistency of humans compared with machines

This is best explained with a story.

1.1.1 A SQL Server DBA first win with PowerShell

When Rob became a SQL Server DBA, his first responsibility every morning was to check that Agent jobs, numbering a little over 100 instances, had run successfully across the SQL Server estate. He would start by connecting to the first instance in SQL Server Management Studio (SSMS), clicking the SQL Server Agent, double-clicking the Job Activity Monitor, and checking the Last Run column for the jobs, as shown in figure 1.1.

Figure 1.1 The Job Activity Monitor

This task would take him a minimum of 90 minutes. He had heard about PowerShell and used it at home to reorder his digital photos into year and month folders, so he asked his boss if he could use PowerShell to make this job easier. His boss said, No, this is the way we do it and have done it for many years, and that wasn’t going to change.

Rob went on holiday, and, in his absence, his boss took responsibility for checking the jobs. One particular job ran on the first of every month. When checking that instance, his boss saw that all jobs had completed successfully on their last run; unfortunately, it was the first of November, and that particular job had last run on the first of October! It took a number of days before this discrepancy was noticed, and it caused some disruption. When Rob came back from holiday, he was given the time to write a PowerShell script to connect to all of the instances, fill a color-coded Excel file (shown in figure 1.2), and save it to a shared drive.

Figure 1.2 The color-coded Excel output from PowerShell

By automating the task, the time he spent performing his daily tasks went from at least 90 minutes each day to the time it takes to open the correct Excel sheet and scroll through, looking for the red cells.

1.2 Automate it

There’s a popular saying in the automation community: If you’re doing something more than once, automate it! Others argue that automation should occur the very first time a task is completed, and we agree; automating a task requires time and thought, which generally results in a greater chance the task will be done properly and thoroughly. As a bonus, the script will be available the next time the task must be completed. PowerShell, a scripting language that can interact with many technologies, is an excellent tool for such automation.

Humans are fallible; they get tired, distracted, or bored with repetitive tasks, but this is where a computer excels. A script, like Rob’s PowerShell script, runs monotonous tasks over and over, all day, without distraction and can also be scheduled to run at antisocial times. A script also would not mistake 11/01/2018 with 10/01/2018, but a human can!

Another task in which computers thrive: dealing with repeatability—running that script again and again, and performing the exact same task over and over. Humans, unfortunately, are not so good at that.

Another well-known saying is relevant at this point: Use the right tool for the job.

We believe that it is better to write a script with good comments and headers that will set up a particular scenario than to add a set of screenshots to a document for a human to follow. Documentation with too many assumptions can lead to mistakes, whereas overdocumenting all of the steps will lead to large, unwieldy, hard-to-follow documentation, which is difficult to maintain.

Ultimately, learning PowerShell will prepare you for our inevitable (and fun!) automation-rich future. PowerShell is everywhere—it’s now available on Windows, Linux, macOS, Raspberry Pi—and it even helps power the cloud. Once you learn how to use an automation tool like PowerShell, you can easily transition your skills to automate everything from Azure and SQL Managed Instances to Spotify and Slack. PowerShell will help empower you to become the automator and not the automated.

1.3 What is dbatools?

dbatools is an open source, cross-platform PowerShell toolkit for SQL Server DBAs, originally created by PowerShell and SQL Server MVP Chrissy LeMaire. With more than 215 contributors from the SQL Server, PowerShell, and C# communities, dbatools is designed and written by the people who use it in their everyday work. dbatools includes solutions for common tasks like performing backups and restores, migrations (see figure 1.3), and setting up availability groups. dbatools is designed to enable SQL DBAs to reliably and repeatedly automate the usual daily tasks.

Figure 1.3 A sample dbatools command, Start-DbaMigration

Often based on solutions found on popular blog posts, Stack Overflow, and Reddit, dbatools commands automate and simplify so many of the tasks we’ve all had to do multiple times. This means that you don’t need to remember the formula for calculating maximum memory settings or where you saved the T-SQL for converting a trace to extended events (thank you, Jonathan Kehayias). dbatools also interacts with many popular SQL Community tools created by data professionals like Ola Hallengren (The Maintenance Solution we love), Glenn Berry (awesome diagnostic queries), Adam Machanic (sp_whoisactive), Brent Ozar (First Responder Kit), and Marcin Gminski (SQLWATCH).

Where is Microsoft in all of this? Although the SQL tools team has its own module, SqlServer (formerly SQLPS), Microsoft has been incredibly supportive of dbatools. Not only do premier field engineers use and blog about dbatools, the SQL tools team also allows us to include many of the bits that power SSMS.

1.4 Is this book for you?

dbatools helps make PowerShell easy for the data platform community because its primary audience is end users instead of developers. Now you no longer have to know how to program PowerShell to work with SQL Server at scale; you can just run a few commands that we built for you.

Our focus in this book will be on PowerShell. However, it is less about showing you how to write and develop PowerShell scripts and more about showing you how to accomplish tasks, as shown in figure 1.4. We expect that you have some knowledge of SQL Server and its administrative tasks, because we won’t be teaching SQL Server concepts other than what is required to understand the PowerShell code.

Figure 1.4 Learning PowerShell

If you don’t know how to use PowerShell just yet, we aim to help you not by teaching PowerShell but by teaching you how to do your current job using PowerShell. If you use the GUI and are hesitant about a future filled with automation and command-line tools, our goal is to transform that hesitation into eagerness, confidence, and excitement.

This book will serve as a learning guide, taking you from gathering information about your estate to performing complex migrations with just a couple lines of code. We will also give you the confidence to explore PowerShell and develop your own solutions for administering SQL Server in your own estate and to use your increased understanding of PowerShell with other technologies.

1.5 How to use this book

The idea here is that you will read one chapter each day. Each chapter should take about 40 minutes to read, giving you time to practice what you just learned. We recommend reading just one chapter a day, rather than reading extra chapters. We think you will benefit more spending that time practicing what you have learned and cementing your knowledge and comfort with using dbatools and PowerShell.

1.5.1 The main chapters

Chapters 1 through 3 will help you become oriented with dbatools, PowerShell, and to a lesser extent, SQL Server. Chapters 4 through 24 represent the primary content of the book, so you can expect to finish in about a month. The chapters build upon one another, so we recommend that you complete them in the order in which they’re provided, even if you’re excited about a particular topic (like disaster recovery, woo!).

1.5.2 Hands-on labs

Many chapters provide a hands-on lab that will help you apply the commands you learned about. These labs are not quizzes, so everything you need will be contained within the book. If you find yourself stuck, however, you can visit the book’s forum at dbatools.io/molforum, and we’ll be there to help you out.

1.5.3 Supplementary materials

The dbatools.io website is rich with content, including blog posts, videos, tutorials, and more. You can also find the answers to all of the labs at dbatools.io/answers.

1.5.4 Further exploration

This book covers several areas within PowerShell and SQL Server but still touches just the tip of the iceberg of Microsoft’s data platform. We personally spend a lot of our time exploring fun, related technologies like Kubernetes and Power BI and think that you may enjoy some of the same exploration as well.

If this sounds appealing to you, we share a lot of our own technical adventures on Twitter and suggest you follow us there: Chrissy is @cl, Rob is @sqldbawithbeard, Jess is @jpomfret, and Cláudio is @ClaudioESSilva. For dbatools-only content, you can also follow @psdbatools.

1.6 Contacting us

We love when people are excited about PowerShell, and we’re eager to help with your questions. We hang out a lot in the SQL Community Slack in the #dbatools channel. You can join over 17,000 SQL Server community members for a live chat at dbatools.io/slack.

In addition to Slack and Twitter, you’ll likely find us at PowerShell, SQL Server, and DevOps conferences around the world as well. If you’re into code livestreaming, you can find us live coding at dbatools.io/live when the mood strikes.

1.7 Being immediately effective with dbatools

The great thing about dbatools is that most of the development work has been done for you. A bunch of community members collectively invested thousands of hours to build a standardized toolset that helps us manage our daily tasks. This also means you can manage these same tasks by executing just a few commands.

Like the authors of Learn PowerShell in a Month of Lunches, Travis Plunk, James Petty, Tyler Leonhardt, Don Jones, and Jeffery Hicks, our primary goal in this book is for you to be immediately effective. This means that a section may be initially light on the details so that you can jump right in and accomplish some tasks. If necessary, we will provide additional depth, theory, and nuances later in the chapter and in online articles, or highlight it in a livestream.

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.

Figure 2.1 Configuration Manager. Note the instance is offline but can still be modified.

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

Try it now 2.1

Set your execution policy to RemoteSigned:

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:

Group Policy: MachinePolicy

Group Policy: UserPolicy

Execution Policy: Process (powershell.exe-ExecutionPolicy)

Execution Policy: CurrentUser

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).

Figure 2.2 Trusted sites

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 Files\WindowsPowerShell\Modules\ C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\ C:\Users\dbatools\Documents\WindowsPowerShell\Modules\ C:\Program Files\Microsoft SQL Server\130\Tools\PowerShell\Modules\ C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\

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:\Windows\System32\WindowsPowerShell\v1.0\Modules

C:\Program Files\WindowsPowerShell\Modules

$home\Documents\WindowsPowerShell\Modules—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.

Try it now 2.2

Evaluate your own $Env:PSModulePath:

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

Figure 2.3 Microsoft’s PowerShell Gallery

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.

Try it now 2.3

Set the PowerShell Gallery to be trusted for installations:

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 Files\PackageManagement\ProviderAssemblies' or 'C:\Users\manikb\AppData\Local\PackageManagement\ProviderAssemblies'. 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 Files\WindowsPowerShell\Modules, as shown in the next sidebar.

Try it now 2.4

Install dbatools for all users on a computer with PowerShellGet:

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.

Try it now 2.5

Use PowerShell to see the newly trusted certificate:

Get-ChildItem Cert:\CurrentUser\TrustedPublisher | Select-Object *

Figure 2.4 The publicly available code signing certificate from dbatools

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 $home\Documents\WindowsPowerShell\Modules, 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.

Try it now 2.6

Saving the dbatools module on a computer that has PowerShellGet:

Save-Module -Name dbatools -Path C:\temp

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

Figure 2.5 dbatools in temp directory

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.

Figure 2.6 Twitter poll results

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-master\dbatools-master, should be renamed dbatools and placed in one of the directories in your $Env:PSModulePath, as shown in figure 2.7.

Figure 2.7 An unzipped directory, placed in a valid module’s path. Note the directory structure.

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.

Figure 2.8 dbatools.io/commands, supporting both search and categories

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

Figure 2.9 Docs website—note that