I have read through the fine PowerShell "Deep Dives" book with its description & examples of loading .NET Framework Assemblies then referencing SMO objects to carry out SQL activity. Yet there is also the PowerShell SQL Provider [SQLPS] and its Invoke-SqlCmd module.
A scattering of comments seem to imply that the SMO objects are quicker, more efficient, and most PowerShell SQL examples use SMO. Other comments say loading the SQL Provider is cleaner [less code, easier for another person to read your code] and SQLPS is likely to gain more yet features, while little further work is expected on the SMO front.
As one who is starting to write PowerShell SQL code, is one recommended over the other at this point in time ? Advice would be appreciated.
If you're just working with data then invoke-sqlcmd is the simplest solution. If you want to administer SQL server you can use SMO. Many admin tasks can also be performed using SQL through invoke-sqlcmd. if you're just starting I'd recommend invoke-sqlcmd as your best starting point then branch out to SMO as required
Thanks for your prompt response (and expert opinion). There are fewer examples for the SQL Provider [at the moment] but is does seem to be simpler to use.
Though IT security people look to other tools, I think PowerShell will be a good investment of my time. After reading the two Ed Wilson books, then the Deep Dives book, I am working through your PowerShell and WMI book — keep up the good work!
One thing to note about the SQL Provider is that the New-Item cmdlet doesn't work – if you want to create objects through the provider you need to revert to SMO at which point you might as well script it from the begiinning