Is Microsoft neglecting SMO?

Question

Post reply

Is Microsoft neglecting SMO?

Phil Factor

SSC-Insane

Points: 20244
More actions
March 5, 2011 at 12:07 pm

#246433

Comments posted to this topic are about the item Is Microsoft neglecting SMO?
Best wishes,
Phil Factor

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

What I wish is that Microsoft would stop ignoring T-SQL. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Factor SSC-Insane Points: 20244 More actions · Answer 2

Maybe we should turn it around and list all the things that they spend way too much time and effort on, Jeff.

Best wishes,
Phil Factor

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Phil Factor (3/6/2011)
Maybe we should turn it around and list all the things that they spend way too much time and effort on, Jeff.

Good idea, Phil, except that list would be too long for a single thread. 😛 I'll just stick with they're not spending enough time on T-SQL...

We still have to use the Quirky Update to do certain previous row calculations. We still have to make a trip to DOS or some other trickery to export. We still have to resort to Tally Table logic to do splits and use FOR XML PATH to concatenate. We still have to use recursive CTE's or While Loop logic to build a simply hierarchical path. And we still can't skip header rows that are different than the body for BULK INSERT without some data loss. We still have to shift to another type of window to select more than one table for deletion. They went backwards on the Query Designer... you can get back to your original SSMS window without closing the designer. AND, there's still no straight forward method to print Query Designer diagrams. There's still no supported method for listing files in a directory never mind finding out their creation date, modification date, or size and there's still nothing to rename them or move them once they've been processed. Heh... and none of that even starts on SMO.

I could go on for an hour or two but that's a start. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cmille19 SSCertifiable Points: 5970 More actions · Answer 4

I think you already know my answer--Yes PowerShell has been neglected in SQL Server 2008 R2 with almost no changes and we can only hope we see changes in Denali. In Denali we've seen a hint of several new cmdlets for managing HADRON. I think six HADRON related cmdlets were mentioned by various bloggers, but still nothing that covers basic-level day-to-day DBA tasks.

As for SMO, I don't have many gripes. Having spent considerable time working with SMO and other object models I feel that SMO is a wonderful object model. Yes, there are pieces of SMOs that are slow and yes there are a few pieces missing (easily remedied by adding synthetic properties using PowerShell), but this is nothing compared to other SQL Server related technologies (AMO, RMO, SSIS, SSRS). These areas have zero PowerShell coverage and some of their object models are difficult to use. The one that gave me the most pain was SSIS. The SSIS object model is clearly ported from a COM interface (several methods that do similar things differing in parameter types). In trying to use the SSIS object model there are things not exposed outside of SSMS which I find very annoying. Manageability seems restricted to the core database engine and very few DBAs solely work with just the core database engine.

One of the promises PowerShell makes is that as an administrator you'll have a consistent way of managing systems, but this only works if they build coverage.

Saravanan Durairajan Grasshopper Points: 11 More actions · Answer 5

Why do I need Powershell, when I can do all the stuff in GUI using SSIS? Powershell is another geek tool.

Phil Factor SSC-Insane Points: 20244 More actions · Answer 6

Why do I need Powershell, when I can do all the stuff in GUI using SSIS? Powershell is another geek tool.

Well, it certainly is a geek tool in the sense that it makes few compromises for non-programmers, but there is a great deal to be learned from Powershell, particularly in the discipline with which Cmdlets are named in order to give you a good clue as to what they do. It is a great naming convention that has many good ideas. Powershell is also quite a good way of exploring .NET assemblies and the functionality they expose.

I just had a look at the projects I'm using Powershell for and there is nothing there that SSIS can be made to do. Obviously, if there is an easier way that you can get the functionality you need 'out of the box' in SSIS, then you'd use it. I use Powershell when there is no other obvious easy way to automate processes. That seems to happen to me quite often.

Best wishes,
Phil Factor

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Saravanan Durairajan (3/6/2011)
Why do I need Powershell, when I can do all the stuff in GUI using SSIS? Powershell is another geek tool.

Heh... because some of us are heavy-duty batch programmers that don't go anywhere near a GUI. 😉 I'd rather see the power added to T-SQL, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Phil Factor (3/7/2011)
I just had a look at the projects I'm using Powershell for and there is nothing there that SSIS can be made to do.

Now THAT's an interesting thought. I'd love to know what some of those are. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chrisn-585491 SSCoach Points: 16006 More actions · Answer 9

Why do I need Powershell, when I can do all the stuff in GUI using SSIS? Powershell is another geek tool.

Because at some point in your career, you will find tasks that can't be done effectively except through scripting. Or you may want to automate certain aspects of your job. Feel free to click away till then...

cmille19 SSCertifiable Points: 5970 More actions · Answer 10

Phil Factor (3/7/2011)
Why do I need Powershell, when I can do all the stuff in GUI using SSIS? Powershell is another geek tool.
Well, it certainly is a geek tool in the sense that it makes few compromises for non-programmers, but there is a great deal to be learned from Powershell, particularly in the discipline with which Cmdlets are named in order to give you a good clue as to what they do. It is a great naming convention that has many good ideas. Powershell is also quite a good way of exploring .NET assemblies and the functionality they expose.
I just had a look at the projects I'm using Powershell for and there is nothing there that SSIS can be made to do. Obviously, if there is an easier way that you can get the functionality you need 'out of the box' in SSIS, then you'd use it. I use Powershell when there is no other obvious easy way to automate processes. That seems to happen to me quite often.

T-SQL is a single server solution that alone does not provides multi-server administration nor does T-SQL provide a natural way of interacting with the OS (disk space, hotfix checks, deleting files, etc.).

SSIS at best is an ETL tool and although you can make write code such WMI in SSIS, it isn't the right tool to do so.

Glenn Wiens Grasshopper Points: 14 More actions · Answer 11

My reluctance to learn and use Powershell stemmed from the fact that it was not universally available on machines at the time I was needing to set up script automation. As a result, I have just lived with the language mix of whatever it takes to get the job done (as an earlier post mentioned): DOS, SQLCMD+TSQL, WMI+VBScript. Heck, I'll even use java & jdbc if I have to.

As one of my instructors said when learning Oracle on unix when I complained about the arcane syntax of vi: It is the one editor that you will always find on every *nix platform.