Perusing Powershell Part 1: Get-SQLServer

I wrote this morning that I’ve shifted my new language focus from F# to PowerShell. I did this for a variety of reasons, but primarily because PowerShell is the future of Microsoft administration while F# is a research project. The thing that interests me most about F# is its support for hybrid OO/functional programing. Turns out, PS uses a different approach, but accomplishes much of the same goal.

In OO, most of the focus is on objects, naturally. However, administrators (i.e. the target audience of PS) tend to be much more task or action focused than object focused. Most OO languages don’t have actions as a first class citizens within the language. C# and Java don’t even allow stand alone functions – they always have to be at least static members of a class.

I’m fairly sure there are many reasons why strongly typed OO languages aren’t popular among administrators. I’m not going to go down the static/dynamic typing rat hole here, but I would guess the object/action language tradeoff is almost as important as the typing tradeoff. What’s nice about PowerShell is that while it has strong object support, it also has strong action support as well. In PS, actions are called Cmdlets. While I’m not a big fan of the name, having first class support for them in PS is one of the things I find most interesting.

PS is designed to be extended. And while there is support for defining functions in PS directly, for the most part PS is designed to be extended in a .NET OO language like C#. I have mixed feeling on this. Languages like F# and Ruby allow for these sorts of extensions to be built within the language itself. On the other hand, having a strong separation between scripting the shell and extending the shell simplifies the scripting experience without sacrificing capability of building extensions.

Here’s a simple cmdlet I wrote called Get-SQLServer. SQL Server already comes with a robust object oriented administration library, but no support for PS (no surprise, since PS just shipped). I imagine future versions of SQL will have PS support, but to me this represents a great opportunity to get deep understanding of PS as well as focus on PS cmdlet design without having to do much of the grunt work.

using System;
using System.Management.Automation;
using Microsoft.SqlServer.Management.Smo.Wmi;
using Microsoft.SqlServer.Management.Smo;

[Cmdlet(VerbsCommon.Get, "SQLServer")]
public class GetSqlServerCommand : Cmdlet
  private string _Name;
  public string Name
    get { return _Name; }
    set { _Name = value; }

  private string _MachineName;
  public string MachineName
    get { return _MachineName; }
    set { _MachineName = value; }

  private SwitchParameter _Default;
  public SwitchParameter Default
    get { return _Default; }
    set { _Default = value; }

  protected override void ProcessRecord()
    string machine = string.IsNullOrEmpty(_MachineName) ? "." : _MachineName;

    if (string.IsNullOrEmpty(_Name) && !_Default.IsPresent)
      //write all server instances on specified machine
      //if _machineName is null or empty, the local machine is used
      ManagedComputer mc =  new ManagedComputer(machine);

      foreach (ServerInstance si in mc.ServerInstances)
        if (si.Name == "MSSQLSERVER")
          WriteObject(new Server(machine));
          WriteObject(new Server(machine + "\\" + si.Name));


    if (!string.IsNullOrEmpty(_Name) && _Default.IsPresent)
        new ErrorRecord(
          new ArgumentException("Default and Name parameters can't both be specified"),


    if (_Default.IsPresent)
      WriteObject(new Server(machine));
      WriteObject(new Server(machine + "-" + _Name));

As you can see, it’s fairly simple. The cmdlet takes three parameters – Name, MachineName and Default. MachineName represents the windows server machine the SQL server instance is running on. Name is a common PS parameter, and here is used to specify the SQL instance name you’re interested in. However, since the default instance of SQL on a given server doesn’t have a name, I had to add a Default flag. Since the cmdlet can return a collection of SMO Server objects, I needed a way to distinguish between “Give me the default instance on a machine” and “Give me all instances on a machine”. I couldn’t use a null or empty Name parameter to mean both. If neither Name or Default are specified, it means the user wants a collection. If both are specified, it’s an error. Otherwise, the cmdlet returns a single Server object – either the default or a named instance as specified.

Using the cmdlet is fairly straight forward. If you simply specify “Get-SQLServer”, it gives you a collection of all the SQL Server instances on the local machine. If you specify “Get-SQLServer -Default”, it gives you just the default SQL Server instance on the local machine. And if you specify “Get-SQLServer -Name sqlexpress”, it gives you just the SQL Express instance on the local machine. Using the -MachineName parameter allows you to connect to a remote SQL server box, but is otherwise the same.

Of course, this is a very simple cmdlet. It doesn’t even change the current state of the system. But now that we have a reference to a SQL Server instance, we can call methods on that instance. In the next post (whenever that is), I’ll build some cmdlets to let me create and drop databases on that instance.

How I Learned to Stop Worrying and Love WCF

Regular readers of DevHawk are likely aware of my obsession interest in SQL Service Broker (aka SSB). I’ve also been doing a lot of WCF work lately. While there are parts of WCF that I think rock, overall I’ve found WCF lacking due to it’s lack of support for long running services, which SSB excels at.

So it was with great interest that I read this recent article on Integrating WF and WCF. WF is expressly designed for long running systems, so I wanted to see how the article dealt with the WCF’s lack of support for such scenarios. Unfortunately, the article basically sidesteps the issue. While it has lots of great info about hosting WF inside a WCF service, the article uses duplex channels for communication between the service and its clients. As I have pointed out before, this approach is impractical because it requires that both the service and its consumer remain alive in memory until the WF end.

Remember this quote from Essential WF?

“It is wishful thinking to assume that the operating system process (or CLR application domain) in which the program begins execution will survive for the required duration.”

So basically this WCF/WF sample is wishful thinking. Fine for a demo, but given the severe lack of information out there on integrating these two technologies, I’m worried that many people will read this article as best practice guidance, which in my opinion would be a mistake.

But instead of firing up my blog (that is, like last time) to write a scathing post about how broken this sample is, I emailed Paul which led to a concall with Shy to discuss WCF’s lack of support for long running services. Imagine my surprise when Shy agreed with me completely, furthermore saying that support for long running services had been “out of scope” for v1 of WCF. I thought that the whole point of duplex channels was for long running services. But apparently I was wrong.

Shy said to think of the duplex channel in terms of sockets, rather than long running conversations. And just like that, WCF made a ton more sense to me. I had been directly comparing the SSB and WCF communication models, but that’s apples and oranges. It would be like comparing SSB to TCP.

If you think about it, vanilla HTTP works a lot more like UDP, even though it’s layered on top of TCP. Both UDP and HTTP support connectionless operations and neither UDP nor HTTP are reliable or provide message ordering. The comparison isn’t perfect: for example, UDP isn’t limited to a single response for an incoming request. But by and large, HTTP is a very UDP style protocol.

If HTTP is basically UDP, then WS-* is trying to be TCP. Frankly, I never understood the point of WS-ReliableMessaging. I always thought reliability == durability == SSB or MSMQ. But when you realize that HTTP lacks TCP-like reliability and ordering capabilities, suddenly this WS spec makes sense. In fact, Shy made this exact point almost a year ago. At the time, I didn’t get it because I didn’t understand the duplex channel as sockets analogy. Now, I see the value of adding these capabilities to HTTP.

What Shy said was clear and to the point but unfortunately completely missing in the official WCF documentation. For example, the docs on Duplex Services say this:

A duplex service contract is a message exchange pattern in which both endpoints can send messages to the other independently. A duplex service, therefore, can send messages back to the client endpoint, providing event-like behavior. Duplex communication occurs when a client connects to a service and provides the service with a channel on which the service can send messages back to the client.

The docs make no mention that the “event-like behavior” of duplex services only works within a session. And I’m not the only one who mistakenly believed that duplex services could be used for long running services (here’s an article in DDJ that makes the same mistake). Shy used the term “episodic” to describe services that span session boundaries. I’d like to see the docs updated to include that concept.

Taking the TCP/UDP analogy even further, I think it demonstrates how pointless the REST vs. SOAP debate is. As UDP is a thin layer on top of IP, REST is a thin layer on top of HTTP. But nobody argues much about UDP vs. TCP these days. I was in grade school when UDP and TCP were standardized, so maybe there were big TCP vs UDP flame wars at the time. But twenty five years later, it’s pretty clear that TCP vs UDP is not an either-or proposition. Some protocols are better built on UDP while others are better built on TCP. I’m guessing we’ll see a similar evolution with SOAP and REST.

Personally, I would expect that message exchanges between services will become more complex over time. Complex message exchanges would seem to favor stateful SOAP over stateless REST for the same reason complex network protocols favor connection-oriented TCP over connectionless UDP. But SOAP could never displace REST any more than TCP could ever displace UDP. Furthermore, as Larry O’Brien recently wrote “the onus is on the WS-* advocates to prove the need”. TCP standardization only lagged a year behind UDP standardization where WS-* has lagged at least six years behind REST. I wonder if UDP would be more prevalent today if it had gotten a six year head start on TCP.

Finally, this “SOAP as Sockets” flash of understanding has also helped me understand how SSB / WCF can evolve together in the future. Some folks have suggested an SSB transport for WCF and I’ve personally looked into such an approach. But given since SSB is at a higher level of abstraction than WCF, it makes much more sense to layer SSB on top of WCF instead of the other way around. Today, SSB uses two protocol layers: the top level Dialog Protocol, which is built on top of the lower-level Adjacent Broker Protocol (ABP), which in turn is built on TCP. I’d like to see a version of ABP that was built on top of WCF instead of directly on top of TCP. SSB’s Dialog Protocol would tie together the WCF duplex sessions into a long-running conversation the same way that it ties together TCP sessions today.

Eventually, I would love to see something that has the programming semantics of SSB and the interoperability of WCF. That would be like the the Reese’s Peanut Butter Cup of service messaging.

Morning Coffee 25

  • I’m surprised we haven’t seen a laptop with a flash memory hard drive yet. Given the significant power, heat and performance advantage of flash memory over hard drives, I would have expected the laptop companies to have high-end laptops with flash memory hard drives by now. I’m probably getting a new laptop in the next six months, but I’d hold out until the end of the year if it meant being able to get one with a flash memory hard drive.
  • I wrote last month that “The next new language I learn will be F#“. I was wrong. It’s PowerShell.
  • I’ve been listening to Scott gush about CodeRush for years now. His post yesterday about the new free version of Refactor! for ASP.NET finally kicked me into action. I installed the CodeRush trial and will be commence bugging my boss to buy it.
  • Looks like big news brewing in the online identity space.
  • Dale is talking about service heartbeats. I’m pretty stoked that Dale is now spending 100% of his work time (when he’s not blogging about sports, politics or video games anyway) with me building service oriented infrastructure for MSIT.