When I first looked at PowerShell, it was v1.0, and I was in a TechEd presentation. The language seemed a mile past the VBScript I was using when T-SQL didn’t function well. The ability to access the .NET namespace, work with objects, and program with error handling was exciting. I played with the language a little, but didn’t find that many places to use it at the time.
Certainly file operations were much easier with PowerShell, and I built scripts to copy backup files around the network. AD operations were easy in PoSh. However, when I tried accessing SQL Server, I thought the code was complex. In fact, whether I was running a stored procedure, or performing a restore, the PowerShell code required was cumbersome. A good example is shown at the beginning of Aaron Nelson’s recently updated post on querying with PoSh. The complexity shown to just make a connection to PoSh made me think I should just write that kind of code in C#, with all the debugging and other software support available in Visual Studio. Building quick utilities with PoSh was something I’d like to avoid.
When I heard that SQL Server Powershell was being updated, I wasn’t too excited. I had visions of there being newer versions of cmdlets, but a similar level of effort to work with SQL Server. However, as I’ve played with the new cmdlets, along with the dbatools module, I see PoSh becoming easier to use than T-SQL in some cases. In fact, I’ve started to keep a command line window open (with ConEmu) and access that to perform lots of quick tasks that aren’t based on simple queries of data. Even a backup with Backup-SqlDatabase seems as simple as it might be in T-SQL, perhaps more so if I don’t have an SSMS connection open.
I know there can be a debate over whether you need PoSh as a DBA or you can get by with just T-SQL. I don’t want to take a side here, and no matter how you feel, if you can get work done, then you are successful. Your job depends on you being able to reliably write code to perform some task over and over. The language doesn’t matter.
My view is that I think there are cases where PoSh seems a better fit and places where T-SQL works really well. I want to improve my skills in both so that I can decide what works best in any particular situation, and feel comfortable in building a solution either way.