Chapter 10. Discovering your servers with PowerShell and SMO
You’ve inherited a SQL Server and you need to collect the relevant information about it. How many databases does it have? When were the backup jobs last run, and were they successful? Are triggers being used in any of the databases? There’s a lot to capture and analyze. Fortunately, you don’t have to point and click to gather this information. You can use PowerShell and SQL Server Management Objects (SMO) to automatically collect the data and display it in a Microsoft Excel spreadsheet. You can even compare the values to industry-recognized best practices and highlight any differences that need your immediate attention.
In this chapter we’ll examine how PowerShell and SMO can be used to automate the analysis and documentation of your SQL Servers.
One of the great things about PowerShell is that it’s inherently aware of the Windows platform and many of the software applications installed on it. Accessing email or working with the Windows Management Interface (WMI) can be accomplished easily using PowerShell. That’s also the case with Excel.
Excel provides a rich environment for reporting and storing information about your servers. You can use this spreadsheet (sometimes called a runbook) to quickly reference a server’s configuration information and dependencies.
