Here we are, 45 cmdlets and a provider later. When I started this series, I did it for two reasons. The first was the general lack of documentation for the cmdlets and a hope that I could bridge that gap for the community. The second was that I realized I didn’t understand much of the functionality that was there myself and writing on it would help me build that knowledge. I can definitely say that now, at the end of it all, I’ve had my eyes opened to what we can and can’t do.
The Good
I have to say, I’m pretty pleased with most of the cmdlets available to us. The cmdlets for managing Availability Groups are great and I make daily use of them. I would like to see some Get- cmdlets for some of those instead of using the path, but in a pinch I can use Get-Item within the provider context to accomplish the same thing. The other alternative is to create SMO server objects and reference the appropriate properties to create the necessary objects.
As I blogged about awhile back, I do like Invoke-SqlCmd and the ability to get query results as datarow objects. This is a handy way to work with data within a Powershell script. I also like the backup and restore cmdlets for abstracting away some of the SQL work into Powershell. I know I can run any of these using T-SQL, but the abstraction makes things easier for me when writing automation.
I also really like the provider. I know it’s clunky, but once you’re past the gotchas, there is a lot of great things you can collect using it. To boot, the collection is fairly simple. Using the provider is a fairly simple and effective way to collect server wide inventories.
The Bad
Some of the functions just seem unnecessary. It strikes me that some cmdlets, like Convert-UrnToPath and Encode/Decode-SqlName were developed for some specific use cases and have limited real world use. This is not surprising, as we have seen cases in the past where Microsoft decided internally that something was useful, but real world adoption did not happen. These only annoy in the way that they are clutter and add confusion.
Of course, the real problem is the initial experience of the provider. So many DBAs get discouraged by the timeouts and strange behavior of the provider that they ask if Powershell is even worth it. The usual path for them is to go back to what they know and code things in T-SQL which, while cumbersome and inelegant for many tasks, just work. The struggle is to get DBAs over that initial hump with Powershell so that they can get on with figuring out what to use the tool for.
The Meh
As we’ve seen, the SQLPS module has some head scratchers. I’m still not 100% clear on why the team needed to leverage the Cloud Adapter Service when creating some of these functions. Tasks like adding a firewall rule, enabling authentication modes, and restarting services already have valid ways to be managed through Powershell, so these cmdlets are really not necessary. Again, we’re left to deal with clutter in the module.
What’s Next
I’ve gotten a lot of feedback in person on this series and it sounds like a lot of people out there have really benefited from my posts. This is great. If you’ve missed the series or maybe a post or two within it, the full list is easy to find.
What I would like to do is to take some time over the next few months and compile this information into an e-book, along with some patterns for use. I also intend to build a presentation for next year that is a deep dive into both the provider and the module. I really do like the SQLPS module and the provider, as it can be extremely powerful if used correctly. I hope that, now that you’ve read this series, the wheels are turning in your brain about where and how you can implement SQL Server and Powershell automation in your environment.
To add to the bad side of things…the complete lack of multisubnetfailover support in any of the cmdlets (this is of particular pain when it comes to invoke-sqlcmd).