contents
1.1 Why data professionals can’t afford to ignore PowerShell
A SQL Server DBA first win with PowerShell
1.2 Automate it
1.6 Contacting us
1.7 Being immediately effective with dbatools
2.2 Signed software
2.3 Understanding installation paths
Trusting the PowerShell Gallery
Installing dbatools using the PowerShell Gallery, all users
PowerShell Gallery, local user
PowerShell Gallery, offline install
2.5 PowerShell Gallery alternatives
2.6 How to find and use commands, the help system, and docs.dbatools.io
2.7 Updating
2.8 Hands-on lab
3.1 Why is a lab included in this book?
3.2 Two options for building a dbatools lab environment
Installation media for our lab
Windows lab is ready for action
3.4 Option 2: Quick demo environments using containers
Running SQL Server in a container
4 A gentle introduction to dbatools commands
4.1 Getting started
4.2 Checking the SQL connection
4.4 Running your first dbatools command
4.5 The -SqlInstance parameter
4.6 The -SqlCredential parameter
Connecting to instances with SQL Server Authentication
Saving the credential to use SQL Server Authentication with multiple commands
Other methods of using credentials for SQL Server Authentication
Connecting to instances with a different Windows account
4.7 The ComputerName parameter
Methods of listing the SQL services on multiple servers
Listing services on a server using a different account at the command line
Listing services on a server using a different account with a credential variable
4.9 Bonus parameter: EnableException
4.10 Hands-on lab
5.1 Piping commands
Importing from a CSV file to a database table
Importing to a database table from a dbatools command
Creating the database table first and then importing from a CSV file
Writing the results of other commands to a table
Writing the results of other commands to an Azure SQL Database
5.3 Copying tables, including their data
5.4 Hands-on lab
6 Finding SQL Server instances on your network
6.1 Background
Finding instances using a list of targets
Finding SQL Servers in an Active Directory domain
Finding SQL Servers in your surrounding network
6.2 Working with detailed results
6.3 OS support
6.4 Hands-on lab
7 Inventorying your SQL estate
7.1 SQL features
7.2 Build
7.3 Host information
7.4 Databases
Filtering databases returned from Get-DbaDatabase
Filtering databases returned from Get-DbaDatabase by last backup time
7.5 Putting it all together into a database
7.6 Hands-on lab
Version-specific RegSrvr.xml files
Importing advanced environment folder structures
8.7 Hands-on lab
9.1 Failed logins
9.3 Logins, users, and permissions source control
Finding nested Active Directory group access
9.5 Hands-on lab
10 Backups
10.1 Creating backups
10.2 Reading backup files
10.3 Backup history
10.5 Testing your backups
10.6 Hands-on lab
11 Restore
11.1 Limitations and considerations
11.2 Restore scenarios
11.3 Restoring to custom data and log directories
Restoring to a marked transaction
11.4 Azure
11.5 Hands-on lab
12 Snapshots
12.1 Snapshots and SSMS
12.2 Application upgrade
12.4 Creating a snapshot
12.5 Upgrading
12.6 Rolling back the entire database from a snapshot
12.7 Restoring certain objects or data from a snapshot
12.8 Cleaning up
12.9 Reporting
12.10 Hands-on lab
13 Install and update SQL Server
13.1 Installing
Benefits of automated installs
Customizing installation options
ConfigurationFile and Configuration
13.2 Updating
13.3 The importance of patching
Burdensome process leads to procrastination
13.5 Hands-on lab
14.1 Exporting an entire instance
14.2 Granular exports
14.3 Special commands
14.4 Exporting server configurations (sp_configure)
14.5 Hands-on lab
15 Performing your first advanced SQL Server instance migration, part 1
15.1 Databases
Staging large databases for migration
Other database migration options
15.2 Hands-on lab
16 Performing your first advanced SQL Server instance migration, part 2
16.1 Logins and groups
Which logins/groups are still needed?
16.2 SQL Agent objects: Jobs, operators, and more!
16.3 Linked servers
16.4 More migration fun
16.5 Hands-on lab
17 High availability and disaster recovery
17.1 Log shipping
Configuring log shipping with dbatools
When log shipping goes bad: Gathering errors with dbatools
Cutting over to a log shipped secondary database
17.2 Windows Server Failover Cluster (WSFC)
17.3 Availability groups
Creating an availability group with dbatools
Explore existing availability groups
17.4 Hands-on lab
18 PowerShell and SQL Server Agent
18.1 Which to choose, CmdExec or PowerShell job steps?
18.2 Creating Agent jobs to run PowerShell and dbatools
Creating a SQL Server credential
Creating a SQL Server Agent proxy
18.3 Creating the SQL Server Agent job with a CmdExec job step
18.4 Tips
Using default parameter values
Ensuring that the Agent job fails when the PowerShell fails
18.5 Hands-on lab
19 SQL Server Agent administration
19.1 Listing SQL Server Agent information
19.2 Agent job results and history
19.3 Hands-on lab
20 Creating and working with SQL Server Agent objects
20.1 SQL Server Agent job creation
20.3 Hands-on lab
21 Data masking
21.1 Getting started
21.2 A common approach
21.3 The better approach
21.4 The process
Generating a configuration file for masking
Validating a data masking configuration file
21.5 Hands-on lab
22.1 When should you use dbatools in DevOps?
22.2 DACPAC
Exporting a DACPAC from an existing database
22.3 Running dbatools (and PowerShell) on a CI/CD system
Ensuring the dbatools module is available
Understanding how to add parameters to the script
22.4 Hands-on lab
23 Tracing SQL Server activity
23.1 SQL Server Trace and SQL Profiler
Converting traces to Extended Events
23.2 Extended Events
Starting and stopping Extended Event sessions
Replicating Extended Event sessions to multiple instances
23.3 Hands-on lab
24.1 Encrypting network connections
24.2 Extended protection for authentication
24.3 Hide an instance
24.4 Transparent data encryption (TDE)
24.5 Database backup encryption
Backing up the database with a certificate
Checking encryption information from the backup
24.7 Hands-on lab
25.1 Types of compression
25.2 How does rowstore data compression work?
25.3 Why use data compression?
25.4 It can’t all be rainbows and unicorns: Compression drawbacks
25.5 What’s compressed?
25.7 What makes a good candidate for compression?
25.8 dbatools, what should I compress?
25.9 Compressing objects the old-fashioned way
25.10 dbatools to the rescue!
25.11 Specifying the compression level
25.12 Advanced settings
25.13 Hands-on lab
26 Validating your estate with dbachecks
26.1 What dbachecks and dbatools have in common
26.2 Our first check
26.3 Viewing all available checks
26.4 Configuring the check parameters
26.5 Storing the output data in a database
26.6 Hands-on lab
27.1 Connecting to Azure
27.2 Service principals and access tokens
27.3 Supported commands
27.4 The future
27.5 Hands-on lab
28 dbatools configurations and logging
28.1 Working with the configuration system
Checking existing configurations
Getting a specific configuration
Changing a configuration value
Resetting to default configuration values
28.2 Taking the configs with you
28.4 Exploring logged activity
28.5 Hands-on lab
29.1 Use dbatools
29.2 More PowerShell
29.4 Farewell