DbSmo PowerShell Module by Cody Konior

Read the CHANGELOG

Test this extremely carefully before deploying to Production. This isn’t a trivial module.

Description

DbSmo iterates a SQL SMO Server or WMI object and writes the contents of every readable property to a database schema it builds and updates and maintains on-the-fly as it discovers new information in your environment. Where possible (SQL 2016+) those tables are automatically made temporal tables to keep a history of your servers over time.

Configuration settings? CPU affinities? Registry information? Logins? Databases? Database settings? Pretty much everything, all neatly ordered in tables named after the SMO objects and linked with foreign keys (exactly as if it had been built by hand).

This makes it excellent to find out what changed on your servers on this day last year, or comparing settings across hundreds of servers at once with simple T-SQL.

Installation

• Install-Module DbSmo

Major functions

• Add-DbSmo
• Add-DbWmi

Demo

Tips

• The destination database should be SQL 2016 or higher to take advantage of temporal tables, otherwise there is no historical data kept.
• The module uses Jojoba so if you pipe in a list of server names they’ll be processed in parallel.
• On the very first run when the bulk of the database is being constructed, there is a higher chance of deadlocking if multiple threads are trying to write at the same time. This will reduce in frequency on subsequent runs.