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

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