To Chain the Beast
Sure, there are plenty of similarities between this one and any given "end-all" mission, but this one had everything just right. In this mission you commanded 1 small army of good against 3 huge armies of evil. You were there not only to eliminate that evil but also to avenge a fallen comrade. The atmosphere brought a sensation similar to that of having a holy yet personal war in hell itself. All of this is immensely amplified by the mission just before it.
Gameplay wise this mission offers a unique 1v3 challenge that includes specific sub-challenges (invincible sunkens, reincarnating Torrasque, Infested Terrans). Also, unlike all of the other "end-all" missions, you start out on low ground, you don't have any choke points, and you have limited starting resources and very few expansions. This mission can kick your ass — hard.
Patriot's Blood
If you could call the SC:BW Terran Campaign an epic Shakespearean tragedy, then this mission would be the climactic fulcrum of that tragedy. There is just so much drama slammed into this one mission that it is gut wrenching beyond all reason. As dramatic as the Raynor and Kerrigan story is in New Gettysburg, I have to say that Patriot's Blood is a powerful contender for being the most soul-seizing. I mean, it just gets ya right there man.
This mission is extremely unique not only for being a installation type mission but also for being more unique than all of the other installation type missions in the whole game. All of the other missions allow the player to take his time and also have a single objective that, once reached, ends with simple success. This one, on the other hand, has two objectives, the second one of which is timed. There are also many opportunities to combat units and commandeer friendlies on the way through.
So yeah, those two are pretty much my favorite :D