SQL - use SQLPS or SMO objects?

Tagged: ,

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Richard Siddaway Richard Siddaway 1 year, 10 months ago.

  • Author
    Posts
  • #22180
    Profile photo of Jeffrey Wagar
    Jeffrey Wagar
    Participant

    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 [2015]? Advice would be appreciated.

  • #22182
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

    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

  • #22183
    Profile photo of Jeffrey Wagar
    Jeffrey Wagar
    Participant

    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!

  • #22187
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

    Thank you

    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

You must be logged in to reply to this topic.