If you work with SQL Server, dbatools is a lifesaver. This book will show you how to use this free and open source PowerShell module to automate just about every SQL server task you can imagine—all in just one month!
In Learn dbatools in a Month of Lunches you will learn how to:
• Perform instance-to-instance and customized migrations
• Automate security audits, tempdb configuration, alerting, and reporting
• Schedule and monitor PowerShell tasks in SQL Server Agent
• Bulk-import any type of data into SQL Server
• Install dbatools in secure environments
Written by a group of expert authors including dbatools creator Chrissy LeMaire, Learn dbatools in a Month of Lunches teaches you techniques that will make you more effective—and efficient—than you ever thought possible. In twenty-eight lunchbreak lessons, you’ll learn the most important use cases of dbatools and the favorite functions of its core developers. Stabilize and standardize your SQL server environment, and simplify your tasks by building automation, alerting, and reporting with this powerful tool.
About the technology
For SQL Server DBAs, automation is the key to efficiency. Using the open-source dbatools PowerShell module, you can easily execute tasks on thousands of database servers at once—all from the command line. dbatools gives you over 500 pre-built commands, with countless new options for managing SQL Server at scale. There’s nothing else like it.
About the book
Learn dbatools in a Month of Lunches teaches you how to automate SQL Server using the dbatools PowerShell module. Each 30-minute lesson introduces a new automation that will make your daily duties easier. Following the expert advice of dbatools creator Chrissy LeMaire and other top community contributors, you’ll learn to script everything from backups to disaster recovery.
What's inside
• Performing instance-to-instance and customized migrations
• Automating security audits, best practices, and standardized configurations
• Administering SQL Server Agent including running PowerShell scripts effectively
• Bulk-importing many types of data into SQL Server
• Executing advanced tasks and increasing efficiency for everyday administration
About the reader
For DBAs, accidental DBAs, and systems engineers who manage SQL Server.
About the author
Chrissy LeMaire is a GitHub Star and the creator of dbatools. Rob Sewell is a data engineer and a passionate automator. Jess Pomfret and Cláudio Silva are data platform architects. All are Microsoft MVPs.
Author(s): Chrissy LeMaire, Rob Sewell, Cláudio Silva, Jess Pomfret
Edition: 1
Publisher: Manning Publications
Year: 2022
Language: English
Commentary: Vector PDF
Pages: 400
City: Shelter Island, NY
Tags: DevOps; Databases; Security; PowerShell; SQL; Relational Databases; Encryption; Microsoft SQL Server; Automation; dbatools; Shell Scripting
Learn dbatools in a Month of Lunches
contents
foreword
preface
acknowledgments
about this book
Who should read this book
How this book is organized: A road map
About the code
liveBook discussion forum
about the authors
Chapter 1: Before you begin
1.1 Why data professionals can’t afford to ignore PowerShell
1.1.1 A SQL Server DBA first win with PowerShell
1.2 Automate it
1.3 What is dbatools?
1.4 Is this book for you?
1.5 How to use this book
1.5.1 The main chapters
1.5.2 Hands-on labs
1.5.3 Supplementary materials
1.5.4 Further exploration
1.6 Contacting us
1.7 Being immediately effective with dbatools
Chapter 2: Installing dbatools
2.1 Minimum requirements
2.1.1 Server
2.1.2 Workstation
2.1.3 Ports
2.1.4 Execution policy
2.2 Signed software
2.3 Understanding installation paths
2.4 Installation methods
2.4.1 The PowerShell Gallery
2.4.2 Trusting the PowerShell Gallery
2.4.3 Installing dbatools using the PowerShell Gallery, all users
2.4.4 PowerShell Gallery, local user
2.4.5 PowerShell Gallery, offline install
2.5 PowerShell Gallery alternatives
2.5.1 Downloading a zipped archive
2.5.2 Additional methods
2.6 How to find and use commands, the help system, and docs.dbatools.io
2.6.1 Get-Command
2.6.2 Find-DbaCommand
2.6.3 Get-Help
2.6.4 docs.dbatools.io
2.7 Updating
2.7.1 PowerShell Gallery
2.7.2 Alternative methods
2.8 Hands-on lab
Chapter 3: The dbatools lab
3.1 Why is a lab included in this book?
3.2 Two options for building a dbatools lab environment
3.3 Option 1: Windows lab
3.3.1 Installation media for our lab
3.3.2 Building the lab
3.3.3 Configuration scripts
3.3.4 Windows lab is ready for action
3.4 Option 2: Quick demo environments using containers
3.4.1 Running SQL Server in a container
Chapter 4: A gentle introduction to dbatools commands
4.1 Getting started
4.2 Checking the SQL connection
4.3 First, getting help
4.4 Running your first dbatools command
4.5 The -SqlInstance parameter
4.5.1 Single instances
4.5.2 Multiple instances
4.6 The -SqlCredential parameter
4.6.1 Connecting to instances with SQL Server Authentication
4.6.2 Saving the credential to use SQL Server Authentication with multiple commands
4.6.3 Other methods of using credentials for SQL Server Authentication
4.6.4 Connecting to instances with a different Windows account
4.7 The ComputerName parameter
4.7.1 Methods of listing the SQL services on multiple servers
4.8 The -Credential parameter
4.8.1 Listing services on a server using a different account at the command line
4.8.2 Listing services on a server using a different account with a credential variable
4.8.3 Listing SQL services by type
4.9 Bonus parameter: EnableException
4.10 Hands-on lab
Chapter 5: Writing to SQL Server
5.1 Piping commands
5.2 Writing to a database
5.2.1 Importing from a CSV file to a database table
5.2.2 Importing to a database table from a dbatools command
5.2.3 Creating the database table first and then importing from a CSV file
5.2.4 Writing the results of other commands to a table
5.2.5 Writing the results of other commands to an Azure SQL Database
5.3 Copying tables, including their data
5.3.1 PowerShell splatting
5.4 Hands-on lab
Chapter 6: Finding SQL Server instances on your network
6.1 Background
6.1.1 Finding an instance
6.1.2 Finding instances using a list of targets
6.1.3 Finding SQL Servers in an Active Directory domain
6.1.4 Finding SQL Servers in your surrounding network
6.2 Working with detailed results
6.3 OS support
6.4 Hands-on lab
Chapter 7: Inventorying your SQL estate
7.1 SQL features
7.2 Build
7.3 Host information
7.4 Databases
7.4.1 Filtering databases returned from Get-DbaDatabase
7.4.2 Filtering databases returned from Get-DbaDatabase by last backup time
7.5 Putting it all together into a database
7.6 Hands-on lab
Chapter 8: Registered Servers
8.1 Local Server Groups
8.1.1 Version-specific RegSrvr.xml files
8.2 Azure Data Studio
8.3 Central Management Server
8.4 Inventory organization
8.4.1 Importing advanced environment folder structures
8.5 Further integration
8.5.1 Adding new Registered Servers
8.5.2 Copy, Export, Import
8.5.3 Moving Registered Servers
8.5.4 Removing Registered Servers
8.6 Registered Server groups
8.7 Hands-on lab
Chapter 9: Logins and users
9.1 Failed logins
9.2 Preventing login issues
9.3 Logins, users, and permissions source control
9.4 How was access gained?
9.4.1 Finding nested Active Directory group access
9.5 Hands-on lab
Chapter 10: Backups
10.1 Creating backups
10.1.1 Azure
10.1.2 Docker
10.2 Reading backup files
10.3 Backup history
10.4 Pruning old backup files
10.5 Testing your backups
10.6 Hands-on lab
Chapter 11: Restore
11.1 Limitations and considerations
11.2 Restore scenarios
11.2.1 File
11.2.2 Directory
11.2.3 Output T-SQL restore scripts
11.3 Restoring to custom data and log directories
11.3.1 No recovery
11.3.2 Renaming a database
11.3.3 Point-in-time restores
11.3.4 Restoring to a marked transaction
11.3.5 Recovering a corrupt database
11.4 Azure
11.4.1 Shared access signatures
11.4.2 Access keys
11.5 Hands-on lab
Chapter 12: Snapshots
12.1 Snapshots and SSMS
12.2 Application upgrade
12.3 When to use snapshots
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
Chapter 13: Install and update SQL Server
13.1 Installing
13.1.1 Benefits of automated installs
13.1.2 Local installs
13.1.3 Remote installs
13.1.4 Customizing installation options
13.1.5 ConfigurationFile and Configuration
13.1.6 Built-in parameters
13.2 Updating
13.3 The importance of patching
13.3.1 Fear of breaking everything
13.3.2 Burdensome process leads to procrastination
13.4 How we make it easier
13.5 Hands-on lab
Chapter 14: Preparing for disaster
14.1 Exporting an entire instance
14.1.1 Scripting options
14.1.2 Setting scripting options
14.1.3 Excluding objects
14.2 Granular exports
14.2.1 Using Export-DbaScript
14.3 Special commands
14.4 Exporting server configurations (sp_configure)
14.5 Hands-on lab
Chapter 15: Performing your first advanced SQL Server instance migration, part 1
15.1 Databases
15.1.1 Backup and restore
15.1.2 Detach and attach
15.1.3 Staging large databases for migration
15.1.4 Other database migration options
15.2 Hands-on lab
Chapter 16: Performing your first advanced SQL Server instance migration, part 2
16.1 Logins and groups
16.1.1 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
Chapter 17: High availability and disaster recovery
17.1 Log shipping
17.1.1 Configuring log shipping with dbatools
17.1.2 When log shipping goes bad: Gathering errors with dbatools
17.1.3 Cutting over to a log shipped secondary database
17.2 Windows Server Failover Cluster (WSFC)
17.3 Availability groups
17.3.1 Creating an availability group with dbatools
17.3.2 Explore existing availability groups
17.3.3 Managing existing AGs
17.4 Hands-on lab
Chapter 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
18.2.1 Creating a SQL Server credential
18.2.2 Creating a SQL Server Agent proxy
18.2.3 The PowerShell file
18.3 Creating the SQL Server Agent job with a CmdExec job step
18.4 Tips
18.4.1 Using default parameter values
18.4.2 Ensuring that the Agent job fails when the PowerShell fails
18.4.3 Logging
18.4.4 Execution policies
18.5 Hands-on lab
Chapter 19: SQL Server Agent administration
19.1 Listing SQL Server Agent information
19.1.1 SQL Server Agent jobs
19.1.2 SQL Server Agent alerts
19.1.3 Finding specific Agent jobs
19.2 Agent job results and history
19.2.1 Agent job results
19.2.2 Time line
19.3 Hands-on lab
Chapter 20: Creating and working with SQL Server Agent objects
20.1 SQL Server Agent job creation
20.1.1 Creating categories
20.1.2 New schedule
20.1.3 New proxy
20.1.4 Create a new operator
20.1.5 Create a new Agent job
20.1.6 The job step
20.2 Bonus Agent job commands
20.2.1 Start-DbaAgentJob
20.2.2 Get-DbaRunningJob
20.2.3 Get-DbaAgentJobHistory
20.3 Hands-on lab
Chapter 21: Data masking
21.1 Getting started
21.2 A common approach
21.3 The better approach
21.3.1 Generating random data
21.4 The process
21.4.1 Finding potential PII data
21.4.2 Generating a configuration file for masking
21.4.3 Applying static data masking
21.4.4 Validating a data masking configuration file
21.5 Hands-on lab
Chapter 22: DevOps automation
22.1 When should you use dbatools in DevOps?
22.2 DACPAC
22.2.1 Exporting a DACPAC from an existing database
22.2.2 Publishing a DACPAC
22.2.3 DACPAC options
22.3 Running dbatools (and PowerShell) on a CI/CD system
22.3.1 Creating a task
22.3.2 Ensuring the dbatools module is available
22.3.3 Understanding how to add parameters to the script
22.4 Hands-on lab
Chapter 23: Tracing SQL Server activity
23.1 SQL Server Trace and SQL Profiler
23.1.1 Converting traces to Extended Events
23.2 Extended Events
23.2.1 SSMS support
23.2.2 dbatools support
23.2.3 Finding Extended Events
23.2.4 Using templates
23.2.5 Starting and stopping Extended Event sessions
23.2.6 Reading data
23.2.7 Replicating Extended Event sessions to multiple instances
23.2.8 Cleanup
23.3 Hands-on lab
Chapter 24: Security and encryption
24.1 Encrypting network connections
24.1.1 Certificate
24.1.2 Forcing encryption
24.2 Extended protection for authentication
24.3 Hide an instance
24.4 Transparent data encryption (TDE)
24.4.1 Encrypting databases
24.4.2 Decrypting databases
24.5 Database backup encryption
24.5.1 Prerequisites
24.5.2 Backing up the database with a certificate
24.5.3 Checking encryption information from the backup
24.6 Multilayered security
24.7 Hands-on lab
Chapter 25: Data compression
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.6 What should we compress?
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
Chapter 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.5.1 Storing data
26.5.2 Power BI dashboard
26.5.3 Configuring the connection
26.6 Hands-on lab
Chapter 27: Working in the cloud
27.1 Connecting to Azure
27.2 Service principals and access tokens
27.2.1 Using Az.Accounts
27.3 Supported commands
27.4 The future
27.5 Hands-on lab
Chapter 28: dbatools configurations and logging
28.1 Working with the configuration system
28.1.1 Checking existing configurations
28.1.2 Getting a specific configuration
28.1.3 Getting just the value
28.1.4 Changing a configuration value
28.1.5 Resetting to default configuration values
28.2 Taking the configs with you
28.3 Using the logging system
28.4 Exploring logged activity
28.4.1 Ongoing logging
28.5 Hands-on lab
Chapter 29: Never the end
29.1 Use dbatools
29.2 More PowerShell
29.3 Contribute to dbatools
29.4 Farewell
index
Symbols
A
B
C
D
E
F
G
H
I
J
L
M
N
O
P
R
S
T
U
V
W
