front matter

foreword

There are over 20 million active installs of Microsoft SQL Server, with continued growth month after month. The SQL Server/Azure Data Community also continues to grow, currently with over 77,000 members across more than 40 countries. The authors Chrissy LeMaire, Rob Sewell, Jess Pomfret, and Cláudio Silva have spent many years being very active in this community—teaching, sharing, and empowering. They have been part of the charge that turned a technology into a community.

A couple of years ago, one of the authors and the creator of dbatools, Chrissy LeMaire, came on my show, Data Exposed, to show data professionals how to automate disaster recovery in SQL Server with dbatools. I was immediately struck by Chrissy’s charisma and ability to take complex topics and make them simple. She also made it clear that dbatools was built by and for the community and is free: “Instance migrations and best practice implementations have never been safer, faster, or freer.” dbatools enables data professionals to automate SQL Server tasks with PowerShell. Not only that, but dbatools also has a rich ecosystem of contributors, tests, and resources to make sure you can learn, report bugs, and contribute.

dbatools is something that all SQL Server professionals should consider learning, and this book is the perfect way to learn, with its combination of knowledge transfer, anecdotes, and hands-on labs. Microsoft is so confident in dbatools’ value that it has sponsored the project through Microsoft’s Free and Open Source Software Fund. dbatools empowers you to leverage automation, so you can stop doing repetitive tasks and sleep easily knowing you have the best practices implemented. In the process of learning dbatools, you’ll also become comfortable with PowerShell, which will help you more generally as a technical professional whether you’re looking to work across databases, data engineering, and applications, or across operating systems, or even across on-premises and public cloud providers, like Microsoft Azure, which is covered in a later chapter.

In this book, you’ll first get oriented with dbatools and PowerShell, and you’ll set up a lab environment where you can get hands-on for free with these tools and SQL Server. I recommend following Chrissy, Rob, Jess, and Cláudio’s guidance of doing one chapter a day (and practicing). With each chapter, you’ll not only become more proficient in automation with dbatools and PowerShell, but you’ll also learn how to manage SQL Server in a secure and scalable manner from some of the top SQL Server experts in the world. I hope you enjoy this book.

—Anna Hoffman

Databases Product Management, Microsoft

preface

In 2014, I was tasked with migrating a SQL Server instance that held a ton of SharePoint databases. I dreaded the thought of performing such an involved process over and over and figured a PowerShell solution must exist for this tedious task. After discovering that there was no automated solution for migrating one SQL Server instance to another, I set out on a journey that would change everything: creating the PowerShell scripts that would eventually become the dbatools module.

Since then, an entire community has grown around dbatools. The dbatools module has changed the way that database professionals work with SQL Server by not only making their processes more efficient but also making their day-to-day work more enjoyable and fun. It’s even recommended by PowerShell’s creator and Microsoft Technical Fellow, Jeffrey Snover!

For years, users asked if any dbatools books were available, and for years, the answer was “not yet but that sounds like a great idea!” I knew I wanted to write for Manning because they’re my favorite publisher, and initially, I thought the book would be a Manning Deep Dive. I worked with the whole dbatools team to create a table of contents and even asked Anna Hoffman if she’d write the foreword (she agreed!).

After a Twitter user suggested writing Learn dbatools in a Month of Lunches instead, we realized that the Month of Lunches series was the perfect format; we all love the series and recommend Learn PowerShell in a Month of Lunches to anyone who asks where to begin with PowerShell.

Learn dbatools in a Month of Lunches is a great first book to read about dbatools, and we hope that you enjoy learning more about our toolkit. As a community, we’ve worked hard to make PowerShell user friendly, approachable, and fun. As authors, Rob, Jess, Cláudio, and I aimed to do the same with this book—you don’t even need programming or scripting experience to get started.

We also worked to ensure that the code in this book will work for years to come; each commit to the dbatools repository will extract code from the book and run Pester tests against live SQL Server instances to ensure exactly this happens.

All code in this book has been tested against dbatools version 1.1.77 and later. Chapter 2 will show you how to install the newest version. If you’ve got any questions, feel free to get in touch with us at dbatools.io/bookforum.

—Chrissy

acknowledgments

Chrissy LeMaire: Even before I was married, I knew that I wanted to write a tech book and dreamed of writing an acknowledgment thanking my wife. What I never imagined was just how incredibly impactful the person I’d marry would be in my journey. Without my wife, Lu, it’s unlikely that I would have had enough personal stability or shoulder health to write dbatools, much less a book.

Throughout the years, she has created space for me to flourish and build a great life. She also helped keep the book on track, often sending me to my office to write a chapter. Lu always cheerfully brings me whatever I need, whether it’s another blanket, a Belgian beer, or a beautiful vegetable plate, thoughtfully arranged. So, thank you, Lu, for helping me write this book and making my long-held dreams come true. C'est toi pour moi, moi pour toi dans la vie.

I’d also like to thank my best friend, Brandon Abshire, who has been with me on my SQL Server journey since the beginning. Brandon taught me PowerShell and was the first person ever to show me the power of SQL Management Objects (SMO). My only regret is that we don’t still live in the same town, but I’m thankful that at least we got the chance to grow up together.

Working through the pandemic was near impossible, and any bit of productivity was directly the result of my buddies in the Brain Trust. Thank you all for not only sharing your lives with me but listening and providing support as I shared mine.

Frances, your guidance has helped me become a better writer, perhaps even more so than my formal schooling. Thank you so much; I will use all that I’ve learned for years to come. A gigantic thank-you also goes out to my three amazing coauthors, Rob Sewell, Cláudio Silva, and Jess Pomfret.

Rob, you’re my perfect presentation partner, and I always enjoy the calm and confidence you bring to our sessions. Thank you for being such an integral part of my career growth.

Cláudio, thank you for your pivotal role in starting the dbatools community and being the first person who offered to add your own code to dbatools. And thank you for fixing all of my terrible T-SQL throughout the years, ha!

Jess, I’m still floored that dbatools was your first foray into PowerShell; we’re so lucky! Thank you for rallying through all the sprints that got us through to 1.0 and accepting the role as co-maintainer. I look forward to seeing your career’s continued meteoric rise.

Rob Sewell: I never believed that I would ever be someone who would have their name as an author for a book. Traci, my wonderful wife, was of a different opinion. Her encouragement, support, unwavering belief in me, and everything that she does to make my life have the space to be able to focus, as well as her never-ending acceptance of the time I spend in “my box,” have enabled me to be able to complete this. Thank you, Traci. I love you.

Chrissy, thank you so much for thinking of me all those years ago, reaching out to me and asking me if I wanted to be involved in dbatools. Without doubt, dbatools has changed my life, and you have changed my life also. My best presentations have been given with you alongside me on the stage, and I cannot wait to walk off the stage into the crowd or get everyone to applaud you again to see the look on your face. Your knowledge and your willingness to share it is incomparable. Without you, dbatools and this book would not be what they are today. You are amazing. I cannot wait to stand on stage again and say, “We are not a couple, we each have our own wife!”

Cláudio and Jess deserve special thanks, too: Cláudio for spotting all of the punctuation that I miss! For your calm consideration and your wonderful caring, generous nature, I thank you. Jess, you are so cool and collected, so clever and so willing to take things on. Thank you for all of the times you have answered when I have reached out with questions.

Most thanks especially go to all of the contributors—far too many to name and always willing and able to step in and give their own time, knowledge, and experience. I salute you, I worship you, I thank you.

William, Gianluca, and André, you three wonderful, gorgeous gentleman have had such an impact on my life and on this book: thank you for all of the support and friendship, for the times shooting the breeze, and for creating great solutions and ideas that can be used. The next beer by the fire in Slovenia is on me.

Jess Pomfret: Writing these acknowledgments has probably been the hardest bit of this book for me. I’m a strong believer that everything happens for a reason. However, there have been many people along the way who have helped me get to this point! English was my worst subject at school; I was even sent to lunchtime handwriting club because no one could even read what I was writing. Who would have thought I’d become an author!

First, thanks must go to the rest of the authors for making this such a fun project to be involved with. Chrissy, thank you for creating not only an amazingly useful tool but such a welcoming community as well. I’ve learned and grown so much from being involved in dbatools. Rob, I can’t thank you enough for all the support with dbatools, dbachecks, and especially with my speaking endeavors. Cláudio, we joined this project at the same time, and I’ve thoroughly enjoyed working with you on it. Thanks to all of you for making this such a great adventure.

Next up, my wife, Kelcie. Thanks for always having my back, putting up with my terrible jokes, my inability to find things that are right in front of my nose, and my impromptu dance parties—which always seem to happen when you’re in the middle of doing something. Without your support, I definitely wouldn’t have had the energy or focus required to get my chapters written.

Finally, my thanks go to my parents for always supporting my dreams, however crazy they were (like moving across the Atlantic at 19), and all the other people who have helped me along the way. There are too many people to name, but so many have had an impact: thank you all!

Cláudio Silva: Who knew that what started as a side project would lead to me being one of the authors of a book?! What a ride! However, this would not have been possible, at all, without the support of my wife, Diana, and my daughter, Matilde. The time that I was not available but you were always by my side; your unconditional support to always go after my dreams and accept the challenges that I wasn’t always sure about, but you always believed I would thrive—I love you!

A tremendous shout-out needs to go out to all my teammates and authors with whom I shared this wonderful chapter of my life!

Chrissy, a huge thank-you goes to you! Who knew that back in 2015, a casual conversation about some lines of code at a TUGA IT conference would lead us to this point of this wonderful project? Thanks for putting this project up, listening, and being so supportive, welcoming, and inspirational! You rock!

Rob, thank you for always being available to share your knowledge and vision about so many things! I learned—and I’m sure I will continue learning—a lot from you, sometimes just by reading/watching you.

Jess, thank you for your fellowship and support. It’s always a pleasure to work with you.

Finally, a big thank-you, really, goes to everyone who was or is part of the dbatools project: you people who keep feeding it, from the ones who take the time to open an issue/feature request to the ones who keep sharing their knowledge and spreading the dbatools word, and obviously everyone who wrote thousands of lines of documentation and code!

All authors: dbatools wouldn’t be the amazing project that it is today without the SQL Server and PowerShell communities. Together, we’ve changed the world of SQL Server and brought joy and ease to countless people’s lives. From the bottom of our hearts, thank you, every single person who has contributed to dbatools, whether it be through code, documentation, tech support, code reviews, filing an issue, and even buying this book. And an extra-special thanks goes to Shawn Melton for helping to maintain dbatools while we were writing this book—without you, we’d be drowning in unmerged PRs.

Also, without the generous monetary contributions from Data Masterminds, we’d be certless and testless. Your support helps us sleep easy at night knowing we’re delivering a secure and well-tested module with every release. You have been big believers in dbatools from the beginning, and we can’t thank you enough for your ongoing support.

Thank you, all the staff at Manning who helped guide us as we were writing this book, and whose hard work produced this text.

Thank you, Anna Hoffman, who wrote the foreword.

And last but not least, thank you, all the reviewers: Amanda Debler, Arav Agarwal, Arthur Zubarev, Ben McNamara, Cristian Antonioli, Danilo Zekovic, Darrin Bishop, Foster Haines, Ian Stirk, Jan Vinterberg, Joseph Houghes, Luis Moux-Dominguez, Marcus Brown, Odalia Zubarev, Paul Broadwith, Peter Bishop, Ranjit Sahai, Raushan Kumar Jha, Ruben Vandeginste, Satej Kumar Sahu, Stanley Anozie, Stephen Goodman, Steve Atchue, and Wayne Mather—your suggestions helped make this a better book.

about this book

Who should read this book

This book is for SQL Server data professionals who want to learn more about dbatools and PowerShell. It’s also helpful for automation engineers who are familiar with PowerShell but want a better understanding of SQL Server.

How this book is organized: A road map

In the first few chapters of this book, you’ll be introduced to dbatools as a whole, then we’ll begin following the path of taking the steps DBAs may take when they inherit their environment. From finding SQL Servers to inventorying, performing backups, preparing for disaster, then on to securing, optimization, and more. Here’s the rundown:

• Chapter 1 introduces readers to the book, as well as to dbatools and automation.

• Chapter 2 delves deeper into dbatools, including OS compatibility, installation, updating, and getting help.

• Chapter 3 will help you set up a lab where you can safely test dbatools commands.

• Chapter 4 outlines the framework for executing dbatools commands with the most commonly used parameters.

• Chapter 5 walks you through writing data to SQL Server using dbatools, including importing data from CSV files.

• Chapter 6 details how to discover undocumented SQL Server instances throughout your network.

• Chapter 7 discusses how dbatools can help inventory your SQL Server estate, including features, builds, databases, and more.

• Chapter 8 details using Registered Servers and dbatools to easily organize your SQL Server estate.

• Chapter 9 tells you all about managing SQL Server logins using dbatools.

• Chapter 10 covers backup management, including easy, automated testing.

• Chapter 11 will help you with restores, including restoring databases to a specific point in time and even marked transactions.

• Chapter 12 discusses snapshots and how much more accessible they are when using dbatools.

• Chapter 13 walks you through installing and updating SQL Server on remote systems, easily and all through the command line.

• Chapter 14 describes how to effectively prepare for a disaster by using dbatools to export logins, Agent jobs, and more.

• Chapter 15, which provides the first part of performing advanced SQL Server migrations, focuses primarily on databases.

• Chapter 16, which provides the second part of performing advanced SQL Server migrations, focuses on other migratable SQL Server features.

• Chapter 17 gives an overview of dbatools support for high availability and disaster recovery features including log shipping, Windows Server Failover Cluster, and availability groups.

• Chapter 18 begins a three-chapter series on SQL Server Agent by providing an overall framework for working with SQL Server Agent and PowerShell.

• Chapter 19 continues the Agent series by focusing on the administration of SQL Server Agent using dbatools.

• Chapter 20 finishes the Agent series by detailing how to create new Agent objects at scale.

• Chapter 21 discusses data masking in depth.

• Chapter 22 describes how dbatools can help enable DevOps within your organization.

• Chapter 23 will help you better understand and manage Trace events and Extended Events within SQL Server.

• Chapter 24 covers security and encryption, which includes network and database encryption.

• Chapter 25 will walk you through compressing your data, saving space, and reducing resource bottlenecks.

• Chapter 26 gives an introduction to dbachecks, which helps you easily validate your SQL Server estate using crowd-sourced checks.

• Chapter 27 provides an overview of how dbatools can help when working in the cloud.

• Chapter 28 describes dbatools configuration and logging in depth.

• Chapter 29 wraps up the book by providing additional resources for working with PowerShell.

About the code

This book contains many examples of source code both in numbered listings and in line with normal text. In both cases, source code is formatted in a fixed-width font like this to separate it from ordinary text.

In many cases, the original source code has been reformatted; we’ve added line breaks and reworked indentation to accommodate the available page space in the book. In some cases, even this was not enough, and listings include line-continuation markers (➥). Additionally, comments in the source code have been removed from the listings when the code is described in the text. Code annotations accompany many of the listings, highlighting important concepts.

You can get executable snippets of code from the liveBook (online) version of this book at https://livebook.manning.com/book/learn-dbatools-in-a-month-of-lunches. The complete code for the examples in the book is available for download from the Manning website at https://www.manning.com/books/learn-dbatools-in-a-month-of-lunches, and from GitHub at https://dbatools.io/bookcode.

liveBook discussion forum

Purchase of Learn dbatools in a Month of Lunches includes free access to liveBook, Manning’s online reading platform. Using liveBook’s exclusive discussion features, you can attach comments to the book globally or to specific sections or paragraphs. It’s a snap to make notes for yourself, ask and answer technical questions, and receive help from the author and other users. To access the forum, go to https://livebook.manning.com/book/learn-dbatools-in-a-month-of-lunches/discussion. You can also learn more about Manning’s forums and the rules of conduct at https://livebook.manning.com/discussion.

Manning’s commitment to our readers is to provide a venue where a meaningful dialogue between individual readers and between readers and authors can take place. It is not a commitment to any specific amount of participation on the part of the authors, whose contribution to the forum remains voluntary (and unpaid). We suggest you try asking them some challenging questions lest their interest stray! The forum and the archives of previous discussions will be accessible from the publisher’s website as long as the book is in print.

about the authors

Chrissy LeMaire (she/her) is a dual Microsoft MVP and GitHub Star. She is a well-known speaker and the creator of several PowerShell modules, including dbatools. Chrissy also holds an M.Sc. in systems engineering and currently works as an automation engineer in Europe.

Rob Sewell (he/him) is a passionate automator who has been recognized as a dual MVP by Microsoft. He is a keen community supporter and has organized, spoken at, and volunteered at many data and PowerShell events all over the world.

Jess Pomfret (she/her) is a data platform architect and a Microsoft MVP. She started working with SQL Server in 2011 and loves automating processes with PowerShell. She also enjoys contributing to dbatools and dbachecks, two open source PowerShell modules that aid DBAs with automating the management of SQL Server instances.

Cláudio Silva (he/him) is a data platform architect, Microsoft MVP, and contributor to open source projects such as dbatools and dbachecks.