Learn dbatools in a Month of Lunches

Learn dbatools in a Month of Lunches

by Rob Sewell, Chrissy LeMaire, Jess Pomfret, Cláudio Silva
Released July 2022
Publisher(s): Manning Publications
ISBN: 9781617296703

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

Quotes
All SQL Server professionals should learn dbatools. With its combination of knowledge transfer, anecdotes, and hands-on labs, this book is the perfect way.
- From the Foreword by Anna Hoffman, Databases Product Management, Microsoft

Excellent guide for dbatools with lots of practical tips! Required reading for anyone interested in dbatools.
- Ruben Vandeginste, PeopleWare

A must-have for any SQL server developer.
- Raushan Kumar Jha, Microsoft

If you want to automate all vital aspects of SQL Server, wait no more! Learn dbatools in a month, with guidance from the best minds in the business.
- Ranjit Sahai, RAM Consulting

Table of contents

  1. inside front cover
  2. Learn dbatools in a Month of Lunches
  3. Copyright
  4. dedication
  5. contents
  6. front matter
    1. foreword
    2. preface
    3. acknowledgments
    4. about this book
      1. Who should read this book
      2. How this book is organized: A road map
      3. About the code
      4. liveBook discussion forum
    5. about the authors
  7. 1 Before you begin
    1. 1.1 Why data professionals can’t afford to ignore PowerShell
      1. 1.1.1 A SQL Server DBA first win with PowerShell
    2. 1.2 Automate it
    3. 1.3 What is dbatools?
    4. 1.4 Is this book for you?
    5. 1.5 How to use this book
      1. 1.5.1 The main chapters
      2. 1.5.2 Hands-on labs
      3. 1.5.3 Supplementary materials
      4. 1.5.4 Further exploration
    6. 1.6 Contacting us
    7. 1.7 Being immediately effective with dbatools
  8. 2 Installing dbatools
    1. 2.1 Minimum requirements
      1. 2.1.1 Server
      2. 2.1.2 Workstation
      3. 2.1.3 Ports
      4. 2.1.4 Execution policy
    2. 2.2 Signed software
    3. 2.3 Understanding installation paths
    4. 2.4 Installation methods
      1. 2.4.1 The PowerShell Gallery
      2. 2.4.2 Trusting the PowerShell Gallery
      3. 2.4.3 Installing dbatools using the PowerShell Gallery, all users
      4. 2.4.4 PowerShell Gallery, local user
      5. 2.4.5 PowerShell Gallery, offline install
    5. 2.5 PowerShell Gallery alternatives
      1. 2.5.1 Downloading a zipped archive
      2. 2.5.2 Additional methods
    6. 2.6 How to find and use commands, the help system, and docs.dbatools.io
      1. 2.6.1 Get-Command
      2. 2.6.2 Find-DbaCommand
      3. 2.6.3 Get-Help
      4. 2.6.4 docs.dbatools.io
    7. 2.7 Updating
      1. 2.7.1 PowerShell Gallery
      2. 2.7.2 Alternative methods
    8. 2.8 Hands-on lab
  9. 3 The dbatools lab
    1. 3.1 Why is a lab included in this book?
    2. 3.2 Two options for building a dbatools lab environment
    3. 3.3 Option 1: Windows lab
      1. 3.3.1 Installation media for our lab
      2. 3.3.2 Building the lab
      3. 3.3.3 Configuration scripts
      4. 3.3.4 Windows lab is ready for action
    4. 3.4 Option 2: Quick demo environments using containers
      1. 3.4.1 Running SQL Server in a container
  10. 4 A gentle introduction to dbatools commands
    1. 4.1 Getting started
    2. 4.2 Checking the SQL connection
    3. 4.3 First, getting help
    4. 4.4 Running your first dbatools command
    5. 4.5 The -SqlInstance parameter
      1. 4.5.1 Single instances
      2. 4.5.2 Multiple instances
    6. 4.6 The -SqlCredential parameter
      1. 4.6.1 Connecting to instances with SQL Server Authentication
      2. 4.6.2 Saving the credential to use SQL Server Authentication with multiple commands
      3. 4.6.3 Other methods of using credentials for SQL Server Authentication
      4. 4.6.4 Connecting to instances with a different Windows account
    7. 4.7 The ComputerName parameter
      1. 4.7.1 Methods of listing the SQL services on multiple servers
    8. 4.8 The -Credential parameter
      1. 4.8.1 Listing services on a server using a different account at the command line
      2. 4.8.2 Listing services on a server using a different account with a credential variable
      3. 4.8.3 Listing SQL services by type
    9. 4.9 Bonus parameter: EnableException
    10. 4.10 Hands-on lab
  11. 5 Writing to SQL Server
    1. 5.1 Piping commands
    2. 5.2 Writing to a database
      1. 5.2.1 Importing from a CSV file to a database table
      2. 5.2.2 Importing to a database table from a dbatools command
      3. 5.2.3 Creating the database table first and then importing from a CSV file
      4. 5.2.4 Writing the results of other commands to a table
      5. 5.2.5 Writing the results of other commands to an Azure SQL Database
    3. 5.3 Copying tables, including their data
      1. 5.3.1 PowerShell splatting
    4. 5.4 Hands-on lab
  12. 6 Finding SQL Server instances on your network
    1. 6.1 Background
      1. 6.1.1 Finding an instance
      2. 6.1.2 Finding instances using a list of targets
      3. 6.1.3 Finding SQL Servers in an Active Directory domain
      4. 6.1.4 Finding SQL Servers in your surrounding network
    2. 6.2 Working with detailed results
    3. 6.3 OS support
    4. 6.4 Hands-on lab
  13. 7 Inventorying your SQL estate
    1. 7.1 SQL features
    2. 7.2 Build
    3. 7.3 Host information
    4. 7.4 Databases
      1. 7.4.1 Filtering databases returned from Get-DbaDatabase
      2. 7.4.2 Filtering databases returned from Get-DbaDatabase by last backup time
    5. 7.5 Putting it all together into a database
    6. 7.6 Hands-on lab
  14. 8 Registered Servers
    1. 8.1 Local Server Groups
      1. 8.1.1 Version-specific RegSrvr.xml files
    2. 8.2 Azure Data Studio
    3. 8.3 Central Management Server
    4. 8.4 Inventory organization
      1. 8.4.1 Importing advanced environment folder structures
    5. 8.5 Further integration
      1. 8.5.1 Adding new Registered Servers
      2. 8.5.2 Copy, Export, Import
      3. 8.5.3 Moving Registered Servers
      4. 8.5.4 Removing Registered Servers
    6. 8.6 Registered Server groups
    7. 8.7 Hands-on lab
  15. 9 Logins and users
    1. 9.1 Failed logins
    2. 9.2 Preventing login issues
    3. 9.3 Logins, users, and permissions source control
    4. 9.4 How was access gained?
      1. 9.4.1 Finding nested Active Directory group access
    5. 9.5 Hands-on lab
  16. 10 Backups
    1. 10.1 Creating backups
      1. 10.1.1 Azure
      2. 10.1.2 Docker
    2. 10.2 Reading backup files
    3. 10.3 Backup history
    4. 10.4 Pruning old backup files
    5. 10.5 Testing your backups
    6. 10.6 Hands-on lab
  17. 11 Restore
    1. 11.1 Limitations and considerations
    2. 11.2 Restore scenarios
      1. 11.2.1 File
      2. 11.2.2 Directory
      3. 11.2.3 Output T-SQL restore scripts
    3. 11.3 Restoring to custom data and log directories
      1. 11.3.1 No recovery
      2. 11.3.2 Renaming a database
      3. 11.3.3 Point-in-time restores
      4. 11.3.4 Restoring to a marked transaction
      5. 11.3.5 Recovering a corrupt database
    4. 11.4 Azure
      1. 11.4.1 Shared access signatures
      2. 11.4.2 Access keys
    5. 11.5 Hands-on lab
  18. 12 Snapshots
    1. 12.1 Snapshots and SSMS
    2. 12.2 Application upgrade
    3. 12.3 When to use snapshots
    4. 12.4 Creating a snapshot
    5. 12.5 Upgrading
    6. 12.6 Rolling back the entire database from a snapshot
    7. 12.7 Restoring certain objects or data from a snapshot
    8. 12.8 Cleaning up
    9. 12.9 Reporting
    10. 12.10 Hands-on lab
  19. 13 Install and update SQL Server
    1. 13.1 Installing
      1. 13.1.1 Benefits of automated installs
      2. 13.1.2 Local installs
      3. 13.1.3 Remote installs
      4. 13.1.4 Customizing installation options
      5. 13.1.5 ConfigurationFile and Configuration
      6. 13.1.6 Built-in parameters
    2. 13.2 Updating
    3. 13.3 The importance of patching
      1. 13.3.1 Fear of breaking everything
      2. 13.3.2 Burdensome process leads to procrastination
    4. 13.4 How we make it easier
    5. 13.5 Hands-on lab
  20. 14 Preparing for disaster
    1. 14.1 Exporting an entire instance
      1. 14.1.1 Scripting options
      2. 14.1.2 Setting scripting options
      3. 14.1.3 Excluding objects
    2. 14.2 Granular exports
      1. 14.2.1 Using Export-DbaScript
    3. 14.3 Special commands
    4. 14.4 Exporting server configurations (sp_configure)
    5. 14.5 Hands-on lab
  21. 15 Performing your first advanced SQL Server instance migration, part 1
    1. 15.1 Databases
      1. 15.1.1 Backup and restore
      2. 15.1.2 Detach and attach
      3. 15.1.3 Staging large databases for migration
      4. 15.1.4 Other database migration options
    2. 15.2 Hands-on lab
  22. 16 Performing your first advanced SQL Server instance migration, part 2
    1. 16.1 Logins and groups
      1. 16.1.1 Which logins/groups are still needed?
    2. 16.2 SQL Agent objects: Jobs, operators, and more!
    3. 16.3 Linked servers
    4. 16.4 More migration fun
    5. 16.5 Hands-on lab
  23. 17 High availability and disaster recovery
    1. 17.1 Log shipping
      1. 17.1.1 Configuring log shipping with dbatools
      2. 17.1.2 When log shipping goes bad: Gathering errors with dbatools
      3. 17.1.3 Cutting over to a log shipped secondary database
    2. 17.2 Windows Server Failover Cluster (WSFC)
    3. 17.3 Availability groups
      1. 17.3.1 Creating an availability group with dbatools
      2. 17.3.2 Explore existing availability groups
      3. 17.3.3 Managing existing AGs
    4. 17.4 Hands-on lab
  24. 18 PowerShell and SQL Server Agent
    1. 18.1 Which to choose, CmdExec or PowerShell job steps?
    2. 18.2 Creating Agent jobs to run PowerShell and dbatools
      1. 18.2.1 Creating a SQL Server credential
      2. 18.2.2 Creating a SQL Server Agent proxy
      3. 18.2.3 The PowerShell file
    3. 18.3 Creating the SQL Server Agent job with a CmdExec job step
    4. 18.4 Tips
      1. 18.4.1 Using default parameter values
      2. 18.4.2 Ensuring that the Agent job fails when the PowerShell fails
      3. 18.4.3 Logging
      4. 18.4.4 Execution policies
    5. 18.5 Hands-on lab
  25. 19 SQL Server Agent administration
    1. 19.1 Listing SQL Server Agent information
      1. 19.1.1 SQL Server Agent jobs
      2. 19.1.2 SQL Server Agent alerts
      3. 19.1.3 Finding specific Agent jobs
    2. 19.2 Agent job results and history
      1. 19.2.1 Agent job results
      2. 19.2.2 Time line
    3. 19.3 Hands-on lab
  26. 20 Creating and working with SQL Server Agent objects
    1. 20.1 SQL Server Agent job creation
      1. 20.1.1 Creating categories
      2. 20.1.2 New schedule
      3. 20.1.3 New proxy
      4. 20.1.4 Create a new operator
      5. 20.1.5 Create a new Agent job
      6. 20.1.6 The job step
    2. 20.2 Bonus Agent job commands
      1. 20.2.1 Start-DbaAgentJob
      2. 20.2.2 Get-DbaRunningJob
      3. 20.2.3 Get-DbaAgentJobHistory
    3. 20.3 Hands-on lab
  27. 21 Data masking
    1. 21.1 Getting started
    2. 21.2 A common approach
    3. 21.3 The better approach
      1. 21.3.1 Generating random data
    4. 21.4 The process
      1. 21.4.1 Finding potential PII data
      2. 21.4.2 Generating a configuration file for masking
      3. 21.4.3 Applying static data masking
      4. 21.4.4 Validating a data masking configuration file
    5. 21.5 Hands-on lab
  28. 22 DevOps automation
    1. 22.1 When should you use dbatools in DevOps?
    2. 22.2 DACPAC
      1. 22.2.1 Exporting a DACPAC from an existing database
      2. 22.2.2 Publishing a DACPAC
      3. 22.2.3 DACPAC options
    3. 22.3 Running dbatools (and PowerShell) on a CI/CD system
      1. 22.3.1 Creating a task
      2. 22.3.2 Ensuring the dbatools module is available
      3. 22.3.3 Understanding how to add parameters to the script
    4. 22.4 Hands-on lab
  29. 23 Tracing SQL Server activity
    1. 23.1 SQL Server Trace and SQL Profiler
      1. 23.1.1 Converting traces to Extended Events
    2. 23.2 Extended Events
      1. 23.2.1 SSMS support
      2. 23.2.2 dbatools support
      3. 23.2.3 Finding Extended Events
      4. 23.2.4 Using templates
      5. 23.2.5 Starting and stopping Extended Event sessions
      6. 23.2.6 Reading data
      7. 23.2.7 Replicating Extended Event sessions to multiple instances
      8. 23.2.8 Cleanup
    3. 23.3 Hands-on lab
  30. 24 Security and encryption
    1. 24.1 Encrypting network connections
      1. 24.1.1 Certificate
      2. 24.1.2 Forcing encryption
    2. 24.2 Extended protection for authentication
    3. 24.3 Hide an instance
    4. 24.4 Transparent data encryption (TDE)
      1. 24.4.1 Encrypting databases
      2. 24.4.2 Decrypting databases
    5. 24.5 Database backup encryption
      1. 24.5.1 Prerequisites
      2. 24.5.2 Backing up the database with a certificate
      3. 24.5.3 Checking encryption information from the backup
    6. 24.6 Multilayered security
    7. 24.7 Hands-on lab
  31. 25 Data compression
    1. 25.1 Types of compression
    2. 25.2 How does rowstore data compression work?
    3. 25.3 Why use data compression?
    4. 25.4 It can’t all be rainbows and unicorns: Compression drawbacks
    5. 25.5 What’s compressed?
    6. 25.6 What should we compress?
    7. 25.7 What makes a good candidate for compression?
    8. 25.8 dbatools, what should I compress?
    9. 25.9 Compressing objects the old-fashioned way
    10. 25.10 dbatools to the rescue!
    11. 25.11 Specifying the compression level
    12. 25.12 Advanced settings
    13. 25.13 Hands-on lab
  32. 26 Validating your estate with dbachecks
    1. 26.1 What dbachecks and dbatools have in common
    2. 26.2 Our first check
    3. 26.3 Viewing all available checks
    4. 26.4 Configuring the check parameters
    5. 26.5 Storing the output data in a database
      1. 26.5.1 Storing data
      2. 26.5.2 Power BI dashboard
      3. 26.5.3 Configuring the connection
    6. 26.6 Hands-on lab
  33. 27 Working in the cloud
    1. 27.1 Connecting to Azure
    2. 27.2 Service principals and access tokens
      1. 27.2.1 Using Az.Accounts
    3. 27.3 Supported commands
    4. 27.4 The future
    5. 27.5 Hands-on lab
  34. 28 dbatools configurations and logging
    1. 28.1 Working with the configuration system
      1. 28.1.1 Checking existing configurations
      2. 28.1.2 Getting a specific configuration
      3. 28.1.3 Getting just the value
      4. 28.1.4 Changing a configuration value
      5. 28.1.5 Resetting to default configuration values
    2. 28.2 Taking the configs with you
    3. 28.3 Using the logging system
    4. 28.4 Exploring logged activity
      1. 28.4.1 Ongoing logging
    5. 28.5 Hands-on lab
  35. 29 Never the end
    1. 29.1 Use dbatools
    2. 29.2 More PowerShell
    3. 29.3 Contribute to dbatools
    4. 29.4 Farewell
  36. index

Product information

  • Title: Learn dbatools in a Month of Lunches
  • Author(s): Rob Sewell, Chrissy LeMaire, Jess Pomfret, Cláudio Silva
  • Release date: July 2022
  • Publisher(s): Manning Publications
  • ISBN: 9781617296703