Learn dbatools in a Month of Lunches Automating SQL server tasks with PowerShell commands 1st Edition by Chrissy Lemaire, Rob Sewell, Claudio Silva, Jess Pomfret 1617296708 9781617296703

Learn dbatools in a Month of Lunches Automating SQL server tasks with PowerShell commands 1st Edition by Chrissy Lemaire, Rob Sewell, Claudio Silva, Jess Pomfret – Ebook PDF Instant Download/Delivery: 1617296708, 9781617296703

Full download Learn dbatools in a Month of Lunches Automating SQL server tasks with PowerShell commands 1st Edition after payment

Product details:

ISBN 10: 1617296708

ISBN 13: 9781617296703

Author: Chrissy Lemaire, Rob Sewell, Cláudio Silva, Jess Pomfret

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. Table of Contents 1 Before you begin 2 Installing dbatools 3 The dbatools lab 4 A gentle introduction to dbatools commands 5 Writing to SQL Server 6 Finding SQL Server instances on your network 7 Inventorying your SQL estate 8 Registered Servers 9 Logins and users 10 Backups 11 Restore 12 Snapshots 13 Install and update SQL Server 14 Preparing for disaster 15 Performing your first advanced SQL Server instance migration, part 1 16 Performing your first advanced SQL Server instance migration, part 2 17 High availability and disaster recovery 18 PowerShell and SQL Server Agent 19 SQL Server Agent administration 20 Creating and working with SQL Server Agent objects 21 Data masking 22 DevOps automation 23 Tracing SQL Server activity 24 Security and encryption 25 Data compression 26 Validating your estate with dbachecks 27 Working in the cloud 28 dbatools configurations and logging 29 Never the end

Learn dbatools in a Month of Lunches Automating SQL server tasks with PowerShell commands 1st Table of contents:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

29 Never the end

29.1 Use dbatools

29.2 More PowerShell

29.3 Contribute to dbatools

29.4 Farewell

People also search for Learn dbatools in a Month of Lunches Automating SQL server tasks with PowerShell commands 1st: 

how to write automation in ableton

how to create automation in ableton

how to curve automation in ableton

learn dbatools in a month of lunches

Tags:

Chrissy Lemaire,Rob Sewell,Cláudio Silva,Jess Pomfret,dbatools,Automating