Data Warehouse Management Command Line Application - PowerShell or C# Console App

Question

I'll preface this question by saying this is for a Microsoft only shop.

If you were to write a console app to manage a data warehouse what would you use:
1) Writing a custom environment for PowerShell (ala the latest flavors of Exchange / SQL Server)
2) Write it as a C# Console App

If #2 are there any frameworks that offload writing a "menu system" or any other tasks for you.

If this app needed to last 6 to 8 years - would you use PowerShell? No one on the team currently has PowerShell experience, but we are quick learners.

Answer 1

When you are saying manage a data warehouse, what kind of tasks are you talking about?

Much of the management I would do in T-SQL (purging, archiving, transforming) - the interface to that can be very thin (even non-existent).

OK, based on your comment I would have the code which does all the work in a stored procs with a .NET assembly (typical API class library), as much in stored procs as possible, with the assembly for stuff which is easier done there or which requires COM or whatever. I would then either wrap the class library in cmdlets or just call the .NET objects from PowerShell (remember that PowerShell can instantiate objects.).

Now you have a .NET library which can also be called from web pages, GUI apps, whatever, if you ever want it, and you have a cmdlet and a direct .NET interface - plus the option of calling them from SQL if they are fully implemented at the SQL layer.

Answer 2

I think you can be successful with both, and should be able to switch between the two without too much hassle. If you start out building a console app but learn PowerShell later, you can throw away a lot of the console app-specific code (command-line parsing code for example) and build a few PowerShell cmdlets to wrap your existing API. Or, if you build a bunch of Cmdlets out the gate, but need to switch to a console app later, you won't have wasted much time writing the Cmdlets.

So, I don't really have strong advice one way or another. I will say: hey, go try PowerShell. If you don't like it, it's not too difficult to switch.

Answer 3

If you write your management functionality as PowerShell cmdlets, then you can surface that functionality either by letting people run cmdlets directly, or by wrapping them in a GUI. Going with PowerShell probably gives you the most long-term flexibility, and as MS implements more PowerShell cmdlets, it means that managing your data warehouse could be incorporated with other, larger business processes if necessary. I would probably NOT choose to write a C# console app - but I have a distinctly more "administrator" perspective. We admins are tired of having custom console apps tossed at us - the idea of PowerShell is to standardize everything in a way that supports both command-line and GUI administration.

Answer 4

I have found that PowerShell is a much more maintainable solution for smaller things. Console apps require relinking into new libraries and recompiling and redistributing out when the libraries you depend on change (in my case going from Visual Studio 2005 - Visual Studio 2008's codecoverage and other things) as well as executables your scripts may call vsinstr, mstest, etc. where as with PowerShell scripts you can easily customize for each environment you have and don't have to go through the compile, link, deploy stuff for every environment you choose. In fact if you get your path info from the registry the same script can run in both environments.

You can do everything you want with either, I just prefer having to maintain a single simple text file, then a console app. Just personal preference.

Answer 5

The beauty of implementing PowerShell instead of a cosole app is that you don't have to code all the parameter parsing or all the formatting. PowerShell takes care of all of that for you. You can, of course, override defaults if you need to. You can get wildcards for free. You can also have your Cmdlet(s) take values from the pipeline which opens up all kinds of possibilities and uses for automation. With PowerShell, both the end user and the developer will have a much better experience.