Stairway to SQL PowerShell Level 7: SQL Server PowerShell and the Basics of SMO

  • Ben Miller

    SSCrazy

    Points: 2964

    Comments posted to this topic are about the item Stairway to SQL PowerShell Level 7: SQL Server PowerShell and the Basics of SMO



    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck - http://dbaduck.com

  • Michael Oberhardt

    Ten Centuries

    Points: 1049

    I know you mean well. At one stage I was really excited about SMO, and RMO (the replication management objects library). However, we got so bitten by it.

    For starters, the coverage is very hit and miss depending on versions. You get no errors, but some classes methods etc just don't work properly depending on the SQL version (particularly 2000). You call them and no exception or errors, but the data returned is rubbish or more or less empty.

    Secondly, using SMO for something that is widely deployed adds some pretty hideous installation requirements. It isn't just a matter of bundling libraries. You need to run a stack of OS and CPU dependent Microsoft SQL artefacts installers, if the target machine either doesn't have SLQ installed at all, or has an earlier version of SQL than the libraries. And with these installers, it isn't as much as install SMO version 2008 or whatever, it means get the x86, i64, etc version of this, then install the correct version of depending on the OS and CPU, then this. It was a deployment nightmare.

    Thirdly, The RMO libraries were abandoned back in SQL 2008 (R1). Microsoft say they are deprecated as of 2012, but that isn't the case. They don't actually work in 2012. They didnt' say they were deprecated in 2008R2 either, but they never issued a version for R2. It did work with R2 however.

    I had written a SQL version agnostic replication setup and management tool using SMO and RMO. It made production deployments easier, but the prerequisites were just messy. Then of course as soon as 2012 arrived it didn't work. So I rewrote it removing all SMO and RMO. The installation is so much cleaner, all those damned SMO and RMO references removed from the project, the error management so much cleaner, and the code so much cleaner.

  • Ben Miller

    SSCrazy

    Points: 2964

    Is this just a comment or is there a question you have? I know that SMO is not perfect, but it has a lot of functionality and in my earlier Levels I explain the simple nature of installing the objects. I am sorry you were so bitten by the objects. I use them every day and to some great success.



    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck - http://dbaduck.com

  • Michael Oberhardt

    Ten Centuries

    Points: 1049

    More a warning, or cautionary tale, than a question...

    Using it on a solution would be fine I guess. As part of a product however I couldn't recommend it. In all though, I was initially excited, the ability to have OO libraries for performing SQL programming sounded fantastic. The reality of it though was it was very uneven, some stuff just didn't work at all (like some event callbacks, from memory), in part abandoned, and a huge overall disappointment. And that is ignoring the deployment requirements it then entailed.

  • Ben Miller

    SSCrazy

    Points: 2964

    OK, thanks. I have used it on SQL 2005 and above with great success so I guess mileage varies on the project and versions of SQL server.

    Thanks for commenting.



    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck - http://dbaduck.com

  • Perry Whittle

    SSC Guru

    Points: 233838

    Hi Ben

    great stairway, very good reading

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply