| Learn dbatools in a Month of Lunches |
|
Author: Chrissy LeMaire et al This book aims to make it easier to manage your SQL Server estate, how does it fare?
DBAs often need to manage many SQL Servers, so a tool that provides automation should help get the work done faster, consistently, and with less errors. PowerShell is often the preferred automation tool, however, many DBAs have been reluctant to learn it. It is hoped that the amount of functionality in the dbatools PowerShell module, and its ease of use, will persuade these DBAs to use PowerShell. dbatools is a Powershell module containing more than 600 commands, covering a wide range of functionality (e.g. installation, backups, migrations, AlwaysOn, best practices). The lead author is the creator of dbatools. The tool helps the administration of SQL Server estates, through discovery and automation. It’s especially useful for large estates. It is possible to gather info about your entire SQL Server estate with a few lines of code. The book is aimed at SQL Server DBAs that are comfortable with DBA Admin tasks (e.g. backups), so assumes some DBA admin knowledge. The book does not assume any knowledge of PowerShell. Completing a daily 30-minute lesson, should enable you to learn dbatools in a month of lunches. Below is an exploration of the topics covered. 
The book opens with a look at how PowerShell can save you time, reduce errors, and provide consistency in automating admin tasks when compared to using SSMS (GUI menu options or T-SQL). dbatools is then examined, it’s a PowerShell module aiming to automate and simplify many common admin tasks. It integrates with many popular components, including Ola Hallengren’s maintenance scripts, who_isactive, and Brent Ozar’s utilities. The book is task-oriented, and you’ll learn PowerShell incidentally as you work with dbatools. Having described the advantages of using dbatools, we move on to installing dbatools. Various means of installation are described, with powershellgallery.com being the preferred method. Other methods are covered, including a manual download. To get up and running, some incidental topics are covered, including signed software, installation paths, and execution policy. For me, installation is always a critical topic and should always be covered in detail, since if you can’t install the product or if you have problems that are not described, you’re likely to abandon the product. There’s a very useful introduction to the detailed help system, where you can easily find commands and see example usage (e.g. Get-Help Test-DbaConnection). There’s a link to the extensive online documentation at docs.dbatools.io. Most chapters end with a Hands-on lab, where tasks relevant to the chapter are posed. You typically install dbatools on a few servers, and then connect to, and query, other servers remotely. Next, there’s a gentle introduction to dbatools commands. Firstly looking at read-only commands (e.g. Test-DbaConnection -SqlInstance $Env:ComputerName - will connect to the SQL Server instance on your computer, and return lots of connection info). It’s possible to specify multiple instances, and a SqlCredential. Various authentication methods are discussed with examples (the book is replete with example code/outputs). There’s a useful example showing what SQL services are on multiple servers. Displaying output is useful, but storing the results to SQL Server is often better. Examples are provided to import data from a csv file into a database table, this can also be done for Azure SQL Databases. PowerShell ‘splatting’ is described as a way to group related parameters into a variable, that is then fed into the dbatools command. Perhaps the most intriguing part of the book now unfolds, how to find all the SQL Server instances on your network. A deceptively simple command (Find-DbaInstance) is used with various parameters to look for SQL Server instances using a variety of methods – some of which are time consuming (warning: remember to notify your security team, else they might think there is a system intrusion!). Having discovered your SQL Server instances, we move naturally on to creating an inventory of your SQL Server estate. Examples are provided to store to a database:
This information is stored in separate tables, and can be used subsequently to drive changes (e.g. patching, version updates, verify full backup been taken, any errors reported, etc). dbatools can make good use of registered servers. The list of registered servers can be piped (i.e. used as input) into most dbatools commands, making enterprise-based work much easier. For example, to get version info for auditors, you can simply use: Get-DbaRegServer | Invoke-DbaQuery -Query “SELECT @@SERVERNAME, @@VERSION” Logins can be added using New-DbaLogin, this is often followed with New-DbaUser. A common problem when restoring a database to another server is orphaned users (where the user exists on the server, but its internal id doesn’t match that on the restored database), this can be observed using Get-DbaOrphanUser, and fixed using Repair-DbaOrphanUser. It’s also possible to find users within nested AD groups. Backups and restores are perhaps the most critical topics for the DBA. dbatools recommends using Ola Hallengren’s maintenance solution, and this can be installed via Install-DbaMaintenanceSolution. One or more databases can be backed up using Backup-DbaDatabase, and history can be examined using Get-DbaBackupHistory. Backups can be tested using Test-DbaLastBackup (this gets the most recent full/differential/log backups, restores the full chain of backups to a newly created database, runs DBCC CHECKDB, and then drops that database – quite an impressive command!). Similarly, database restores are well catered for, using Restore-DbaDatabase. It is possible to restore all the databases whose backup files are given in a folder. All the usual restore parameters are applicable (e.g. NoRecovery, RestoreTime, StopMark). There’s a very useful example given, of recovering a corrupt database, by replacing only the suspect pages with pages from a restore. Snapshots can be useful if you want to rollback data and schema changes quickly (rather than create backup, apply application changes, notice a problem, then restore the backup). Snapshots also provide point-in-time reporting. A new snapshot can be created with New-DbaCreateSnapshot. I hope you’re noticing how easy it is to guess the name of the relevant dbatools command to use… Installing and updating SQL Server can be a time-consuming exercise. Automation helps keep your SQL Server estate up to date. Installation uses Install-DbaInstance, and updating uses Update-DbaInstance. In both cases many parameters can be provided (alternatively, a config file can be used). It’s easy to download patches, have remote patching, and detect which servers need patching. Another important topic for the DBA is preparing for disaster (e.g. fire in a data center). dbatools allows you to export an entire instance, using Export-DbaInstance. This produces scripts for databases, logins, linked servers, credentials, agent jobs, proxies, alerts, config settings etc. It doesn’t create database backups, but it does produce scripts to restore databases using the latest full/differential/log backups. Useful filtering options are provided (e.g. exclude Linked Servers). SQL Server instance migration is often the primary reason DBAs start to use dbatools. While many parameters can be used, basic migration starts with just 4 parameters (e.g. Start-DbaMigration -Source sql01 -Destination sql02 -BackupRestore -SharedPath \\nas\sql\migration). Various methods for migration are possible, including: log shipping, backup and restore, detach and attach. Backup and restore is a common migration method, especially for smaller databases. Invoke-DbaDbLogShipping and Invoke-DbaDbLogShipRecovery provide a simple means of implementing log shipping and failover. This is followed by a look at migrating objects other than the database (e.g. logins, groups, SQL Agent jobs, operators, linked servers etc). Ensuring your systems are available soon after a problem is immensely important. Localised/recoverable errors (e.g. disk failure) are typically covered by High Availability (HA), whereas, larger problems (e.g. fire in a data center) are covered by Disaster Recovery (DR). dbatools has functionality to manage Log Shipping, Windows Server Failover Cluster (WSFC), and Availability Groups (AG). As is typical of dbatools, it makes work much easier – but be aware of some subtleties (e.g. in Log Shipping, after the recovery/failover, the primary database isn’t taken offline). Next, there are several chapters relating to SQL Agent. This is to be expected since it’s SQL Server’s primary scheduling/automation subsystem. There’s a useful discussion on using the OS CmdExec job type to execute PowerShell scripts, instead of the PowerShell type (the latter uses SQLPS library which has much less functionality). The various steps to run a dbatools job are discussed with examples. There are some very useful tips (e.g. use the -EnableException param to ensure any failure is propagated from PowerShell to SQL Agent). There are useful commands to explore SQL Server Agent, jobs, alerts, and finding specific jobs (i.e. Find-DbaAgentJob -SqlInstance sqi01 -Jobname *textToFind*). Perhaps illustrating the minutiae of dbatools, there’s a section on Data Masking. Data protection, security, and regulation, all show the growing importance of protecting sensitive information. dbatools can generate random data and update Personal Identifying Information (PII). It is possible to discover potential PII data (Invoke-DbaDbPiiScan), create a config file for masking data (New-DbaDbMaskingConfig), apply the data masking (Invoke-DbaDbDataMasking), and validate the data masking configuration file (Test-DbaDbDataMaskingConfig). DevOps is a meld of people, processes, and technology – with a very flexible definition. Various suggestions are given for using dbatools in DevOps (e.g. create new table, run unit tests, deploy package to database etc). As a more detailed example, the use of DACPAC, a common way to deploy a database, is examined. Using dbatools with Continuous Development / Continuous Integration (CD/CI) is discussed in outline, since it typically depends on the vendor. You need to ensure dbatools is available to the vendor software (typically, as a plug-in). Tracing SQL Server activity is essential for helping debug problems. SQL Server profiler and trace have been deprecated for a while. Extended Events (XEs) are the replacement for profiler and trace, they’re more lightweight and monitor more events - however some DBAs have been reluctant to use them. Commands exist to get/start/stop/remove traces and XEs. There’s a very useful dbatools command to convert your trace definitions in to XEs (ConvertTo-DbaXESession). SQL Server security and encryption are well catered for in dbatools. Features include encrypting network connection (between instance and client), using public keys and certificates. Transparent data encryption (TDE) protects data-at-rest, and database backup encryption provides another layer of protection. It’s recommended to implement multiple layers of security (defense in depth) as a way of reducing the risk of security breaches. Next, there’s another section covering data compression, another niche topic. Often, I/O is a major performance concern with SQL Server, causing queries to take longer to run. Compression can often reduce I/O, especially for queries that scan many rows. This section concentrates on rowstore compression. It’s noted that compression can lead to more CPU usage, especially for page compression. There’s a VERY useful command (Test-DbaDbCompression), that calculates the balance of I/O improvement against more CPU usage, and makes recommendations for compression (this is based on work from the SQL Server Tiger Team). Compressing using the SSMS GUI interface is relatively slow, since you compress a table or index individually, the command Set-DbaDbCompression can compress all the tables/indexes in one go. All DBAs probably have scripts to perform SQL Server health checks. While not part of dbatools, the associated PowerShell library dbaChecks was written by the dbatools team and make extensive use of dbatools. Many checks can be made (e.g. does instance use latest patch). There’s a config file that contains default values (e.g. report if last full backup is within last 24 hours), and this can be edited to your needs. Storing the output data in a database is useful for discerning trends, and an example is provided linking the output to a Power BI dashboard, giving an impressive visual presentation (Start-DbcPowerBI -FromDatabase). The dbaChecks functionality alone is an excellent reason to start using PowerShell/dbatools. With increasing numbers of businesses moving to the cloud, you might expect dbatools to cater for this, however, it currently has limited support. That said, the product is still developing, with new and updated features added continuously. It’s suggested dbatools will make use of the existing Microsoft Az.sql module. Examples are provided on how to connect to Azure, involving service principals and access tokens (service principals are like on-prem service accounts, and access tokens are like one-time passwords). There’s a brief look at dbatools configurations and logging, helpful for debugging how it does its work, and potentially useful in debugging any dbatools problems. It is possible to take your custom config files with you, to provide a standard approach across SQL Server estates (Get-DbatoolsConfig | Export-DbatoolsConfig -Outpath c:\temp\abc.json, and Import-DbatoolsConfig). The book ends with a reminder that dbatools is a work in progress, but it already has 600+ commands you can explore. These commands should make it easier to automate the management of your SQL Server estate. Hopefully, using dbatools will encourage DBAs to explore PowerShell more widely, and some useful book references are included. On a final note, the authors suggest you contribute to the dbatools module too, this might be as simple as documenting addition examples of its usage. Conclusion This book aims to make it easier to manage your SQL Server estate, and certainly succeeds. The book provides a gentle introduction to using the dbatools PowerShell module. It is generally easy to read, with good explanations, and a bountiful supply of excellent code examples provided throughout. It covers a lot of ground, both in range and depth (e.g. installation, upgrades, patching, migration, backups, AlwaysOn, health checks). Many of the chapters build on previous ones, giving progressively more understanding. Although no knowledge of PowerShell is needed to start the book, an understanding of SQL Server admin is required (but some concepts are outlined). I suspect if PowerShell-reluctant DBAs start using dbatools, it would make their lives much easier, with plenty of automation, and open a world of possible improvements in their job. If you want to make your DBA work easier and more interesting, you should get this book. Highly recommended. To keep up with our coverage of books for programmers, follow @bookwatchiprog on Twitter or subscribe to I Programmer's Books RSS feed for each day's new addition to Book Watch and for new reviews.
|
|||
| Last Updated ( Tuesday, 12 September 2023 ) |