A service from Red Gate

in
Home SQL .NET SysAdmin Opinion Blogs Forums Books About
Sign in | Join | Help
Home Blogs Forums
Blogs Home > Tony Davis > One Shell to Rule Them All (and in the darkness bind them)

Tony Davis

Simple-Talk Editor
News, views and good brews

One Shell to Rule Them All (and in the darkness bind them)

Published Thursday, May 14, 2009 12:06 PM

It has been obvious for a while that Powershell 2 was going to be strongly supported as the natural scripting language for Windows Server 2008 R2 and Windows 7. It comes with interesting new features, such as the ability to execute scripts on remote systems and write native "ScriptCmdlets". What's more it now has a very nice IDE, and the various administrative GUIs in Windows Server will now generate Powershell scripts that can be saved for reuse. It seems certain that Powershell will be on all the servers in your organisation and that all administrative scripting is going to be in Powershell. If you are a system or database administrator, you are just going to have to grit your teeth and get started on one of the 400-page books that introduce the tool.

Were it not for the fact that Powershell is as intuitive as Linear B, it would be game over. As it is, IronPython and IronRuby remain interesting alternatives. They make the scripting process far simpler, and the syntax is much closer to the type of programming language that DBAs are used to. They become even more attractive when one considers the versatility of the .NET Dynamic Language Runtime (DLR), which allows you to plug IronPython or IronRuby into your .NET applications, call C# methods and so on. Maybe, with these DLR-based languages, we really are moving closer to achieving the dream of all DBAs: one scripting language to do everything. However, your Sysamin will loathe them as he will be engrossed in the extended culture-shock of having to redo all his favourite scripts in Powershell.

Sadly, there have been losers as well as winners in the scripting wars. Jscript.net, which briefly appeared in Silverlight, seems to have quit this mortal coil, doomed by the emergence of the DLR, which was more likely to deliver a fast-performing script. Perl too doesn't seem to have made the leap into the .NET environment.

Does any of this matter? Does a DBA even need scripting languages? Many DBAs out there still perform maintenance tasks by hand, server-by-server, database-by-database, occasionally reaching for SQLCMD and SQL Agent. Or perhaps, at most, using a few VBScript /DMO solutions that they haven't quite got round to updating for SMO.

Is this an unfair representation of DBAs attitude to scripting? Is it time to redo all those old scripts in Powershell, or are there viable alternatives? We'd love to hear from you.

Cheers,

Tony.

by Tony Davis

Comments

 

randyvol said:

After all the variants of "THE" Windows scripting language, why should I invest my scarce and valuable free time reading 400 pages about Powershell?  Just because it is new, it is supposedly the "end all" to the scripting language wars on Windows?

Nonsense.  I predict that within 2 years, yet another scripting language variant will be forthcoming from Redmond.  

When I can no longer take care of my machines using T-SQL, and/or SSIS, and/or the occasional simple text file renamed to a .bat and scheduled as a task, then I will invest my time.

As to 'remoting' - being able to run the same thing across multiple machines, I seem to recall this cool tool from some company, "multi - something" ;-)
May 14, 2009 9:18 AM
 

Phil Factor said:

I must say that the fact that you can run powershell scripts 'natively' in SQL 2008 Scheduler should be a warning to us all that the dominant language for SQL Server scripting is Powershell. In the famous words of the Daleks, 'Resistance is futile'.
Here is a tip I'd throw in to save your sanity if you have a heap of existing SMO scripts. Install NET Reflector, get hold of the PowerShell add-in and then use Reflector to convert all your existing SMO scripts written in C# or VB into Powershell. The results aren't perfect, but they save you eons of time. Happier now?
May 14, 2009 11:48 AM
 

timothyawiseman@gmail.com said:

Personally, I have only just begun evaluating powershell, but I am immediately hesitant when anyone says "Resistence is futile" or that there is any one language to rule them all.  Thus far, I have done the vast majority of my DBA automation in pure T-sql which can then be automatically through the agent and run accross multiple servers with products like multiscript or more recently through SSMS 2008.
When I need something more versatile and more complete, I use python.  

I will happily switch over if I am convinced that powershell is sufficiently superior to make it worth tackling the learning curve.  It very well may be, but that is a decision that needs to be made on the merit of the product.  It is the merit and power of the product that matters, not the fact it can run 'natively' which will matter.
May 14, 2009 12:51 PM
 

arasmin said:

Sorry to nitpick - "Resistance is futile" is Borg. Daleks went for "EXTERMINATE! EXTERMINATE!"

(So I'm a geek ....:P)

But in reference to the Powersheel taking over idea - this is what is being done with Exchange. The GUI is being gutted in some instances in favor of Powershell. This is a good thing at times as making the same change on many objects is a real pain if the only way to do it is a GUI but can be a real drag in others such as one-offs("now was that get-qaduser domain\user or get-qaduser cn=user, ou=Users, dc=domain, dc=com?")

I will give MS credit in that they seem, at least for the most part, to be pushing PowerShell as a "learn one script language, use many places". PS can be used for machine tasks, WMI tasks, remote tasks, local tasks, Exchange, SQL, SPS, etc, etc so an admin can learn one language and then port the principals and even some commands to another platform where as pre-PS one would have to learn the Exchange GUI, start all over to get TSQL, start all over to get VB, start over with VBScript, start over for VB for apps, start over with VB for Outlook, etc.
May 14, 2009 3:18 PM
 

Saggi Neumann said:

I always preferred the Vogon's "Resistance is useless"...

It seems like Microsoft decided Powershell is THE script - native to SQL server, Exchange, DPM and probably more, so we'll have to figure it out someday...

I hope that most DBAs DO script out their maintenance tasks (at least within SQL server) and not perform it by hand. If they don't they should probably start before their databases become too many and too big to handle manually...
May 18, 2009 1:15 PM
 

chrisn said:

Instead of reinventing the wheel, Microsoft could have borrowed a well known common scripting language and garnered wide spread support. Compared to Ruby, Python or JScript, PowerShell is clunky and very non-intuitive to even experienced developers. Forcing it down administrators and developers throats is one way to insure is popularity and survival.
May 19, 2009 7:36 AM
 

Granted said:

I'm still fumbling through learning PowerShell, but I'm finding it incredibly useful. It's not that it replaces SSIS or TSQL. It works within them and along side them. I've blogged about it, but it doesn't hurt to repeat it here. My company is going through a multi-phase data center move in which we're moving sets of servers on different days over months. I've developed a series of simple powershell scripts that can work from a designated list of servers to perform various tasks using SMO & TSQL to ready the servers for the move or help recover them afterwards. I put the scripts together as a learning tool, but what I learned was that having a simple mechanism for controlling my servers internally (TSQL) and externally (services, files, what have you) through a single language that can exchange data freely and easily between the two areas is a must. Powershell really seems to answer that need quite well.

Right now Microsoft is pushing Powershell pretty hard. It's embedded in SQL Server 2008. It's becoming a major factor in Operations Manager and Exchange. As they keep pushing it into various products, it's really going to be the place to go for quick and direct scripting. It's about time.

But, could MS switch it all in three or four years? Yes, yes they could. So? If you're working within the MS sphere of applications, you're generally going to do what the 800 lb gorilla wants you to. Yes, you could switch to Apple or one of the forms of Linux, but once they develop as large a market share as MS currently enjoys, they'll be the 800 lb gorilla and you'll have to follow them in the directions they lead. It's somewhat the nature of our business. New technologies and new directions and new methodologies are constantly arriving. You can learn them and embrace them or you can get passed by.

I for one welcome our new Powershell masters.
May 19, 2009 8:00 AM
 

tor said:

I am going with "timothy a wise man" sort of.

What exactly are people doing with powershell that I can't do in TSQL?

I admit it is highly likely that there is stuff I could do with other scripting languages but I find TSQL covers off enough that I have never really bothered too much.

The whole powershell thing as explained to me by exchange guys and everything really boiled down to "we can script stuff we need to do" We dba's have had that for a long time.

If someone can point out real world uses for it I am of course interested as I am a lazy lazy man. I have some 15 years of scripts that I carry with me constantly and add to because I would rather do a manual task once then just find the saved script that does it from then on.

If it turns out MS invest large chunks into it and it gets uptake I suppose I will start using it but at the moment I genuinely don't see the advantage.
May 22, 2009 10:50 PM
 

AndyD said:

PowerShell is definitely here to stay. It will evolve, of course, but will end up assimilating all other scripting languages (oops, bit geeky).

The fact that PowerShell is part of Windows Server 2008 R2 and Windows 7 confirms Microsoft's commitment to its new baby. It may get thrown out with the bathwater in a few years, but right now it is here to stay.

I don't think you need to wade through 400 pages to get started though. The simplicity and power of Cmdlets enables you to do some very useful things with very little effort.

At a recent TechDays conference, I saw a demo of creating a 2-node Microsoft Cluster, with everything configured, just using a fairly simple PowerShell script, of less than 10 lines. That alone could save half-a-day's work.

And to answer Tor - you can do loads of really clever things in TSQL, but when you start trying to interact directly with the file system, for example, I bet you use xp_cmdshell; and this is where PowerShell really works well.
May 26, 2009 9:48 AM
 

chrisn said:

"At a recent TechDays conference, I saw a demo of creating a 2-node Microsoft Cluster, with everything configured, just using a fairly simple PowerShell script, of less than 10 lines. That alone could save half-a-day's work."

How much research and work went into those ten lines of code? I could say the same about the code I use daily, but I'm leveraging libraries that I've wrote and used hundreds of times.
May 29, 2009 7:35 AM
You need to sign in to comment on this blog
  Opinion Home

  Opinion Pieces (159 articles)

  Geek of the Week (65 articles)

  Tony Davis - Editor (5 articles)

  Phil Factor (53 articles)

  Damon Armstrong (10 articles)

  Douglas Reilly (40 articles)

  Adrian Furnham (1 articles)

  Claire Brooking (1 articles)

  Richard Morris (68 articles)

  Tim Gorman (1 articles)

  Jesse Liberty (5 articles)

  Sarah Blow (1 articles)

  Anna Larjomaa (1 articles)

  Gaidar Magdanurov (2 articles)

  Regular Columnists (1 articles)

  Ben Hall (5 articles)

  Larry Gonick (48 articles)

  Blogs Home
<May 2009>
SuMoTuWeThFrSa
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456
Raw Materials: Robotic Resources
 Derek is judged by a jury of his peers. Read more...

.NET Performance Testing and Optimization - The Complete Guide
 In the Complete Guide .NET Performance and Optimization, Paul Glavich and Chris Farrell offer a... Read more...

Mission Critical: SQL Server 2008 Engine Post-Installation
 Even an experienced DBA finds it safer to double-check that all the tasks have been done, and in the... Read more...

Raw Materials: The Trojan Horse
 Excuse me, was that geek or Greek? Read more...

Adding a KPI to an SQL Server Analysis Services Cube
 Key Performance Indicators, which vary according to the application, are widely used as a measure of... Read more...