Here's a quick summary of my free/open source PowerShell modules so have a look through and see if there's anything you can use. Most of my work needs reliability at scale and this means writing my own tools. Sometimes those look similar to other tools but there are differences that matter.
OftenOn is my latest project to spin up a 5-node multi-subnet Availability Group on SQL Server 2012 and Windows Server 2012 with one
command. How? Through Hyper-V, Desired State Configuration, the
Lability PowerShell module, and an immense amount of work.
It takes time on the first run to download about 10GB of Evaluation ISOs from Microsoft. After this though each time the lab is destroyed and competely recreated takes 45-60 minutes. You'll know it's done when all the VMs go to the login screen.
Then remote in yourself! Remote Desktop from your machine should work to the short computer names (e.g. CHDC01) via IPv6 but it may not work on some configurations.
Username LAB\LocalAdministrator Password Local2018!
The VMs built are as follows:
- CHDC01 the domain controller in the Chicago subnet that also does routing for other subnets.
- CHWK01 a workstation in Chicago with .NET 4.7.2 and SSMS 17.9.
- SEC1N1 SEC1N2 SEC1N3 are three nodes of the C1 cluster in the Seattle subnet.
- DAC1N1 DAC1N2 are two nodes of the C1 cluster in the Dallas subnet.
The C1 cluster has an Availability Group (AG1), a listener (AG1L), and a database (DummyAG1). It's all sync'd to Dallas and ready to failover for your testing!
All VMs have WMF 5.1 installed. There's a couple resources (like SQL patches and the .NET 4.7.2 updater) on \CHDC01\Resources. Those aren't installed everywhere because that's part of the fun of the lab - exploring various issues with and without them. It's also loaded with all my other PowerShell modules.
Do you use Invoke-SqlCmd, Invoke-SqlCmd2, or interact directly with SMO (Server Management Objects)? If so then this is for you! Get rid of Invoke-SqlCmd and manual SMO instantiation and do these instead:
New-DbConnection -ServerInstance SEC1N1 -DatabaseName msdb | New-DbCommand 'SELECT * FROM dbo.suspect_pages;' | Get-DbData -OutputAs DataTable $smo = Get-DbSmo -ServerInstance SEC1N1 -Preload
But let's break it down. Make connections using
New-DbConnection. This takes a parameter for every option .NET supports including ApplicationIntent,
MultiSubnetFailover, and more. Want a connection string instead? Ask for -AsString. It can also accept SqlCredential and translate
Then form a query with
New-DbCommand and get the data with
Get-DbData. Don't use unsafe string concatenation though! Pass in parameters using a hashtable.
And if T-SQL isn't enough for you then form an SMO object and query that. Or a WMI object. No more tricky syntax to remember.
The magic however is behind the scenes.
- Each connection made this way have an automatic override which makes the connection timeouts more resilient. Without this and when running at scale SQL Server can sometimes hang during the handshake and stop your scripts dead. No more!
- SMO objects can have their data preloaded (queried all at once instead of row by agonizing row) using the
-Preloadparameter. There's also a minified
-PreloadAGif you just want to pull back AG information quickly without a lot of round trips.
- It's thread-safe and can run at scale unlike
And there's more!
- DataTable types returned from
Altermethod which accepts a Hashtable and does upserts automatically. Want to delete rows? Delete them and run
Alterand they'll disappear.
- Want to retry deadlocks? Wrap your code in a
- There's also print output capture, transaction support, and bulk inserts.
Jojoba is my multi-threaded unit testing framework for PowerShell. It starts as an extra few lines to your function template which makes it run in parallel automatically, and manages error handling by returning results in the exact same format every single time. What was tested, did the test pass, and what's wrong if it didn't.
It's similar to Pester but approaches testing from a different angle. All of your "tests" are still normal functions and so can be run easily from the command line, call each other or be called by your other workflows, or get augmented with repair switches.
CIM is the replacement for WMI in Windows Server 2008 and above and aside from being very easy to use, it's also important to use because it's faster, less resource intensive, more reliable, and supports timeouts out of the box. Unfortunately the components built into PowerShell only work to Windows Server 2012 and above.
Cim, the module, changes that by giving you an easy way to create the CIM connection to computers as old as Windows Server 2003. It also manages connections so you don't create the same one over and over (a problem the built-in function has).
$cimSession = New-CimSessionDown -ComputerName SEC1N1 Get-CimRegKey -CimSession $cimSession -Key 'Software\Microsoft\Microsoft SQL Server' | Get-CimRegValue | Format-Table
But another big part of WMI and CIM that is often overlooked is remote registry operations. Most remote registry commands have to be run over a PSSession and cause a shell to be provisioned and torn down each time. There's no reason for this except laziness, but now the Cim module lets you do this all remotely over the CimSession. It also supports easy chaining to get the data you want.
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.
Don't go yet!
"A man reduced to a single instinct: Survive." — Fury Road
I've been publicly blogging, coding, presenting, answering questions online, running PASS User Groups, and being the public bearer of bad news for so many SQL Server issues I've discovered that there is no count (also Connect is gone and with it my list of those achievements).
And somehow I've never been nominated for Microsoft's Most Valuable Professional award. Why? Well I think it has something to do with living in a city, an entire state, where there are no Data Platform or PowerShell MVPs. And although consultancy is one of the common pathways to become an MVP (join a famous company get a famous MVP) I'm not a consultant - I work full-time for major corporations.
So my chances of being recognised are increasingly slim despite my endless contributions.
If you can help change that and WITNESS ME by NOMINATING ME it would be greatly appreciated. Unfortunately this can only be done by Microsoft MVPs and Microsoft FTEs. But if you know one you could always put in the good word for me!